Data Access Application Block .NET 2.0: Get Return Values, Output Values

• Download source files - 1.11 MB

SqlHelper with a twist...A must read if you use SqlHelper

The first part of the article will show you how to get the return value from ExecuteDataSet. The second part of the article will show you how to get a return value and output values from ExecuteNonQuery.

This article will show you how to use Microsoft Application Blocks and be able to get your Stored Procedure's return value and output parameters. You will find many articles on the web about calling a Stored Procedure and getting the return value and output values using ADO.NET... but if you want to use SqlHelper, you are out of luck for getting your return value.

I will go in to the code of the SqlHelper class and explain how we can modify the code (just a bit, don't run away) to get our golden return value from our Stored Procedure.

A small review of the Sqlhelper class

The SqlHelper has four main functions. Let's review them:

• ExecuteDataset generates a DataSet from a SQL query.
• ExecuteReader generates a SqlDataReader from a SQL query.
• ExecuteScalar generates a single value object from a SQL query.
• ExecuteNonQuery runs a SQL query with no return value (we will change that soon).

ExecuteDataset: ExecuteDataset will run your basic SELECT query and generate a DataSet, which can then be bound to a server object or used to create a DataView. As with all of the methods, there are a number of overloads.

ExecuteReader: ExecuteReader is also for a SELECT statement, but it's usually reserved for situations where performance really matters. SqlDataReaders are like forward-only, read-only recordsets from ADO classic. They are good for filling ListBoxes and CheckBoxLists.

ExecuteScalar: The ExecuteScalar method has several uses, like returning a SELECT query with only one value such as a COUNT. But the most common usage will be to run an INSERT statement that returns the new row ID. This is a fairly common trick in Transact SQL, but it requires a CAST in the Stored Procedure to make sure that the resultant row ID is returned in the easiest format for .NET.

INSERT (ColumnName1, ColumnName2) VALUES (@parameter1, @parameter2) 
SELECT CAST(@@Identity AS INTEGER)

Developers were forced to use this method to return values back from a Stored Procedure to to your business class. Here is a sample of how you had to do it if you wanted to return the last ID number of an Insert. To keep things simple, the value is returned to .NET as an Object. To get an integer row ID back, use the Convert statement.

int newRowId = Convert.ToInt32(SqlHelper.ExecuteScalar(connString,
 "usp_InsertStuffProcedure",
 parameter1,
 parameter2));

Part 1 - An Example with ExecuteDataset in detail

Objective: Get a dataset from a Stored Procedure and get the return value from the Stored Procedure using ExecuteDataset from SqlHelper.

OK, let's get some code here:

• SqlProductProvider.cs will play the role of our business object.
• SqlHelper.cs will play the role of our data layer (Microsoft code).
• default.aspx (default.cs) will play the role of the UI layer.
• web.config - will hold our connection string.

Here is snapshot of our Stored Procedure:

SELECT
    ProductName
FROM
    Products
WHERE
     ProductName like @pProductName +'%'
     
     ....
     ....
     Return (1) -- This is the Return Value

The UI (default.cs) has a button for getting a DataSet object to link to a GridView (very simple). We are calling the business layer in order to get our DataSet (the business layer will talk to the data layer to give us the DataSet object).

// Pass the Letter "a" to get all products that start with the letter "a"
protected void Button2_Click(object sender, EventArgs e)
{
    GridView2.DataSource = sqlProductProvider.dsGetProductListsTest("a");
    GridView2.DataBind();
}

The sqlProductProvider has this code:

public static DataSet dsGetProductListsTest(String owner)
{
    int ReturnValue;
    object[] objParams = { 0, owner };
    
    DataSet  ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
    ReturnValue = (int)objParams[0];

    return ds;
}

Here is the part where I ask SqlHelper to give me the DataSet. Note that there are 9 overloaded methods to get a DataSet, but in this article, I will focus on only one way. The picture below shows the overloaded method that I will use (6 methods from 9).

Let's review the parameters we have to pass for ExecuteDataset overload #6:

• 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 (we talk about this one later...).
• Return - A DataSet containing the resultset generated by the command.

Let's review the code line by line.

Here, we should have the return value:

int ReturnValue;

Here is the array of parameters I am going to send to ExecuteDataset. In index [0], I have a value of 0, and the second value is a string that I am passing to my Stored Procedure. Index [0] is for the return value from the Stored Procedure, and index [1] is an input parameter for the Stored Procedure.

object[] objParams = { 0, owner };

Calling ExecuteDataset and getting my return value:

DataSet  ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
ReturnValue = (int)objParams[0];

Let's review:

1. Have an array that has the values of the Stored Procedure parameters where index [0] is always for the return value.
2. We execute the method, and during ExecuteDataset, the SqlHelper class will give us back the return value (magic? No, we have work to do).
3. Assign array index [0] to our int variable.

A closer look at the Sqlhelper dungeons... be afraid, be very afraid (joking... it's simple)

All the magic is happening in the ExecuteDataset part. Here is a global break down of how to get the return value, and then I will prove it with the debugger:

1. Pass an array of values having index 0 for the return value. Let's call it ArrayA.
2. Call the SqlHelperParameterCache.GetSpParameterSet which returns us an array of SQL parameter objects. Let's call it ArrayB.
3. This step just tells us how many parameters (including the return value) our Stored Procedure has, but I still don't have values in these parameters yet.
4. Calling AssignParameterValues gives values to our parameters. You map ArrayA values to ArrayB and assign the parameter values.
5. Call ExecuteDataset - by running this method, we should get an updated ReturnValue parameter in ArrayB, not ArrayA.

Here is the part where I show you how to get your return value or any output values that you may have. We are going to look in detail at ExecuteDataset (overload #6) and show you how to get the return value from the Stored Procedure with very little change in the code. First, let's have a global look at the ExecuteDataset (overload #6) method. I will explain line by line in detail afterwards.

internal static DataSet ExecuteDataset(string connectionString, 
         string spName, params object[] parameterValues)
{
    DataSet dsReturn;
    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)
        //Original code from sqlHelper
        //SqlParameter[] commandParameters = 
        //     SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
        // Added Parameter true to support ReturnValues
        SqlParameter[] commandParameters =
        //    SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true ); 

        // 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);
        //Modify code - just store the dataset to dsReturn
        dsReturn = ExecuteDataset(connectionString, 
                    CommandType.StoredProcedure, spName, commandParameters);

        //Update the array -  parameterValues from the new CommandParameters
        //that should have the ReturnValue
        UpdateParameterValues(commandParameters, parameterValues);
    }
    else
    {
        // Otherwise we can just call the SP without params
        //return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
        //Modify code
        dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
    }
    //Modify code
    return dsReturn;
}

The code is straightforward. Pull the parameters for this Stored Procedure from the parameter cache (or discover them and populate the cache).

// Pull the parameters for this stored procedure from
// the parameter cache (or discover them & populate the cache)
//Original code from sqlHelper
//SqlParameter[] commandParameters =
//   SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Added Parameter true to support ReturnValues
SqlParameter[] commandParameters = 
  SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);

