Data Access Application Block .NET 2.0: Get Return Values, Output Values
Using SqlHelper in .NET 2.0 and getting return values and output values.
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 aDataSet
from a SQL query.ExecuteReader
generates aSqlDataReader
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. SqlDataReader
s are like forward-only, read-only recordsets from ADO classic. They are good for filling ListBox
es and CheckBoxList
s.
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 aSqlConnection
.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
- ADataSet
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:
- Have an array that has the values of the Stored Procedure parameters where index [0] is always for the return value.
- We execute the method, and during
ExecuteDataset
, theSqlHelper
class will give us back the return value (magic? No, we have work to do). - 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:
- Pass an array of values having index 0 for the return value. Let's call it
ArrayA
. - Call the
SqlHelperParameterCache.GetSpParameterSet
which returns us an array of SQL parameter objects. Let's call itArrayB
. - 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.
- Calling
AssignParameterValues
gives values to our parameters. You mapArrayA
values toArrayB
and assign the parameter values. - Call
ExecuteDataset
- by running this method, we should get an updatedReturnValue
parameter inArrayB
, notArrayA
.
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 Insert
s and Update
s.
\\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.