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

int OpenDB(sqlite3** db)

{

  int result = sqlite3_open(DBNAME,db);

  #ifdef DEBUG

  if(result!=0)

  {

    printf("Open DataBase Fail ,Result Code:%d ",result);

  }

  else

  {

    printf("Open DataBase Sucess ");

  }

  #endif

  return result;

}

int CloseDB(sqlite3* db)

{

  int result =  sqlite3_close(db);

  #ifdef DEBUG

  if(result!=0)

  {

    printf("Close DataBase fail,ResultCode:%d ",result);

  }

  else

  {

    printf("Close DataBase Success ");

  }

  #endif

  return result;

}

创建表:

view sourceprint?

//创建表操作

void CreateTable()

{

  sqlite3 *db;

  sqlite3_stmt *stmt = NULL;

  char * ErrMessage = NULL;

  const char * Error = NULL;

  char * str_CreateTable="Create Table Student ( studentID INTEGER,studentName Text,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 ",ErrMessage);

    return;

  }

  sqlite3_finalize(stmt);

  CloseDB(db);

}

view sourceprint?

view sourceprint?

Insert操作

view sourceprint?

//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("准备执行插入语句失败！Result Code:%d 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("插入失败！Result Code:%d  ErrorMessage:%s",rc,Error);

    return;

  }

  printf("插入成功! ");

view sourceprint?

CloseDB(db);

}

 

Update操作:

view sourceprint?

//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("准备执行插入语句失败！Result Code:%d 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("插入失败！Result Code:%d  ErrorMessage:%s",rc,Error);

    return;

  }

  printf("插入成功! ");

view sourceprint?

CloseDB(db);

}

Delete操作:

view sourceprint?

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("准备执行删除语句失败！Result Code:%d ErrorMessage:%s",rc,Error);

    return;

  }

  rc = sqlite3_step(stmt);

  if(rc!=SQLITE_DONE)

  {

    printf("删除失败！Result Code:%d  ErrorMessage:%s",rc,Error);

    return;

  }

  printf("删除成功! ");

view sourceprint?

CloseDB(db);

}

view sourceprint?

view sourceprint?

Select操作:

view sourceprint?

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("准备执行查询语句失败！Result Code:%d ErrorMessage:%s",rc,Error);

    return;

  }

  while(1)

  {

    if(sqlite3_step(stmt)!=SQLITE_ROW)

    {

      sqlite3_finalize(stmt);

      break;

    }

   int studentid =sqlite3_column_int(stmt,0);

   const char * studentName = sqlite3_column_text(stmt,1);

   const void * image = sqlite3_column_blob(stmt,2);

   printf("studentID is %d ,Name is %s,HeadImage is %s ",studentid,studentName,image);

  }

  CloseDB(db);

}