最近要做一个单机小程序，但涉及到一些数据的存取和增删改查，研究了一番，觉得还是轻量级数据库sqlite最合适，于是先做了个样例，方便进一步开发。
sqlite可以在如下网址下载
https://www.sqlite.org/download.html
但可直接下载System.Data.SQLite用在c#中，在如下网址下载
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
我选择了其中的Precompiled Binaries for 64-bit Windows (.NET Framework 4.0)
sqlite-netFx40-binary-bundle-x64-2010-1.0.113.0.zip
不过貌似需要tunnel cross great wall。也可下载我文末的样例程序包，里面已经包含了必要的库及帮助文件。
新建工程，因为下载的是64位SQLite库，在配制管理器中修改工程为x64架构。
然后引用中添加System.Data.SQLite.dll即可使用。
程序本身比较简单，核心是下面的SQLite帮助类，用于封装一些函数，便于调用。直接上代码，每个函数都有注释，也比较简单。
using System; using System.Collections.Generic; using System.Data; using System.Data.SQLite; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLiteDemo { public enum SQLiteType { INTEGER, REAL, TEXT, BLOB } class SQLiteHelper { string m_connString; public SQLiteHelper(string databasepath) { m_connString = "data source = " + databasepath; if (!File.Exists(databasepath)) { SQLiteConnection.CreateFile(databasepath); } } /// <summary> /// 执行不带参数的SQL语句 /// </summary> /// <param name="cmdString">SQL语句</param> /// <returns>受影响的行数</returns> public int Execute(string cmdString) { using (SQLiteConnection conn = new SQLiteConnection(m_connString)) { using (SQLiteCommand command = new SQLiteCommand(cmdString, conn)) { try { conn.Open(); int row = command.ExecuteNonQuery(); return row; } catch (SQLiteException e) { conn.Close(); throw new Exception(e.Message); } } } } /// <summary> /// 批量执行SQL语句 /// </summary> /// <param name="cmdStrings">要批量执行的所有SQL语句</param> public void BatchExecute(string[] cmdStrings) { using (SQLiteConnection conn = new SQLiteConnection(m_connString)) { conn.Open(); SQLiteTransaction trans = conn.BeginTransaction(); SQLiteCommand command = new SQLiteCommand(conn); command.Transaction = trans; try { for (int i = 0; i < cmdStrings.Length; i++) { command.CommandText = cmdStrings[i]; command.ExecuteNonQuery(); } trans.Commit(); } catch (SQLiteException e) { trans.Rollback(); throw new Exception(e.Message); } } } /// <summary> /// 创建数据表 /// </summary> /// <param name="tableName">数据表名</param> /// <param name="colNames">所有列名称</param> /// <param name="colTypes">每一列的类型</param> /// <param name="infos">每一列的其他描述，比如是否可为NULL，是否为主键</param> /// <returns>返回数据表Reader</returns> public SQLiteDataReader CreateTable(string tableName, string[] colNames, SQLiteType[] colTypes, string[] infos = null) { string cmd = "CREATE TABLE IF NOT EXISTS " + tableName + "( "; for (int i = 0; i < colNames.Length; i++) { cmd += colNames[i] + " " + colTypes[i].ToString(); if (infos != null) { cmd += " " + infos[i]; } if (i != colNames.Length - 1) { cmd += ", "; } } cmd += " )"; return Select(cmd); } /// <summary> /// 删除数据表 /// </summary> /// <param name="tableName">要删除的数据表</param> /// <returns>该表的行数</returns> public int DropTable(string tableName) { string cmd = "DROP TABLE " + tableName; return Execute(cmd); } /// <summary> /// 在数据表中插入数据，但不适用于插入二进制数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="types">每一列的类型</param> /// <param name="values">每一列的值</param> /// <param name="columns">可选参数，每一列的列名</param> /// <returns>插入的行数</returns> public int InsertData(string tableName, SQLiteType[] types, string[] values, string[] columns = null) { string cmd = "INSERT INTO " + tableName; if (columns != null) { cmd += " ( "; for (int i = 0; i < columns.Length; i++) { cmd += columns[i]; if (i != columns.Length - 1) { cmd += ", "; } } cmd += " ) "; } cmd += " VALUES ( "; for (int i = 0; i < values.Length; i++) { switch (types[i]) { case SQLiteType.INTEGER: case SQLiteType.REAL: cmd += values[i]; break; case SQLiteType.BLOB: throw new Exception("Please use InsertBinary to do the job!"); default: cmd += "‘" + values[i] + "‘"; break; } if (i != values.Length - 1) { cmd += ", "; } } cmd += " )"; return Execute(cmd); } /// <summary> /// 执行带参数的SQL语句 /// </summary> /// <param name="cmdString">带参数的SQL语句</param> /// <param name="parameters">对应的参数</param>