一个事务包含多个存储过程,经测试如果出现异常绝对可以回滚到起点
来源:互联网 发布:阿里云邮箱有客户端吗 编辑:程序博客网 时间:2024/05/14 21:36
'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
- 一个事务包含多个存储过程,经测试如果出现异常绝对可以回滚到起点
- SqlServer 较可以学习的存储过程 对事务,出现错误的导向
- 进程 vs 线程 :一个进程可以包含多个线程
- 进程 vs 线程 :一个进程可以包含多个线程
- mysql存储过程定义一个判断事务是否异常的错误变量
- 可以多个字段排序的分页存储过程
- SQL存储过程(包含事务/参数/嵌套调用/游标/循环)
- Mysql存储过程包含事务,且传入sql数据执行
- oracle 存储过程 是一个事务
- Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
- Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
- mysql c++ 调用存储过程返回多个结果集,如果选择适合自己的?
- 如果存储过程返回多个结果集,怎么取结果
- c#如果存储过程返回多个结果集,怎么取结果
- 根据存储过程包含某个关键字,查找相关所有存储过程信息及根据多个主键删除重复列
- SQL SERVER中 存储过程使用事务与捕获异常
- Enum扩展,可以包含多个属性
- 一个字符串分隔多个参数存储过程调试
- bzoj 1026 windy数(数位DP)
- Oracle的锁表与解锁
- JQuery.Ajax()的data参数类型
- jplayer播放器完整标准的写法应该是这样的
- 【有上下界最大费用可行流】HDU-4862 Jump
- 一个事务包含多个存储过程,经测试如果出现异常绝对可以回滚到起点
- Android 添加新的键值,自定义按键
- WebBrowser介绍——Javascript与C++互操作
- Android JAR包手动混淆操作
- 浅谈Redis数据库的键值设计
- 10.20国际财经解读,行情分析
- C++ boost库----share_from_this类的作用和实现原理
- iOS in-house发布基础知识
- Opencv2.4.9源码分析——FAST