Using a stored procedure in C# ADO.NET is easy. You simply have to follow four steps...
---
title: "Commands with Stored Procedures - ADO.NET in a Nutshell"
author: Bill Hamilton
date: April 1, 2003
source: https://www.oreilly.com/library/view/adonet-in-a/0596003617/ch04s05.html
---
## Commands with Stored Procedures
Stored procedures---SQL scripts stored in the database---are a key ingredient in
any successful large-scale database applications. One advantage of stored
procedures is improved performance. Stored procedures typically execute faster
than ordinary SQL statements because the database can create, optimize, and
cache a data access plan in advance. Stored procedures also have a number of
other potential benefits. They:
- Improve security. A client can be granted permissions to execute a stored
procedure to add or modify a record in a specify way, without having full
permissions on the underlying tables.
- Are easy to maintain, because they are stored separately from the
application code. Thus, you can modify a stored procedure without
recompiling and redistributing the .NET application that uses it.
- Add an extra layer of indirection, potentially allowing some database
details to change without breaking your code. For example, a stored
procedure can remap field names to match the expectations of the client
program.
- Reduce network traffic, because SQL statements can be executed in batches.
Of course, stored procedures aren't perfect. Most of their drawbacks are in the
form of programming annoyances:
- Using stored procedures in a program often involves importing additional
database-specific details (such as parameter data types) into your code. You
can control this problem by creating a dedicated component that encapsulates
all your data access code.
- Stored procedures are created entirely in the SQL language (with variations
depending on the database vendor) and use script-like commands that are
generally more awkward than a full-blown object-oriented language such as C#
or VB .NET, particularly with respect to error handling and code reuse.
Microsoft promises that the next version of SQL Server (code-named Yukon)
will allow stored procedures to be written using .NET languages like C#.
Stored procedures can be used for any database task, including retrieving rows
or aggregate information, updating data, and removing or inserting rows.
### Executing a Stored Procedure
Using a stored procedure with ADO.NET is easy. You simply follow four steps:
1. Create a `Command`, and set its `CommandType` property to `StoredProcedure`.
2. Set the `CommandText` to the name of the stored procedure.
3. Add any required parameters to the `Command.Parameters` collection.
4. Execute the `Command` with the `ExecuteNonQuery( )`, `ExecuteScalar( )`, or
`ExecuteQuery( )` method (depending on the type of output generated by the
stored procedure).
For example, consider the generic update command defined earlier:
<a name="example-4-3"></a>
**Example 4-3.** Updating a record with a parameterized command (SQL Server)
```sql
UPDATE Categories
SET CategoryName=@CategoryName
WHERE CategoryID=@CategoryID
```
You can encapsulate this logic in a stored procedure quite easily. You'll
probably use Visual Studio .NET or a third-party product (like SQL Server's
Enterprise Manager) to create the stored procedure, but the actual stored
procedure code will look something like this:
```sql
CREATE PROCEDURE UpdateCategory
(
@CategoryID int,
@CategoryName nvarchar(15)
)
AS
UPDATE Categories SET CategoryName=@CategoryName
WHERE CategoryID=@CategoryID
GO
```
You'll notice that the actual SQL statement is unchanged. However, it is now
wrapped in a SQL stored procedure called `UpdateCategory` that requires two
input parameters. The stored procedure defines the required data types for all
parameters, and you should pay close attention: your code must match exactly.
[Example 4-5][1] rewrites [Example 4-3][2] to use this stored procedure. The
only two changes are found in the `CommandText` and `CommandType` properties of
the `Command` object.
<a name="example-4-5"></a>
**Example 4-5.** Updating a record with a stored procedure
```cs
// SProcUpdateSQL.cs - Updates a single Category record
using System;
using System.Data;
using System.Data.SqlClient;
public class UpdateRecord
{
public static void Main()
{
string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "UpdateCategory";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
param.Value = "Beverages";
param = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
param.Value = 1;
// Execute the command.
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
// Display the result of the operation.
Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
}
}
```
One common use of a stored procedure is to insert a record in a table that uses
a unique identity field. This type of stored procedure accepts several input
parameters that identify the data for new row and one output parameter that
returns the automatically generated unique ID to your .NET code. This saves you
re-querying the database to find this information.
The Northwind sample database doesn't use this technique; the database used by
the IBuySpy e-commerce store does. You can install the store database with
IBuySpy code download from Microsoft's site or just refer to the following
example.
Here is the `CustomerAdd` stored procedure code in the store database:
```sql
CREATE Procedure CustomerAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50),
@CustomerID int OUTPUT
)
AS
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
SELECT
@CustomerID = @@Identity
GO
```
This stored procedure defines three input parameter and one output parameter for
the generated ID. The stored procedure begins by inserting the new record and
sets the output parameter using the special global SQL Server system function
`@@Identity`.
Using this routine in code is just as easy, but you need to configure the
`@CustomerID` parameter to be an output parameter (input is the default) (see
[Example 4-6][3]).
<a name="example-4-6"></a>
**Example 4-6.** Using a stored procedure with an output parameter
```cs
// AddCustomer.cs - Runs the CustomerAdd stored procedure.
using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
public static void Main()
{
string connectionString = "Data Source=localhost;" +
"Initial Catalog=store;Integrated Security=SSPI";
string procedure = "CustomerAdd";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
// Configure command and add input parameters.
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
param.Value = "John Smith";
param = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
param.Value = "john@mydomain.com";
param = cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
param.Value = "opensesame";
// Add the output parameter.
param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
// Execute the command.
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("New customer has ID of " + param.Value);
}
}
```
Your stored procedure is free to return any type of information in an output
parameter, as long as it uses the correct data type. There's also no limit to
the number of parameters, output or otherwise, that you can use with a stored
procedure.
### Stored Procedure Return Values
Stored procedures can also return information through a return value. The return
value works in much the same way as an output parameter, but it isn't named, and
every stored procedure can have at most one return value. In SQL Server stored
procedure code, the return value is set using the RETURN statement.
Here's how the `CustomerAdd` stored procedure can be rewritten to use a return
value instead of an output parameter:
```sql
CREATE Procedure CustomerAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50),
)
AS
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
RETURN @@Identity
GO
```
This revision carries no obvious advantages or disadvantages. It's really a
matter of convention. Different database developers have their own system for
determining when to use a return value; many use a return value to provide
ancillary information such as the number of rows processed or an error
condition.
As with input and output parameters, the return value is represented by a
`Parameter` object. The difference is that the `Parameter` object for a return
value must have the `Direction` property set to `ReturnValue`. In addition, some
providers (e.g., the OLE DB provider) require that the `Parameter` object
representing the return value is the first in the `Parameter` collection for a
`Command`.
[Example 4-7][4] shows how to call the revised `CustomerAdd` stored procedure.
<a name="example-4-7"></a>
**Example 4-7.** Using a stored procedure with a return value
```cs
// AddCustomerReturn.cs - Runs the CustomerAdd stored procedure.
using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
public static void Main()
{
string connectionString = "Data Source=localhost;" +
"Initial Catalog=store;Integrated Security=SSPI";
string procedure = "CustomerAdd";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
// Configure the command.
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
// Add the parameter representing the return value.
param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
// Add the input parameters.
param = cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
param.Value = "John Smith";
param = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
param.Value = "john@mydomain.com";
param = cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
param.Value = "opensesame";
// Execute the command.
con.Open();
cmd.ExecuteNonQuery();
con.Close();
param = cmd.Parameters["@CustomerID"];
Console.WriteLine("New customer has ID of " + param.Value);
}
}
```
So far, the stored procedure examples suffer in one respect: they import
numerous database-specific details into your code. Not only do you need to
hardcode exact parameter names, but you need to know the correct SQL Server data
type, and the field length for any text data.
One way to get around these details is to use a `CommandBuilder` class. This
class is used with `DataSet` updates (which we'll consider in [Chapter 5][5]),
but it also is useful when dealing with stored procedures. It allows you to
retrieve and apply all the parameter metadata for a command. The disadvantage of
this approach is that it requires an extra round trip to the data source. This
is a significant price to pay for simplified code, and as a result, you won't
see it used in enterprise-level database code.
Once the parameter information is drawn from the database, all you need to do is
set the parameter values. You can retrieve individual parameter objects either
by index number or by parameter name from the `Command.Parameters` collection.
[Example 4-8][6] shows how the `AddCustomer` code can be rewritten to use this
technique.
<a name="example-4-8"></a>
**Example 4-8.** Retrieving parameter information programmatically
```cs
// DeriveParameter.cs - Retrieves stored procedure parameter information
using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
public static void Main()
{
string connectionString = "Data Source=localhost;" +
"Initial Catalog=store;Integrated Security=SSPI";
string procedure = "CustomerAdd";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
// Configure command and add input parameters.
cmd.CommandType = CommandType.StoredProcedure;
// Execute the command.
con.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters[1].Value = "Faria MacDonald";
cmd.Parameters[2].Value = "joe@mydomain.com";
cmd.Parameters[3].Value = "opensesame";
cmd.Parameters[4].Value = DBNull.Value;
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("New customer has ID of " +
cmd.Parameters[4].Value);
}
}
```
### Warning
Note that though most .NET providers include a `CommandBuilder` class, they
aren't in any way generic. Different `CommandBuilder` classes don't inherit from
a common base class or implement a common interface, which means you can't use
this class generically.
Because deriving parameters adds extra overhead, it's not suitable for a
performance-critical application. It's a much better idea to create a dedicated
database component that encapsulates the code that creates and populates stored
procedure parameters and all the database-specific details.
[1]: #example-4-5 "Example 4-5. Updating a record with a stored procedure"
[2]: #example-4-3 "Example 4-3. Updating a record with a parameterized command (SQL Server)"
[3]: #example-4-6 "Example 4-6. Using a stored procedure with an output parameter"
[4]: #example-4-7 "Example 4-7. Using a stored procedure with a return value"
[5]: https://www.oreilly.com/library/view/adonet-in-a/0596003617/ch05s01.html "Chapter 5. DataReaders"
[6]: #example-4-8 "Example 4-8. Retrieving parameter information programmatically"