I can tell SqlHelperParameterCache.GetSpParameterSet to have my return value (the original code does not support the return value in SqlHelperParameterCache.GetSpParameterSet). Have a look at the picture below to see the overloaded method:

After calling:

// Added Parameter true to support ReturnValues
SqlParameter[] commandParameters = 
   SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);

The debugger shows us that SqlHelper found my ReturnValue param and ProductName param, and both of them have no value which is normal.

After calling:

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

We can see that our values that we passed from the Business layer (the array) is stored in SqlParameter[] commandParameters. Here is the code so you don't have to scroll up.

public static DataSet dsGetProductListsTest(String owner)
{
    int ReturnValue;
    //0 - for the RetunValue, owner is the string we passed
    // from the UI (have the value of "a") 
    object[] objParams = { 0, owner }; 

    DataSet  ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);
    ReturnValue = (int)objParams[0];

    return ds;
}

Here is the debug to prove it:

After calling:

// Call the overload that takes an array of SqlParameters
//return ExecuteDataset(connectionString, CommandType.StoredProcedure, 
//                      spName, commandParameters);

//Modify code - just store the dataset to dsReturn
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, 
                          spName, commandParameters);

We can see the we get our return value in commandParameters:

The array does not have the same values as CommandParameters:

So far so good. But having the return value in CommandParameters in SqlHelper (deep in the Data Layer) can't help us much. Our objective is to get the return value in the Business layer and the UI layer. In order to do that, I had to copy the values from the CommandParameters array to the array that I passed and update index 0 with our value of the return value. Well, at this point, I had to write a small function that does that.

