SQLite入門操作（一）

//++其他的標頭檔案

#include "sqlite3.h"
#pragma comment(lib,"sqlite3.lib")

int GetItemCount(sqlite3 *db,LPCTSTR str)//select count(*) from x ;
{
    sqlite3_stmt *pstmt=NULL;
    sqlite3_prepare16_v2(db,str,-1,&pstmt,NULL);
    sqlite3_step(pstmt);
    int nCount = sqlite3_column_int(pstmt,0);
    sqlite3_finalize(pstmt);

    return nCount;
}

int main()
{

    wcout.imbue(std::locale("chs"));
#define _SIZEBUF 2000*1024
#define _QUERYITEMS L"select count(*) from stt"

    sqlite3 *db = NULL;
    TCHAR *errMsg = NULL;
    sqlite3_stmt *pstmt=NULL;
    TCHAR *psql = NULL;

int nRet = -1,nRows=-1;
nRet = sqlite3_open16(L"F:\\my.db",&db);
if (nRet)
{
    wcout<<L"無法開啟sqlite資料庫:"<<sqlite3_errmsg16(db)<<endl;
    sqlite3_close(db);
    cin.get();

    return 1;
}
else
{
    wcout<<L"成功開啟my.db"<<endl;

}

sqlite3_exec(db,"create table if not exists stt(id nchar(20) primary key,name nchar(20));insert into stt values('1','zhang');insert into stt values('2','li');",NULL,NULL,NULL);

cout<<"表裡面的專案數為:"<<GetItemCount(db,_QUERYITEMS)<<endl;

//刪除or插入

//psql=L"delete from stt where id='4';";
//sqlite3_prepare16_v2(db,psql,-1,&pstmt,NULL);
//sqlite3_step(pstmt);
//sqlite3_finalize(pstmt);
//cout<<"表裡面的專案數為:"<<GetItemCount(db,_QUERYITEMS)<<endl;

//psql=L"insert or replace into stt where id='4';";
//sqlite3_prepare16_v2(db,psql,-1,&pstmt,NULL);
//sqlite3_step(pstmt);
//sqlite3_finalize(pstmt);
//cout<<"表裡面的專案數為:"<<GetItemCount(db,_QUERYITEMS)<<endl;

//更新

psql = L"update st set name='zhang123' where id=3";
sqlite3_prepare16(db,psql,_tcslen(psql)*sizeof(TCHAR),&pstmt,NULL);
nRet = sqlite3_step(pstmt);
switch (nRet)
{
case SQLITE_DONE:
    cout<<"SQLITE_DONE"<<endl;break;//注意，未找到或者已經遍歷到結果集末尾的話，返回SQLITE_DONE
case SQLITE_ROW:
    cout<<"SQLITE_ROW"<<endl;break;//若找到（甚至一個），則返回sqlite_row
case SQLITE_ERROR:
    cout<<"SQLITE_ERROR"<<endl;break;
default:
    cout<<"Unknown"<<endl;break;
}
//sqlite3_finalize(pstmt);
int nAffected = sqlite3_changes(db);
cout<<"受影響的行數為："<<nAffected<<endl;
sqlite3_reset(pstmt);

sqlite3_close(db);
cin.get();
return 1;

}