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. 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)
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).
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:
- 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
, the SqlHelper
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 it ArrayB
.
- 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 map ArrayA
values to ArrayB
and assign the parameter values.
- 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 ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters =
AssignParameterValues(commandParameters, parameterValues);
dsReturn = ExecuteDataset(connectionString,
CommandType.StoredProcedure, spName, commandParameters);
UpdateParameterValues(commandParameters, parameterValues);
}
else
{
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
return dsReturn;
}
The code is straightforward. Pull the parameters for this Stored Procedure from the parameter cache (or discover them and populate the cache).
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:
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:
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;
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:
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.
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))
{
return;
}
{
throw new ArgumentException("Parameter count does " +
"not match Parameter Value count.");
}
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
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:
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
@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
IF (@intError = 0) BEGIN
SET @pInsertStatus = 'Insert Successfully'
SET @intReturn = @@IDENTITY
END ELSE BEGIN
SET @pInsertStatus = 'Insert Faild'
SET @intReturn = 0
END
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)
{
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 ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters =
SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
AssignParameterValues(commandParameters, parameterValues);
intReturn = ExecuteNonQuery(connectionString,
CommandType.StoredProcedure, spName, commandParameters);
UpdateParameterValues(commandParameters, parameterValues);
return intReturn;
}
else
{
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
.
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):
AssignParameterValues(commandParameters, parameterValues);
I only show here index 1, but they all have values:
Calling ExecuteNonQuery
:
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
:
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.