VC++6.0 Sqlite3调用例子

1,为什么要使用Sqlite3?

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

2,为什么使用SQLite version 3.8.4.3 2014-04-03 16:53:12的版本

（1）dll体积小661KB，最新的都已经超过2M，这个只有几百k

（2）使用旧版本日常所需功能旧版本都已经很完备，无需升级最新版本

3，使用教程

3.1拷贝必备的sqlite.dll 和.h .lib文件到工程目录

3.2 代码中导入 sqlite3.lib文件，及导入sqlite3.h头文件

3.3 编写业务代码及sqlite3操作代码

sqliteDataCenter.h

#pragma once
#include <tchar.h>
#include "sqlite3.h"
#include <iostream>
#include <list>
#include <stdio.h>
using namespace std;
#pragma comment(lib,"sqlite3.lib")

#if !defined(CSQLITE3_CENTER_HEAD)
#define CSQLITE3_CENTER_HEAD
class CFileBean;

#define NULL_CALLBACK NULL

#define SQLSMALLINT (short)
#define SQLUSMALLINT (unsigned short)
#define SQLUINTEGER (unsigned long)
/*
struct tagTIMESTAMP_STRUCT {
	SQLSMALLINT year;
	SQLUSMALLINT month;
	SQLUSMALLINT day;
	SQLUSMALLINT hour;
	SQLUSMALLINT minute;
	SQLUSMALLINT second;
	SQLUINTEGER fraction;
} TIMESTAMP_STRUCT;
*/
/* 常量定义 */
class CFileBean
{
public:
	int	iId;
	CString strFilePath;
	CString strFileName;
	long lSize;
	BYTE byMd5[16];
	BYTE byRepeat;
	BYTE byMoved;
	int iRepeatCount;

	CTime ctCreate;
	CTime ctModify;
	int iRemark;
public:
	CRITICAL_SECTION cs;//可以理解为锁定一个资源
	CFileBean(int iId,CString strFilePath,CString  strFileName,long lSize,unsigned char *pbyMd5,BYTE byRepeat,BYTE byMoved,int iRepeatCount,CTime ctCreate,CTime ctModify,int iRemark)
	{
		memset(byMd5,0,16);
		this->iId = iId;
		this->strFilePath = strFilePath;
		this->strFileName = strFileName;
		this->lSize = lSize;
		if(pbyMd5 != NULL)
		{
			memcpy(this->byMd5,pbyMd5,16);
		}
		this->byRepeat = byRepeat;
		this->byMoved = byMoved;
		this->iRepeatCount = iRepeatCount;
		this->iRemark = iRemark;
		this->ctCreate = ctCreate;
		this->ctModify = ctModify;
		
		//TRACE("test %I64u  time=%s\r\n",createTime,ct.Format("%Y-%m-%d %H:%M:%S"));
		
	}
	void InitializeCs()
	{
		InitializeCriticalSection(&cs);//初始化临界区
	}
		
};

class CSqliteDataCenter
{
public:
	CSqliteDataCenter();
	~CSqliteDataCenter(void);
public:
	bool m_bOpenFlag;
	char *m_pErrMsg;
	CString MbcsToUtf8(const char *file);
	bool openDB(CString dbName);//打开数据库
	bool createUserTable();//创建记录表
	bool createBinTable();
	bool createConfigTable();
	bool creaetAllTable();

	/* t_user Table */
	bool insertUser(CFileBean *fb);


	bool queryTableCount(char *sql,int &iCount);
	int QueryTableExist(CString strTableName);

	list<CFileBean*> g_listUser;

	//list<CCofig*> g_listConfig;
private :
	void clearAll();

};
#endif

sqliteDataCenter.cpp

#include "StdAfx.h"
#include "sqliteDataCenter.h"


sqlite3 * pDbConnection = NULL;

