sqlhelper vb.net版

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Public MustInherit Class SqlHelper
    ''' <summary>
    ''' 哈希表：缓存参数
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared parmCache As Hashtable = Hashtable.Synchronized(New Hashtable())
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="connectionString"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteNonquery(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Int32
        Dim cmd As SqlCommand = New SqlCommand
        Using conn As SqlConnection = New SqlConnection(connectionString)
            PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Int32 = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
            Return val
        End Using
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteNonQuery(ByVal connection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Int32
        Dim cmd As SqlCommand = New SqlCommand
        PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
        Dim val As Int32 = cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        Return val
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="trans"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteNonQuery(ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Int32
        Dim cmd As SqlCommand = New SqlCommand
        PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)
        Dim val As Int32 = cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        Return val
    End Function
    ''' <summary>
    ''' 返回数据集，可以包含多个表，这是我自己加上的，用以返回数据集，表
    ''' </summary>
    ''' <param name="connectionString"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataSet(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As DataSet
        Dim cmd As SqlCommand = New SqlCommand
        Using conn As SqlConnection = New SqlConnection(connectionString)
            PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
            Dim adp As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim ds As DataSet = New DataSet
            Try
                adp.Fill(ds)
                cmd.Parameters.Clear()
            Finally
                adp.Dispose()
            End Try
            Return ds
        End Using
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="connectionString"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteReader(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As SqlDataReader
        Dim cmd As SqlCommand = New SqlCommand
        Dim conn As SqlConnection = New SqlConnection(connectionString)
        Try
            PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
            Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            cmd.Parameters.Clear()
            Return rdr
        Catch ex As Exception
            conn.Close()
            Throw
        End Try
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="connectionString"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteScalar(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Object
        Dim cmd As SqlCommand = New SqlCommand
        Using connection As SqlConnection = New SqlConnection(connectionString)
            PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Object = cmd.ExecuteScalar()
            cmd.Parameters.Clear()
            Return val
        End Using
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="connection"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters">ParamArray 表示函数参数个数不确定C#中为params</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteScalar(ByVal connection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Object
        Dim cmd As SqlCommand = New SqlCommand
        PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
        Dim val As Object = cmd.ExecuteScalar()
        cmd.Parameters.Clear()
        Return val
    End Function
    ''' <summary>
    ''' add parameter array to the cache
    ''' </summary>
    ''' <param name="cacheKey"></param>
    ''' <param name="commandParameters">an array of SqlParamters to be cached</param>
    ''' <remarks></remarks>
    Public Shared Sub CacheParameters(ByVal cacheKey As String, ByVal ParamArray commandParameters As SqlParameter())
        parmCache(cacheKey) = commandParameters
    End Sub

    Public Shared Function GetCachedParameters(ByVal cacheKey As String) As SqlParameter()
        Dim cachedParms As SqlParameter() = CType(parmCache(cacheKey), SqlParameter())
        If cachedParms Is Nothing Then Return Nothing
        Dim clonedParms(cachedParms.Length - 1) As SqlParameter
        Dim i As Integer
        For i = 0 To cachedParms.Length - 1
            clonedParms(i) = CType(CType(cachedParms(i), ICloneable).Clone(), SqlParameter)
        Next
        Return clonedParms
    End Function
    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="cmd"></param>
    ''' <param name="conn"></param>
    ''' <param name="trans"></param>
    ''' <param name="cmdType"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="cmdParms"></param>
    ''' <remarks></remarks>
    Private Shared Sub PrepareCommand(ByVal cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As SqlParameter())
        If conn.State <> ConnectionState.Open Then conn.Open()
        cmd.Connection = conn
        cmd.CommandText = cmdText
        If trans IsNot Nothing Then cmd.Transaction = trans
        cmd.CommandType = cmdType
        If cmdParms IsNot Nothing Then
            Dim parm As SqlParameter
            For Each parm In cmdParms
                cmd.Parameters.Add(parm)
            Next
        End If
    End Sub
End Class