SqlHelper

'这是在网上找的中文解释的SqlHelper
Imports System

Imports System.Configuration

Imports System.Data

Imports System.Data.SqlClient

Imports System.Collections



Namespace com.DataAccess




    ' <summary>

    ' SqlHelper类提供很高的数据访问性能, 

    ' 使用SqlClient类的通用定义.

    ' </summary>

    Public MustInherit Class SqlHelper

        Public Shared Function GetConnStr() As String

            Return My.Settings.constr

        End Function



        '定义数据库连接串

        '        Public Shared ReadOnly ConnectionStringLocalTransaction As String = ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
        Public Shared ReadOnly ConnectionStringLocalTransaction As String = GetConnStr()

        'public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;

        'public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;

        'public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;



        ' 存贮Cache缓存的Hashtable集合

        Private Shared parmCache As Hashtable = Hashtable.Synchronized(New Hashtable())



        ' <summary>

        ' 使用连接字符串，执行一个SqlCommand命令（没有记录返回）

        ' 使用提供的参数集.

        ' </summary>

        ' <remarks>

        ' 示例:  

        '  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="connectionString">一个有效的SqlConnection连接串</param>

        ' <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>

        ' <returns>受此命令影响的行数</returns>

        Public Shared Function ExecuteNonQuery(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Integer




            Dim cmd As New SqlCommand()



            Using conn As New SqlConnection(connectionString)


                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)

                Dim val As Integer = cmd.ExecuteNonQuery()

                cmd.Parameters.Clear()


                Return val
            End Using

        End Function



        ' <summary>

        ' 在一个存在的连接上执行数据库的命令操作

        ' 使用提供的参数集.

        ' </summary>

        ' <remarks>

        ' e.g.:  

        '  int result = ExecuteNonQuery(connection, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="conn">一个存在的数据库连接对象</param>

        ' <param name="commandType">命令类型CommandType (stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>

        ' <returns>受此命令影响的行数</returns>

        Public Shared Function ExecuteNonQuery(ByVal connection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Integer

            Dim cmd As New SqlCommand()



            PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)

            Dim val As Integer = cmd.ExecuteNonQuery()

            cmd.Parameters.Clear()

            Return val

        End Function



        ' <summary>

        ' 在一个事务的连接上执行数据库的命令操作

        ' 使用提供的参数集.

        ' </summary>

        ' <remarks>

        ' e.g.:  

        '  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="trans">一个存在的事务</param>

        ' <param name="commandType">命令类型CommandType (stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>

        ' <returns>受此命令影响的行数</returns>

        Public Shared Function ExecuteNonQuery(ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Integer


            Dim cmd As New SqlCommand()

            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)

            Dim val As Integer = cmd.ExecuteNonQuery()

            cmd.Parameters.Clear()

            Return val

        End Function



        ' <summary>

        ' 在一个连接串上执行一个命令，返回一个SqlDataReader对象

        ' 使用提供的参数.

        ' </summary>

        ' <remarks>

        ' e.g.:  

        '  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="connectionString">一个有效的SqlConnection连接串</param>

        ' <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>

        ' <returns>一个结果集对象SqlDataReader</returns>

        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 New SqlCommand()

            Dim conn As New SqlConnection(connectionString)



            ' 如果不存在要查询的对象，则发生异常

            ' 连接要关闭

            ' CommandBehavior.CloseConnection在异常时不发生作用

            Try


                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)

                Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                cmd.Parameters.Clear()


                Return rdr

            Catch


                conn.Close()


                Throw
            End Try

        End Function



        ' <summary>

        ' 在一个连接串上执行一个命令，返回表中第一行，第一列的值

        ' 使用提供的参数.

        ' </summary>

        ' <remarks>

        ' e.g.:  

        '  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="connectionString">一个有效的SqlConnection连接串</param>

        ' <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>        /// <returns>返回的对象，在使用时记得类型转换</returns>

        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 New SqlCommand()



            Using connection As 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>

        ' <remarks>

        ' e.g.:  

        '  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));

        ' </remarks>

        ' <param name="connectionString">一个有效的SqlConnection连接</param>

        ' <param name="commandType">命令类型CommandType(stored procedure, text, etc.)</param>

        ' <param name="commandText">存贮过程名称或是一个T-SQL语句串</param>

        ' <param name="commandParameters">执行命令的参数集</param>        /// <returns>返回的对象，在使用时记得类型转换</returns>

        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 New SqlCommand()



            PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)

            Dim val As Object = cmd.ExecuteScalar()

            cmd.Parameters.Clear()

            Return val

        End Function



        ' <summary>

        ' 在缓存中添加参数数组

        ' </summary>

        ' <param name="cacheKey">参数的Key</param>

        ' <param name="cmdParms">参数数组</param>

        Public Shared Sub CacheParameters(ByVal cacheKey As String, ByVal ParamArray commandParameters As SqlParameter())


            parmCache(cacheKey) = commandParameters

        End Sub



        ' <summary>

        ' 提取缓存的参数数组

        ' </summary>

        ' <param name="cacheKey">查找缓存的key</param>

        ' <returns>返回被缓存的参数数组</returns>

        Public Shared Function GetCachedParameters(ByVal cacheKey As String) As SqlParameter()


            Dim cachedParms As SqlParameter() = DirectCast(parmCache(cacheKey), SqlParameter())



            If cachedParms Is Nothing Then

                Return Nothing
            End If



            Dim clonedParms As SqlParameter() = New SqlParameter(cachedParms.Length) {}



            Dim i As Integer = 0, j As Integer = cachedParms.Length
            While i < j

                clonedParms(i) = DirectCast((DirectCast(cachedParms(i), ICloneable)).Clone(), SqlParameter)
                System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
            End While



            Return clonedParms

        End Function



        ' <summary>

        ' 提供一个SqlCommand对象的设置

        ' </summary>

        ' <param name="cmd">SqlCommand对象</param>

        ' <param name="conn">SqlConnection 对象</param>

        ' <param name="trans">SqlTransaction 对象</param>

        ' <param name="cmdType">CommandType 如存贮过程，T-SQL</param>

        ' <param name="cmdText">存贮过程名或查询串</param>

        ' <param name="cmdParms">命令中用到的参数集</param>

        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()
            End If



            cmd.Connection = conn

            cmd.CommandText = cmdText



            If trans IsNot Nothing Then

                cmd.Transaction = trans
            End If



            cmd.CommandType = cmdType



            If cmdParms IsNot Nothing Then


                For Each parm As SqlParameter In cmdParms

                    cmd.Parameters.Add(parm)

                Next
            End If

        End Sub

    End Class

End Namespace