在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

下载地址：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）：

-- admin表

create table admin (

  id nvarchar(32) primary key,

  admin_account nvarchar(32) not null,

  password nvarchar(32) not null

);

-- file表

create table file (

  id nvarchar(32) 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(4)

);

-- file_remove_history表

create table file_remove_history (

  id nvarchar(32) 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(4),

  remove_user nvarchar(20),

  remove_time timestamp

);

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();

        }

    }

}

更多参考资料：

组件包引用参考：https://blog.csdn.net/wzj0808/article/details/78910457/
执行sql参考：https://www.cnblogs.com/LeeSki/p/14381192.html

SQLite中的字段类型：https://www.cnblogs.com/wjcoding/p/11671335.html

在C#中使用SQLite数据库的相关教程结束。