#pragma once
#include <Windows.h>
#include <list>
#include <string>
#include "sqlite3.h"
typedef int (*QueryCallback) (void *para, int n_column, char **column_value, char **column_name);
typedef enum _SQLITE_DATATYPE
{
SQLITE_DATATYPE_INTEGER = SQLITE_INTEGER,
SQLITE_DATATYPE_FLOAT = SQLITE_FLOAT,
SQLITE_DATATYPE_TEXT = SQLITE_TEXT,
SQLITE_DATATYPE_BLOB = SQLITE_BLOB,
SQLITE_DATATYPE_NULL = SQLITE_NULL,
}SQLITE_DATATYPE;
class SQLite;
class SQLiteDataReader
{
public:
SQLiteDataReader(sqlite3_stmt *pStmt);
~SQLiteDataReader();
public:
// 读取一行数据
BOOL Read();
// 关闭Reader,读取结束后调用
void Close();
// 总的列数
int ColumnCount(void);
// 获取某列的名称
LPCTSTR GetName(int nCol);
// 获取某列的数据类型
SQLITE_DATATYPE GetDataType(int nCol);
// 获取某列的值(字符串)
LPCTSTR GetStringValue(int nCol);
// 获取某列的值(整形)
int GetIntValue(int nCol);
// 获取某列的值(长整形)
long GetInt64Value(int nCol);
// 获取某列的值(浮点形)
double GetFloatValue(int nCol);
// 获取某列的值(二进制数据)
const BYTE* GetBlobValue(int nCol, int &nLen);
private:
sqlite3_stmt *m_pStmt;
};
class SQLiteCommand
{
public:
SQLiteCommand(SQLite* pSqlite);
SQLiteCommand(SQLite* pSqlite, LPCTSTR lpSql);
~SQLiteCommand();
public:
// 设置命令
BOOL SetCommandText(LPCTSTR lpSql);
// 绑定参数(index为要绑定参数的序号,从1开始)
BOOL BindParam(int index, LPCTSTR szValue);
BOOL BindParam(int index, const int nValue);
BOOL BindParam(int index, const double dValue);
BOOL BindParam(int index, const unsigned char* blobValue, int nLen);
// 执行命令
BOOL Excute();
// 清除命令(命令不再使用时需调用该接口清除)
void Clear();
private:
SQLite *m_pSqlite;
sqlite3_stmt *m_pStmt;
};
class SQLite
{
public:
SQLite(void);
~SQLite(void);
public:
// 打开数据库
BOOL Open(LPCTSTR lpDbFlie);
// 关闭数据库
void Close();
// 执行非查询操作(更新或删除)
BOOL ExcuteNonQuery(LPCTSTR lpSql);
BOOL ExcuteNonQuery(SQLiteCommand* pCmd);
// 查询
SQLiteDataReader ExcuteQuery(LPCTSTR lpSql);
// 查询(回调方式)
BOOL ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack);
// 开始事务
BOOL BeginTransaction();
// 提交事务
BOOL CommitTransaction();
// 回滚事务
BOOL RollbackTransaction();
// 获取上一条错误信息
LPCTSTR GetLastErrorMsg();
//检查表是否存在
BOOL CheckTableExist(LPCTSTR lpTableName);
public:
friend class SQLiteCommand;
private:
sqlite3* m_db;
};
class SQLiteImp
{
public:
SQLiteImp();
~SQLiteImp();
BOOL Init(LPCTSTR lpDbName);
BOOL UnInit();
BOOL CreateTable();
BOOL Insert(LPCTSTR lpID, LPCTSTR lpContent);
BOOL UpData(LPCTSTR lpID, LPCTSTR lpContent);
INT Query(LPCTSTR lpID, std::list<std::wstring>& contentList);
INT Count(LPCTSTR lpID);
BOOL UpDataGroupName(LPCTSTR lpGroupName, LPCTSTR lpNewGroupName);
private:
SQLite* m_pSQLite;
};
#include "SQliteImp.h"
#include <tchar.h>
const char* WcharToUtf8(const wchar_t *pwStr)
{
if (pwStr == NULL)
{
return NULL;
}
int len = WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, NULL, 0, NULL, NULL);
if (len <= 0)
{
return NULL;
}
char *pStr = new char[len];
WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, pStr, len, NULL, NULL);
return pStr;
}
const wchar_t* Utf8ToWchar(const char *pStr)
{
if (pStr == NULL)
{
return NULL;
}
int len = MultiByteToWideChar(CP_UTF8, 0, pStr, -1, NULL, 0);
if (len <= 0)
{
return NULL;
}
wchar_t *pwStr = new wchar_t[len];
MultiByteToWideChar(CP_UTF8, 0, pStr, -1, pwStr, len);
return pwStr;
}
SQLite::SQLite(void) :
m_db(NULL)
{
}
SQLite::~SQLite(void)
{
Close();
}
BOOL SQLite::Open(LPCTSTR lpDbFlie)
{
if (lpDbFlie == NULL)
{
return FALSE;
}
#ifdef UNICODE
if (sqlite3_open16(lpDbFlie, &m_db) != SQLITE_OK)
#else
if (sqlite3_open(lpDbFlie, &m_db) != SQLITE_OK)
#endif
{
return FALSE;
}
return TRUE;
}
void SQLite::Close()
{
if (m_db)
{
sqlite3_close(m_db);
m_db = NULL;
}
}
BOOL SQLite::ExcuteNonQuery(LPCTSTR lpSql)
{
if (lpSql == NULL)
{
return FALSE;
}
sqlite3_stmt* stmt;
#ifdef UNICODE
if (sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
#else
if (sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
#endif
{
return FALSE;
}
sqlite3_step(stmt);
return (sqlite3_finalize(stmt) == SQLITE_OK) ? TRUE : FALSE;
}
BOOL SQLite::ExcuteNonQuery(SQLiteCommand* pCmd)
{
if (pCmd == NULL)
{
return FALSE;
}
return pCmd->Excute();
}
// 查询(回调方式)
BOOL SQLite::ExcuteQuery(LPCTSTR lpSql, QueryCallback pCallBack)
{
if (lpSql == NULL || pCallBack == NULL)
{
return FALSE;
}
char *errmsg = NULL;
#ifdef UNICODE
const char *szSql = WcharToUtf8(lpSql);
if (sqlite3_exec(m_db, szSql, pCallBack, NULL, &errmsg) != SQLITE_OK)
{
delete[] szSql;
return FALSE;
}
delete[] szSql;
#else
if (sqlite3_exec(m_db, lpSql, pCallBack, NULL, &errmsg) != SQLITE_OK)
{
return FALSE;
}
#endif
return TRUE;
}
// 查询
SQLiteDataReader SQLite::ExcuteQuery(LPCTSTR lpSql)
{
if (lpSql == NULL)
{
return FALSE;
}
sqlite3_stmt* stmt;
#ifdef UNICODE
if (sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
#else
if (sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
#endif
{
return FALSE;
}
return SQLiteDataReader(stmt);
}
// 开始事务
BOOL SQLite::BeginTransaction()
{
char * errmsg = NULL;
if (sqlite3_exec(m_db, "BEGIN TRANSACTION;", NULL, NULL, &errmsg) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
// 提交事务
BOOL SQLite::CommitTransaction()
{
char * errmsg = NULL;
if (sqlite3_exec(m_db, "COMMIT TRANSACTION;;", NULL, NULL, &errmsg) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
// 回滚事务
BOOL SQLite::RollbackTransaction()
{
char * errmsg = NULL;
if (sqlite3_exec(m_db, "ROLLBACK TRANSACTION;", NULL, NULL, &errmsg) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
// 获取上一条错误信息
LPCTSTR SQLite::GetLastErrorMsg()
{
#ifdef UNICODE
return (LPCTSTR)sqlite3_errmsg16(m_db);
#else
return sqlite3_errmsg(m_db);
#endif
}
BOOL SQLite::CheckTableExist(LPCTSTR lpTableName)
{
char* sErrMsg = NULL;
char sTemp[] = "select * from PCList;";
if (sqlite3_exec(m_db, sTemp, NULL, NULL, &sErrMsg) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
SQLiteDataReader::SQLiteDataReader(sqlite3_stmt *pStmt) :
m_pStmt(pStmt)
{
}
SQLiteDataReader::~SQLiteDataReader()
{
Close();
}
// 读取一行数据
BOOL SQLiteDataReader::Read()
{
if (m_pStmt == NULL)
{
return FALSE;
}
if (sqlite3_step(m_pStmt) != SQLITE_ROW)
{
return FALSE;
}
return TRUE;
}
// 关闭Reader,读取结束后调用
void SQLiteDataReader::Close()
{
if (m_pStmt)
{
sqlite3_finalize(m_pStmt);
m_pStmt = NULL;
}
}
// 总的列数
int SQLiteDataReader::ColumnCount(void)
{
return sqlite3_column_count(m_pStmt);
}
// 获取某列的名称
LPCTSTR SQLiteDataReader::GetName(int nCol)
{
#ifdef UNICODE
return (LPCTSTR)sqlite3_column_name16(m_pStmt, nCol);
#else
return (LPCTSTR)sqlite3_column_name(m_pStmt, nCol);
#endif
}
// 获取某列的数据类型
SQLITE_DATATYPE SQLiteDataReader::GetDataType(int nCol)
{
return (SQLITE_DATATYPE)sqlite3_column_type(m_pStmt, nCol);
}
// 获取某列的值(字符串)
LPCTSTR SQLiteDataReader::GetStringValue(int nCol)
{
#ifdef UNICODE
return (LPCTSTR)sqlite3_column_text16(m_pStmt, nCol);
#else
return (LPCTSTR)sqlite3_column_text(m_pStmt, nCol);
#endif
}
// 获取某列的值(整形)
int SQLiteDataReader::GetIntValue(int nCol)
{
return sqlite3_column_int(m_pStmt, nCol);
}
// 获取某列的值(长整形)
long SQLiteDataReader::GetInt64Value(int nCol)
{
return (long)sqlite3_column_int64(m_pStmt, nCol);
}
// 获取某列的值(浮点形)
double SQLiteDataReader::GetFloatValue(int nCol)
{
return sqlite3_column_double(m_pStmt, nCol);
}
// 获取某列的值(二进制数据)
const BYTE* SQLiteDataReader::GetBlobValue(int nCol, int &nLen)
{
nLen = sqlite3_column_bytes(m_pStmt, nCol);
return (const BYTE*)sqlite3_column_blob(m_pStmt, nCol);
}
SQLiteCommand::SQLiteCommand(SQLite* pSqlite) :
m_pSqlite(pSqlite),
m_pStmt(NULL)
{
}
SQLiteCommand::SQLiteCommand(SQLite* pSqlite, LPCTSTR lpSql) :
m_pSqlite(pSqlite),
m_pStmt(NULL)
{
SetCommandText(lpSql);
}
SQLiteCommand::~SQLiteCommand()
{
}
BOOL SQLiteCommand::SetCommandText(LPCTSTR lpSql)
{
#ifdef UNICODE
if (sqlite3_prepare16_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)
#else
if (sqlite3_prepare_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)
#endif
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteCommand::BindParam(int index, LPCTSTR szValue)
{
#ifdef UNICODE
if (sqlite3_bind_text16(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)
#else
if (sqlite3_bind_text(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)
#endif
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteCommand::BindParam(int index, const int nValue)
{
if (sqlite3_bind_int(m_pStmt, index, nValue) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteCommand::BindParam(int index, const double dValue)
{
if (sqlite3_bind_double(m_pStmt, index, dValue) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteCommand::BindParam(int index, const unsigned char* blobBuf, int nLen)
{
if (sqlite3_bind_blob(m_pStmt, index, blobBuf, nLen, NULL) != SQLITE_OK)
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteCommand::Excute()
{
sqlite3_step(m_pStmt);
return (sqlite3_reset(m_pStmt) == SQLITE_OK) ? TRUE : FALSE;
}
void SQLiteCommand::Clear()
{
if (m_pStmt)
{
sqlite3_finalize(m_pStmt);
}
}
SQLiteImp::SQLiteImp():
m_pSQLite(NULL)
{
}
SQLiteImp::~SQLiteImp()
{
}
BOOL SQLiteImp::Init(LPCTSTR lpDbName)
{
if (m_pSQLite == NULL)
{
m_pSQLite = new SQLite;
}
BOOL bRet = FALSE;
if (m_pSQLite != NULL)
{
bRet = m_pSQLite->Open(lpDbName);
}
return bRet;
}
BOOL SQLiteImp::UnInit()
{
if (m_pSQLite != NULL)
{
m_pSQLite->Close();
delete m_pSQLite;
m_pSQLite = NULL;
}
return TRUE;
}
BOOL SQLiteImp::CreateTable()
{
if (!m_pSQLite)
{
return FALSE;
}
BOOL bHas = m_pSQLite->CheckTableExist(L"PCList");
if (bHas)
{
return TRUE;
}
TCHAR sql[1024] = { 0 };
_stprintf_s(sql, 1024, _T("%s"),
_T("CREATE TABLE [PCList] (")
_T("[pcid] TEXT,")
_T("[content] TEXT);")
);
if (!m_pSQLite->ExcuteNonQuery(sql))
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteImp::Insert(LPCTSTR lpID, LPCTSTR lpContent)
{
if (!m_pSQLite)
{
return FALSE;
}
TCHAR sql[1024] = { 0 };
_stprintf_s(sql, 1024, _T("insert into PCList(pcid, content) values('%s', '%s')"), lpID, lpContent);
if (!m_pSQLite->ExcuteNonQuery(sql))
{
return FALSE;
}
return TRUE;
}
BOOL SQLiteImp::UpData(LPCTSTR lpID, LPCTSTR lpContent)
{
if (!m_pSQLite)
{
return FALSE;
}
TCHAR sql[1024] = { 0 };
_stprintf_s(sql, 1024, _T("update PCList set content = '%s' where pcid = '%s'"), lpContent, lpID);
if (!m_pSQLite->ExcuteNonQuery(sql))
{
return FALSE;
}
return TRUE;
}
INT SQLiteImp::Query(LPCTSTR lpID, std::list<std::wstring>& contentList)
{
if (!m_pSQLite)
{
return FALSE;
}
TCHAR sql[1024] = { 0 };
if (wcslen(lpID) > 0)
{
_stprintf_s(sql, 1024, _T("select * from PCList where pcid = '%s'"), lpID);
}
else
{
_stprintf_s(sql, 1024, _T("select * from PCList"));
}
SQLiteDataReader Reader = m_pSQLite->ExcuteQuery(sql);
int index = 0;
int len = 0;
while (Reader.Read())
{
index++;
std::wstring strContent = Reader.GetStringValue(1);
contentList.push_back(strContent);
}
return index;
}
INT SQLiteImp::Count(LPCTSTR lpID)
{
if (!m_pSQLite)
{
return FALSE;
}
TCHAR sql[1024] = { 0 };
_stprintf_s(sql, 1024, _T("select count(*) from PCList"));
SQLiteDataReader Reader = m_pSQLite->ExcuteQuery(sql);
while (Reader.Read())
{
std::wstring strName = Reader.GetName(0);
int ncount = Reader.GetIntValue(0);
}
}
BOOL SQLiteImp::UpDataGroupName(LPCTSTR lpGroupName, LPCTSTR lpNewGroupName)
{
if (!m_pSQLite)
{
return FALSE;
}
TCHAR sql[1024] = { 0 };
_stprintf_s(sql, 1024, _T("update PCList set groupname = '%s' where groupname = '%s'"), lpNewGroupName, lpGroupName);
if (!m_pSQLite->ExcuteNonQuery(sql))
{
return FALSE;
}
return TRUE;
}