Sqlite的操作(增加,删除,查询,修改)

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:%d ErrorMessage:%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:%d ErrorMessage:%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:%d ErrorMessage:%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);

}