vB.net DbHelper类（完整）

本人经过多次使用，无需修改可以直接使用的数据库类

Imports System
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.Common
Imports System.Collections.Generic

Namespace Tianli.DBUtility

    '修改成實際項目的命名空間名稱 
    ''' <summary> 
    ''' 資料訪問基礎類(基於Oracle) 
    ''' 可以用戶可以修改滿足自己專案的需要。 
    ''' </summary> 
    Public MustInherit Class DbHelperSQL

        ' Protected Shared connectionString As String = ConfigurationSettings.AppSettings("conectiongstring")
        Protected Shared connectionString As String = PubConstant.ConnectionString

        Public Sub New()
        End Sub

#Region "公用方法"

        Public Shared Function Exists(ByVal strSql As String, ByVal ParamArray cmdParms As SqlParameter()) As Boolean
            Dim obj As Object = Getsingle(strSql, cmdParms)
            Dim cmdresult As Integer
            If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                cmdresult = 0
            Else
                cmdresult = Integer.Parse(obj.ToString())
            End If
            If cmdresult = 0 Then
                Return False
            Else
                Return True
            End If
        End Function
        Public Shared Function Exists(ByVal strSql As String) As Boolean
            Dim obj As Object = Getsingle(strSql)
            Dim cmdresult As Integer
            If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                cmdresult = 0
            Else
                cmdresult = Integer.Parse(obj.ToString())
            End If
            If cmdresult = 0 Then
                Return False
            Else
                Return True
            End If
        End Function
#End Region

