int OpenDB(sqlite3** db)
{
int result= sqlite3_open(DBNAME,db);
#ifdefDEBUG
if(result!=0)
{
printf("OpenDataBase Fail ,Result Code:%d \n",result);
}
else
{
printf("OpenDataBase Sucess\n");
}
#endif
return result;
}
int CloseDB(sqlite3* db)
{
int result= sqlite3_close(db);
#ifdefDEBUG
if(result!=0)
{
printf("CloseDataBase fail,ResultCode:%d \n",result);
}
else
{
printf("CloseDataBase Success\n");
}
#endif
return result;
}
创建表:viewsourceprint?
//创建表操作
void CreateTable()
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
char *ErrMessage = NULL;
const char *Error = NULL;
char *str_CreateTable="Create Table Student ( studentID INTEGER,studentNameText,HeadImage Blob)";//创建表的SQL语句
int rc=0;
if(OpenDB(&db)!=SQLITE_OK)
{
return;
}
rc =sqlite3_exec(db,str_CreateTable,0,0,&ErrMessage);//执行
if(rc!=SQLITE_OK)
{
printf("操作数据库失败!ErrorMessge:%s\n",ErrMessage);
return;
}
sqlite3_finalize(stmt);
CloseDB(db);
}
viewsourceprint?
viewsourceprint?
Insert操作
viewsourceprint?
//Insert操作
void InsertOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
char *ErrMessage = NULL;
const char *Error = NULL;
char *str_Insert="Insert Into Student ( [studentID],[studentName],[HeadImage])values(1,"Alex",?)";//SQL语句
int rc=0;
if(OpenDB(&db)!=SQLITE_OK)
{
return;
}
rc =sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
if(rc!=SQLITE_OK)
{
printf("准备执行插入语句失败!ResultCode:%d \n ErrorMessage:%s",rc,Error);
return;
}
char _headImage[3] = {0x41,0x42,0x43};
sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);//二进制数据
rc = sqlite3_step(stmt);
if(rc!=SQLITE_DONE)
{
printf("插入失败!ResultCode:%dErrorMessage:%s",rc,Error);
return;
}
printf("插入成功!\n");
viewsourceprint?
CloseDB(db);
}
Update操作:
viewsourceprint?
//Insert操作
void InsertOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
char *ErrMessage = NULL;
const char *Error = NULL;
char *str_Insert="Insert Into Student ( [studentID],[studentName],[HeadImage])values(1,"Alex",?)";//SQL语句
int rc=0;
if(OpenDB(&db)!=SQLITE_OK)
{
return;
}
rc =sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
if(rc!=SQLITE_OK)
{
printf("准备执行插入语句失败!ResultCode:%d \n ErrorMessage:%s",rc,Error);
return;
}
char _headImage[3] = {0x41,0x42,0x43};
sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);//二进制数据
rc = sqlite3_step(stmt);
if(rc!=SQLITE_DONE)
{
printf("插入失败!ResultCode:%dErrorMessage:%s",rc,Error);
return;
}
printf("插入成功!\n");
viewsourceprint?
CloseDB(db);
}
Delete操作:
viewsourceprint?
void DeleteOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
char *ErrMessage = NULL;
const char *Error = NULL;
char *str_Update="Delete From Student where [studentID]=1"; //SQL语句
int rc=0;
if(OpenDB(&db)!=SQLITE_OK)
{
return;
}
rc =sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
if(rc!=SQLITE_OK)
{
printf("准备执行删除语句失败!ResultCode:%d \n ErrorMessage:%s",rc,Error);
return;
}
rc = sqlite3_step(stmt);
if(rc!=SQLITE_DONE)
{
printf("删除失败!ResultCode:%dErrorMessage:%s",rc,Error);
return;
}
printf("删除成功!\n");
viewsourceprint?
CloseDB(db);
}
viewsourceprint?
viewsourceprint?
Select操作:
viewsourceprint?
void SelectOperation(void)
{
sqlite3*db;
sqlite3_stmt *stmt = NULL;
char *ErrMessage = NULL;
const char *Error = NULL;
char *str_Update="select * From Student "; //SQL语句
int rc=0;
if(OpenDB(&db)!=SQLITE_OK)
{
return;
}
rc =sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
if(rc!=SQLITE_OK)
{
printf("准备执行查询语句失败!ResultCode:%d \n ErrorMessage:%s",rc,Error);
return;
}
while(1)
{
if(sqlite3_step(stmt)!=SQLITE_ROW)
{
sqlite3_finalize(stmt);
break;
}
intstudentid=sqlite3_column_int(stmt,0);
constchar *studentName = sqlite3_column_text(stmt,1);
constvoid *image = sqlite3_column_blob(stmt,2);
printf("studentIDis %d ,Name is %s,HeadImage is %s \n",studentid,studentName,image);
}
CloseDB(db);
}