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.
Using a stored procedure with ADO.NET is easy. You simply follow four steps:
Create a Command, and set its
CommandType property to
StoredProcedure.
Set the CommandText to the name of the stored
procedure.
Add any required parameters to the
Command.Parameters
collection.
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:
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:
CREATE PROCEDURE UpdateCategory
(
@CategoryID int,
@CategoryName nvarchar(15)
)
AS
UPDATE Categories SET CategoryName=@CategoryName
WHERE CategoryID=@CategoryID
GOYou’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 rewrites Example 4-3 to
use this stored procedure. The only two changes are found in the
CommandText and CommandType
properties of the Command object.
// 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 http://www.ibuyspy.com site or just refer to the following example.
Here is the CustomerAdd stored procedure code in
the store database:
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
GOThis 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).
// 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 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:
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
GOThis 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 shows how to call the revised
CustomerAdd stored procedure.
// 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), 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
shows how the AddCustomer code can be rewritten to
use this technique.
// 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);
}
}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.