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 Function

2、执行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 Function

3、执行存储过程，无返回数据集

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 Function

4、执行存储过程，有数据集返回、有参数返回

    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