SqlHelper类

 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