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