客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
Imports System
Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Imports System.ComponentModel Namespace PetShop.DBUtility ''' <summary> ''' The SqlHelper class is intended to encapsulate high performance, ''' scalable best practices for common uses of SqlClient. ''' </summary> Public MustInherit Class SQLHelper 'Database connection strings Public Shared ReadOnly ConnectionStringLocalTransaction As String = ConfigurationManager.ConnectionStrings("SQLConnString1").ConnectionString Public Shared ReadOnly ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings("SQLConnString2").ConnectionString Public Shared ReadOnly ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings("SQLConnString3").ConnectionString Public Shared ReadOnly ConnectionStringProfile = ConfigurationManager.ConnectionStrings("SQLProfileConnString").ConnectionString 'Hashtable to store cached parameters Private Shared parmCache As Hashtable = Hashtable.Synchronized(New Hashtable) '''<summary> '''Execute a SqlCommand (that returns no resultset) against the database specified in the connection string '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="connectionString">a valid connection string for a SqlConnection</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>an int representing the number of rows affected by the command</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 SqlCommand = New SqlCommand Using conn As SqlConnection = New SqlConnection(connectionString) PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters) Dim val As Integer = cmd.ExecuteNonQuery() cmd.Parameters.Clear() conn.Dispose() Return val End Using End Function '''<summary> '''Execute a SqlCommand (that returns no resultset) against an existing database connection '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="conection">an existing database connection</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>an int representing the number of rows affected by the command</returns> Public Shared Function ExecuteNonQuery(ByVal conection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) Dim cmd As SqlCommand = New SqlCommand PrepareCommand(cmd, conection, Nothing, cmdType, cmdText, commandParameters) Dim val As Integer = cmd.ExecuteNonQuery() cmd.Parameters.Clear() Return val End Function '''<summary> '''Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="trans">an existing sql transaction</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>an int representing the number of rows affected by the command</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 SqlCommand = New SqlCommand() PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters) Dim val As Integer = cmd.ExecuteNonQuery() cmd.Parameters.Clear() Return val End Function '''<summary> '''Execute a SqlCommand that returns a resultset against the database specified in the connection string '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="connectionString">a valid connection string for a SqlConnection</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>A SqlDataReader containing the results</returns> Public Shared Function ExcuteReader(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() ExceptionManagement.SystemError.SystemLog(ex.Message) Throw End Try End Function '''<summary> '''Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="connectionString">a valid connection string for a SqlConnection</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>An object that should be converted to the expected type using Convert.To{Type}</returns> Public Shared Function ExcuteScalar(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) Dim val As Object = cmd.ExecuteScalar cmd.Parameters.Clear() connection.Dispose() Return val End Using End Function '''<summary> ''' Execute a SqlCommand that returns the first column of the first record against an existing database connection '''using the provided parameters. '''</summary> '''<remarks> '''e.g.: ''' Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); '''</remarks> '''<param name="connection">an existing database connection</param> '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param> '''<param name="cmdText">the stored procedure name or T-SQL command</param> '''<param name="commandParameters">an array of SqlParamters used to execute the command</param> '''<returns>An object that should be converted to the expected type using Convert.To{Type}</returns> Public Shared Function ExcuteScalar(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() connection.Dispose() Return val End Function ''' <summary> ''' add parameter array to the cache ''' </summary> ''' <param name="cacheKey">Key to the parameter cache</param> ''' <param name="commandParameters">an array of SqlParamters to be cached</param> Public Shared Sub CacheParameters(ByVal cacheKey As String, ByVal ParamArray commandParameters As SqlParameter()) parmCache(cacheKey) = commandParameters End Sub ''' <summary> ''' Retrieve cached parameters ''' </summary> ''' <param name="cacheKey">key used to lookup parameters</param> ''' <returns>Cached SqlParamters array</returns> 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 End If Dim clonedParms As SqlParameter() = New SqlParameter(cachedParms.Length - 1) {} Dim i As Integer For i = 0 To cachedParms.Length - 1 clonedParms(i) = DirectCast(DirectCast(cachedParms(i), ICloneable).Clone(), SqlParameter) Next Return clonedParms End Function '''<summary> '''Prepare a command for execution '''</summary> '''<param name="cmd">SqlCommand object</param> '''<param name="conn">SqlConnection object</param> '''<param name="trans">SqlTransaction object</param> '''<param name="cmdType">Cmd type e.g. stored procedure or text</param> '''<param name="cmdText">Command text, e.g. Select * from Products</param> '''<param name="cmdParms">SqlParameters to use in the command</param> Private Shared Sub PrepareCommand(ByRef 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 MSDN DirectCast 关键字： 引入类型转换操作。该关键字的使用方法与 CType 关键字相同，如下列所示：
|
请发表评论