#Region "執行簡單SQL語句"
        ''' <summary>
        ''' 执行sql语句，返回影响的记录数
        ''' </summary>
        ''' <param name="Sqlstring">sql语句</param>
        ''' <returns>影响记录数据</returns>
        ''' <remarks></remarks>
        Public Shared Function ExecuteSql(ByVal Sqlstring As String) As Integer
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim trans As SqlTransaction = connection.BeginTransaction
            Dim cmd As New SqlCommand
            cmd.Connection = connection
            cmd.Transaction = trans
            cmd.CommandText = Sqlstring
            Try
                Dim row As Integer = cmd.ExecuteNonQuery
                trans.Commit()
                Return row
            Catch ex As Exception
                trans.Rollback()
                Throw New Exception(ex.Message)
            End Try

        End Function


        Public Shared Sub ExecuteSqltran(ByVal sqlstringList As ArrayList)
            Dim conn As New SqlConnection(connectionString)
            conn.Open()
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            Dim trans As SqlTransaction = conn.BeginTransaction
            Dim cmd As New SqlCommand
            cmd.Connection = conn
            cmd.Transaction = trans
            Try
                For n As Integer = 0 To sqlstringList.Count - 1
                    Dim strsql As String = sqlstringList(n).ToString()
                    If strsql.Trim().Length > 1 Then
                        cmd.CommandText = strsql
                        cmd.ExecuteNonQuery()
                    End If
                Next
                trans.Commit()
            Catch ex As Exception
                trans.Rollback()
                Throw New Exception(ex.Message)
            End Try
        End Sub

        ''' <summary> 
        ''' 執行一條計算查詢結果語句，返回查詢結果（object）。 
        ''' </summary> 
        ''' <param name="SQLString">計算查詢結果語句</param> 
        ''' <returns>查詢結果（object）</returns> 
        Public Shared Function Getsingle(ByVal sqlstring) As Object
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim cmd As New SqlCommand(sqlstring, connection)
            Try
                Dim obj As Object = cmd.ExecuteScalar()
                If ([Object].Equals(obj, Nothing)) Or ([Object].Equals(obj, System.DBNull.Value)) Then
                    Return Nothing
                Else
                    Return obj
                End If
            Catch ex As Exception
                connection.Close()
                Throw New Exception(ex.Message)
            End Try

        End Function

        ''' <summary> 
        ''' 執行查詢語句，返回SqlExecuteReader 
        ''' </summary> 
        ''' <param name="strSQL">查詢語句</param> 一般用於聚集函數返回值
        ''' <returns>SqlExecuteReader</returns> 
        Public Shared Function SqlExecuteReader(ByVal strSQL As String) As SqlDataReader
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim cmd As New SqlCommand(strSQL, connection)
            Try
                Dim myReader As SqlDataReader = cmd.ExecuteReader()
                Return myReader
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
        End Function

        ''' <summary> 
        ''' 執行帶一個存儲過程參數的的SQL語句。 
        ''' </summary> 
        ''' <param name="SQLString">SQL語句</param> 
        ''' <param name="content">參數內容,比如一個欄位是格式複雜的文章，有特殊符號，可以通過這個方式添加</param> 
        ''' <returns>影響的記錄數</returns> 

        Public Shared Function ExecuteSql(ByVal SQLString As String, ByVal content As String) As Integer
            Dim connection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand(SQLString, connection)
            Dim myParameter As New System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText)
            myParameter.Value = content
            cmd.Parameters.Add(myParameter)
            Try
                connection.Open()
                Dim rows As Integer = cmd.ExecuteNonQuery()
                Return rows
            Catch E As System.Data.SqlClient.SqlException
                Throw New Exception(E.Message)
            Finally
                cmd.Dispose()
                connection.Close()
            End Try
        End Function

        ''' <summary>
        ''' 返回最大值
        ''' </summary>
        ''' <param name="FieldName">字段名称</param>
        ''' <param name="TableName">表名称</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetMaxID(ByVal FieldName As String, ByVal TableName As String) As Integer
            Dim strsql As String = "select max(" + FieldName + ")+1 from " + TableName
            Dim obj As Object = Getsingle(strsql)
            If obj Is Nothing Then
                Return 1
            Else
                Return Integer.Parse(obj.ToString())
            End If
        End Function

        ' <summary> 
        ' 向資料庫裏插入圖像格式的欄位(和上面情況類似的另一種實例) 
        ' </summary> 
        ' <param name="strSQL">SQL語句</param> 
        ' <param name="fs">圖像位元組,資料庫的欄位類型為image的情況</param> 
        ' <returns>影響的記錄數</returns> 

        '//保存SQL server2000的Image
        'string pathName; 
        'pathName = this.openFileDialog1.FileName; 
        '//將圖像讀入到字節數組 
        'System.IO.FileStream fs = new System.IO.FileStream(pathName,System.IO.FileMode.Open,System.IO.FileAccess.Read); 
        'byte[] buffByte = new byte[fs.Length]; 
        'fs.Read(buffByte,0,(int)fs.Length); 
        'fs.Close(); 
        'fs = null; 

        'public byte[] ConvertImage(Image image) 
        '{ 
        '    FileStream fs=new FileStream("imagetemp",FileMode.Create,FileAccess.Write,FileShare.None); 
        '    BinaryFormatter bf = new BinaryFormatter(); 
        '    bf.Serialize(fs,(object)image); 
        '    fs.Close(); 
        '    fs=new FileStream("imagetemp",FileMode.Open,FileAccess.Read,FileShare.None); 
        '    byte[] bytes = new byte[fs.Length]; 
        '    fs.Read(bytes,0,(int)fs.Length); 
        '    fs.Close(); 
        '    return bytes; 
        '} 

        'public Image ReadImage(byte[] bytes) 
        '{ 
        '　　FileStream fs=new FileStream("imagetemp1",FileMode.Create,FileAccess.Write,FileShare.None); 
        '　　foreach(byte a in bytes) 
        '　　{ 
        '　　　　fs.WriteByte(a); 
        '　　} 
        '    fs.Close(); 
        '    fs=new FileStream("imagetemp1",FileMode.Open,FileAccess.Read,FileShare.None); 
        '    BinaryFormatter bf = new BinaryFormatter(); 
        '    object obj=bf.Deserialize(fs); 
        '　　fs.Close(); 
        '　　return (Image)obj; 
        '} 
        Public Shared Function ExecuteSqlInsertImg(ByVal strSQL As String, ByVal fs As Byte()) As Integer
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim cmd As New SqlCommand(strSQL, connection)
            Dim myParameter As New SqlParameter("@fs", SqlDbType.Image)
            myParameter.Value = fs
            cmd.Parameters.Add(myParameter)
            Try
                Dim rows As Integer = cmd.ExecuteNonQuery
                Return rows
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                cmd.Dispose()
                connection.Close()
            End Try
        End Function


        ''' <summary> 
        ''' 執行查詢語句，返回DataSet 
        ''' </summary> 
        ''' <param name="SQLString">查詢語句</param> 
        ''' <returns>DataSet</returns>

        Public Shared Function Query(ByVal SQLString As String) As DataSet
            Dim connection As New SqlConnection(connectionString)
            Dim ds As New DataSet
            Try
                connection.Open()
                Dim myda As New SqlDataAdapter(SQLString, connection)
                myda.Fill(ds)
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
            Return ds
        End Function

#End Region

#Region "消息模塊"
        'Public Shared Function Show(ByVal page As System.Web.UI.Page, ByVal msg As String)
        '    page.RegisterStartupScript("message", "<script language='javascript' defer>alert('" + msg.ToString() + "');</script>")
        'End Function
#End Region

