使用 Visual Basic 的 SQLite 事务 - 《ZetCode 中文系列教程》

显式回滚调用
错误

原文： http://zetcode.com/db/sqlitevb/trans/

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

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

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

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

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

Option Strict On
Imports Mono.Data.Sqlite
Module Example
    Sub Main()
        Dim cs As String = "URI=file:test.db"
        Using con As New SqliteConnection(cs)
            con.Open()
            Using cmd As New SqliteCommand(con)
                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()
            End Using
            con.Close()
        End Using
    End Sub
End Module

我们创建一个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()方法启动自定义事务。

Option Strict On
Imports Mono.Data.Sqlite
Module Example
    Sub Main()
        Dim cs As String = "URI=file:test.db"
        Using con As New SqliteConnection(cs)
            con.Open()
            Dim tr As SqliteTransaction = con.BeginTransaction()
            Using tr 
                Using cmd As New SqliteCommand(con)
                    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()
                End Using
                tr.Commit()
            End Using
            con.Close()
        End Using
    End Sub
End Module

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

Dim tr As SqliteTransaction = con.BeginTransaction()

BeginTransaction()方法启动事务。

cmd.Transaction = tr

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

tr.Commit()

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

显式回滚调用

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

Option Strict On
Imports Mono.Data.Sqlite
Module Example
    Sub Main()
        Dim cs As String = "URI=file:test.db"
        Dim con As SqliteConnection     
        Dim tr As SqliteTransaction
        Dim cmd As SqliteCommand
        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 ex As SqliteException
            Console.WriteLine("Error: {0}",  ex.ToString())
            If tr IsNot Nothing
                Try                 
                    tr.Rollback()                    
                Catch ex2 As SqliteException
                    Console.WriteLine("Transaction rollback failed.")
                    Console.WriteLine("Error: {0}",  ex2.ToString())
                Finally                
                    tr.Dispose()
                End Try
            End If
        Finally 
            If cmd IsNot Nothing
                cmd.Dispose()
            End If
            If tr IsNot Nothing
                tr.Dispose()
            End If
            If con IsNot Nothing
                Try                 
                    con.Close()
                Catch ex As SqliteException                
                    Console.WriteLine("Closing connection failed.")
                    Console.WriteLine("Error: {0}",  ex.ToString())
                Finally                
                    con.Dispose()
                End Try
            End If
        End Try    
    End Sub
End Module

我们创建自己的Try，Catch和Finally块，在其中处理可能的问题。

Catch ex As SqliteException
    Console.WriteLine("Error: {0}",  ex.ToString())
    If tr IsNot Nothing
        Try                 
            tr.Rollback()                    
        Catch ex2 As SqliteException
            Console.WriteLine("Transaction rollback failed.")
            Console.WriteLine("Error: {0}",  ex2.ToString())
        Finally                
            tr.Dispose()
        End Try
    End If

在创建Friends表的过程中引发异常时，我们将调用Rollback()方法。回滚事务也可能失败；我们检查这种情况。

If cmd IsNot Nothing
    cmd.Dispose()
End If
If tr IsNot Nothing
    tr.Dispose()
End If

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

If con IsNot Nothing
    Try                 
        con.Close()
    Catch ex As SqliteException                
        Console.WriteLine("Closing connection failed.")
        Console.WriteLine("Error: {0}",  ex.ToString())
    Finally                
        con.Dispose()
    End Try
End If

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

错误

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

Option Strict On
Imports Mono.Data.Sqlite
Module Example
    Sub Main()
        Dim cs As String = "URI=file:test.db"
        Using con As New SqliteConnection(cs)
            con.Open()
            Dim tr As SqliteTransaction = con.BeginTransaction()
            Using tr 
                Using cmd As New SqliteCommand(con)
                    cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
                    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()
                End Using
            End Using
            con.Close()
        End Using
    End Sub
End Module

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

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

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

$ mono 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。

Option Strict On
Imports Mono.Data.Sqlite
Module Example
    Sub Main()
        Dim cs As String = "URI=file:test.db"
        Using con As New SqliteConnection(cs)
            con.Open()
            Using cmd As New SqliteCommand(con)
                cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
                cmd.CommandText = "UPDATE Friends SET Name='Thomas' WHERE Id=1"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4"
                cmd.ExecuteNonQuery()
            End Using
            con.Close()
        End Using
    End Sub
End Module

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

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

此UPDATE语句不正确。

$ mono error2.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语句被保存。第二个不是。

在 SQLite Visual Basic 教程的这一部分中，我们处理了事务。