4.5 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.

4.5.1 Executing a Stored Procedure

Using a stored procedure with ADO.NET is easy. You simply follow four steps:

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

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 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.

Example 4-5. Updating a record with a stored procedure

// 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");
    }
}

4.5.2 Output Parameters

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

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).

Example 4-6. Using a stored procedure with an output parameter

// 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.

4.5.3 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:

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 shows how to call the revised CustomerAdd stored procedure.

Example 4-7. Using a stored procedure with a return value

// 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);

    }
}

4.5.4 Deriving Parameters

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.

Example 4-8. Retrieving parameter information programmatically

// 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.