C#中SQLite的使用及工具类
SQLite简介
SQLite是一款轻型的数据库，一个数据库就是一个文件，详细介绍参考官网：https://www.sqlite.org/index.html
SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。
您可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。
SQLite数据与常见的MySQL、SQL等的数据库不一样，它是动态类型数据库，每个值在数据库占的存储空间根据值的大小确定，使用时需要注意数据类型的问题。
存储类
每个存储在 SQLite 数据库中的值都具有以下存储类之一：
| 存储类 | 描述 |
|---|---|
| NULL | 值是一个 NULL 值。 |
| INTEGER | 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
| REAL | 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。 |
| TEXT | 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。 |
| BLOB | 值是一个 blob 数据，完全根据它的输入存储。 |
亲和类型
SQLite支持列的亲和类型概念，任何列仍然可以存储任何类型的数据，当数据插入时该字段的数据将会优先采用亲和类型作为该值的存储方式。
创建 SQLite3 表时可使用的各种数据类型名称及相应的亲和类型，如下：
| 数据类型 | 亲和类型 |
|---|---|
| INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 |
INTEGER：对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。 |
| CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB |
TEXT：数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。 |
| BLOB no datatype specified |
NONE：不做任何的转换，直接以该数据所属的数据类型进行存储。 |
| REAL DOUBLE DOUBLE PRECISION FLOAT |
REAL：其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。 |
| NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME |
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时： 如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据； 如果转换失败，SQLite仍会以TEXT方式存储该数据。 对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。 注：对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。 |
引用System.Data.SQLite.dll
在C#中使用SQLite数据库需要引用System.Data.SQLite.dll，下载链接：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
注：System.Data.SQLite是SQLite的ADO.NET提供程序，两者是两个不同的开源项目，现在System.Data.SQLite的开发和维护工作大部分由SQLite开发团队执行。
System.Data.SQLite的下载页面选项太多，一般人进来都不清楚要下载那些内容，下面对下载界面中的软件包做一个简单介绍。
软件包分类
下载内容按类型分为安装包、非静态连接的二进制包和静态连接的二进制包三种，区别如下：
- 安装程序包仅用于在开发人员计算机上安装，然后仅在需要VisualStudio的设计时组件时安装，不建议安装在客户机器上（理论上也可以）。
- 安装包会安装相关的动态库到系统内，并注册到GAC（Global Assembly Cache）。
- 二进制软件包旨在供开发人员使用，以便获得开发所需的汇编二进制文件，并通过XCOPY部署将其应用程序部署到客户机上。
- 两种二进制包的区别在于非托管部分的连接方式不同，非静态连接的二进制包在使用时需要VC运行时库的支持。
注：如果所有目标机器已经安装了VisualC++运行时，或者可以容易地部署，则应该避免“静态”包。
每个类型都按.NET版本分成了若干小组，每个.NET版本又分为32位和64位两组：
- 支持的.NET版本有 2.0 SP2 、 3.5 SP2 、4.0 、 4.5 、 4.5.1 、4.6 。
- 选用32位还是64位是根据使用系统来决定的，如开发时是64位（使用64位dll）系统而发布后运行在32位（使用32位dll）系统上。
注：虽然.NET高版本兼容低版本，但强烈建议选择与目标.NET Framework版本匹配的包。
在每个.NET版本-位数分组中都有2个文件包，一个带有“bundle”字样，另一个没有：
- 带有“bundle”字样的表示动态库是按混合模式编译的，在使用的时候只需要System.Data.SQLite.dll就可以了。
- 不带“bundle”的则是将非托管部分和托管部分分别编译，System.Data.SQLite.dll不能独立使用，还需要有SQLite.Interop.dll才能使用。
注：除非认为绝对必要，否则应避免使用“bundle”包。
根据上面的介绍，如果开发机器和客户机器可能具有不同的处理器体系结构，则可能需要一个以上的二进制程序包。
使用本机库预加载
本机库预加载功能从1.0.80.0版本开始可用，并且默认情况下已启用，能够自动适应当前系统的位数。为了利用此功能，必须将单独的托管程序集和互操作程序集与XCOPY部署一起使用（混合模式程序集、安装软件包部署不支持此功能）。
使用本机库预加载功能时，应用程序部署看起来如下（ bin 表示将在目标计算机上部署应用程序二进制文件的目录）：
- bin \ App.exe（可选，仅受管应用程序可执行程序集）
- bin \ App.dll（可选，仅托管应用程序库程序集）
- bin \ System.Data.SQLite.dll（必需，仅受管核心程序集）
- bin \ System.Data.SQLite.Linq.dll（可选，仅托管LINQ程序集）
- bin \ System.Data.SQLite.EF6.dll（可选，仅托管EF6程序集）
- bin \ x86 \ SQLite.Interop.dll（必需，x86本机互操作程序集）
- bin \ x64 \ SQLite.Interop.dll（必需，x64本机互操作程序集）
启用本机库预加载功能并显示上面的应用程序部署后，System.Data.SQLite仅限托管程序集将尝试自动检测当前进程的处理器体系结构并预加载适当的本机库，此时不用考虑客户机器的是64位还是32位。
常用部署包
我把.NET的4.0 、4.5版本对应的软件包按本机库预加载功能的要求重新组装，使用时直接复制到Debug目录下即可：
- sqlite-netFx45-binary-Win32-x64-2012-1.0.113.0.zip 提取码: r4yy
- sqlite-netFx40-binary-Win32-x64-2010-1.0.113.0.zip 提取码: j88h
- sqlite-netFx45-static-binary-Win32-x64-2012-1.0.113.0.zip 提取码: 33kp
- sqlite-netFx40-static-binary-Win32-x64-2010-1.0.113.0.zip 提取码: iqr2
注：官方建议不使用静态的二进制包，我个人则喜欢用静态的二进制包，这样就不用考虑客户机器上是否安装有对应的VC运行时库了。
工具类
工具类大部分内容来自c# Sqlite帮助类，考虑到SQLite是一个数据库一个文件、一个项目可能需要多个数据库，我将工具类改为通过对象实例操作数据库并提供一个静态的对象实例字典。
工具类代码如下：
public class SQLiteHelper
{
/// <summary>
/// 数据库列表
/// </summary>
public static Dictionary<string, SQLiteHelper> DataBaceList = new Dictionary<string, SQLiteHelper>();
/// <summary>
/// 构造函数
/// </summary>
/// <param name="filename">数据库文件名</param>
public SQLiteHelper(string filename=null)
{
DataSource = filename;
}
/// <summary>
/// 数据库地址
/// </summary>
public string DataSource { get; set; }
/// <summary>
/// 创建数据库，如果数据库文件存在则忽略此操作
/// </summary>
public void CreateDataBase()
{
string path = Path.GetDirectoryName(DataSource);
if ((!string.IsNullOrWhiteSpace(path)) && (!Directory.Exists(path))) Directory.CreateDirectory(path);
if (!File.Exists(DataSource)) SQLiteConnection.CreateFile(DataSource);
}
/// <summary>
/// 获得连接对象
/// </summary>
/// <returns>SQLiteConnection</returns>
public SQLiteConnection GetSQLiteConnection()
{
string connStr =string.Format("Data Source={0}", DataSource);
var con = new SQLiteConnection(connStr);
return con;
}
/// <summary>
/// 准备操作命令参数
/// </summary>
/// <param name="cmd">SQLiteCommand</param>
/// <param name="conn">SQLiteConnection</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (data != null && data.Count >= 1)
{
foreach (KeyValuePair<String, String> val in data)
{
cmd.Parameters.AddWithValue(val.Key, val.Value);
}
}
}
/// <summary>
/// 查询，返回DataSet
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data = null)
{
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds;
}
/// <summary>
/// 查询，返回DataTable
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data = null)
{
var dt = new DataTable();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader();
dt.Load(reader);
}
return dt;
}
/// <summary>
/// 返回一行数据
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataRow</returns>
public DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data = null)
{
DataSet ds = ExecuteDataset(cmdText, data);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0];
return null;
}
/// <summary>
/// 执行数据库操作
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string cmdText, Dictionary<string, string> data=null)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 返回SqlDataReader对象
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>SQLiteDataReader</returns>
public SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data = null)
{
var command = new SQLiteCommand();
SQLiteConnection connection = GetSQLiteConnection();
try
{
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch
{
connection.Close();
command.Dispose();
throw;
}
}
/// <summary>
/// 返回结果集中的第一行第一列，忽略其他行或列
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>object</returns>
public object ExecuteScalar(string cmdText, Dictionary<string, string> data = null)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, cmdText, data);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="recordCount">总记录数</param>
/// <param name="pageIndex">页牵引</param>
/// <param name="pageSize">页大小</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="countText">查询总记录数的Sql文本</param>
/// <param name="data">命令参数</param>
/// <returns>DataSet</returns>
public DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data = null)
{
if (recordCount < 0)
recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
}
return ds;
}
/// <summary>
/// 重新组织数据库：VACUUM 将会从头重新组织数据库
/// </summary>
public void ResetDataBass()
{
using (SQLiteConnection conn = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = "vacuum";
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
cmd.ExecuteNonQuery();
}
}
}
工具类使用方法如下：
static void Main(string[] args)
{
SQLiteHelper testDb = new SQLiteHelper("test.db");
SQLiteHelper.DataBaceList.Add("TEST", testDb);
//建库
testDb.CreateDataBase();
//建表
StringBuilder sbr = new StringBuilder();
sbr.AppendLine("CREATE TABLE IF NOT EXISTS `test_table`(");
sbr.AppendLine("`id` INTEGER PRIMARY KEY AUTOINCREMENT,");//自增id主键
sbr.AppendLine("`name` VARCHAR(100) NOT NULL,");
sbr.AppendLine("`password` VARCHAR(40) NOT NULL,");
sbr.AppendLine("`create_time` datetime DEFAULT CURRENT_TIMESTAMP,");
sbr.AppendLine("`update_time` datetime DEFAULT CURRENT_TIMESTAMP );");
sbr.AppendLine();
sbr.AppendLine("CREATE TRIGGER IF NOT EXISTS `trigger_test_table_update_time` ");//触发器-自动更新update_time
sbr.AppendLine("AFTER UPDATE ON `test_table` ");
sbr.AppendLine("FOR EACH ROW ");
sbr.AppendLine("BEGIN ");
sbr.AppendLine("UPDATE `test_table` SET `update_time` = CURRENT_TIMESTAMP WHERE id = old.id; ");
sbr.AppendLine("END;");
string cmdText = sbr.ToString();
int val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数：" + val);
//增
sbr.Clear();
sbr.Append("INSERT INTO test_table (name,password) VALUES ");
sbr.Append("(11,111), ");
sbr.Append("(12,222); ");
cmdText = sbr.ToString();
val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数：" + val);
//删
sbr.Clear();
sbr.Append("DELETE FROM test_table ");
sbr.Append("WHERE id=1;");
cmdText = sbr.ToString();
val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数：" + val);
//改
sbr.Clear();
sbr.Append("UPDATE test_table SET ");
sbr.Append("name='13', ");
sbr.Append("password='333' ");
sbr.Append("WHERE id=@id;");
cmdText = sbr.ToString();
Dictionary<string, string> data = new Dictionary<string, string>();
data.Add("@id", "2");
val = testDb.ExecuteNonQuery(cmdText, data);
Console.WriteLine("影响行数：" + val);
//查
sbr.Clear();
sbr.Append("SELECT name,password FROM test_table ");
sbr.Append("WHERE id=@id;");
cmdText = sbr.ToString();
DataTable dt = testDb.ExecuteDataTable(cmdText, data);
Console.WriteLine("结果行数：" + dt.Rows.Count);
//删除表
sbr.Clear();
sbr.Append("DROP TABLE test_table;");
cmdText = sbr.ToString();
val = SQLiteHelper.DataBaceList["TEST"].ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数：" + val);
//重组数据库
SQLiteHelper.DataBaceList["TEST"].ResetDataBass();
Console.ReadKey();
}