#Region "執行帶參數的SQL語句"

        Private Shared Sub PrepareCommand(ByRef cmd As SqlCommand, ByRef conn As SqlConnection, ByRef trans As SqlTransaction, ByRef cmdText As String, ByRef cmdParms As SqlParameter())
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            cmd.Connection = conn
            cmd.CommandText = cmdText
            If Not trans Is Nothing Then
                cmd.Transaction = trans
            End If

            cmd.CommandType = CommandType.Text
            If Not cmdParms Is Nothing Then
                For Each parm As SqlParameter In cmdParms
                    cmd.Parameters.Add(parm)
                Next
            End If
        End Sub

        Public Shared Function ExecuteSql(ByVal SQLString As String, ByVal ParamArray cmdParms As SqlParameter()) As Integer
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim trans As SqlTransaction = connection.BeginTransaction
            Dim cmd As New SqlCommand
            Try
                PrepareCommand(cmd, connection, trans, SQLString, cmdParms)
                Dim rows As Integer = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                trans.Commit()
                Return rows
            Catch ex As Exception
                trans.Rollback()
                Throw New Exception(ex.Message)
            Finally
                connection.Close()
                cmd.Dispose()
            End Try
        End Function


        '*
        ' Dim parameters As OracleParameter() = {New OracleParameter("DPPM_", OracleType.Number), New OracleParameter("Issue_id_", OracleType.Number), New OracleParameter("code1_id_", OracleType.Number), New OracleParameter("defectcode_id_", OracleType.Number)}
        ' parameters(0).Value = dppm
        ' parameters(1).Value = issue_id
        ' parameters(2).Value = code1_id
        ' parameters(3).Value = defect_code_id
        ' Return ITSC.DbHelperSQL.ExecuteSP("Updatecsm_issue_code", parameters)
        '*'
        Private Shared Sub PrepareCommandSP(ByVal cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdText As String, ByVal cmdParms As SqlParameter())
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            cmd.Connection = conn
            If Not trans Is Nothing Then
                cmd.Transaction = trans
            End If
            cmd.CommandText = cmdText
            cmd.CommandType = CommandType.StoredProcedure
            If Not cmdParms Is Nothing Then
                For Each parm As SqlParameter In cmdParms
                    cmd.Parameters.Add(parm)
                Next
            End If
        End Sub


        Public Shared Function ExecuteSP(ByVal SP As String, ByVal ParamArray cmdParms As SqlParameter()) As Integer
            Dim connection As New SqlConnection(connectionString)
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
            Dim trans As SqlTransaction = connection.BeginTransaction()
            Dim cmd As New SqlCommand
            Try
                PrepareCommandSP(cmd, connection, trans, SP, cmdParms)
                Dim rows As Integer = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                trans.Commit()
                Return rows
            Catch ex As System.Data.SqlClient.SqlException
                trans.Rollback()
                Throw New Exception(ex.Message)
            End Try
        End Function


        ''' <summary> 
        ''' 執行一條計算查詢結果語句，返回查詢結果（object）。 
        ''' </summary> 
        ''' <param name="SQLString">計算查詢結果語句</param> 
        ''' <returns>查詢結果（object）</returns> 
        Public Shared Function GetSingle(ByVal SQLString As String, ByVal ParamArray cmdParms As SqlParameter()) As Object
            Dim connection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand
            Try
                PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
                Dim obj As Object = cmd.ExecuteScalar()
                cmd.Parameters.Clear()
                If ([Object].Equals(obj, Nothing)) OrElse ([Object].Equals(obj, System.DBNull.Value)) Then
                    Return Nothing
                Else
                    Return obj
                End If
            Catch e As System.Data.SqlClient.SqlException
                Throw New Exception(e.Message)
            End Try
        End Function

        ''' <summary>
        ''' 执行查询语句，返回DataReader
        ''' </summary>
        ''' <param name="SQLString">查询语句</param>
        ''' <param name="cmdParms">查询参数</param>
        ''' <returns>DataReader</returns>
        ''' <remarks>DataReader</remarks>
        Public Shared Function ExecuteReader(ByVal SQLString As String, ByVal ParamArray cmdParms As SqlParameter()) As SqlDataReader
            Dim connection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand
            Try
                PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
                Dim myReader As SqlDataReader = cmd.ExecuteReader()
                cmd.Parameters.Clear()
                Return myReader
            Catch e As System.Data.SqlClient.SqlException
                Throw New Exception(e.Message)
            End Try
        End Function


        ''' <summary> 
        ''' 執行查詢語句，返回DataSet 
        ''' </summary> 
        ''' <param name="SQLString">查詢語句</param> 
        ''' <returns>DataSet</returns> 
        Public Shared Function Query(ByVal SQLString As String, ByVal ParamArray cmdParms As SqlParameter()) As DataSet
            Dim connection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand
            PrepareCommand(cmd, connection, Nothing, SQLString, cmdParms)
            Dim Myda As New SqlDataAdapter(cmd)
            Dim ds As New DataSet
            Try
                Myda.Fill(ds, "ds")
                cmd.Parameters.Clear()
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message)
            End Try
            Return ds
        End Function

        ''' <summary> 
        ''' 執行多條SQL語句，實現資料庫事務。 
        ''' </summary> 
        ''' <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的OracleParameter[]）</param> 
        Public Shared Sub ExecuteSqlTran(ByVal SQLStringList As Hashtable)
            Dim conn As New SqlConnection(connectionString)
            conn.Open()
            Dim trans As SqlTransaction = conn.BeginTransaction()
            Dim cmd As New SqlCommand
            Try
                '迴圈 
                For Each myDE As DictionaryEntry In SQLStringList
                    Dim cmdText As String = myDE.Key.ToString()
                    Dim cmdParms As SqlParameter() = DirectCast(myDE.Value, SqlParameter())
                    PrepareCommand(cmd, conn, trans, cmdText, cmdParms)
                    Dim val As Integer = cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                    trans.Commit()
                Next
            Catch
                trans.Rollback()
                Throw
            End Try
        End Sub
#End Region

#Region "存儲過程操作"

        ''' <summary> 
        ''' 執行存儲過程 
        ''' </summary> 
        ''' <param name="storedProcName">存儲過程名</param> 
        ''' <param name="parameters">存儲過程參數</param> 
        ''' <returns>OracleDataReader</returns> 
        Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter()) As SqlDataReader
            Dim connection As New SqlConnection(connectionString)
            Dim returnReader As SqlDataReader
            connection.Open()
            Dim command As SqlCommand = BuildQueryCommand(connection, storedProcName, parameters)
            command.CommandType = CommandType.StoredProcedure
            returnReader = command.ExecuteReader()
            Return returnReader
        End Function

        ''' <summary> 
        ''' 執行存儲過程 
        ''' </summary> 
        ''' <param name="storedProcName">存儲過程名</param> 
        ''' <param name="parameters">存儲過程參數</param> 
        ''' <param name="tableName">DataSet結果中的表名</param> 
        ''' <returns>DataSet</returns> 
        Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter(), ByVal tableName As String) As DataSet
            Dim connection As New SqlConnection(connectionString)
            Dim dataSet As New DataSet
            connection.Open()
            Dim sqlDA As New SqlDataAdapter
            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters)
            sqlDA.Fill(dataSet, tableName)
            connection.Close()
            Return dataSet
        End Function
        ''' <summary> 
        ''' 構建 OracleCommand 物件(用來返回一個結果集，而不是一個整數值) 
        ''' </summary> 
        ''' <param name="connection">資料庫連接</param> 
        ''' <param name="storedProcName">存儲過程名</param> 
        ''' <param name="parameters">存儲過程參數</param> 
        ''' <returns>OracleCommand</returns> 
        Private Shared Function BuildQueryCommand(ByVal connection As SqlConnection, ByVal storedProcName As String, ByVal parameters As IDataParameter()) As SqlCommand
            Dim command As New SqlCommand(storedProcName, connection)
            command.CommandType = CommandType.StoredProcedure
            For Each parameter As SqlParameter In parameters
                command.Parameters.Add(parameter)
            Next
            Return command
        End Function

        ''' <summary> 
        ''' 執行存儲過程，返回影響的行數 
        ''' </summary> 
        ''' <param name="storedProcName">存儲過程名</param> 
        ''' <param name="parameters">存儲過程參數</param> 
        ''' <param name="rowsAffected">影響的行數</param> 
        ''' <returns></returns> 
        Public Shared Function RunProcedure(ByVal storedProcName As String, ByVal parameters As IDataParameter(), ByRef rowsAffected As Integer) As Integer
            Dim connection As New SqlConnection(connectionString)
            Dim result As Integer
            connection.Open()
            Dim command As SqlCommand = BuildIntCommand(connection, storedProcName, parameters)
            rowsAffected = command.ExecuteNonQuery()
            result = CInt(command.Parameters("ReturnValue").Value)
            'Connection.Close(); 
            Return result
        End Function

        ''' <summary> 
        ''' 創建 OracleCommand 物件實例(用來返回一個整數值) 
        ''' </summary> 
        ''' <param name="storedProcName">存儲過程名</param> 
        ''' <param name="parameters">存儲過程參數</param> 
        ''' <returns>OracleCommand 物件實例</returns> 
        Private Shared Function BuildIntCommand(ByVal connection As SqlConnection, ByVal storedProcName As String, ByVal parameters As IDataParameter()) As SqlCommand
            Dim command As SqlCommand = BuildQueryCommand(connection, storedProcName, parameters)
            command.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, _
            0, String.Empty, DataRowVersion.[Default], Nothing))
            Return command
        End Function
#End Region
    End Class
End Namespace