前几个月下载的SqlHelper运行在.net2.0会出错,今天重新试了一下正常了。http://www.microsoft.com/china/msdn/archives/library/dnbda/html/daab-rm.asp#daab-rm_downloadingandinstallingthedaab
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SqlHelper2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
' The SqlHelper class is intended to encapsulate high performance, scalable best practices for
' common uses of SqlClient.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
' ===============================================================================
' Release history
' VERSION DESCRIPTION
' 2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
'
' ===============================================================================
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
Public NotInheritable Class SqlHelperClass SqlHelper
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
private utility methods & constructors#Region "private utility methods & constructors"
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Since this class provides only static methods, make the default constructor private to prevent
' instances from being created with "new SqlHelper()".
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Sub New()Sub New()
End Sub ' New
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' This method is used to attach array of SqlParameters to a SqlCommand.
' 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.
' Parameters:
' -command - The command to which the parameters will be added
' -commandParameters - an array of SqlParameters to be added to command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Shared Sub AttachParameters()Sub AttachParameters(ByVal command As SqlCommand, ByVal commandParameters() As SqlParameter)
If (command Is Nothing) Then Throw New ArgumentNullException("command")
If (Not commandParameters Is Nothing) Then
Dim p As SqlParameter
For Each p In commandParameters
If (Not p Is Nothing) Then
' Check for derived output value with no value assigned
If (p.Direction = ParameterDirection.InputOutput OrElse p.Direction = ParameterDirection.Input) AndAlso p.Value Is Nothing Then
p.Value = DBNull.Value
End If
command.Parameters.Add(p)
End If
Next p
End If
End Sub ' AttachParameters
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' This method assigns dataRow column values to an array of SqlParameters.
' Parameters:
' -commandParameters: Array of SqlParameters to be assigned values
' -dataRow: the dataRow used to hold the stored procedure' s parameter values
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Overloads Shared Sub AssignParameterValues()Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal dataRow As DataRow)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If commandParameters Is Nothing OrElse dataRow Is Nothing Then
' Do nothing if we get no data
Exit Sub
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Set the parameters values
Dim commandParameter As SqlParameter
Dim i As Integer
For Each commandParameter In commandParameters
' Check the parameter name
If (commandParameter.ParameterName Is Nothing OrElse commandParameter.ParameterName.Length <= 1) Then
Throw New Exception(String.Format("Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ' {1}' .", i, commandParameter.ParameterName))
End If
If dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) <> -1 Then
commandParameter.Value = dataRow(commandParameter.ParameterName.Substring(1))
End If
i = i + 1
Next
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' This method assigns an array of values to an array of SqlParameters.
' Parameters:
' -commandParameters - array of SqlParameters to be assigned values
' -array of objects holding the values to be assigned
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Overloads Shared Sub AssignParameterValues()Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim i As Integer
Dim j As Integer
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then
' Do nothing if we get no data
Return
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' We must have the same number of values as we pave parameters to put them in
If commandParameters.Length <> parameterValues.Length Then
Throw New ArgumentException("Parameter count does not match Parameter Value count.")
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Value array
j = commandParameters.Length - 1
For i = 0 To j
' If the current array value derives from IDbDataParameter, then assign its Value property
If TypeOf parameterValues(i) Is IDbDataParameter Then
Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)
If (paramInstance.Value Is Nothing) Then
commandParameters(i).Value = DBNull.Value
Else
commandParameters(i).Value = paramInstance.Value
End If
ElseIf (parameterValues(i) Is Nothing) Then
commandParameters(i).Value = DBNull.Value
Else
commandParameters(i).Value = parameterValues(i)
End If
Next
End Sub ' AssignParameterValues
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' This method opens (if necessary) and assigns a connection, transaction, command type and parameters
' to the provided command.
' Parameters:
' -command - the SqlCommand to be prepared
' -connection - a valid SqlConnection, on which to execute this command
' -transaction - a valid SqlTransaction, or ' null'
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParameters to be associated with the command or ' null' if no parameters are required
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Shared Sub PrepareCommand()Sub PrepareCommand(ByVal command As SqlCommand, _
ByVal connection As SqlConnection, _
ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandParameters() As SqlParameter, ByRef mustCloseConnection As Boolean)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (command Is Nothing) Then Throw New ArgumentNullException("command")
If (commandText Is Nothing OrElse commandText.Length = 0) Then Throw New ArgumentNullException("commandText")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' If the provided connection is not open, we will open it
If connection.State <> ConnectionState.Open Then
connection.Open()
mustCloseConnection = True
Else
mustCloseConnection = False
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Associate the connection with the command
command.Connection = connection
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' If we were provided a transaction, assign it.
If Not (transaction Is Nothing) Then
If transaction.Connection Is Nothing Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
command.Transaction = transaction
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Set the command type
command.CommandType = commandType
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Attach the command parameters if they are provided
If Not (commandParameters Is Nothing) Then
AttachParameters(command, commandParameters)
End If
Return
End Sub ' PrepareCommand
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
ExecuteNonQuery#Region "ExecuteNonQuery"
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
' the connection string.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders")
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Integer
' Pass through the call providing null for the set of SqlParameters
Return ExecuteNonQuery(connectionString, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
' using the provided parameters.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
' Create & open a SqlConnection, and dispose of it after we are done
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
Return ExecuteNonQuery(connection, commandType, commandText, commandParameters)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
' the connection string using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim result As Integer = ExecuteNonQuery(connString, "PublishOrders", 24, 36)
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Integer
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters)
' Otherwise we can just call the SP without params
Else
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders")
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Integer
' Pass through the call providing null for the set of SqlParameters
Return ExecuteNonQuery(connection, commandType, commandText, CType(Nothing, SqlParameter()))
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Integer
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Finally, execute the command
retval = cmd.ExecuteNonQuery()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (mustCloseConnection) Then connection.Close()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim result As integer = ExecuteNonQuery(conn, "PublishOrders", 24, 36)
' Parameters:
' -connection - a valid SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal connection As SqlConnection, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Integer
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders")
' Parameters:
' -transaction - a valid SqlTransaction associated with the connection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Integer
' Pass through the call providing null for the set of SqlParameters
Return ExecuteNonQuery(transaction, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
' using the provided parameters.
' e.g.:
' Dim result As Integer = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An int representing the number of rows affected by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Integer
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Finally, execute the command
retval = cmd.ExecuteNonQuery()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlTransaction
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim result As Integer = SqlHelper.ExecuteNonQuery(trans, "PublishOrders", 24, 36)
' Parameters:
' -transaction - a valid SqlTransaction
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteNonQuery()Function ExecuteNonQuery(ByVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Integer
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteNonQuery
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
ExecuteDataset#Region "ExecuteDataset"
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
' the connection string.
' e.g.:
' Dim ds As DataSet = SqlHelper.ExecuteDataset("", commandType.StoredProcedure, "GetOrders")
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String) As DataSet
' Pass through the call providing null for the set of SqlParameters
Return ExecuteDataset(connectionString, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
' using the provided parameters.
' e.g.:
' Dim ds As Dataset = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
' Create & open a SqlConnection, and dispose of it after we are done
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
Return ExecuteDataset(connection, commandType, commandText, commandParameters)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
' the connection string using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim ds As Dataset= ExecuteDataset(connString, "GetOrders", 24, 36)
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As DataSet
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' Dim ds As Dataset = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String) As DataSet
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Pass through the call providing null for the set of SqlParameters
Return ExecuteDataset(connection, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' Dim ds As Dataset = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dataAdatpter As SqlDataAdapter
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Try
' Create the DataAdapter & DataSet
dataAdatpter = New SqlDataAdapter(cmd)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Fill the DataSet using default values for DataTable names, etc
dataAdatpter.Fill(ds)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
Finally
If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
End Try
If (mustCloseConnection) Then connection.Close()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Return the dataset
Return ds
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim ds As Dataset = ExecuteDataset(conn, "GetOrders", 24, 36)
' Parameters:
' -connection - a valid SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal connection As SqlConnection, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As DataSet
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' If we receive parameter values, we need to figure out where they go
If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteDataset(connection, CommandType.StoredProcedure, spName)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
' e.g.:
' Dim ds As Dataset = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders")
' Parameters
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String) As DataSet
' Pass through the call providing null for the set of SqlParameters
Return ExecuteDataset(transaction, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
' using the provided parameters.
' e.g.:
' Dim ds As Dataset = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A dataset containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dataAdatpter As SqlDataAdapter
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Try
' Create the DataAdapter & DataSet
dataAdatpter = New SqlDataAdapter(cmd)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Fill the DataSet using default values for DataTable names, etc
dataAdatpter.Fill(ds)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
Finally
If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
End Try
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Return the dataset
Return ds
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
' SqlTransaction using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim ds As Dataset = ExecuteDataset(trans, "GetOrders", 24, 36)
' Parameters:
' -transaction - a valid SqlTransaction
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteDataset()Function ExecuteDataset(ByVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As DataSet
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteDataset(transaction, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteDataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
ExecuteReader#Region "ExecuteReader"
' this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
' we can set the appropriate CommandBehavior when calling ExecuteReader()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Enum SqlConnectionOwnershipEnum SqlConnectionOwnership
' Connection is owned and managed by SqlHelper
Internal
' Connection is owned and managed by the caller
[External]
End Enum ' SqlConnectionOwnership
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
' If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
' If the caller provided the connection, we want to leave it to them to manage.
' Parameters:
' -connection - a valid SqlConnection, on which to execute this command
' -transaction - a valid SqlTransaction, or ' null'
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParameters to be associated with the command or ' null' if no parameters are required
' -connectionOwnership - indicates whether the connection parameter was provided by the caller, or created by SqlHelper
' Returns: SqlDataReader containing the results of the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Private Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandParameters() As SqlParameter, _
ByVal connectionOwnership As SqlConnectionOwnership) As SqlDataReader
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim mustCloseConnection As Boolean = False
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Try
' Create a reader
Dim dataReader As SqlDataReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call ExecuteReader with the appropriate CommandBehavior
If connectionOwnership = SqlConnectionOwnership.External Then
dataReader = cmd.ExecuteReader()
Else
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
Dim canClear As Boolean = True
Dim commandParameter As SqlParameter
For Each commandParameter In cmd.Parameters
If commandParameter.Direction <> ParameterDirection.Input Then
canClear = False
End If
Next
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (canClear) Then cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return dataReader
Catch
If (mustCloseConnection) Then connection.Close()
Throw
End Try
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
' the connection string.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String) As SqlDataReader
' Pass through the call providing null for the set of SqlParameters
Return ExecuteReader(connectionString, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
' using the provided parameters.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As SqlDataReader
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create & open a SqlConnection
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
connection.Open()
' Call the private overload that takes an internally owned connection in place of the connection string
Return ExecuteReader(connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, SqlConnectionOwnership.Internal)
Catch
' If we fail to return the SqlDatReader, we need to close the connection ourselves
If Not connection Is Nothing Then connection.Dispose()
Throw
End Try
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
' the connection string using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim dr As SqlDataReader = ExecuteReader(connString, "GetOrders", 24, 36)
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -spName - the name of the stored procedure
' -parameterValues - an array of objects to be assigned as the input values of the stored procedure
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As SqlDataReader
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters)
' Otherwise we can just call the SP without params
Else
Return ExecuteReader(connectionString, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String) As SqlDataReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return ExecuteReader(connection, commandType, commandText, CType(Nothing, SqlParameter()))
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As SqlDataReader
' Pass through the call to private overload using a null transaction value
Return ExecuteReader(connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, SqlConnectionOwnership.External)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim dr As SqlDataReader = ExecuteReader(conn, "GetOrders", 24, 36)
' Parameters:
' -connection - a valid SqlConnection
' -spName - the name of the stored procedure
' -parameterValues - an array of objects to be assigned as the input values of the stored procedure
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As SqlDataReader
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
' If we receive parameter values, we need to figure out where they go
If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteReader(connection, CommandType.StoredProcedure, spName)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String) As SqlDataReader
' Pass through the call providing null for the set of SqlParameters
Return ExecuteReader(transaction, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
' using the provided parameters.
' e.g.:
' Dim dr As SqlDataReader = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As SqlDataReader
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
' Pass through to private overload, indicating that the connection is owned by the caller
Return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External)
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim dr As SqlDataReader = ExecuteReader(trans, "GetOrders", 24, 36)
' Parameters:
' -transaction - a valid SqlTransaction
' -spName - the name of the stored procedure
' -parameterValues - an array of objects to be assigned as the input values of the stored procedure
' Returns: A SqlDataReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteReader()Function ExecuteReader(ByVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As SqlDataReader
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' If we receive parameter values, we need to figure out where they go
If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then
commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteReader(transaction, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
ExecuteScalar#Region "ExecuteScalar"
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
' the connection string.
' e.g.:
' Dim orderCount As Integer = CInt(ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Object
' Pass through the call providing null for the set of SqlParameters
Return ExecuteScalar(connectionString, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
' using the provided parameters.
' e.g.:
' Dim orderCount As Integer = Cint(ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Object
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
' Create & open a SqlConnection, and dispose of it after we are done.
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
Return ExecuteScalar(connection, commandType, commandText, commandParameters)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
' the connection string using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim orderCount As Integer = CInt(ExecuteScalar(connString, "GetOrderCount", 24, 36))
' Parameters:
' -connectionString - a valid connection string for a SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Object
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters)
' Otherwise we can just call the SP without params
Else
Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Object
' Pass through the call providing null for the set of SqlParameters
Return ExecuteScalar(connection, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' Dim orderCount As Integer = CInt(ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Object
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Object
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute the command & return the results
retval = cmd.ExecuteScalar()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (mustCloseConnection) Then connection.Close()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim orderCount As Integer = CInt(ExecuteScalar(conn, "GetOrderCount", 24, 36))
' Parameters:
' -connection - a valid SqlConnection
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal connection As SqlConnection, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Object
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteScalar(connection, CommandType.StoredProcedure, spName)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
' e.g.:
' Dim orderCount As Integer = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"))
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String) As Object
' Pass through the call providing null for the set of SqlParameters
Return ExecuteScalar(transaction, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
' using the provided parameters.
' e.g.:
' Dim orderCount As Integer = CInt(ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)))
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An object containing the value in the 1x1 resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Object
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Object
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute the command & return the results
retval = cmd.ExecuteScalar()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim orderCount As Integer = CInt(ExecuteScalar(trans, "GetOrderCount", 24, 36))
' Parameters:
' -transaction - a valid SqlTransaction
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteScalar()Function ExecuteScalar(ByVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Object
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters)
Else ' Otherwise we can just call the SP without params
Return ExecuteScalar(transaction, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteScalar
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
ExecuteXmlReader#Region "ExecuteXmlReader"
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' Dim r As XmlReader = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO"
' Returns: An XmlReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String) As XmlReader
' Pass through the call providing null for the set of SqlParameters
Return ExecuteXmlReader(connection, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' Dim r As XmlReader = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -connection - a valid SqlConnection
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO"
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An XmlReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As XmlReader
' Pass through the call using a null transaction value
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim mustCloseConnection As Boolean = False
Try
Dim retval As XmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create the DataAdapter & DataSet
retval = cmd.ExecuteXmlReader()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
Catch
If (mustCloseConnection) Then connection.Close()
Throw
End Try
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim r As XmlReader = ExecuteXmlReader(conn, "GetOrders", 24, 36)
' Parameters:
' -connection - a valid SqlConnection
' -spName - the name of the stored procedure using "FOR XML AUTO"
' -parameterValues - an array of objects to be assigned as the input values of the stored procedure
' Returns: An XmlReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal connection As SqlConnection, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As XmlReader
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters)
' Otherwise we can just call the SP without params
Else
Return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction
' e.g.:
' Dim r As XmlReader = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders")
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO"
' Returns: An XmlReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String) As XmlReader
' Pass through the call providing null for the set of SqlParameters
Return ExecuteXmlReader(transaction, commandType, commandText, CType(Nothing, SqlParameter()))
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
' using the provided parameters.
' e.g.:
' Dim r As XmlReader = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
' Parameters:
' -transaction - a valid SqlTransaction
' -commandType - the CommandType (stored procedure, text, etc.)
' -commandText - the stored procedure name or T-SQL command using "FOR XML AUTO"
' -commandParameters - an array of SqlParamters used to execute the command
' Returns: An XmlReader containing the resultset generated by the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal transaction As SqlTransaction, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As XmlReader
' Create a command and prepare it for execution
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim cmd As New SqlCommand
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim retval As XmlReader
Dim mustCloseConnection As Boolean = False
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create the DataAdapter & DataSet
retval = cmd.ExecuteXmlReader()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Return retval
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction
' using the provided parameter values. This method will discover the parameters for the
' stored procedure, 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.:
' Dim r As XmlReader = ExecuteXmlReader(trans, "GetOrders", 24, 36)
' Parameters:
' -transaction - a valid SqlTransaction
' -spName - the name of the stored procedure
' -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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Function ExecuteXmlReader()Function ExecuteXmlReader(ByVal transaction As SqlTransaction, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As XmlReader
If (transaction Is Nothing) Then Throw New ArgumentNullException("transaction")
If Not (transaction Is Nothing) AndAlso (transaction.Connection Is Nothing) Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Dim commandParameters As SqlParameter()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' 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)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
Return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters)
' Otherwise we can just call the SP without params
Else
Return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName)
End If
End Function ' ExecuteXmlReader
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#End Region
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
FillDataset#Region "FillDataset"
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
' the connection string.
' e.g.:
' FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"})
' Parameters:
' -connectionString: A valid connection string for a SqlConnection
' -commandType: the CommandType (stored procedure, text, etc.)
' -commandText: the stored procedure name or T-SQL command
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connectionString As String, ByVal commandType As CommandType, ByVal commandText As String, ByVal dataSet As DataSet, ByVal tableNames() As String)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (dataSet Is Nothing ) Then Throw New ArgumentNullException( "dataSet" )
' Create & open a SqlConnection, and dispose of it after we are done
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
' using the provided parameters.
' e.g.:
' FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() = {"orders"}, new SqlParameter("@prodid", 24))
' Parameters:
' -connectionString: A valid connection string for a SqlConnection
' -commandType: the CommandType (stored procedure, text, etc.)
' -commandText: the stored procedure name or T-SQL command
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
' -commandParameters: An array of SqlParamters used to execute the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connectionString As String, ByVal commandType As CommandType, ByVal commandText As String, ByVal dataSet As DataSet, _
ByVal tableNames() As String, ByVal ParamArray commandParameters() As SqlParameter)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (dataSet Is Nothing ) Then Throw New ArgumentNullException( "dataSet" )
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create & open a SqlConnection, and dispose of it after we are done
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (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 is 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.:
' FillDataset (connString, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"}, 24)
' Parameters:
' -connectionString: A valid connection string for a SqlConnection
' -spName: the name of the stored procedure
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
' -parameterValues: An array of objects to be assigned As the input values of the stored procedure
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connectionString As String, ByVal spName As String, _
ByVal dataSet As DataSet, ByVal tableNames As String(), ByVal ParamArray parameterValues() As Object)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
If (dataSet Is Nothing ) Then Throw New ArgumentNullException( "dataSet" )
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Create & open a SqlConnection, and dispose of it after we are done
Dim connection As SqlConnection
Try
connection = New SqlConnection(connectionString)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
connection.Open()
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes a connection in place of the connection string
FillDataset(connection, spName, dataSet, tableNames, parameterValues)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
' e.g.:
' FillDataset (conn, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"})
' Parameters:
' -connection: A valid SqlConnection
' -commandType: the CommandType (stored procedure, text, etc.)
' -commandText: the stored procedure name or T-SQL command
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connection As SqlConnection, ByVal commandType As CommandType, _
ByVal commandText As String, ByVal dataSet As DataSet, ByVal tableNames As String())
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
FillDataset(connection, commandType, commandText, dataSet, tableNames, Nothing)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
' using the provided parameters.
' e.g.:
' FillDataset (conn, CommandType.StoredProcedure, "GetOrders", ds, new String() {"orders"}, new SqlParameter("@prodid", 24))
' Parameters:
' -connection: A valid SqlConnection
' -commandType: the CommandType (stored procedure, text, etc.)
' -commandText: the stored procedure name or T-SQL command
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
' -commandParameters: An array of SqlParamters used to execute the command
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connection As SqlConnection, ByVal commandType As CommandType, _
ByVal commandText As String, ByVal dataSet As DataSet, ByVal tableNames As String(), _
ByVal ParamArray commandParameters() As SqlParameter)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
FillDataset(connection, Nothing, commandType, commandText, dataSet, tableNames, commandParameters)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
' 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 is 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.:
' FillDataset (conn, "GetOrders", ds, new string() {"orders"}, 24, 36)
' Parameters:
' -connection: A valid SqlConnection
' -spName: the name of the stored procedure
' -dataSet: A dataset wich will contain the resultset generated by the command
' -tableNames: this array will be used to create table mappings allowing the DataTables to be referenced
' by a user defined name (probably the actual table name)
' -parameterValues: An array of objects to be assigned as the input values of the stored procedure
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Public Overloads Shared Sub FillDataset()Sub FillDataset(ByVal connection As SqlConnection, ByVal spName As String, ByVal dataSet As DataSet, _
ByVal tableNames() As String, ByVal ParamArray parameterValues() As Object)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
If (dataSet Is Nothing ) Then Throw New ArgumentNullException( "dataSet" )
If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' If we receive parameter values, we need to figure out where they go
If Not parameterValues Is Nothing AndAlso parameterValues.Length > 0 Then
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
Dim commandParameters() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(connection, spName)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
' Call the overload that takes an array of SqlParameters
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters)
Else ' Otherwise we can just call the SP without params
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames)
End If
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)