ZetCode

使用 Visual Basic 处理 SQLite 事务

最后修改于 2020 年 7 月 6 日

在本章中，我们将使用事务。首先，我们提供一些基本定义。然后我们将提供一些程序，演示如何使用事务。

事务是针对一个或多个数据库中的数据执行的数据库操作的原子单元。事务中所有 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 表并用数据填充它。我们没有显式地启动事务，也没有调用提交或回滚方法。但是数据被写入表中。这是因为默认情况下，我们在自动提交模式下工作。在这种模式下，每个 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 教程的这一部分中，我们使用了事务。