一个事务包含多个存储过程，经测试如果出现异常绝对可以回滚到起点

最新推荐文章于 2021-02-27 01:34:15 发布

stanforddsk

最新推荐文章于 2021-02-27 01:34:15 发布

阅读量851

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/StanfordDSK/article/details/40301541

版权

数据库专栏收录该内容

0 篇文章 0 订阅

订阅专栏

'1个事务，a.事务: 创建账单(父类), 此时会自动创建子类(这个账单包括哪些收费项目) b.根据上传的数据，修改收费项目应该对应的值

Public Shared Function AddUploadInditexBillSpecial(ByVal generalColCount As Integer, ByVal dsUploadData As DataSet, ByVal relationName As String) As String
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim tranAddBillSpecial As SqlTransaction
        Dim sqlda As SqlDataAdapter
        Dim dsNewBillData As DataSet
        Const STR_MAIN_TRANSACTION_NAME As String = "AddBillSpecial"
        Dim i As Integer = 0
        Dim ds As New DataSet

        Try
            Dim importData As DataTable = dsUploadData.Tables(0)
            Dim importDetailData As DataTable = dsUploadData.Tables(1)
            conn = New SqlConnection(DataSqlHelp.connection)
            conn.Open()
            tranAddBillSpecial = conn.BeginTransaction(STR_MAIN_TRANSACTION_NAME)
            For rowIndex As Integer = 0 To importData.Rows.Count - 1

Dim row As DataRow = importData.Rows(rowIndex)

                '1.创建账单(a存储过程)
                cmd = New SqlCommand("SP_AddInditexBillSpecial", conn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Transaction = tranAddBillSpecial
                cmd.CommandTimeout = 0

                cmd.Parameters.AddWithValue("@SupplierID", row("SupplierID").ToString().Trim())
                cmd.Parameters.AddWithValue("@Customer", row("Customer").ToString().Trim())
                cmd.Parameters.AddWithValue("@Location", row("Location").ToString().Trim())
                cmd.Parameters.AddWithValue("@BillType", row("BillType").ToString().Trim())
                cmd.Parameters.AddWithValue("@TransMode", row("TransMode").ToString().Trim())
                cmd.Parameters.AddWithValue("@BillMonth", row("BillMonth").ToString().Trim())
                cmd.Parameters.AddWithValue("@OperationTime", Convert.ToDateTime(row("OperationTime")).ToString("yyyyMMdd"))

                Dim paramNewBillId As SqlParameter = New SqlParameter("@newBillID", SqlDbType.NVarChar, 20)
                paramNewBillId.Direction = ParameterDirection.Output
                cmd.Parameters.Add(paramNewBillId)
                Dim paramErrorMsg As SqlParameter = New SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 200)
                paramErrorMsg.Direction = ParameterDirection.Output
                cmd.Parameters.Add(paramErrorMsg)

                sqlda = New SqlDataAdapter(cmd)
                dsNewBillData = New DataSet()
                sqlda.Fill(dsNewBillData)

                '2.更改Detail(b存储过程)
                If paramErrorMsg.Value = String.Empty Then
                    If (Not dsNewBillData.Tables(0) Is Nothing) AndAlso dsNewBillData.Tables(0).Rows.Count > 0 Then
                        Dim detailRows As DataRow() = row.GetChildRows(relationName)
                        If (Not detailRows Is Nothing) AndAlso detailRows.Length > 0 Then
                            For Each detailRow As DataRow In detailRows
                                Dim newBillDetailRow As DataRow = dsNewBillData.Tables(0).Select(String.Format("ChargeCode='{0}'", detailRow("ChargeCode").ToString().Trim()))(0)
                                For Each col As DataColumn In importDetailData.Columns
                                    If detailRow(col.ColumnName) Is Nothing OrElse detailRow(col.ColumnName) Is DBNull.Value Then
                                        detailRow(col.ColumnName) = newBillDetailRow(col.ColumnName)
                                    End If
                                Next

                                cmd = New SqlCommand("SP_UpdateBillSpecialDetail", conn)
                                cmd.CommandType = CommandType.StoredProcedure
                                cmd.Transaction = tranAddBillSpecial
                                cmd.CommandTimeout = 0

                                cmd.Parameters.AddWithValue("@SupplierID", detailRow("SupplierID").ToString())
                                cmd.Parameters.AddWithValue("@Cost", detailRow("Cost").ToString())
                                cmd.Parameters.AddWithValue("@Cost_UnitPrice", detailRow("Cost_UnitPrice").ToString())
                                cmd.Parameters.AddWithValue("@Cost_Qty", detailRow("Cost_Qty").ToString())
                                cmd.Parameters.AddWithValue("@Cost_InvoiceNo", DBNull.Value)
                                cmd.Parameters.AddWithValue("@Cost_Note", IIf(detailRow("Cost_Note") Is DBNull.Value, DBNull.Value, detailRow("Cost_Note").ToString()))
                                cmd.Parameters.AddWithValue("@Revenue", detailRow("Revenue").ToString())
                                cmd.Parameters.AddWithValue("@Revenue_UnitPrice", detailRow("Revenue_UnitPrice").ToString())
                                cmd.Parameters.AddWithValue("@Revenue_Qty", detailRow("Revenue_Qty").ToString())
                                cmd.Parameters.AddWithValue("@Revenue_InvoiceNo", DBNull.Value)
                                cmd.Parameters.AddWithValue("@accrualCost", DBNull.Value)
                                cmd.Parameters.AddWithValue("@accrualRevenue", DBNull.Value)
                                cmd.Parameters.AddWithValue("@Note", IIf(detailRow("Note") Is DBNull.Value, DBNull.Value, detailRow("Note").ToString()))
                                cmd.Parameters.AddWithValue("@BillSpecialDetailID", detailRow("BillSpecialDetailID").ToString())
                                cmd.Parameters.AddWithValue("@Operator", detailRow("Operator").ToString())
                                cmd.ExecuteNonQuery()
                            Next
                        End If
                    Else
                        Throw New Exception("Create Bill Failed")
                    End If
                Else
                    Throw New Exception(paramErrorMsg.Value)
                End If
                i = i + 1
            Next

            tranAddBillSpecial.Commit()
        Catch ex As Exception
            tranAddBillSpecial.Rollback()
            Return ex.Message
        Finally
            conn.Close()
            conn.Dispose()
        End Try

Return String.Empty
End Function