在C#中使用SQLite数据库
轻量级桌面程序数据库不太适合用SQLServer、MySQL之类的重量级数据库，嵌入式数据库更好。在对比Access、SQLite、Firebird数据库后发现SQLite较另外两个有较多优点。
环境：.NET Framework 3.5、windows11 64位、Visual Studio 2010.
C#使用SQLite需要从SQLite官网下载DLL组件。
我是windows11，64位的开发环境，最开始下载的64位的，结果运行时报异常。通过查资料，情况比较复杂（参考：https://blog.51cto.com/xxjjing/5804868），遂重新下载了32位的包：sqlite-netFx35-binary-Win32-2008-1.0.117.0
SQLite版本：1.0.117.0
下载地址：https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
下载后将包解压放到一个固定目录，需要依赖System.Data.SQLite.dll和SQLite.Interop.dll这两个文件，
在项目里右键项目 > 添加引用 > 选“浏览”选项卡，找到解压后的目录，引入System.Data.SQLite.dll，另一个文件SQLite.Interop.dll不可以通过引用方式添加，必须只能复制文件到运行目录下，通过调试发现程序会自动把System.Data.SQLite.dll也复制到运行目录下，System.Data.SQLite.dll和SQLite.Interop.dll文件会在一起。（尝试过直接复制这两个文件到程序的运行目录下不可行，Visual Studio里不管怎么刷新项目的引用列表都不会出现这两个文件，运行会报错。）
C# 中使用SQLite示例：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data; using System.Data.SQLite; namespace SQLiteTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { Console.WriteLine("SQL Lite 数据库试验"); // 连接数据库，FailIfMissing=false时若文件不存在会自动创建 string connStr = "Data Source=test.db;Version=3;Pooling=true;FailIfMissing=false;"; SQLiteConnection conn = new SQLiteConnection(connStr); conn.Open(); //在指定数据库中创建一个table string sql = "create table highscores (name varchar(20), score int)"; SQLiteCommand command = new SQLiteCommand(sql, conn); command.ExecuteNonQuery(); // 插入一些数据 sql = "insert into highscores (name, score) values ('Me', 3000)"; command = new SQLiteCommand(sql, conn); command.ExecuteNonQuery(); sql = "insert into highscores (name, score) values ('Myself', 6000)"; command = new SQLiteCommand(sql, conn); command.ExecuteNonQuery(); sql = "insert into highscores (name, score) values ('And I', 9001)"; command = new SQLiteCommand(sql, conn); command.ExecuteNonQuery(); // 查询数据 sql = "select * from highscores order by score desc"; command = new SQLiteCommand(sql, conn); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]); } } } }
一般建表使用文本文件，不使用代码建（build.sql）：
-- 管理员账号表 create table admin ( id integer primary key, admin_account nvarchar(32) not null, password nvarchar(32) not null ); insert into admin (admin_account, password) values ('admin', '123456'); -- 学生表 create table user ( id integer primary key, user_account nvarchar(32) not null, user_name nvarchar(32) not null, password nvarchar(32) not null, create_time timestamp default current_timestamp ); -- file表 create table file ( id integer primary key, user_account nvarchar(32) not null, user_name nvarchar(32) not null, file_path nvarchar(256) not null, upload_start_time timestamp, upload_end_time timestamp, upload_ip nvarchar(20), file_md5 nvarchar(32), file_size integer, file_suffix nvarchar(3) ); -- 文件删除历史表 create table file_remove_history ( id integer primary key, user_account nvarchar(32) not null, user_name nvarchar(32) not null, file_path nvarchar(256) not null, upload_start_time timestamp, upload_end_time timestamp, upload_ip nvarchar(20), file_md5 nvarchar(32), file_size integer, file_suffix nvarchar(3), remove_user nvarchar(20), remove_time timestamp default current_timestamp );
从外部文件执行sql
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data; using System.Data.SQLite; using System.IO; namespace U8FileBackupServer { public partial class Form1 : Form { string dbFile = System.Environment.CurrentDirectory + "\\xxx.db"; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { if (!File.Exists(dbFile)) { Console.WriteLine("文件不存在，执行创建。"); SQLiteConnection.CreateFile(dbFile); // 连接数据库，FailIfMissing=false时若文件不存在也会自动创建 SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Pooling=true;FailIfMissing=false;"); conn.Open(); // 打开连接 // 建表 string sqlText = new StreamReader(System.Environment.CurrentDirectory + "\\build.sql").ReadToEnd(); Console.WriteLine("= = = = = = = = = = = = = = = = = = = = = = = = ="); Console.WriteLine(sqlText); Console.WriteLine("= = = = = = = = = = = = = = = = = = = = = = = = ="); SQLiteCommand cmd = new SQLiteCommand(sqlText, conn); cmd.ExecuteNonQuery(); conn.Close(); // 关闭连接 } SQLiteConnection conn1 = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Pooling=true;FailIfMissing=true;"); conn1.Open(); // 插入一些数据 string sql = "insert into admin (id, admin_account, password) values ('111', '管理员', 'admin')"; SQLiteCommand command = new SQLiteCommand(sql, conn1); command.ExecuteNonQuery(); // 查询数据 sql = "select * from admin"; command = new SQLiteCommand(sql, conn1); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("admin_account: " + reader["admin_account"] + "\tpassword: " + reader["password"]); }
conn1.Close(); } } }
自增列
主键字段添加 integer 关键字，insert时会自动生成自增值
create table file ( id integer primary key, ... );
SQLite插入记录时自增列字段不需要指定值
insert into user (user_account, user_name, password) values ('zhangsan', '张三', '123456')
C# 封装 SQLite 工具类：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Runtime.InteropServices; using System.IO; using System.Data; using System.Data.SQLite; namespace U8FileBackupServer { /// <summary> /// SQLite数据库操作工具类 /// </summary> class DBUtil { static string dbFile = System.Environment.CurrentDirectory + "\\u8file.db"; /// <summary> /// 检查和初始化数据库 /// </summary> public static void checkAndInitDB() { if (!File.Exists(dbFile)) { Console.WriteLine("文件不存在，执行创建。"); SQLiteConnection.CreateFile(dbFile); // 连接数据库，FailIfMissing=false时若文件不存在也会自动创建 SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Pooling=true;FailIfMissing=false;"); conn.Open(); // 打开连接 // 建表 string sqlText = new StreamReader(System.Environment.CurrentDirectory + "\\build.sql").ReadToEnd(); Console.WriteLine("= = = = = = = = = = = = = = = = = = = = = = = = ="); Console.WriteLine(sqlText); Console.WriteLine("= = = = = = = = = = = = = = = = = = = = = = = = ="); SQLiteCommand cmd = new SQLiteCommand(sqlText, conn); cmd.ExecuteNonQuery(); conn.Close(); // 关闭连接 } } /// <summary> /// 通用查询方法 /// 用法： /// DataTable result = DBUtil.query("select * from admin"); /// string username = result[0]["username"]; /// </summary> /// <param name="sql"></param> /// <returns></returns> public static List<Dictionary<string, object>> query(string sql) { List<Dictionary<string, object>> result = new List<Dictionary<string, object>>(); SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Pooling=true;FailIfMissing=true;"); conn.Open(); SQLiteCommand command = new SQLiteCommand(sql, conn); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { Dictionary<string, object> kv = new Dictionary<string, object>(); for (int i = 0; i < reader.FieldCount; i++) { kv[reader.GetName(i)] = reader.GetValue(i); } result.Add(kv); } command.Dispose(); conn.Dispose(); return result; }
/// <summary>
/// 通用修改方法，返回影响的行数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int update(string sql) { SQLiteConnection conn = new SQLiteConnection("Data Source=" + dbFile + ";Version=3;Pooling=true;FailIfMissing=true;"); conn.Open(); SQLiteCommand command = new SQLiteCommand(sql, conn); int rowCount = command.ExecuteNonQuery(); command.Dispose(); conn.Dispose(); return rowCount; } } }
SQLite中时间的处理
存储日期值，建表时可以使用 timestamp 字段，可在建表时通过 default current_timestamp 设置默认值。
create table demo ( id integer primary key, ... create_time timestamp default current_timestamp );
在插入数据时候指定timestamp字段的值为当前系统时间（localtime 表示使用本地时区）
insert into demo(create_time) values(datetime(CURRENT_TIMESTAMP, 'localtime'));
直接指定timestamp的值
insert into demo(create_time) values('2023-05-27 11:23:45');
C#中指定SQLite timestamp的值（如果C#获取时间用了localtime，则sqlite中不能再用）
string sql = "insert into demo(create_time) values(datetime('" + DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss") + "'));"
根据时间作为条件查询
string timeSql = "select * from file where upload_start_time > '2023-05-20 00:00:00' and upload_end_time > '2023-05-27 14:20:00' order by upload_start_time ";
List<Dictionary<string, object>> data3 = query(timeSql); foreach (Dictionary<string, object> row in data3) { Console.WriteLine(row["id"] + "\t" + row["user_name"] + "\t" + row["upload_start_time"] + "\t" + row["upload_start_time"] + "\t" + row["upload_end_time"] + "\t" + row["file_size"]); }
也支持betwwen语句
查询总数、最大id
List<Dictionary<string, object>> total = query("select count(id) as count from file"); Console.WriteLine("总记录数：" + total.First()["count"]); List<Dictionary<string, object>> maxid = query("select max(id) as maxid from file"); Console.WriteLine("最大id：" + maxid.First()["maxid"]);
分页查询
string timeSql = "select * from file where upload_start_time between '2020-04-20 00:00:00' and '2024-05-20 00:00:00' order by upload_start_time limit 5 Offset 0 "; List<Dictionary<string, object>> data3 = query(timeSql); foreach (Dictionary<string, object> row in data3) { Console.WriteLine(row["id"] + "\t" + row["user_name"] + "\t" + row["upload_start_time"] + "\t" + row["upload_start_time"] + "\t" + row["upload_end_time"] + "\t" + row["file_size"]); }
更多参考资料：
SQLite中的字段类型：https://www.cnblogs.com/wjcoding/p/11671335.html
