在C#中使用SQLite数据库-Toy模板网

这篇具有很好参考价值的文章主要介绍了在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"]);
}