VB.NET 数据操作封装

1、查询数据集

 '获取查询数据集    Public Overloads Function QueryCommand(ByRef cmd As SqlCommand, Optional ByRef errMsg As String = "") As DataTable        errMsg = ""        Try            If cmd.Connection Is Nothing Then                GetSqlConnection()                cmd.Connection = Me.cn                Me.openSqlConnection(Me.cn)            End If            Dim ds As New DataSet            Dim ap As SqlDataAdapter = New SqlDataAdapter(cmd)            ap.Fill(ds, "table01")            cmd.Dispose()            ap.Dispose()            ds.Dispose()            Return ds.Tables(0)        Catch ex As Exception            errMsg = "执行QueryCommand失败" & Chr(13) & ex.Message            WriteLog("执行QueryCommand失败" & Chr(13) & "SQL:" & cmd.CommandText & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)            Return Nothing        End Try    End Function2、执行SQL查询
Public Function QuerySQL(sqlstr As String, ByRef errmsg As String) As DataTable        errmsg = ""        Dim cmd As New SqlCommand        Dim errcode As String = TenpayUtil.getTimestamp        Try            Me.GetSqlConnection()            Me.openSqlConnection(Me.cn)            cmd.Connection = Me.cn            cmd.CommandText = sqlstr            errmsg = ""            Return QueryCommand(cmd)        Catch ex As Exception            errmsg = "执行查询出错，错误码:" & errcode            WriteLog(errmsg & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)            Return Nothing        Finally            Me.closeSqlConnection(Me.cn)            If cmd IsNot Nothing Then                cmd.Dispose()            End If        End Try    End Function3、执行存储过程，无返回数据集
Public Function ExecSQL(sqlstr As String, ByRef errmsg As String, Optional ByRef sqltran As SqlTransaction = Nothing) As Integer        errmsg = ""        Dim cmd As New SqlCommand        Dim errcode As String = TenpayUtil.getTimestamp        Try            If sqltran IsNot Nothing Then                If sqltran.Connection IsNot Nothing Then                    cmd.Connection = sqltran.Connection                    cmd.Transaction = sqltran                Else                    Me.GetSqlConnection()                    Me.openSqlConnection(Me.cn)                    cmd.Connection = Me.cn                End If            Else                Me.GetSqlConnection()                Me.openSqlConnection(Me.cn)                cmd.Connection = Me.cn            End If            cmd.CommandText = sqlstr            errmsg = ""            Return cmd.ExecuteNonQuery        Catch ex As Exception            errmsg = "执行SQL出错，错误码:" & errcode            WriteLog(errmsg & "SQL:" & sqlstr & Chr(13) & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)            Return 0        Finally            If sqltran Is Nothing Then                Me.closeSqlConnection(Me.cn)            End If            If cmd IsNot Nothing Then                cmd.Dispose()            End If        End Try    End Function4、执行存储过程，有数据集返回、有参数返回
    Sub execPRoc(procName As String, inputs As Dictionary(Of String, Object), ByRef outputs As Dictionary(Of String, SqlParameter), ByRef errMsg As String, ByRef resTable As DataTable, Optional ByRef sqlTran As SqlTransaction = Nothing)        '事务优先        Dim errcode As String = TenpayUtil.getTimestamp        Dim cmd As SqlCommand        Dim sqlpara As SqlParameter        If inputs Is Nothing Then            errMsg = "inputs不能为Nothing"            Exit Sub        End If        If outputs Is Nothing Then            errMsg = "outputs不能为Nothing"            Exit Sub        End If        Try            cmd = New SqlCommand(procName)            If sqlTran IsNot Nothing Then                If sqlTran.Connection IsNot Nothing Then                    cmd.Connection = sqlTran.Connection                    cmd.Transaction = sqlTran                Else                    Me.cn = GetSqlConnection()                    Me.openSqlConnection(Me.cn)                    cmd.Connection = Me.cn                End If            Else                Me.cn = GetSqlConnection()                Me.openSqlConnection(Me.cn)                cmd.Connection = Me.cn            End If            cmd.CommandType = CommandType.StoredProcedure            '输入参数            For Each key As String In inputs.Keys                sqlpara = getSQLParaByObj(key, inputs(key))                cmd.Parameters.Add(sqlpara)            Next            '输出参数            If outputs IsNot Nothing Then                For Each key As String In outputs.Keys                    If outputs(key) IsNot Nothing Then                        outputs(key).Direction = ParameterDirection.Output                        cmd.Parameters.Add(outputs(key))                    End If                Next            End If            cmd.ExecuteNonQuery()            Dim ds As New DataSet            Dim ap As SqlDataAdapter = New SqlDataAdapter(cmd)            ap.Fill(ds, "table01")            cmd.Dispose()            ap.Dispose()            ds.Dispose()            resTable = ds.Tables(0)        Catch ex As SqlException            If ex.Number <> 266 Then                errMsg = "执行" & procName & "出错，错误码:" & errcode                WriteLog(errMsg & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)            End If        Finally            If sqlTran Is Nothing Then                Me.closeSqlConnection(Me.cn)            End If        End Try    End Sub