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

 

    '**********************************************************************
    '* 函数名称: 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.csdn.net/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