SQLCommand与数据库事务

SQLCommand与数据库事务

SQLCommand支持数据库事务。

SQLCommand有三个和事务相关的方法，分别是：

BeginTransaction
开启事务
　
Commit
提交事务
　
Rollback
回滚事务

提示：

1、同一个事务的所有操作针对的必须是同一个数据源。

2、事务一旦开启，在提交或回滚之前，只能通过开启事务的SQLCommand执行SQL语句，不能有其他读写后台数据库的操作。

3、本节的示例代码使用了Try语句，如果你还不熟悉此语句，可参考：捕获异常错误

示例一

假定有两个表，分别是订单表(Orders)和订单明细表(OrderDetails)，两个表通过订单编号(OrderID)建立关联。

如果要删除指定编号的订单及其对应的订单明细，且希望两个删除操作是原子性质，不能出现一个表删除成功，另一个表删除失败的情况，代码为：

Dim cmd As new SQLCommand()
cmd.ConnectionName = "Orders" '指定数据源
Try
    Dim id As Integer = 10258
    cmd.BeginTransaction() '开启事务
    cmd.CommandText = "Delete From OrderDetails Where OrderID = " & id
    cmd.ExecuteNonQuery()
    '继续使用同一个SQLCommand执行其它SQL语句.
    cmd.CommandText = "Delete From Orders Where OrderID = " & id
    cmd.ExecuteNonQuery()
    cmd.Commit() '提交事务
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

如果已经在Foxtable中加载了数据，可以在提交事务之后，用RemoveFor方法移除已经在后台删除的行，例如：

Dim cmd As new SQLCommand()
cmd.ConnectionName = "Orders" '指定数据源
Try
    Dim id As Integer = 10260
    cmd.BeginTransaction() '开启事务
    cmd.CommandText = "Delete From OrderDetails Where OrderID = " & id
    cmd.ExecuteNonQuery()
    '继续使用同一个SQLCommand执行其它SQL语句.
    cmd.CommandText = "Delete From Orders Where OrderID = " & id
    cmd.ExecuteNonQuery()
    cmd.Commit() '提交事务
    DataTables("Orders").RemoveFor("OrderID = " & id) '从DataTable移除已经成功删除的数据
    DataTables("OrderDetails").RemoveFor("OrderID = " & id)
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

示例二

同样，你可以用通过事务实现"原子"更新订单及其对应的订单明细，参考代码：

Dim cmd As new SQLCommand()
cmd.ConnectionName = "Orders" '指定数据源
Try
    Dim id As Integer = 10258
    cmd.BeginTransaction() '开启事务
    cmd.CommandText = "Update Orders Set ShipCountry = 'China' Where OrderID = " & id
    cmd.ExecuteNonQuery()
    '继续使用同一个SQLCommand执行其它SQL语句.
    cmd.CommandText = "Update OrderDetails Set Discount= 0.3 Where OrderID = " & id
    cmd.ExecuteNonQuery()
    cmd.Commit() '提交事务
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

示例三

通过ExcuteReader生成的DataTable支持事务，例如：

Dim pd As DataTable '订单表
Dim od As DataTable '订单明细表
Dim cmd As New SqlCommand()
cmd.ConnectionName = "Orders"
Try
    cmd.BeginTransaction()
    cmd.CommandText = "Select * From Orders Where OrderID = 10259"
    pd = cmd.ExecuteReader(True) '加上参数True,生成的DataTable可修改保存
    cmd.CommandText = "Select * From OrderDetails Where OrderID = 10259"
    od = cmd.ExecuteReader(True)
    If pd.DataRows.Count > 0 Then
        pd.DataRows(0)("OrderDate") = Date.Today
    End If
    For Each dr As DataRow In od.DataRows
        If dr("Quantity") > 50 Then
            dr("Discount") = 0.2
        Else
            dr("Discount") = 0.1
        End If
    Next
    pd.Save()
    od.Save()
    If pd.HasChanges OrElse od.HasChanges Then '如果任何一个表保存失败
        cmd.Rollback() '则回滚事务
    Else
        cmd.Commit() '否则提交事务
    End If
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

