Today, my co-worker said that i need help. Then i looked at him screen than i saw that, he was working on a SqlCommand object for fetching some output parameters from a stored procedure. He was using Microsoft's SQLHelper class and then we noticed that, that class was not have a method which returns output parameters. Simply we added that method to fix what we need;


/// <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 static SqlParameter[] ExecuteNonQueryReturnParms(string connectionString, string spName, params object [] parameterValues)
    if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
    if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

    // If we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
        // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
        SqlParameter[] commandParameters = 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
        ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);

        return commandParameters;
        // Otherwise we can just call the SP without params
        ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);

        return new SqlParameter[] { };

Happy coding :)

If you like this, follow my RSS channel!