学步园

SQLite 是一个免费的文件型数据库。

1、 下载地址。

http://www.sqlite.org/download.html。

2、 编译。

C++：

建空的动态库或静态库，加入sqlite3.c，sqlite3.h和sqlite3ext.h，编译即可。

需注意的是动态库要把sqlite3.c 中的

#ifndef SQLITE_API

# define SQLITE_API

#endif

改为

#ifndef SQLITE_API

# define SQLITE_API                  __declspec(dllexport)

#endif

C#：

还没编译http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
中的源码，只是用了它的二进制安装包。

3、 建表。

包含字段 ID（图像ID，自增），FILENAME（图像名），DATA（图像数据）。

C++：

BOOL Ascii2Utf8(const char* pcszAscii, char* pszUtf8, int* pnUtf8Size)<br /> {<br /> if (!pcszAscii || !pszUtf8 || !pnUtf8Size)<br /> return FALSE;</p> <p> int nWCharCount = MultiByteToWideChar(CP_ACP, 0, pcszAscii, -1, NULL, 0);<br /> wchar_t* pwszStr = new wchar_t[nWCharCount];<br /> MultiByteToWideChar(CP_ACP, 0, pcszAscii, -1, pwszStr, nWCharCount);</p> <p> int nUtf8Count = WideCharToMultiByte(CP_UTF8, 0, pwszStr, nWCharCount, NULL, 0, NULL, NULL);<br /> if (nUtf8Count > *pnUtf8Size)<br /> {<br /> *pnUtf8Size = nUtf8Count;<br /> return FALSE;<br /> }</p> <p> WideCharToMultiByte(CP_UTF8, 0, pwszStr, nWCharCount, pszUtf8, nUtf8Count, NULL, NULL);<br /> delete[] pwszStr;<br /> *pnUtf8Size = nUtf8Count;<br /> return TRUE;<br /> }<br /> BOOL Utf82Ascii(const char* pcszUtf8, char* pszAscii, int* pnAsciiSize)<br /> {<br /> if (!pcszUtf8 || !pszAscii || !pnAsciiSize)<br /> return FALSE;</p> <p> int nWCharCount = MultiByteToWideChar(CP_UTF8, 0, pcszUtf8, -1, NULL, 0);<br /> wchar_t* pwszStr = new wchar_t[nWCharCount];<br /> MultiByteToWideChar(CP_UTF8, 0, pcszUtf8, -1, pwszStr, nWCharCount);</p> <p> int nAsciiCount = WideCharToMultiByte(CP_ACP, 0, pwszStr, nWCharCount, NULL, 0, NULL, NULL);<br /> if (nAsciiCount > *pnAsciiSize)<br /> {<br /> *pnAsciiSize = nAsciiCount;<br /> return FALSE;<br /> }</p> <p> WideCharToMultiByte(CP_ACP, 0, pwszStr, nWCharCount, pszAscii, nAsciiCount, NULL, NULL);<br /> delete[] pwszStr;<br /> *pnAsciiSize = nAsciiCount;<br /> return TRUE;<br /> }<br /> BOOL OpenDatabase(const char* pcszDBFileName, int nFlag, sqlite3** ppDb)<br /> {<br /> char szUtf8DBFileName[MAX_PATH];<br /> int nSize = MAX_PATH;<br /> if (!Ascii2Utf8(pcszDBFileName, szUtf8DBFileName, &nSize))<br /> return FALSE;</p> <p> sqlite3* pDb;<br /> int nRtn = sqlite3_open_v2(szUtf8DBFileName, &pDb, nFlag, NULL);<br /> if (nRtn != SQLITE_OK)<br /> return FALSE;<br /> *ppDb = pDb;<br /> return TRUE;<br /> }<br /> void CDemoDlg::OnCreatetable()<br /> {<br /> // TODO: Add your control notification handler code here<br /> const char* pcszDBFileName = "d://测试中文//demo.db";<br /> sqlite3* pDb;<br /> if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, &pDb))<br /> return;</p> <p> const char* pcszSQL = "create table images(ID integer primary key, FILENAME varchar(50), DATA blob);";<br /> char* pszErrorInfo = NULL;<br /> int nRtn = sqlite3_exec(pDb, pcszSQL, NULL, NULL, &pszErrorInfo);<br /> if (nRtn != SQLITE_OK)<br /> {<br /> TRACE("sqlite3_exec error: %s/n", pszErrorInfo);<br /> sqlite3_free(pszErrorInfo);<br /> }</p> <p> sqlite3_close(pDb);<br /> return;<br /> }<br />  

C#：

private void CreateTable_Click(object sender, EventArgs e)<br /> {<br /> string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3";</p> <p> SQLiteConnection conn = new SQLiteConnection(strDataSource);</p> <p> string strSQL = "create table images(ID integer primary key, FILENAME varchar(50), DATA blob);";<br /> SQLiteCommand cmd = conn.CreateCommand();<br /> cmd.CommandText = strSQL;<br /> try<br /> {<br /> conn.Open();<br /> int nRtn = cmd.ExecuteNonQuery();<br /> MessageBox.Show("建表成功。");<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.ToString());<br /> }<br /> finally<br /> {<br /> cmd.Dispose();<br /> conn.Close();<br /> }<br /> return;<br /> } 

4、 插入数据。

C++：

BYTE* GetFileData(const char* pcszFileName, int* pnFileLen)<br /> {<br /> FILE* fp = fopen(pcszFileName, "rb");<br /> if (!fp)<br /> return NULL;</p> <p> fseek(fp, 0, SEEK_END);<br /> int nFileLen = ftell(fp);<br /> fseek(fp, 0, SEEK_SET);</p> <p> BYTE* pbyData = new BYTE[nFileLen];<br /> if (!pbyData)<br /> {<br /> fclose(fp);<br /> return NULL;<br /> }<br /> fread(pbyData, nFileLen, 1, fp);<br /> fclose(fp);<br /> if (pnFileLen)<br /> *pnFileLen = nFileLen;</p> <p> return pbyData;<br /> }<br /> void FreeFileData(void* pData)<br /> {<br /> delete[] pData;<br /> }<br /> void CDemoDlg::OnInsert()<br /> {<br /> // TODO: Add your control notification handler code here<br /> const char* pcszDBFileName = "d://测试中文//demo.db";<br /> sqlite3* pDb;<br /> if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READWRITE, &pDb))<br /> return;</p> <p> const char* pcszFileName = "d://测试图片//图片1.jpg";<br /> int nFileLen;<br /> BYTE* pbyData = GetFileData(pcszFileName, &nFileLen);<br /> if (!pbyData)<br /> return;</p> <p> char szSQL[100];<br /> sprintf(szSQL, "insert into images values(NULL, '%s', ?);", pcszFileName);<br /> char szUtf8SQL[200];<br /> int nSize = 200;<br /> if (!Ascii2Utf8(szSQL, szUtf8SQL, &nSize))<br /> {<br /> FreeFileData(pbyData);<br /> return;<br /> }</p> <p> sqlite3_stmt* pStmt = NULL;<br /> int nRtn = sqlite3_prepare_v2(pDb, szUtf8SQL, -1, &pStmt, NULL);<br /> if (nRtn == SQLITE_OK)<br /> {<br /> nRtn = sqlite3_bind_blob(pStmt, 1, pbyData, nFileLen, NULL);<br /> if (nRtn == SQLITE_OK)<br /> {<br /> nRtn = sqlite3_step(pStmt);<br /> if (nRtn == SQLITE_DONE)<br /> {<br /> TRACE("insert succ!/n");<br /> }<br /> }<br /> sqlite3_finalize(pStmt);<br /> }</p> <p> FreeFileData(pbyData);<br /> sqlite3_close(pDb);<br /> return;<br /> }<br />  

C#：

private void Insert_Click(object sender, EventArgs e)<br /> {<br /> string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3";</p> <p> SQLiteConnection conn = new SQLiteConnection(strDataSource);<br /> string strFileName = "d://测试图片//1//ISV000005_liuyang_001.JPG";<br /> FileStream fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read);<br /> int nFileLen = (int)fs.Length;<br /> byte[] pbyData = new byte[nFileLen];<br /> fs.Read(pbyData, 0, nFileLen);<br /> fs.Close();<br /> string strSQL = "insert into images values(null, :filename, :data);";<br /> SQLiteCommand cmd = conn.CreateCommand();<br /> cmd.CommandText = strSQL;</p> <p> SQLiteParameter param = new SQLiteParameter("filename", DbType.String);<br /> param.Value = strFileName;<br /> cmd.Parameters.Add(param);<br /> SQLiteParameter param2 = new SQLiteParameter("data", DbType.Binary);<br /> param2.Value = pbyData;<br /> cmd.Parameters.Add(param2);<br /> try{<br /> conn.Open();<br /> int nRowCount = cmd.ExecuteNonQuery();<br /> MessageBox.Show("已插入" + nRowCount + "行。");<br /> }<br /> catch(Exception ex)<br /> {<br /> MessageBox.Show(ex.ToString());<br /> }<br /> finally<br /> {<br /> cmd.Dispose();<br /> conn.Close();<br /> }<br /> } 

5、 查询。

C++：

void CDemoDlg::OnQuery()<br /> {<br /> // TODO: Add your control notification handler code here<br /> const char* pcszDBFileName = "d://测试中文//demo.db";<br /> sqlite3* pDb;<br /> if (!OpenDatabase(pcszDBFileName, SQLITE_OPEN_READONLY, &pDb))<br /> return;</p> <p> const char* pcszSQL = "select * from images;";</p> <p> sqlite3_stmt* pStmt = NULL;<br /> int nRtn = sqlite3_prepare_v2(pDb, pcszSQL, -1, &pStmt, NULL);<br /> if (nRtn == SQLITE_OK)<br /> {<br /> nRtn = sqlite3_step(pStmt);<br /> while (nRtn == SQLITE_ROW)<br /> {<br /> int nID = sqlite3_column_int(pStmt, 0);<br /> const char* pcszUtf8FileName = (const char*)sqlite3_column_text(pStmt, 1);<br /> char szFileName[MAX_PATH];<br /> int nSize = MAX_PATH;<br /> Utf82Ascii(pcszUtf8FileName, szFileName, &nSize);<br /> const BYTE* pbyData = (const BYTE*)sqlite3_column_blob(pStmt, 2);<br /> int nDataLen = sqlite3_column_bytes(pStmt, 2);<br /> TRACE("id=%d, filename=%s, data len=%d/n", nID, szFileName, nDataLen);</p> <p> char szNewFileName[MAX_PATH];<br /> strcpy(szNewFileName, szFileName);<br /> char* pszExtension = strrchr(szNewFileName, '.');<br /> *pszExtension = 0;<br /> strcat(szNewFileName, "__");<br /> pszExtension = strrchr(szFileName, '.');<br /> strcat(szNewFileName, pszExtension);<br /> FILE* fp = fopen(szNewFileName, "wb");<br /> if (fp)<br /> {<br /> fwrite(pbyData, nDataLen, 1, fp);<br /> fclose(fp);<br /> }<br /> nRtn = sqlite3_step(pStmt);<br /> }<br /> sqlite3_finalize(pStmt);<br /> }<br /> sqlite3_close(pDb);<br /> return;<br /> }<br />  

C#：

private void Query_Click(object sender, EventArgs e)<br /> {<br /> string strDataSource = "Data Source=d://测试中文//demo.db;UTF8Encoding=True;Version=3";<br /> SQLiteConnection conn = new SQLiteConnection(strDataSource);<br /> string strSQL = "select * from images;";<br /> SQLiteCommand cmd = conn.CreateCommand();<br /> cmd.CommandText = strSQL;</p> <p> try<br /> {<br /> conn.Open();<br /> SQLiteDataReader dataReader = cmd.ExecuteReader();<br /> while (dataReader.Read())<br /> {<br /> int nID = dataReader.GetInt32(0);<br /> string strFileName = dataReader.GetString(1);<br /> int nDataLen = (int)dataReader.GetBytes(2, 0, null, 0, 0);<br /> byte[] pbyData = new byte[nDataLen];<br /> dataReader.GetBytes(2, 0, pbyData, 0, nDataLen);<br /> int nPos = strFileName.LastIndexOf('.');<br /> string strNewFileName = strFileName.Substring(0, nPos);<br /> strNewFileName += "_";<br /> strNewFileName += strFileName.Substring(nPos);<br /> FileStream fs = new FileStream(strNewFileName, FileMode.Create, FileAccess.Write);<br /> fs.Write(pbyData, 0, nDataLen);<br /> fs.Close();<br /> }<br /> dataReader.Close();<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.ToString());<br /> }<br /> finally<br /> {<br /> cmd.Dispose();<br /> conn.Close();<br /> }<br /> }