C#中SQLite的使⽤及⼯具类
⽬录
SQLite简介
SQLite是⼀款轻型的数据库，⼀个数据库就是⼀个⽂件，详细介绍参考官⽹：
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，下载链接：
注：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 \ （可选，仅受管应⽤程序可执⾏程序集）
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⽬录下即可：
注：官⽅建议不使⽤静态的⼆进制包，我个⼈则喜欢⽤静态的⼆进制包，这样就不⽤考虑客户机器上是否安装有对应的VC运⾏时库了。
⼯具类
⼯具类⼤部分内容来⾃，考虑到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 ");
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论