Stored Procedures


SQL Server 2005 allows creating stored procedures with C#. However, this isn't 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 CustOrdersOrders that is part of the Northwind database. This stored procedure returns orders from the customer that is specified with the parameter CustomerID:

 CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) AS SELECT OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE CustomerID = @CustomerID  ORDER BY OrderID 

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 similar to the code you saw in Chapter 19, 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 GetCustomersOrders(string customerId) { SqlConnection connection = new SqlConnection("Context Connection=true"); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "SELECT OrderID, OrderDate, RequiredDate, " + "ShippedDate FROM Orders " + "WHERE CustomerId = @CustomerID ORDER BY OrderID"; command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); command.Parameters["@CustomerID"].Value = customerId; SqlDataReader reader = command.ExecuteReader(); SqlPipe pipe = SqlContext.Pipe; pipe.Send(reader); } }; 

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 GetCustomersOrders ( @CustomerID nchar(5) ) AS EXTERNAL NAME Demo.StoredProcedures.GetCustomersOrders 

Using Stored Procedures

The CLR stored procedure can be invoked just like a normal 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 is defined. GetCustomerOrders is the name of the previously created stored procedure. The method ExecuteReader() returns a SqlDataReader object to read record by record:

 using System.Data.SqlClient; //... string dsn = @"server=localhost;database=Northwind;trusted_connection=true"; SqlConnection connection = new SqlConnection(dsn); SqlCommand command = connection.CreateCommand(); command.CommandText = "GetCustomersOrders"; command.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter("@customerId", "ALFKI"); command.Parameters.Add(param); connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Console.WriteLine("{0} {1}", reader["OrderID"], reader["OrderDate"]); } reader.Close(); Console.ReadLine(); 



Professional C# 2005
Pro Visual C++ 2005 for C# Developers
ISBN: 1590596080
EAN: 2147483647
Year: 2005
Pages: 351
Authors: Dean C. Wills

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net