Stored Procedures | Professional C# 2005 with .NET 3.0

SQL Server 2005 allows the creation of stored procedures with C#. A stored procedure is a subroutine. Stored procedures are physically stored in the database. They definitely are not to be considered a replacement for T-SQL. T-SQL still has an advantage when the procedure is mainly data-driven.

Take a look at the T-SQL stored procedure GetCustomerOrders, which returns information from customer orders from the AdventureWorks database. This stored procedure returns orders from the customer that is specified with the parameter CustomerID:

  CREATE PROCEDURE GetCustomerOrders    (    @CustomerID int    ) AS SELECT SalesOrderID, OrderDate, DueDate, ShipDate FROM Sales.SalesOrderHeader    WHERE (CustomerID = @CustomerID)    ORDER BY SalesOrderID

Creating Stored Procedures

As you can see in the following code listing, implementing the same stored procedure with C# has more complexity. The attribute [SqlProcedure] is used to mark a stored procedure for deployment. With the implementation, a SqlCommand object is created. With the constructor of the SqlConnection object, the string “Context Connection=true” is passed to use the connection that was already opened by the client calling the stored procedure. Very similarly to the code you saw in Chapter 25, the SQL SELECT statement is set and one parameter is added. The ExecuteReader() method returns a SqlDataReader object. This reader object is returned to the client by invoking the Send() method of the SqlPipe:

  using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class StoredProcedures {    [SqlProcedure]    public static void GetCustomerOrdersCLR(int customerId)    {       SqlConnection connection = new SqlConnection("Context Connection=true");       connection.Open();       SqlCommand command = new SqlCommand();       command.Connection = connection;       command.CommandText = "SELECT SalesOrderID, OrderDate, DueDate, ShipDate " +             "FROM Sales.SalesOrderHeader " +             "WHERE (CustomerID = @CustomerID)" +             "ORDER BY SalesOrderID";       command.Parameters.Add("@CustomerID", SqlDbType.Int);       command.Parameters["@CustomerID"].Value = customerId;       SqlDataReader reader = command.ExecuteReader();       SqlPipe pipe = SqlContext.Pipe;       pipe.Send(reader);       connection.Close();    } };

CLR stored procedures are deployed with SQL Server either using Visual Studio or with the CREATE PROCEDURE statement. With this SQL statement the parameters of the stored procedure are defined, as well as the name of the assembly, class, and method:

  CREATE PROCEDURE GetCustomerOrdersCLR (    @CustomerID nchar(5) ) AS EXTERNAL NAME Demo.StoredProcedures.GetCustomerOrdersCLR

Using Stored Procedures

The CLR stored procedure can be invoked just like a T-SQL stored procedure by using classes from the namespace System.Data.SqlClient. First, a SqlConnection object is created. The CreateCommand() method returns a SqlCommand object. With the command object, the name of the stored procedure GetCustomerOrdersCLR is set to the CommandText property. As with all stored procedures, the CommandType property must be set to CommandType.StoredProcedure. The method ExecuteReader() returns a SqlDataReader :

  using System.Data.SqlClient; //...       string connectionString =          @"server=(local);database=AdventureWorks;trusted_connection=true";       SqlConnection connection = new SqlConnection(connectionString);       SqlCommand command = connection.CreateCommand();       command.CommandText = "GetCustomerOrdersCLR";       command.CommandType = CommandType.StoredProcedure;       SqlParameter param = new SqlParameter("@customerId", 3);       command.Parameters.Add(param);       connection.Open();       SqlDataReader reader =             command.ExecuteReader(CommandBehavior.CloseConnection);       while (reader.Read())       {          Console.WriteLine("{0} {1:d}", reader["SalesOrderID"],             reader["OrderDate"]);       }       reader.Close();

Tip

The classes from the namespace System.Data.SqlClient are discussed in Chapter 25, “Data Access with .NET.”

Invoking the stored procedure written with T-SQL or with C# is not different at all. The code for calling stored procedures is completely identical; from the caller code you don’t know if the stored procedure is implemented with T-SQL or the CLR. An extract of the result shows the order dates for the customer with ID 3:

 44124 9/1/2001 44791 12/1/2001 45568 3/1/2002 46377 6/1/2002 47439 9/1/2002 48378 12/1/2002

As you’ve seen, mainly data-driven stored procedures are better done with T-SQL. The code is a lot shorter. Writing stored procedures with the CLR has the advantage if you need some specific data-processing, for example, by using the .NET cryptography classes.