带有事务集合整理--更新中

最新推荐文章于 2023-11-09 16:09:43 发布

lsgis2001

最新推荐文章于 2023-11-09 16:09:43 发布

阅读量700

点赞数

CC 4.0 BY-SA版权

分类专栏： .NET使用笔记文章标签： dataset integer exception function string insert

本文链接：https://blog.youkuaiyun.com/lsgis2001/article/details/2047385

.NET使用笔记专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一种更新数据集并处理多个表的方法，确保在出现错误时能回滚事务，同时还提供了一个执行SQL数组中命令的事务提交示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

    '**********************************************************************
    '* 函数名称: UpdateDs
    '* 函数描述: 更新一个DATASET，可以更新多个表，如果有一个出错，要全部回滚事务.
    '* 参数列表: arrSql:输入DS 表格的查询字符串数组，从下标为0处开始执行SQL语句。
    '*           Ds:传入要修改的DS
    '*           arrTableName:DS中的表名，
    '* 返回值: x=更新后的DS
    '* 作        者: Itc-Lsg
    '* 创建日期: 2007-12-22
    '**********************************************************************
    Public Function UpdateDs(ByVal arrSql() As String, ByRef Ds As DataSet, ByVal arrTableName() As String) As DataSet

        Dim sqlcon As New SqlClient.SqlConnection(Me.ConnectionString)
        Dim s As SqlClient.SqlCommandBuilder
        Dim da As SqlClient.SqlDataAdapter
        Dim sTableName As String = ""
        Dim strSql As String = ""
        Dim nArrLen As Integer = 0
        Dim i As Integer = 0
        Dim tbChange As New DataTable
        Dim Trans As Data.SqlClient.SqlTransaction
        nArrLen = arrSql.Length
        If Not sqlcon.State = ConnectionState.Open Then
            sqlcon.Open()
        End If

        Try
            Trans = sqlcon.BeginTransaction
            For i = 0 To nArrLen - 1
                strSql = arrSql(i).Trim()
                sTableName = arrTableName(i)
                da = New SqlClient.SqlDataAdapter(strSql, sqlcon)
                s = New SqlClient.SqlCommandBuilder(da)

                If sTableName.Trim() = "" Then
                    tbChange = CType(Ds.Tables(0).GetChanges, DataTable)
                Else
                    tbChange = CType(Ds.Tables(sTableName).GetChanges, DataTable)
                End If
                da.SelectCommand.Transaction = Trans
                If Not tbChange Is Nothing Then
                    If sTableName.Trim() = "" Then
                        da.Update(Ds)
                        Ds.Tables(sTableName).Clear()
                        da.Fill(Ds)
                    Else
                        da.Update(Ds, sTableName)
                        Ds.Tables(sTableName).Clear()
                        da.Fill(Ds, sTableName)
                    End If
                    Ds.Merge(tbChange)

                End If
            Next
            Ds.AcceptChanges()

Trans.Commit()

            Return Ds
        Catch ex As Exception
            Throw ThrowErr(ex)
            Trans.Rollback()
        Finally
            sqlcon.Close()
            sqlcon.Dispose()

End Try
End Function

下面是另一种事务提交,没有亲自试验过.希望好用:

http://topic.youkuaiyun.com/u/20080116/16/94918fac-9ecc-4e9e-a097-46a3dfef371a.html

     '**********************************************************************
    '* 函数名称: GetCustomer
    '* 函数描述: 得到客户信息.不写参数得到系统默认客户.
    '* 参数列表: sCustomerNum,客户ID,默认为"",得到系统默认客户
    '*
    '* 返回值: 公用变量oCustomer值
    '* 作        者: Itc-Lsg
    '* 创建日期: 2008-01-18
    '**********************************************************************
    ''' <summary>
    ''' 执行无返回的查询执行一个数组,里面含有事务:Del,Update or Insert
    ''' </summary>
    ''' <param name="sqlstr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecSqlNonQuery(ByVal sqlstr As Array) As Integer

        Dim mySqlCommd As New SqlClient.SqlCommand
        Dim mySqlcon As New SqlClient.SqlConnection
        Dim Trans As SqlClient.SqlTransaction

        Try
            mySqlcon.ConnectionString = Me.ConnectionString
            mySqlcon.Open()
            mySqlCommd.CommandType = CommandType.Text
            mySqlCommd.Connection = mySqlcon
            Trans = mySqlcon.BeginTransaction()
            mySqlCommd.Transaction = Trans
            Dim i As Integer = 0
            For i = 0 To sqlstr.Length - 1
                mySqlCommd.CommandText = sqlstr(i)
                mySqlCommd.ExecuteNonQuery()
            Next i

Trans.Commit()

        Catch ex As Exception
            Throw ThrowErr(ex)
            Trans.Rollback()
        Finally
            mySqlcon.Close()
            mySqlcon.Dispose()
            mySqlcon = Nothing
            mySqlCommd.Dispose()
            mySqlCommd = Nothing

Trans = Nothing
End Try

End Function