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文件