char asSqlExistTable[]="SELECT COUNT(*) FROM sqlite_master where type='table' and name='%s';";
/* Create table */
char acSqlCreateTable_t_file_records[] = ("create table t_file_records (iid integer primary key autoincrement,strFilePath TEXT,strFileName TEXT,  lSize integer,byMd5 TEXT,byRepeat integer,byMoved integer,iRepeatCount integer,ctCreate bigint,ctModify bigint,addtime bigint default (datetime('now', 'localtime')),remarks integer);");



//insert into t_user (userId,password, permissions,nickname,createTime,remarks) values(8,'pass',2,'nickname2',(datetime('now','localtime')),2);

char acSqlInsertTable_t_file_record[] = ("insert into t_file_records (strFilePath,strFileName,lSize,byMd5,byRepeat,byMoved,iRepeatCount,ctCreate,ctModify,remarks) values('%s','%s',%d,'%d',%d,%d,%d,%I64d,%I64d,%d);");
char acSqlQueryTable_t_file_record[] = ("select iid,strFilePath,strFileName,lSize,byMd5,byRepeat,byMoved,iRepeatCount,ctCreate,ctModify,remarks from t_file_records;");
char acSqlQueryCount_t_file_record[] = ("select count(id) from t_file_records where byRepeat=0;");



const int constCharMsgMaxLen = 256;
const int constCharQuerySqlLen256 = 256;
const int constCharQuerySqlLen512 = 512;
const int constCharQuerySqlLen1K = 1024;
const int constCharQuerySqlLen2K = 2048;
CSqliteDataCenter::CSqliteDataCenter()
{

}

CSqliteDataCenter::~CSqliteDataCenter(void)
{
	sqlite3_close(pDbConnection);
}
CString CSqliteDataCenter::MbcsToUtf8(const char *file)
{
	CString str;
	WCHAR   *pwchar=0;
	CHAR    *pchar=0;
	int len=0;
	int codepage = AreFileApisANSI() ? CP_ACP : CP_OEMCP;
	len=MultiByteToWideChar(codepage, 0, file, -1, NULL,0);
	pwchar=new WCHAR[len];
	if(pwchar!=0)
	{
		len = MultiByteToWideChar(codepage, 0, file, -1, pwchar, len);
		if( len!=0 ) 
		{
			len = WideCharToMultiByte(CP_UTF8, 0, pwchar, -1, 0, 0, 0, 0);
			pchar=new CHAR[len];
			if(pchar!=0)
			{
				len = WideCharToMultiByte(CP_UTF8, 0, pwchar, -1, pchar, len,0, 0);
				if(len!=0)                 
				{
					str=pchar;                    
				}
				delete pchar;
			}
			delete pwchar; 
		}
	}
	return str;
}
//在调用 sqlite_open 前将文件路径，通过MbcsToUtf8函数将编码转换到utf8即可支持中文路径。 
bool CSqliteDataCenter::openDB(CString dbName)
{

	dbName = MbcsToUtf8(dbName.GetBuffer(dbName.GetLength()));
	m_bOpenFlag = false;
	m_pErrMsg = NULL;
	if(SQLITE_OK == sqlite3_open(dbName.GetBuffer(dbName.GetLength()), &pDbConnection) )
	{
		m_bOpenFlag = true;
		//creaetAllTable 来创建数据库表
		if(QueryTableExist("t_file_records")<1)
		{		
			creaetAllTable();
			//插入通用模板用户
		/*	CUserBean cUser(0, time(NULL),"通用用户","password",5,"通用用户",0,0);
			if(insertUser(&cUser))
			{
				TRACE("初始化通用模板用户\r\n");

			}else
			{
				TRACE("初始化通用模板用户----[失败]\r\n");
			}*/
		}else
		{
			
		}

	}
	dbName.ReleaseBuffer();
	return m_bOpenFlag;
}
int CSqliteDataCenter::QueryTableExist(CString strTableName)
{
	char acSql[constCharQuerySqlLen256] = {0};

	sprintf(acSql,asSqlExistTable,strTableName);
	int iCountTemp = 0;
	if(!queryTableCount(acSql,iCountTemp))
	{
		//查询失败了
		iCountTemp = -1;
	}
	return iCountTemp;

}
/* create table */
bool CSqliteDataCenter::creaetAllTable()
{
	bool bResult = true;;
	char acMsg[constCharMsgMaxLen] = {0};
	char *pc = NULL;//&acMsg[0];//(char*) malloc(256);
	//memset(pc,0,256);
	if(!(SQLITE_OK == sqlite3_exec(pDbConnection, acSqlCreateTable_t_file_records, NULL_CALLBACK, NULL, &m_pErrMsg)))
	{
		TRACE("CSqliteDataCenter()->%s createUserTable()\r\n",m_pErrMsg);
		sqlite3_free(m_pErrMsg);
		m_pErrMsg = NULL;
		bResult =  false;
	}else
	{
		TRACE("创建表:t_user\r\n");
	}

	return bResult;
}


