在C#中实现SQLite的事务处理

一、SQLite事务介绍

事务是针对一个或多个数据库中数据操作的基本单位，操作时可以把许多个SQLite语句组合为一组，把所有这些放在一起作为事务的一部分进行执行。

所有 SQL 命令组成一个单元。 要么全部保存，要么什么都不保存。 这是事务背后的基本思想。
事务控制指令只能和数据库操作语言（DML命令INSERT、UPDATA、DELETE）结合使用。不能再创建表和删除表时使用，因为这些操作在数据库中是自动提交的。
事务包括4个属性

• 原子性
  确保工作单位内的所有操作都能完成，否则事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
• 一致性
  指的是确保数据库在成功提交事务上正确的改变状态。
• 隔离性
  事务和事务之间互不干涉、相互之间没有影响。通过数据库级上的独占性和共享锁来实现，读取时允许多个进程或者线程，而进行写数据时只能有一个进程或者线程。
• 持久性
  确保已提交的事务的结果或效果在系统发生故障的情况下仍然存在。

SQLtie支持事务处理主要是通过事务标记、事务提交以及事务回滚来保证一个事务中所有操作都完成或者回滚到事务开始前的状态。

SQL语句	功能	用法
BEGIN TRANSACTION	标记一个事务起点	BEGIN[TRANSACTION[name]]
END TRANSACTION	标记一个事务终止	END[TRANSACTION[name]]
ROLL BACK TRANSACTION	回滚到事务起始点	ROLL BACK[TRANSACTION[name]]
COMMIT TRANSACTION	标记一个事务结束	COMMIT[TRANSACTION[name]]

二、SQLite事务实战

        static void Main(string[] args)
        {
            //连接了你选中的那个数据库
            string connectionString = "E:\\SQLiteTRANSACTION\\SQLiteTRANSACTION\\bin\\Debug\\DatabaseTest.db";
            SQLiteConnection dbConnection = new SQLiteConnection();//***
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = connectionString;
            dbConnection.ConnectionString = connstr.ToString();
            dbConnection.Open();
            //
            SQLiteTransaction tr = dbConnection.BeginTransaction();//事务开始
            try
            {             
               
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = "DROP TABLE IF EXISTS Company1";
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE TABLE Company1(ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL)";//创建一个表，ID为主键，NOT NULL 表示这个不能为空
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('ALBABA')";//表插入内容
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('HKWS')";
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('HUAW')";
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('GSYH')";
                cmd.Connection = dbConnection;
                cmd.ExecuteNonQuery();               
                Console.Write("FINALLY");
                tr.Commit();//把事务调用的更改保存到数据库中，事务结束
                dbConnection.Close();
            }
            catch(Exception ex)
            {
                Console.Write(ex.Message);
                tr.Rollback();//回滚
            }
        }

三、事务的优点

事务除了带来安全的数据操作之外，还提升了数据插入的效率。
此处案例参考了博客【如何高效使用SQLite事务 .net (C#】
1.在不使用事务的前提下，批量插入1000条数据，我测试时时间花费4分22.44576秒

using System.Data;
using System.Data.Common;
using System.Data.SQLite;
 
// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
 
DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
  // 连接数据库
  conn.ConnectionString = "Data Source=test1.db3";
  conn.Open();
 
  // 创建数据表
  string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
  DbCommand cmd = conn.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandText = sql;
  cmd.ExecuteNonQuery();
 
  // 添加参数
  cmd.Parameters.Add(cmd.CreateParameter());
  
  // 开始计时
  Stopwatch watch = new Stopwatch();
  watch.Start();
  
  // 连续插入1000条记录
  for (int i = 0; i < 1000; i++)
  {
    cmd.CommandText = "insert into [test1] ([s]) values (?)";
    cmd.Parameters[0].Value = i.ToString();
 
    cmd.ExecuteNonQuery();
  }
 
  // 停止计时
  watch.Stop();
  Console.WriteLine(watch.Elapsed);
}

2.在使用事务批量插入1000条数据时，测试发现中共花费2秒

using System.Data;
using System.Data.Common;
using System.Data.SQLite;
 
// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
 
DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
  // 连接数据库
  conn.ConnectionString = "Data Source=test1.db3";
  conn.Open();
 
  // 创建数据表
  string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
  DbCommand cmd = conn.CreateCommand();
  cmd.Connection = conn;
  cmd.CommandText = sql;
  cmd.ExecuteNonQuery();
 
  // 添加参数
  cmd.Parameters.Add(cmd.CreateParameter());
  
  // 开始计时
  Stopwatch watch = new Stopwatch();
  watch.Start();
  
  DbTransaction trans = conn.BeginTransaction(); // <-------------------
  try 
  {
    // 连续插入1000条记录
    for (int i = 0; i < 1000; i++)
    {
      cmd.CommandText = "insert into [test1] ([s]) values (?)";
      cmd.Parameters[0].Value = i.ToString();
 
      cmd.ExecuteNonQuery();
    }
 
    trans.Commit(); // <-------------------
  }
  catch
  {
    trans.Rollback(); // <-------------------
    throw; // <-------------------
  }
 
  // 停止计时
  watch.Stop();
  Console.WriteLine(watch.Elapsed);
}

两者如此大的差距是因为SQLite 缺省为每个操作启动一个事务，那么原代码 1000 次插入起码开启了 1000 个事务，“事务开启 + SQL 执行 + 事务关闭” 自然耗费了大量的时间，这也是后面显示启动事务后为什么如此快的原因。其实这是数据库操作的基本常识，大家要紧记，不好的代码效率差的不是一点半点。