'********************************************************************* ' Microsoft Data Access Application Block for .NET ' http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp ' ' <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper.cs ' ' This file contains the implementations of the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper and <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache ' classes. ' ' For more information see the Data Access Application Block Implementation Overview. ' '********************************************************************* ' Copyright (C) 2000-2001 Microsoft Corporation ' All rights reserved. ' THIS CODE AND INFORMATION=PROVIDED "AS IS" WITHOUT WARRANTY ' OF ANY KIND,EITHER EXPRESSED OR IMPLIED,INCLUDING BUT NOT ' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR ' FITNESS FOR A PARTICULAR PURPOSE. '********************************************************************* Imports System Imports System.Data Imports System.Data.<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Client Imports System.Collections Namespace EnPaper.EnData '********************************************************************* ' ' The <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper class=intended to encapsulate high performance,scalable best practices for ' common uses of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Client. ' '********************************************************************* Public NotInheritable Class <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper '********************************************************************* ' ' Since this class provides only static methods,make the default constructor private to prevent ' instances from being created with "new <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper()". ' '********************************************************************* Private Sub New() End Sub 'New '********************************************************************* ' ' This method=used to attach array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters to a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command. ' ' This method will assign a value of DbNull to any parameter with a direction of ' InputOutput and a value of null. ' ' This behavior will prevent default values from being used,but ' this will be the less common case than an intended pure output parameter (derived as InputOutput) ' where the user provided no input value. ' ' param name="command" The command to which the parameters will be added ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters tho be added to command ' '********************************************************************* Private Shared Sub AttachParameters(ByVal command As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command,ByVal commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) Dim p As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter For Each p In commandParameters 'check for derived output value with no value assigned If p.Direction = ParameterDirection.InputOutput AndAlso p.Value Is Nothing Then p.Value = DBNull.Value End If command.Parameters.Add(p) Next p End Sub 'AttachParameters '********************************************************************* ' ' This method assigns an array of values to an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters. ' ' param name="commandParameters" array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters to be assigned values ' param name="parameterValues" array of objects holding the values to be assigned ' '********************************************************************* Private Shared Sub AssignParameterValues(ByVal commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter,ByVal parameterValues() As Object) If commandParameters Is Nothing OrElse parameterValues Is Nothing Then 'do nothing if we get no data Return End If If commandParameters.Length <> parameterValues.Length Then Throw New ArgumentException("Parameter count does not match Parameter Value count.") End If Dim i,j As Integer j = commandParameters.Length For i = 0 To j commandParameters(i).Value = parameterValues(i) Next End Sub 'AssignParameterValues 'iterate through the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters,assigning the values from the corresponding position in the 'value array '********************************************************************* ' ' This method opens (if necessary) and assigns a connection,transaction,command type and parameters ' to the provided command. ' ' param name="command" the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command to be prepared ' param name="connection" a valid <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,on which to execute this command ' param name="transaction" a valid <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Transaction,or 'null' ' param name="commandType" the CommandType (stored procedure,text,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters to be associated with the command or 'null' if no parameters are <a href="https://www.jb51.cc/tag/required/" target="_blank" class="keywords">required</a> ' '********************************************************************* Private Shared Sub PrepareCommand(ByVal command As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command,ByVal connection As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,ByVal transaction As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Transaction,ByVal commandType As CommandType,ByVal commandText As String,ByVal commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) 'if the provided connection=not open,we will open it If connection.State <> ConnectionState.Open Then connection.Open() End If 'associate the connection with the command command.Connection = connection 'set the command text (stored procedure name or <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> statement) command.CommandText = commandText 'if we were provided a transaction,assign it. If Not (transaction Is Nothing) Then command.Transaction = transaction End If 'set the command type command.CommandType = commandType 'attach the command parameters if they are provided If Not (commandParameters Is Nothing) Then AttachParameters(command,commandParameters) End If Return End Sub 'PrepareCommand '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns no resultset) against the database specified in the connection string ' using the provided parameters. ' ' e.g.: ' int result = ExecuteNonQuery(connString,CommandType.StoredProcedure,"PublishOrders",new <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter("@prodid",24)); ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandType" the CommandType (stored procedure,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns an int representing the number of rows affected by the command ' '********************************************************************* Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As Integer 'create & open a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,and dispose of it after we are done. Dim cn As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection(connectionString) Try cn.Open() 'call the overload that takes a connection in place of the connection string Return ExecuteNonQuery(cn,commandType,commandText,commandParameters) Finally cn.Dispose() End Try End Function 'ExecuteNonQuery '********************************************************************* ' ' Execute a stored procedure via a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns no resultset) against the database specified in ' the connection string using the provided parameter values. This method will query the database to discover the parameters for the ' stored procedure (the first time each stored procedure=called),and assign the values based on parameter order. ' ' This method provides no access to output parameters or the stored procedure's return value parameter. ' ' e.g.: ' int result = ExecuteNonQuery(connString,24,36); ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored prcedure ' param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure ' returns an int representing the number of rows affected by the command ' '********************************************************************* Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String,ByVal spName As String,ByVal ParamArray parameterValues() As Object) As Integer 'if we receive parameter values,we need to figure out where they go If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then 'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) Dim commandParameters As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() = <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache.GetSpParameterSet(connectionString,spName) 'assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters,parameterValues) 'call the overload that takes an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters Return ExecuteNonQuery(connectionString,spName,commandParameters) 'otherwise we can just call the SP without params Else Return ExecuteNonQuery(connectionString,spName) End If End Function 'ExecuteNonQuery '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns no resultset) against the specified <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' using the provided parameters. ' ' e.g.: ' int result = ExecuteNonQuery(conn,24)); ' ' param name="connection" a valid <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandType" the CommandType (stored procedure,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns an int representing the number of rows affected by the command ' '********************************************************************* Public Overloads Shared Function ExecuteNonQuery(ByVal connection As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As Integer 'create a command and prepare it for execution Dim cmd As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command PrepareCommand(cmd,connection,CType(Nothing,<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Transaction),commandParameters) 'finally,execute the command. Dim retval As Integer = cmd.ExecuteNonQuery() ' detach the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters from the command object,so they can be used again. cmd.Parameters.Clear() Return retval End Function 'ExecuteNonQuery '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a resultset) against the database specified in the connection string ' using the provided parameters. ' ' e.g.: ' DataSet ds = ExecuteDataset(connString,"GetOrders",24)); ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandType" the CommandType (stored procedure,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns a dataset containing the resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteDataset(ByVal connectionString As String,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As DataSet 'create & open a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,and dispose of it after we are done. Dim cn As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection(connectionString) Try cn.Open() 'call the overload that takes a connection in place of the connection string Return ExecuteDataset(cn,commandParameters) Finally cn.Dispose() End Try End Function 'ExecuteDataset '********************************************************************* ' ' Execute a stored procedure via a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a resultset) against the database specified in ' the connection string using the provided parameter values. This method will query the database to discover the parameters for the ' stored procedure (the first time each stored procedure=called),and assign the values based on parameter order. ' ' This method provides no access to output parameters or the stored procedure's return value parameter. ' ' e.g.: ' DataSet ds = ExecuteDataset(connString,36); ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored procedure ' param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure ' returns a dataset containing the resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteDataset(ByVal connectionString As String,ByVal ParamArray parameterValues() As Object) As DataSet 'if we receive parameter values,parameterValues) 'call the overload that takes an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters Return ExecuteDataset(connectionString,commandParameters) 'otherwise we can just call the SP without params Else Return ExecuteDataset(connectionString,spName) End If End Function 'ExecuteDataset '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a resultset) against the specified <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' using the provided parameters. ' ' e.g.: ' DataSet ds = ExecuteDataset(conn,24)); ' ' param name="connection" a valid <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandType" the CommandType (stored procedure,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns a dataset containing the resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteDataset(ByVal connection As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As DataSet 'create a command and prepare it for execution Dim cmd As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command PrepareCommand(cmd,commandParameters) 'create the DataAdapter & DataSet Dim da As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>DataAdapter(cmd) Dim ds As New DataSet 'fill the DataSet using default values for DataTable names,etc. da.Fill(ds) ' detach the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters from the command object,so they can be used again. cmd.Parameters.Clear() 'return the dataset Return ds End Function 'ExecuteDataset '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a 1x1 resultset) against the database specified in the connection string ' using the provided parameters. ' ' e.g.: ' int orderCount = (int)ExecuteScalar(connString,"GetOrderCount",etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns an object containing the value in the 1x1 resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteScalar(ByVal connectionString As String,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As Object 'create & open a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,and dispose of it after we are done. Dim cn As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection(connectionString) Try cn.Open() 'call the overload that takes a connection in place of the connection string Return ExecuteScalar(cn,commandParameters) Finally cn.Dispose() End Try End Function 'ExecuteScalar '********************************************************************* ' ' Execute a stored procedure via a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a 1x1 resultset) against the database specified in ' the connection string using the provided parameter values. This method will query the database to discover the parameters for the ' stored procedure (the first time each stored procedure=called),and assign the values based on parameter order. ' ' This method provides no access to output parameters or the stored procedure's return value parameter. ' ' e.g.: ' int orderCount = (int)ExecuteScalar(connString,36); ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored procedure ' param name="parameterValues" an array of objects to be assigned as the input values of the stored procedure ' returns an object containing the value in the 1x1 resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteScalar(ByVal connectionString As String,ByVal ParamArray parameterValues() As Object) As Object 'if we receive parameter values,parameterValues) 'call the overload that takes an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters Return ExecuteScalar(connectionString,commandParameters) 'otherwise we can just call the SP without params Else Return ExecuteScalar(connectionString,spName) End If End Function 'ExecuteScalar '********************************************************************* ' ' Execute a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command (that returns a 1x1 resultset) against the specified <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' using the provided parameters. ' ' e.g.: ' int orderCount = (int)ExecuteScalar(conn,etc.) ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters used to execute the command ' returns an object containing the value in the 1x1 resultset generated by the command ' '********************************************************************* Public Overloads Shared Function ExecuteScalar(ByVal connection As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As Object 'create a command and prepare it for execution Dim cmd As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command PrepareCommand(cmd,commandParameters) 'execute the command & return the results Dim retval As Object = cmd.ExecuteScalar() ' detach the <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters from the command object,so they can be used again. cmd.Parameters.Clear() Return retval End Function 'ExecuteScalar End Class '<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Helper '********************************************************************* ' ' <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache provides functions to leverage a static cache of procedure parameters,and the ' ability to discover parameters for stored procedures at run-time. ' '********************************************************************* Public NotInheritable Class <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache '********************************************************************* ' ' Since this class provides only static methods,make the default constructor private to prevent ' instances from being created with "new <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache()". ' '********************************************************************* Private Sub New() End Sub 'New Private Shared paramCache As Hashtable = Hashtable.Synchronized(New Hashtable) '********************************************************************* ' ' resolve at run time the appropriate set of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters for a stored procedure ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored procedure ' param name="includeReturnValueParameter" whether or not to include their return value parameter ' '********************************************************************* Private Shared Function DiscoverSpParameterSet(ByVal connectionString As String,ByVal includeReturnValueParameter As Boolean) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() Dim cn As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection(connectionString) Try Dim cmd As New <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Command(spName,cn) Try cn.Open() cmd.CommandType = CommandType.StoredProcedure <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>CommandBuilder.DeriveParameters(cmd) If Not includeReturnValueParameter Then cmd.Parameters.RemoveAt(0) End If Dim discoveredParameters(cmd.Parameters.Count) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter cmd.Parameters.CopyTo(discoveredParameters,0) Return discoveredParameters Finally cmd.Dispose() End Try Finally cn.Dispose() End Try End Function 'DiscoverSpParameterSet Private Shared Function CloneParameters(ByVal originalParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() 'deep copy of cached <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter array Dim clonedParameters(originalParameters.Length) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter Dim i,j As Integer j = originalParameters.Length For i = 0 To j clonedParameters(i) = CType(CType(originalParameters(i),ICloneable).Clone(),<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) Next Return clonedParameters End Function 'CloneParameters '********************************************************************* ' ' add parameter array to the cache ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' param name="commandParameters" an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters to be cached ' '********************************************************************* Public Shared Sub CacheParameterSet(ByVal connectionString As String,ByVal ParamArray commandParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter) Dim hashKey As String = connectionString + ":" + commandText paramCache(hashKey) = commandParameters End Sub 'CacheParameterSet '********************************************************************* ' ' Retrieve a parameter array from the cache ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="commandText" the stored procedure name or T-<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> command ' returns an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Paramters ' '********************************************************************* Public Shared Function GetCachedParameterSet(ByVal connectionString As String,ByVal commandText As String) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() Dim hashKey As String = connectionString + ":" + commandText Dim cachedParameters As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() = CType(paramCache(hashKey),<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter()) If cachedParameters Is Nothing Then Return Nothing Else Return CloneParameters(cachedParameters) End If End Function 'GetCachedParameterSet '********************************************************************* ' ' Retrieves the set of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters appropriate for the stored procedure ' ' This method will query the database for this information,and then store it in a cache for future requests. ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored procedure ' returns an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters ' '********************************************************************* Public Overloads Shared Function GetSpParameterSet(ByVal connectionString As String,ByVal spName As String) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() Return GetSpParameterSet(connectionString,False) End Function 'GetSpParameterSet '********************************************************************* ' ' Retrieves the set of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters appropriate for the stored procedure ' ' This method will query the database for this information,and then store it in a cache for future requests. ' ' param name="connectionString" a valid connection string for a <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Connection ' param name="spName" the name of the stored procedure ' param name="includeReturnValueParameter" a bool value indicating whether the return value parameter should be included in the results ' returns an array of <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameters ' '********************************************************************* Public Overloads Shared Function GetSpParameterSet(ByVal connectionString As String,ByVal includeReturnValueParameter As Boolean) As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter() Dim hashKey As String = connectionString + ":" + spName + IIf(includeReturnValueParameter,":include ReturnValue Parameter","") Dim cachedParameters() As <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter cachedParameters = CType(paramCache(hashKey),<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter()) If cachedParameters Is Nothing Then paramCache(hashKey) = DiscoverSpParameterSet(connectionString,includeReturnValueParameter) cachedParameters = CType(paramCache(hashKey),<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Parameter()) End If Return CloneParameters(cachedParameters) End Function 'GetSpParameterSet End Class '<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>HelperParameterCache End Namespace ' EnPaper.EnData