bool CSqliteDataCenter::insertUser(CFileBean *fb)
{
	//TRACE("insertRecord()pad_dev_id=%d,dev_type=%d,result=%d,i64Time=%I64d,source=%d,voltage=%d,current=%d,temp=%d,remark=%d\r\n",rc->pad_dev_id,
																															

	char szSqlInsert[constCharQuerySqlLen256] = {0};
	sprintf(szSqlInsert,acSqlInsertTable_t_file_record,
	 fb->strFilePath,
	 fb->strFileName,
	 fb->lSize,
	 fb->byMd5[0],
	 fb->byRepeat,
	 fb->byMoved,
	 fb->iRepeatCount,
	 fb->ctCreate,
	 fb->ctModify,
	 fb->iRemark);
	TRACE("CSqliteDataCenter::insertUser()->%s %s\r\n",szSqlInsert,fb->strFileName);
	if( SQLITE_OK !=  sqlite3_exec(pDbConnection , szSqlInsert, NULL_CALLBACK, NULL, &m_pErrMsg) )
	{
		TRACE("CSqliteDataCenter::insertUser()->%s %s\r\n",m_pErrMsg,fb->strFileName);
		sqlite3_free(m_pErrMsg);
		m_pErrMsg = NULL;
		return false;
	}
	return true;
}



bool CSqliteDataCenter::queryTableCount(char *sql,int &iCount)
{
	iCount = 0;
	//	执行SELECT语句查询数据
	sqlite3_stmt* stmt3 = NULL;
	//acSqlQueryTable_t_Config
	if (sqlite3_prepare_v2(pDbConnection,sql,strlen(sql),&stmt3,NULL) != SQLITE_OK) 
	{
		if (stmt3)
		{	
			sqlite3_finalize(stmt3);
		}
		return false;
	}
	do 
	{
		int r = sqlite3_step(stmt3);
		if (r == SQLITE_ROW) 
		{
			iCount = sqlite3_column_int(stmt3,0);	
		}
		else if (r == SQLITE_DONE) 
		{
			TRACE("Select queryCount Finished\n");
			break;
		} 
		else 
		{
			TRACE("Failed to queryCount SELECT.\n");
			sqlite3_finalize(stmt3);
			return false;
		}

	} while (true);

	sqlite3_finalize(stmt3);
	return true;
}

4 调用，#include "sqliteDataCenter.h" 后在::OnInitDialog()中调用

	
	// TODO: Add extra initialization here
	CSqliteDataCenter sqlitedc;
	sqlitedc.openDB("C:\\Users\\stw\\sqlite3test\\Debug\\filetest.db");
	BYTE byMd5[16]={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16};
	//	CFileBean(int iId,CString strFilePath,CString  strFileName,long lSize,unsigned char *pbyMd5,BYTE byRepeat,BYTE byMoved,int iRepeatCount,CTime ctCreate,CTime ctModify,int iRemark)
	CFileBean fb(0,"C:\\Users\\stw\\sqlite3test\\Debug\\","filetest.db",111,byMd5,0,1,2,3,4,5);
	sqlitedc.insertUser(&fb);

5，工具使用说明，下载的sqlite3.exe是可执行文件的工具，可以用它来操作db数据库或创建数据库

完整代码上传到资源。