//Update the array -  parameterValues from
// the new CommandParameters that should have the ReturnValue
UpdateParameterValues(commandParameters, parameterValues);

And here is the method. I placed it in sqlHelper.cs just after the AssignParameterValues method (you have the complete code in the download sample).

private static void UpdateParameterValues(
        SqlParameter[] commandParameters, object[] parameterValues)
{
    if ((commandParameters == null) || (parameterValues == null))
    {
        // Do nothing if we get no data
        return;
    }

    // We must have the same number of values
    // as we pave parameters to put them in
    {
        throw new ArgumentException("Parameter count does " + 
                  "not match Parameter Value count.");
    }

    // Iterate through the SqlParameters, assigning the values
    // from the corresponding position in the value array
    for (int i = 0, j = commandParameters.Length; i < j; i++)
    {
        //Update the Return Value
        if (commandParameters[i].Direction == ParameterDirection.ReturnValue)
        {
            parameterValues[i] = commandParameters[i].Value;
        }

        if (commandParameters[i].Direction == ParameterDirection.InputOutput)
            parameterValues[i] = commandParameters[i].Value;
    }
}

One thing to point out is I always put the return value in my array in index 0 and value 0. This is the code that gives me the return value in my array:

//Update the Return Value
if (commandParameters[i].Direction == ParameterDirection.ReturnValue)
{
    parameterValues[i] = commandParameters[i].Value;
}

And for the case of the output parameters, I place them at the end of the array after the input parameters. In this example, I did not use any output parameter. I will show it later when using ExecuteNonQuery for Inserts and Updates.

\\Copy Input and Output Param to the Array 
if (commandParameters[i].Direction == ParameterDirection.InputOutput) 
    parameterValues[i] = commandParameters[i].Value;

Let's have a look at the debugger at this point. Here you can see that the commandParameters array and the parameterValues array have the same values. And most importantly, the return value!

In the .NET array are ByRef types and not ByValue, which means that our array is updated in the Business layer. Now, I will show you the big picture in our Business layer that calls SqlHelper. This method is in our Business layer in the SqlProductProvider class. I will prove that my array gets updated and I have the return value, step by step.

public static DataSet dsGetProductListsTest(String owner)
{
    int ReturnValue;
    object[] objParams = { 0, owner };
    DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, 
                           "GetProjectLists", objParams);
    ReturnValue = (int)objParams[0];
    return ds;
}

Before running SqlHelper.ExecuteDataset, our Array[0] = 0 (that's the default value I gave index 0).

After running:

DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "GetProjectLists", objParams);

Here you can see that I got my ReturnValueobjParams[0] = 1 and my array gets updated from ExecuteDataset (our Data layer).

Part 2 - An example with ExecuteNonQuery - getting the return value and output values

SqlHelper does the same steps with ExecuteNonQuery; only with this method, we don't get back a DataSet or DataReader. In fact, using this method will make more sense to get a return value and output values. Our Stored Procedure does a very simple Insert and have an output value called @pInsertStatus, and our return value will give us the @@IDENTITY value of the new record. Our objective in this sample is to get the return value and our new output value.

Create PROCEDURE Products_SP_Insert
-- ****************************************************************************
-- PARAM
-- ****************************************************************************
@pProductName nvarchar(40), 
@pSupplierID int, 
@pCategoryID int, 
@pQuantityPerUnit nvarchar(20), 
@pUnitPrice money, 
@pUnitsInStock smallint, 
@pUnitsOnOrder smallint, 
@pReorderLevel smallint, 
@pDiscontinued bit,
@pInsertStatus varchar(50) output
AS
....

INSERT INTO 
    Products
    (
    ProductName ,
    SupplierID ,
    CategoryID ,
    QuantityPerUnit ,
    UnitPrice ,
    UnitsInStock ,
    UnitsOnOrder ,
    ReorderLevel ,
    Discontinued 
    )
    VALUES
    (
    @pProductName ,
    @pSupplierID ,
    @pCategoryID ,
    @pQuantityPerUnit ,
    @pUnitPrice ,
    @pUnitsInStock ,
    @pUnitsOnOrder ,
    @pReorderLevel ,
    @pDiscontinued 
    )

