使用 C# 的 SQLite 事务

使用 C# 的 SQLite 事务，在本章中，我们将处理事务。 首先，我们提供一些基本定义。 然后，我们将有一些程序显示如何处理事务。

事务 是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

在 SQLite 中，除SELECT以外的任何命令都将启动隐式事务。 同样，在事务中，诸如CREATE TABL …，VACUUM和PRAGMA之类的命令将在执行之前提交先前的更改。

手动事务以BEGIN TRANSACTION语句开始，并以COMMIT或ROLLBACK语句结束。

SQLite 支持三种非标准的事务级别：DEFERRED，IMMEDIATE 和 EXCLUSIVE。 除非我们启动自己的事务，否则 SQLite 会自动将每个命令放入其自己的事务中。 请注意，这也可能会受到驱动程序的影响。 SQLite Python 驱动程序默认情况下已关闭自动提交模式，并且第一个 SQL 命令启动新事务。

using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        

        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using (SqliteCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "DROP TABLE IF EXISTS Friends";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                    Name TEXT)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
}

我们创建一个 Friends 表，并用数据填充它。 我们没有明确地启动事务，也没有调用 commit 或 rollback 方法。 但是数据已写入表中。 这是因为默认情况下，我们在自动提交模式下工作。 在这种模式下，每个 SQL 语句立即生效。

cmd.CommandText = "DROP TABLE IF EXISTS Friends";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                    Name TEXT)";
cmd.ExecuteNonQuery();

如果Friends表已经存在，我们将其删除。 然后，我们使用CREATE TABLE语句创建表。

cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
cmd.ExecuteNonQuery();
...

我们插入两行。

sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian

Friends表已成功创建。

在第二个示例中，我们将使用BeginTransaction()方法启动自定义事务。

using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        

        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using(SqliteTransaction tr = con.BeginTransaction())
            {
                using (SqliteCommand cmd = con.CreateCommand())
                {

                    cmd.Transaction = tr;
                    cmd.CommandText = "DROP TABLE IF EXISTS Friends";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                        Name TEXT)";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jane')";
                    cmd.ExecuteNonQuery();
                }

                tr.Commit();
            }

            con.Close();
        }
    }
}

所有 SQL 命令组成一个单元。 要么全部保存，要么什么都不保存。 这是事务背后的基本思想。

using(SqliteTransaction tr = con.BeginTransaction())

BeginTransaction()方法启动事务。

cmd.Transaction = tr;

我们设置SqliteCommand在其中执行的事务。

tr.Commit();

如果一切正常，我们将整个事务提交到数据库。 如果发生异常，则事务将在后台回滚。

显式回滚调用

现在，我们将显示一个示例，在发生异常的情况下，我们手动回滚事务。

using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";   

        SqliteConnection con = null;     
        SqliteTransaction tr = null;
        SqliteCommand cmd = null;

        try 
        {
            con = new SqliteConnection(cs);

            con.Open();

            tr = con.BeginTransaction();
            cmd = con.CreateCommand();

            cmd.Transaction = tr;
            cmd.CommandText = "DROP TABLE IF EXISTS Friends";
            cmd.ExecuteNonQuery();
            cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                Name TEXT)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jane')";
            cmd.ExecuteNonQuery();

            tr.Commit();            

        } catch (SqliteException ex) 
        {
            Console.WriteLine("Error: {0}",  ex.ToString());

            if (tr != null)
            {
                try 
                {
                    tr.Rollback();

                } catch (SqliteException ex2)
                {

                    Console.WriteLine("Transaction rollback failed.");
                    Console.WriteLine("Error: {0}",  ex2.ToString());

                } finally
                {
                    tr.Dispose();
                }
            }
        } finally 
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }

            if (tr != null) 
            {
                tr.Dispose();
            }

            if (con != null)
            {
                try 
                {
                    con.Close();                    

                } catch (SqliteException ex)
                {

                    Console.WriteLine("Closing connection failed.");
                    Console.WriteLine("Error: {0}",  ex.ToString());

                } finally
                {
                    con.Dispose();
                }
            }
        }    
    }
}

我们创建自己的 try，catch，finally 块，以解决可能的问题。

} catch (SqliteException ex) 
{
    Console.WriteLine("Error: {0}",  ex.ToString());

    if (tr != null)
    {
        try 
        {
            tr.Rollback();

        } catch (SqliteException ex2)
        {

            Console.WriteLine("Transaction rollback failed.");
            Console.WriteLine("Error: {0}",  ex2.ToString());

        } finally
        {
            tr.Dispose();
        }
    }
}

在创建Friends表的过程中引发异常时，我们将调用Rollback()方法。 即使进行回滚，也可能会发生异常。 因此，我们也检查这种情况。

if (cmd != null)
{
    cmd.Dispose();
}

if (tr != null) 
{
    tr.Dispose();
}

当一切顺利时，我们将配置资源。

if (con != null)
{
    try 
    {
        con.Close();                    

    } catch (SqliteException ex)
    {

        Console.WriteLine("Closing connection failed.");
        Console.WriteLine("Error: {0}",  ex.ToString());

    } finally
    {
        con.Dispose();
    }
}

关闭连接时，我们可能会收到另一个异常。 我们在这里处理这种情况。

错误

当事务中存在错误时，将回滚事务，并且不会将任何更改提交到数据库。

using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        

        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using(SqliteTransaction tr = con.BeginTransaction())
            {
                using (SqliteCommand cmd = con.CreateCommand())
                {

                    cmd.Transaction = tr;
                    cmd.CommandText = "UPDATE Friends SET Name='Thomas' WHERE Id=1";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
                    cmd.ExecuteNonQuery();
                }

                tr.Commit();
            }

            con.Close();
        }
    }
}

在代码示例中，我们要更改两个名称。 有两个语句构成一个事务。 第二个 SQL 语句中有错误。 因此，该事务将回滚。

cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";

表名称不正确。 数据库中没有 Friend 表。

$ mono trans_error.exe 

Unhandled Exception: Mono.Data.Sqlite.SqliteException: SQLite error
no such table: Friend
...

运行示例将显示此错误消息。 事务回滚。

sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Jane

Friends表中没有发生任何变化。 即使第一个UPDATE语句正确。

我们将再次尝试更改两行； 这次不使用SqliteTransaction。

using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        

        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using (SqliteCommand cmd = con.CreateCommand())
            {

                cmd.CommandText = "UPDATE Friends SET Name='Thomas' WHERE Id=1";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
                cmd.ExecuteNonQuery();
            }

            con.Close();
        }
    }
}

我们尝试更新Friends表中的两个名称，汤姆（Tom）到托马斯（Thomas）和罗伯特（Robert）到鲍勃（Bob）。

cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
cmd.ExecuteNonQuery();

此UPDATE语句不正确。

$ mono notrans_error.exe 

Unhandled Exception: Mono.Data.Sqlite.SqliteException: SQLite error
no such table: Friend

我们收到与上一个示例相同的错误消息。

sqlite> SELECT * FROM Friends;
1|Thomas
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Jane

但是这一次，第一个UPDATE语句被保存，第二个不是。