Use the SQLHelper class to call stored procedures with output and return parameters

Notes for using SqlHelper (stored procedures with output and return parameters ):

In SqlHelper, if your stored procedure parameters include output and return parameters. Some functions cannot be used. Specifically:

        In SQLHelp, if the input parameters include params SqlParameter [] commandParameters, it is okay. The problem is that the input parameters include params object [] parameterValues:

For example:
Public static int ExecuteNonQuery (string connectionString, CommandType commandType, string commandText, params SqlParameter [] commandParameters)This is not mentioned.

This
Public static int ExecuteNonQuery (string connectionString, string spName, params object [] parameterValues)
Problems mentioned.

The following is a list of functions with these problems:

Public static int ExecuteNonQuery (string connectionString, string spName, params object [] parameterValues)
Public static int ExecuteNonQuery (SqlConnection connection, string spName, params object [] parameterValues)
Public static int ExecuteNonQuery (SqlTransaction transaction, string spName, params object [] parameterValues)

Public static DataSet ExecuteDataset (string connectionString, string spName, params object [] parameterValues)
Public static DataSet ExecuteDataset (SqlConnection connection, string spName, params object [] parameterValues)
Public static DataSet ExecuteDataset (SqlTransaction transaction, string spName, params object [] parameterValues)

Public static SqlDataReader ExecuteReader (string connectionString, string spName, params object [] parameterValues)
Public static SqlDataReader ExecuteReader (SqlConnection connection, string spName, params object [] parameterValues)
Public static SqlDataReader ExecuteReader (SqlTransaction transaction, string spName, params object [] parameterValues)

Public static object ExecuteScalar (string connectionString, string spName, params object [] parameterValues)
Public static object ExecuteScalar (SqlConnection connection, string spName, params object [] parameterValues)
Public static object ExecuteScalar (SqlTransaction transaction, string spName, params object [] parameterValues)

Public static XmlReader ExecuteXmlReader (SqlConnection connection, string spName, params object [] parameterValues)
Public static XmlReader ExecuteXmlReader (SqlTransaction transaction, string spName, params object [] parameterValues)

Public static void FillDataset (string connectionString, string spName,
  DataSet dataSet, string [] tableNames,
  Params object [] parameterValues)
Public static void FillDataset (SqlConnection connection, string spName,
  DataSet dataSet, string [] tableNames,
  Params object [] parameterValues)
Public static void FillDataset (SqlTransaction transaction, string spName,
  DataSet dataSet, string [] tableNames,
  Params object [] parameterValues)

 

The features of functions are as follows:
Params object [] parameterValues

If you need to use these functions, you will see that you cannot receive the returned Stored Procedure output and returned parameters.

Cause:
     In the internal implementation of these functions, he converts object [] parameterValues to SqlParameter [] commandParameters, while commandParameters is a local variable. After leaving this function, these cannot be accessed again. Before return, SqlHelper does not convert the content of commandParameters to parameterValues. Therefore, you cannot access the returned or output parameter values.

See the implementation of one of the functions:
Public static XmlReader ExecuteXmlReader (SqlConnection connection, string spName, params object [] parameterValues)
{
    If (connection = null) throw new ArgumentNullException ("connection ");
    If (spName = null | spName. Length = 0) throw new ArgumentNullException ("spName ");
    If (parameterValues! = Null) & (parameterValues. Length> 0 ))
   {
         SqlParameter [] commandParameters = SqlHelperParameterCache. GetSpParameterSet (connection, spName );
         AssignParameterValues (commandParameters, parameterValues );
         Return ExecuteXmlReader (connection, CommandType. StoredProcedure, spName, commandParameters );
   }
   Else
   {
         Return ExecuteXmlReader (connection, CommandType. StoredProcedure, spName );
   }
}

       I have never noticed this before. In the code written a few days ago, the parameters in the stored procedure are always unable to be returned. during debugging, I never thought about SqlHelper's situation. It took me a long time to find the problem and it was very depressing. Take it for granted. Debugging took a detour most of the time.