.NET Stored Procedures in Yukon
You have heard about .NET Stored Procedues in Yukon. You have also heard about the power of TSQL. So which to use, TSQL or .NET? Here is the best rule of thumb. TSQL is for data access. Just about 99% of the time you will want to use TSQL to return data. When you want to do heavy CPU computational "things" you will want to use a CLR Language like C# or VB .NET. Here are some scenarios where a CLR language is much better:
1. Your queries have very complex procedural logic and require intensive computation
2. You need to access external resources like a text file or event log
3. You want to take advantage of .NET Base class libraries (like RegEx for example)
4. You want to leverage you database code across multiple projects
How does this work? You create a .NET assembly in Visual Studio and use what is called the In-Process Service Provider, or ADO in-proc for short. To use the In-Process Service Provider you can use the System.Data.SqlServer namespace to get access to all the classes in ADO In-proc. Then using your .NET language and ADO In-Proc you write some stored procedures, etc and then compile the assembly and bind it to your database. Now you can call your new database objects. Let's take a look at how to do that. Let's get started. Open up Visual Studio (you will need a beta of the next version of Visual Studio) and select "SQL Server Project" from the New Project dialog box. After you set your project type to SQL Server Project, Visual Studio asks you to set a "Add Database Reference" or basically set an active connection. Lastly, add a new item to your Visual Studio project like normal and choose a "stored procedure" from the dialog.
Ok, we are now ready to start coding. We are going to return a single value back to the calling application by querying the database using the SqlServer namespace classes and returning a single value. The first thing that you will notice is the namespaces used, by default when Visual Studio loads up a Stored Procedure project it adds the three new namespaces for you:
sing System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
These are the namespaces that you will use in your .NET Stored Procedure. The most important one is the SqlServer namespace. Here is a simple SP.
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public class CSingleValue
//My first .NET Stored Procedure! :)
{
[SqlProcedure]
public static int SingleValue()
{
//Use a SqlCommand object and the SqlContext
//SqlContext will always return you an active
//command (or what you ask for) from the current
//database that exists for your project
//notice a lot like SqlClient, but no connection
SqlCommand cmd = SqlContext.GetCommand();
//now that we have an active command we are going
//to set the command text to return a single value
//here we are going to send back the total sales
//figure in the Northwind database
cmd.CommandText = "select Sum((UnitPrice*Quantity)-Discount) From [Order Details] As TotalSales";
//now we have to execute a datareader to get the single row
SqlDataReader dr = cmd.ExecuteReader();
//our datareader has the value in it
//now do something with it, like return it to
//as the result of the procedure
dr.Read();
int intTotalSales = (int)dr["TotalSales"];
//return the value
return intTotalSales;
}
};
Let's dissect the code. First you use a SqlCommand from the SqlServer namespace and create it via the SqlContext object. SqlContext is your friend. For starters it will always give you the current connection of the calling function and in this case set all of the command's behaviors due to the scope of the connection. The next line of code sets the CommandText property of the Command object to a valid SQL statement. Then we create and execute a SqlDataReader and read the results into a variable and return that value.
Easy no? Well we are not there just yet. You have to bind your stored procedure to your database. Visual Studio should do this for you, but here is how to manually bind it.
Create Assembly SqlServerProject1
From 'C:\SqlServerProject1.dll'
With Permission_Set=SAFE
You have three options with the permissions:
1. Safe
2. External_Access
3. UnSafe
Safe will only permit you access inside the database objects, unable to access the outside world. The assembly must be type-safe. External_Access gives you all of safe, but allows you to access the outside world, files, system resources, etc. UnSafe does not have to be type-safe and can call unmanaged code. Now you have to bind your procedure to the database, once again Visual Studio should do this for you, but here is how to manually do it:
Create PROCEDURE SingleValue
As
External Name SqlServerProject1:[SqlServerProject1.CSingleValue]::SingleValue
Calling your stored procedure is quite simple, just call it like any other stored procedure in SQL Workbench or in client side ADO .NET code:
Exec SingleValue