SQL Server allows the creation of stored procedures with C#. A stored procedure is a subroutine, and they 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:
SELECT SalesOrderIO, OrderDate, DueOate, ShipOate FROMSales.SalesOrderHeader
WHERE(CustomerIO = @CustomerIO)
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 SqlCommaIfd 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 26, the SQL SELECT statement is set and one parameter is added. The ExecuteReader () method returns a SqloataReader object, This reader object is returned to the client by invoking the Send () method of the SqlPipe:
CLR stored procedures are deployed to SQL Server either using Visual Studio or with the Create RESOUCRE statement, With this SQL statement the parameters of the stored procedure as well as the name of the assembly, class,and method:
CREATE PROCEDURE GetCustomerOrdersCLR
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 Command Type preoperty must be set to Command Type, Stored Procedure, The method ExecuteReader () returns a SqlDataReader object to read record by record:
Invoking the stored procedure written with T-SQL or with 0# 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 withID3:
As you have 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 cryptogr’aphy classes.