SQLite是一个小型的关系型数据库系统,应用非常广泛,通常可以作为本地数据的临时存储,比如一些移动App使用SQLite作为本地存储,下面简单介绍一下SQLite的使用,以及注意事项。

    在C#中使用SQLite比较简单。首先要去官网下载System.Data.SQLite 文件,这个是对SQLite3的C语言dll的.NET包装,根据当前的.NET版本以及操作系统是32位,还是64位,下载不同的程序集。当然,还可以在Visual Studio中,通过NuGet packages添加。

    下载完成之后,在项目中引用System.Data.SQLite.dll就可以使用ADO.NET以类似访问SQL Server的方式访问SQLite了,当然,上面下载的安装包中,还包括System.Data.SQLite.EF6.dll,System.Data.SQLite.Linq.dll等以支持LINQ2SQLite,EntityFramework功能。

     首先,需要给SQLite库文件起一个名称,然后后面可以定义一个字符串,字符串中需要标明库文件路径,版本号,因为SQLite有两个版本:版本2和版本3

private static string dbName = "myDatabase.sqlite";
private string connectionString = "Data Source=" + dbName + ";Version=3";
创建库

SQLiteConnection.CreateFile(dbName)

使用SQLiteConnection.CreateFile(dbName)可以创建一个新的SQLite库文件,数据库密码的设置,需要调用SQLiteConnection实体方法SetPassword来实现:

cn.SetPassword("123456");

修改密码用ChangePassword:

cn.ChangePassword("123456");

如果有密码,数据库链接字符串需要把“Password=xxx”加上。

新建表

使用create语句,然后调用ExecuteNoQuery可以创建表:

if (!File.Exists(dbName))
{
                //创建库
                SQLiteConnection.CreateFile(dbName);

                //创建表
                string sql = @"CREATE TABLE [Student] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [Name] NVARCHAR(100)  NULL,
                    [Gender]  BOOLEAN NULL,
                    [Address] NVARCHAR(200)  NULL
                    )";

                using (SQLiteConnection cn = new SQLiteConnection(connectionString))
                {
                    try
                    {
                        cn.Open();
                        SQLiteCommand command = new SQLiteCommand(sql, cn);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine(exception);
                    }
                }
}

插入

使用标准的insert语句,然后调用ExecuteNoQuery可以插入数据

using (SQLiteConnection cn = new SQLiteConnection(connectionString))
{
                try
                {
                    cn.Open();
                    string sql = "insert into Student (Name, Gender,Address) values ('" + this.txtBtnName.Text + "','" + gender + "','" + this.txtBtnAddress.Text + "')";
                    SQLiteCommand command = new SQLiteCommand(sql, cn);
                    command.ExecuteNonQuery();
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception);
                }
}

另外,SQLite还支持事务,下面是通过事务批量插入数据的演示。

using (SQLiteConnection cn = new SQLiteConnection(connectionString))
 {
                cn.Open();
                SQLiteTransaction tran = cn.BeginTransaction();
                string gender = this.rbnMen.Checked ? "true" : "false";
                try
                {
                    SQLiteCommand command = new SQLiteCommand(cn);
                    command.Transaction = tran;
                    for (int i = 0; i < 100; i++)
                    {
                        command.CommandText = "insert into Student (Name, Gender,Address) values (@Name,@Gender,@Address)";
                        command.Parameters.AddWithValue("@Name", this.txtBtnName.Text + " " + i);
                        command.Parameters.AddWithValue("@Gender", gender);
                        command.Parameters.AddWithValue("@Address", this.txtBtnAddress.Text + " " + i);
                        command.ExecuteNonQuery();
                    }
                     tran.Commit();
              
                }
                catch (Exception exception)
                {
                    tran.Rollback();
                    Console.WriteLine(exception);
                }
}

查询

using (SQLiteConnection cn = new SQLiteConnection(connectionString))
            {
                try
                {
                    cn.Open();
                    SQLiteCommand cmd = new SQLiteCommand("select * from Student", cn);
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Columns.Add("Id", typeof(int));
                    dt.Columns.Add("Name", typeof(string));
                    dt.Columns.Add("Gender", typeof(bool));
                    dt.Columns.Add("Address", typeof(string));
                    while (reader.Read())
                    {
                        dt.Rows.Add(new Object[] { reader["Id"], reader["Name"], reader["Gender"], reader["Address"] });
                    }
                    reader.Close();
                    this.dataGridView1.DataSource = dt;
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception);
                }
 }
优化

上面这些是System.Data.SQLite 的用法,基本上跟在ADO.NET下使用SQLServer类似,在性能上应该满足要求(文件大小性能可以查看https://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file,速度可以查看下面链接),但是如果硬是要优化性能,可能这个封装没有提供其他的参数来优化,在C接口中,提供了很多参数,可以来优化提高性能,具体的一些优化下面这个链接有:https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite

大体包括:

1:对于批量插入,使用事务一次性提交。

2:使用Prepared Statement提前将SQL语句编译为模板,然后参数填充,个人理解有点像存储过程那样,不用每次执行都编译SQL语句,只编译一次,然后后面的执行,只要把参数注入进去。

3:设置PRAGMA synchronous = OFF。默认的,SQLite在每次系统发起一个操作系统级别的写指令时,都会暂停一下,这能保证数据能写到磁盘中。将synchronous = OFF告诉SQLite不必等待OS的写操作,但是这样存在一个风险就是,当计算机遭受灾难性崩溃,比如断电时,有可能数据还没有来得及保存到磁盘,导致数据库文件损坏。

4:设置PRAGMA journal_mode = MEMORY。将日志模式设置为内存模式能够加快事务的执行或者回滚速度,风险同3,当事务正在执行时突然断电,可能会导致数据库状态不正确。

5:设置索引

除了5,上面这些,都是C接口里面可以设置的参数,如果要在C#中设置这些,通过修改System.Data.SQLite.dll源代码设置。

其他

下面这个SQLite Developer工具,可以管理SQLite文件