While performing database interactions  you need to made a repository class in which all SQL mehods are predefined.

 That class is called as Base Repository

Imports System.Data.SqlClient
Imports System.Xml
Imports System.Globalization

Public NotInheritable Class BaseRepository
#Region "private utility methods & constructors"

    'Since this class provides only static methods, make the default constructor private to prevent 
    'instances from being created with "new SqlHelper()".
    Private Sub New()
    End Sub

    ''' <summary>
    ''' 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.
    ''' </summary>
    ''' <param name="command">The command to which the parameters will be added</param>
    ''' <param name="commandParameters">an array of SqlParameters tho be added to command</param>
    Private Shared Sub AttachParameters(command As SqlCommand, commandParameters As SqlParameter())
        For Each p As SqlParameter In commandParameters
            'check for derived output value with no value assigned
            If (p.Direction = ParameterDirection.InputOutput) AndAlso (p.Value Is Nothing) Then
                p.Value = DBNull.Value
            End If

            command.Parameters.Add(p)
        Next
    End Sub

    ''' <summary>
    ''' This method assigns an array of values to an array of SqlParameters.
    ''' </summary>
    ''' <param name="commandParameters">array of SqlParameters to be assigned values</param>
    ''' <param name="parameterValues">array of objects holding the values to be assigned</param>
    Private Shared Sub AssignParameterValues(commandParameters As SqlParameter(), parameterValues As Object())
        If (commandParameters Is Nothing) OrElse (parameterValues Is Nothing) Then
            'do nothing if we get no data
            Return
        End If

        ' 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

        'iterate through the SqlParameters, assigning the values from the corresponding position in the 
        'value array
        Dim i As Integer = 0, j As Integer = commandParameters.Length
        While i < j
            commandParameters(i).Value = parameterValues(i)
            i += 1
        End While
    End Sub

    ''' <summary>
    ''' This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
    ''' to the provided command.
    ''' </summary>
    ''' <param name="command">the SqlCommand to be prepared</param>
    ''' <param name="connection">a valid SqlConnection, on which to execute this command</param>
    ''' <param name="transaction">a valid SqlTransaction, or 'null'</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
    Private Shared Sub PrepareCommand(command As SqlCommand, connection As SqlConnection, transaction As SqlTransaction, commandType As CommandType, commandText As String, commandParameters As SqlParameter())
        'if the provided connection is not open, we will open it
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If

        'associate the connection with the command
        command.Connection = connection

        'set the command text (stored procedure name or SQL statement)
        command.CommandText = commandText

        'if we were provided a transaction, assign it.
        If transaction IsNot Nothing Then
            command.Transaction = transaction
        End If

        'set the command type
        command.CommandType = commandType

        'attach the command parameters if they are provided
        If commandParameters IsNot Nothing Then
            AttachParameters(command, commandParameters)
        End If

        Return
    End Sub

#End Region

#Region "ExecuteNonQuery"

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
    ''' the connection string. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connectionString As String, commandType As CommandType, commandText As String) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery with no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteNonQuery(connectionString, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connectionString As String, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery started")
        'create & open a SqlConnection, and dispose of it after we are done.
        Using cn As New SqlConnection(connectionString)
            cn.Open()

            'call the overload that takes a connection in place of the connection string
            Return ExecuteNonQuery(cn, commandType, commandText, commandParameters)
        End Using
    End Function

    ''' <summary>
    ''' 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 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="spName">the name of the stored prcedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connectionString As String, spName As String, ParamArray parameterValues As Object()) As Integer
        'Logger.LogInformation(Convert.ToString("Repository Method: ExecuteNonQuery started for spName: ") & spName)
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            'call the overload that takes an array of SqlParameters
            Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters)
        Else
            'otherwise we can just call the SP without params
            Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName)
        End If
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connection As SqlConnection, commandType As CommandType, commandText As String) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery that returns no resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteNonQuery(connection, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connection As SqlConnection, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery that returns no resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, connection, DirectCast(Nothing, SqlTransaction), commandType, commandText, commandParameters)

        'finally, execute the command.
        Dim retval As Integer = cmd.ExecuteNonQuery()

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()
        Return retval
    End Function

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns no 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(connection As SqlConnection, spName As String, ParamArray parameterValues As Object()) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery that Executes a stored procedure and returns no resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(transaction As SqlTransaction, commandType As CommandType, commandText As String) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery that returns no resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteNonQuery(transaction, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(transaction As SqlTransaction, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Integer
        'Logger.LogInformation("Repository Method: ExecuteNonQuery that returns no resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters)

        'finally, execute the command.
        Dim retval As Integer = cmd.ExecuteNonQuery()

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()
        Return retval
    End Function

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified 
    ''' SqlTransaction 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an int representing the number of rows affected by the command</returns>
    Public Shared Function ExecuteNonQuery(transaction As SqlTransaction, spName As String, ParamArray parameterValues As Object()) As Integer
        'Logger.LogInformation(Convert.ToString("Repository Method: ExecuteNonQuery that returns no resultset started for spName: ") & spName)
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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

#End Region

#Region "ExecuteDataSet"

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
    ''' the connection string. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connectionString As String, commandType As CommandType, commandText As String) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteDataSet(connectionString, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connectionString As String, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset started")
        'create & open a SqlConnection, and dispose of it after we are done.
        Using cn As New SqlConnection(connectionString)
            'call the overload that takes a connection in place of the connection string
            Return ExecuteDataSet(cn, commandType, commandText, commandParameters)
        End Using
    End Function

    ''' <summary>
    ''' 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connectionString As String, spName As String, ParamArray parameterValues As Object()) As DataSet

        'Logger.LogInformation("Repository Method: ExecuteDataSet that Execute a stored procedure and returns a resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            'call the overload that takes an array of 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

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connection As SqlConnection, commandType As CommandType, commandText As String) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteDataSet(connection, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connection As SqlConnection, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, connection, DirectCast(Nothing, SqlTransaction), commandType, commandText, commandParameters)

        'create the DataAdapter & DataSet
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        ds.Locale = CultureInfo.InvariantCulture

        'fill the DataSet using default values for DataTable names, etc.
        da.Fill(ds)

        ' detach the SqlParameters from the command object, so they can be used again.			
        cmd.Parameters.Clear()

        'return the dataset
        Return ds
    End Function

    ''' <summary>
    ''' 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(connection As SqlConnection, spName As String, ParamArray parameterValues As Object()) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that Execute a stored procedure and returns a resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(transaction As SqlTransaction, commandType As CommandType, commandText As String) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteDataSet(transaction, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(transaction As SqlTransaction, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As DataSet
        'Logger.LogInformation("Repository Method: ExecuteDataSet that returns a resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters)

        'create the DataAdapter & DataSet
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        ds.Locale = CultureInfo.InvariantCulture

        'fill the DataSet using default values for DataTable names, etc.
        da.Fill(ds)

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()

        'return the dataset
        Return ds
    End Function

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
    ''' SqlTransaction 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>a dataset containing the resultset generated by the command</returns>
    Public Shared Function ExecuteDataSet(transaction As SqlTransaction, spName As String, ParamArray parameterValues As Object()) As DataSet
        'Logger.LogInformation(Convert.ToString("Repository Method: ExecuteDataSet that returns a resultset started for spName: ") & spName)
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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

#End Region

#Region "ExecuteReader"

    ''' <summary>
    ''' 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()
    ''' </summary>
    Private Enum SqlConnectionOwnership
        ''' <summary>Connection is owned and managed by SqlHelper</summary>
        Internal

        ''' <summary>Connection is owned and managed by the caller</summary>
        External
    End Enum

    ''' <summary>
    ''' Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
    ''' </summary>
    ''' <remarks>
    ''' 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.
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection, on which to execute this command</param>
    ''' <param name="transaction">a valid SqlTransaction, or 'null'</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
    ''' <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
    ''' <returns>SqlDataReader containing the results of the command</returns>
    Private Shared Function ExecuteReader(connection As SqlConnection, transaction As SqlTransaction, commandType As CommandType, commandText As String, commandParameters As SqlParameter(), connectionOwnership As SqlConnectionOwnership) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters)

        'create a reader
        Dim dr As SqlDataReader

        ' call ExecuteReader with the appropriate CommandBehavior
        If connectionOwnership = SqlConnectionOwnership.External Then
            dr = cmd.ExecuteReader()
        Else
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        End If

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()

        Return dr
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(connection As SqlConnection, commandType As CommandType, commandText As String) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteReader(connection, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(connection As SqlConnection, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that returns a resultset started")
        'pass through the call to the private overload using a null transaction value and an externally owned connection
        Return ExecuteReader(connection, DirectCast(Nothing, SqlTransaction), commandType, commandText, commandParameters, SqlConnectionOwnership.External)
    End Function

    ''' <summary>
    ''' 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(connection As SqlConnection, spName As String, ParamArray parameterValues As Object()) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that Execute a stored procedure and returns a resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connection.ConnectionString, spName)

            AssignParameterValues(commandParameters, parameterValues)

            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
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(transaction As SqlTransaction, commandType As CommandType, commandText As String) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteReader(transaction, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(transaction As SqlTransaction, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that returns a resultset started")
        '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

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
    ''' SqlTransaction 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>a SqlDataReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteReader(transaction As SqlTransaction, spName As String, ParamArray parameterValues As Object()) As SqlDataReader
        'Logger.LogInformation("Repository Method: ExecuteReader that Execute a stored procedure and returns a resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection.ConnectionString, spName)

            AssignParameterValues(commandParameters, parameterValues)

            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

#End Region

#Region "ExecuteScalar"

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
    ''' the connection string. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connectionString As String, commandType As CommandType, commandText As String) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteScalar(connectionString, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connectionString As String, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset started")
        'create & open a SqlConnection, and dispose of it after we are done.
        Using cn As New SqlConnection(connectionString)
            cn.Open()

            'call the overload that takes a connection in place of the connection string
            Return ExecuteScalar(cn, commandType, commandText, commandParameters)
        End Using
    End Function

    ''' <summary>
    ''' 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 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
    ''' </remarks>
    ''' <param name="connectionString">a valid connection string for a SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connectionString As String, spName As String, ParamArray parameterValues As Object()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that Execute stored procedure and returns a 1x1 resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            'call the overload that takes an array of SqlParameters
            Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters)
        Else
            'otherwise we can just call the SP without params
            Return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName)
        End If
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connection As SqlConnection, commandType As CommandType, commandText As String) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteScalar(connection, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connection As SqlConnection, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, connection, DirectCast(Nothing, SqlTransaction), commandType, commandText, commandParameters)

        'execute the command & return the results
        Dim retval As Object = cmd.ExecuteScalar()

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()
        Return retval
    End Function

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns a 1x1 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(connection As SqlConnection, spName As String, ParamArray parameterValues As Object()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that Execute a stored procedure and returns a 1x1 resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(transaction As SqlTransaction, commandType As CommandType, commandText As String) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteScalar(transaction, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command</param>
    ''' <param name="commandParameters">an array of SqlParamters used to execute the command</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(transaction As SqlTransaction, commandType As CommandType, commandText As String, ParamArray commandParameters As SqlParameter()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that returns a 1x1 resultset started")
        'create a command and prepare it for execution
        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters)

        'execute the command & return the results
        Dim retval As Object = cmd.ExecuteScalar()

        ' detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear()
        Return retval
    End Function

    ''' <summary>
    ''' Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
    ''' SqlTransaction 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.
    ''' </summary>
    ''' <remarks>
    ''' This method provides no access to output parameters or the stored procedure's return value parameter.
    ''' 
    ''' e.g.:  
    '''  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
    ''' </remarks>
    ''' <param name="transaction">a valid SqlTransaction</param>
    ''' <param name="spName">the name of the stored procedure</param>
    ''' <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    ''' <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
    Public Shared Function ExecuteScalar(transaction As SqlTransaction, spName As String, ParamArray parameterValues As Object()) As Object
        'Logger.LogInformation("Repository Method: ExecuteScalar that Execute a stored procedure and returns a 1x1 resultset started")
        'if we receive parameter values, we need to figure out where they go
        If (parameterValues IsNot Nothing) AndAlso (parameterValues.Length > 0) Then
            'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            Dim commandParameters As SqlParameter() = SqlHelperParameterCache.GetSPParameterSet(transaction.Connection.ConnectionString, spName)

            'assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            '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

#End Region

#Region "ExecuteXmlReader"

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
    ''' </remarks>
    ''' <param name="connection">a valid SqlConnection</param>
    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
    ''' <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
    ''' <returns>an XmlReader containing the resultset generated by the command</returns>
    Public Shared Function ExecuteXmlReader(connection As SqlConnection, commandType As CommandType, commandText As String) As XmlReader
        'Logger.LogInformation("Repository Method: ExecuteXmlReader that returns a resultset and takes no parameters started")
        'pass through the call providing null for the set of SqlParameters
        Return ExecuteXmlReader(connection, commandType, commandText, DirectCast(Nothing, SqlParameter()))
    End Function

    ''' <summary>
    ''' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    ''' using the provided parameters.
    ''' </summary>
    ''' <remarks>
    ''' e.g.:  
    '''  XmlReader r