提示：HasChanges返回False，说明此DataTable存在没有保存的已修改数据，也就是说保存失败了.

示例四

上面是通过ExcuteReader修改数据的例子，再来一个增加数据的例子，下面的代码增加了一个订单，以及对应的两条订单明细：

Dim pd As DataTable '订单表
Dim od As DataTable '订单明细表
Dim cmd As New SqlCommand()
cmd.ConnectionName = "Orders"
Try
    cmd.BeginTransaction()
    cmd.CommandText = "Select * From Orders Where OrderID Is Null" '无需加载任何数据
    pd = cmd.ExecuteReader(True)
    cmd.CommandText = "Select * From OrderDetails Where OrderID Is Null"
    od = cmd.ExecuteReader(True)
    Dim dr As DataRow = pd.AddNew() '增加一个订单
    dr("OrderID") = 11086
    dr("CustomerID") = "PICCO"
    dr("EmployeeID") = 2
    dr("OrderDate") = Date.Today
    dr = od.AddNew() '增加第一个订单明细
    dr("OrderID") = 11086
    dr("ProductID") = 37
    dr("UnitPrice") = 15.2
    dr("Quantity") = 50
    dr = od.AddNew() '增加第二个订单明细
    dr("OrderID") = 11086
    dr("ProductID") = 57
    dr("UnitPrice") = 20.7
    dr("Quantity") = 28
    pd.Save()
    od.Save()
    If pd.HasChanges OrElse od.HasChanges Then '如果任何一个表保存失败
        cmd.Rollback() '则回滚事务
    Else
        cmd.Commit() '否则提交事务
    End If
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

示例五

假定Orders表的主键为OrdersID列，且此列为自动增量列，那么示例四的代码是没有办法正常运行的，因为作为自动增量列，OrdersID的值是自动生成的，而且是在保存后才生成。

我们必须想办法获取某个新增订单保存后的OrdersID，并赋值给对应的订单明细的OrdersID列，否则新增的订单(Order)及其对应的订单明细(OrderDetails)之间将会失去联系。

Foxtable处理这种问题非常简单，因为每次保存新增行之后，Foxtable都会自动访问后台数据库，获取新增行的主键值并写入到主键列中。

修改后的代码如下，留意加粗显示的代码：

Dim pd As DataTable '订单表
Dim od As DataTable '订单明细表
Dim cmd As New SqlCommand()
cmd.ConnectionName = "Orders"
Try
    cmd.BeginTransaction()
    cmd.CommandText = "Select * From Orders Where OrderID Is Null" '无需加载任何数据
    pd = cmd.ExecuteReader(True)
    cmd.CommandText = "Select * From OrderDetails Where OrderID Is Null"
    od = cmd.ExecuteReader(True)
    Dim dr As DataRow = pd.AddNew() '增加一个订单,注意无需给新增行指定OrderID.
    dr("CustomerID") = "PICCO"
    dr("EmployeeID") = 2
    dr("OrderDate") = Date.Today
    pd.Save() '必须提前保存Orders表, 这样OrdersID列的值才是实际生成的主键值.
    Dim tr = od.AddNew() '增加第一个订单明细
    tr("OrderID") = dr("OrderID") '将自动生成的OrderID赋值给新增的订单明细
    tr("ProductID") = 37
    tr("UnitPrice") = 15.2
    tr("Quantity") = 50
    tr = od.AddNew() '增加第二个订单明细
    tr("OrderID") = dr("OrderID") '将自动生成的OrderID赋值给新增的订单明细
    tr("ProductID") = 57
    tr("UnitPrice") = 20.7
    tr("Quantity") = 28
    od.Save()
    If pd.HasChanges OrElse od.HasChanges Then '如果任何一个表保存失败
        cmd.Rollback() '则回滚事务
    Else
        cmd.Commit() '否则提交事务
    End If
Catch ex As Exception '如果出错
    cmd.Rollback() '则回滚事务
    MessageBox.Show(ex.Message, "错误",MessageBoxButtons.OK,MessageBoxIcon.Error)
End Try

本页地址：http://www.foxtable.com/webhelp/topics/3312.htm