SET @intError = @@Error
SET 

-- Error validation
IF (@intError = 0) BEGIN
    SET @pInsertStatus = 'Insert Successfully'
    SET @intReturn = @@IDENTITY
END ELSE BEGIN
    SET @pInsertStatus = 'Insert Faild'
    SET @intReturn = 0
END
-- ****************************************************************************
-- RETURN
-- ****************************************************************************
SET NOCOUNT OFF
RETURN (@intReturn)

From the UI, I have this code. Note: all the output parameters are at the end of the array and return values are at index 0.

protected void Button3_Click(object sender, EventArgs e)
{
    // In a real world project it would be good to pass these values 
    // by Class object (or typed dataset) and give it to the Bussniess layer
    // and in the bussiness layer you can fill up the array, 
    //to make things simple I hard coded the values here in the UI

    object[] objParams = { 0,"ProductTest",1, 1, 
                           "48 - 6 oz jars",15.00,50,20,0,false,null};
    sqlProductProvider.InsertProduct(objParams);
}

Here is the code in the Business layer. I use the same overloaded method, like I did before with the ExecuteDataSet.

public static int InsertProduct(object[] objParams)
{
    int ReturnValue;
    string OutPutValue;

    ReturnValue = SqlHelper.ExecuteNonQuery(ConnectionString, "Products_SP_Insert", objParams);
    ReturnValue = (int)objParams[0];
    OutPutValue = (string)Params[10];

    return ReturnValue;
}

Here is the SqlHelper.ExecuteNonQuery method:

internal static int ExecuteNonQuery(string connectionString, 
         string spName, params object[] parameterValues)
{
    int intReturn;
    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,true);

        // 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); 
        intReturn = ExecuteNonQuery(connectionString, 
                    CommandType.StoredProcedure, spName, commandParameters);

        //Update the array -  parameterValues from the new CommandParameters
        //That should have the ReturnValue (I add this Method)

        UpdateParameterValues(commandParameters, parameterValues);
        return intReturn;
    }
    else
    {
        // Otherwise we can just call the SP without params
        return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
    }
}

Let's look at the debugger line by line and see the changes. Here is a look at our array where index 0 is our return value and index 10 is our output value, for now they are empty.

Here we call GetSpParameterSet. Note: I set the last parameter to true to get the return value in SqlHelperParameterCache.GetSpParameterSet.

// Pull the parameters for this stored procedure
// from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(
                                       connectionString, spName,true);

Our debug shows:

Here we call AssignParameterValues (link our array to the parameter array and copy the values):

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

I only show here index 1, but they all have values:

Calling ExecuteNonQuery:

//I changed this line to the line below
//return ExecuteNonQuery(connectionString,
//       CommandType.StoredProcedure, spName, commandParameters);
intReturn = ExecuteNonQuery(connectionString, CommandType.StoredProcedure, 
                            spName, commandParameters);

Our debug for index 0 and index 10 (return value and output value) is shown below. As you can see, I have my return and output values in commandParameters, but still not in my array.

Calling UpdateParameterValues:

//Update the array -  parameterValues from the new CommandParameters
// that should have the Return Value (I add this Method)
UpdateParameterValues(commandParameters, parameterValues);

Our debug shows:

Our array is updated with the return value in index 0, and we get our output value at index 10. After all this, our Business layer will have the return value and the output value.

ReturnValue = SqlHelper.ExecuteNonQuery(ConnectionString, 
                        "Products_SP_Insert", objParams);
ReturnValue = (int)objParams[0];
OutPutValue = (string)Params[10];

And to prove it:

Summary

Well, I am done! In this article, I showed you how you can have a very clean BL with only 3 to 4 lines of code, calling a Stored Procedure and getting the return value and the output values. You don't need to worry about anything, but just pass the values in good order to SqlHelper.

Note: I could not get the return value for the ExecuteDataReader because it keeps an open connection to the SQL Server. If you really need to get a return value from a "RecordSet", then use ExectueDataSet and use the DataTable object. I have included the full source code of the SQLHelper class (with my small modifications).

Feedback

Feel free to leave any feedback on this article. Hope you liked it.