Description
This article explains the various steps involved in writing and executing a Yukon CLR Procedure. This article also is a starting point to develop complex data crunching SQL procedures.
Prerequisites:
1. Successful installation of SQL Server Yukon beta1.
Introduction
Yukon is the latest version of SQL server released by Microsoft. One of the most promising features of Yukon is its integration with the .NET CLR runtime. For more info on Yukon look at the Yukon FAQ. A SQL Server object which is written in a .NET compliant language is called as a .NET Routine
A .NET routine can be of the following types
1. Stored procedures.
2. User defined Scalar Functions
3. User defined Table Valued Functions
4. User defined Triggers.
A .NET Stored procedure is no different in usage than compared to the earlier SQL Stored Procedure. In earlier versions of the SQL server a stored procedure would be a single or multiple batch's of T-SQL statements. In a .NET Stored Procedure the comprising code could be a mixture of T-SQL statements or a .NET compliant language constructs.
Writing your first .NET routine consists of the following steps. (For the purpose of this article i would be using C# as the .NET language for development.)
1. Create a class with references to System.Data.SqlServer and System.Data.Sql.
2. Implement the Business logic in a public method of this class.
3. Compile the C# program either at command line or using Visual Studio.NET.
4. Register the C# assembly created with the database.
5. Register the .NET Routine with the database.
6. Use the .NET Routine.
For the purpose of this article we would be developing our first .NET Routine as a stored procedure.
You can either use the Visual Studio.NET or your favorite text editor to write the C# program. Listing 1 below shows the piece of C# code. Also for this program to work I assume that your database has a table called product.
Listing 1.
using System.Data.Sql;
using System.Data.SqlServer;
/// <summary>
/// This is my first .NET routine.
/// </summary>
public class MyNETRoutines
{
public static void FirstProc()
{
SqlPipe myPipe = SqlContext.GetPipe();
myPipe.Send("Hello World");
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText ="select * from product";
myPipe.Send(cmd.ExecuteReader());
}
} |
Now let us examine the above program line by line. All the classes that are needed for developing .NET Routines are part of the namespace System.Data.SqlServer and System.Data.Sql. These namespace contain classes like SqlContext which is the class that gives access to the SQL server. unlike ADO.NET you need not get a connection using a connection string. As the assembly is loaded in the SQL server no explicit creation of connection is required. A .NET routine can access the SQL server resource using a In-Process Managed Provider. The above mentioned namespaces contain the implementation of this In-process managed provider.
The two classes I would be focusing in this article are SqlContext and SqlPipe. An SqlContext class contain methods which retrieve a connection, command etc to the database instance. And SqlPipe is an implementation to send query results and messages to the client. This has a lot of similarity with the Response class in ADO.NET.
There are many 4 overloads to the SqlPipe.Send() method. This is the method that would be used to communicate with the client. The overloads to this send method are
1. SqlPipe.Send(string meg)
2. SqlPipe.Send(SqlError se)
3. SqlPipe.Send(ISqlRecord record)
4. SqlPipe.Send(ISqlReader reader)
You could send either a message, reader, error or a record to the client program.
A difference between the Send(string) and the send(reader) is that all messages go to the message pane and the reader would go to the results pane in the SQL Serve Workbench.
When working with the beta, you will find the System.Data.SqlServer namespace in the assembly called SqlAccess.dll. You could compile the above code on the command promt by using
| csc.exe /t:library /r:"<path>\sqlaccess.dll" /out:MyNETRoutines.dll MyNETRoutines.cs |
Once the C# program is built, the generated MyNETRoutines.dll is the assembly that contains the stored procedure. The code below can be used to execute the procedure.
USE AdventureWorks
CREATE ASSEMBLY test from 'C:\Yukon\Projects\MyNETRoutines.dll'
go
create procedure FirstProc
as EXTERNAL NAME MyNETRoutines:MyNETRoutines::FirstProc
go
exec FirstProc |
Figure 1 and Figure 2 below show the result of the execution of the procedure.
Figure 1.
Figure 2.
Summary
The integration of the .NET runtime with the database engine surely offers a lot of flexibility to the developer to build complex logic using the .NET base class libraries. But one should be aware of the boon and bane of using the runtime. Hence, sufficient thought has to be given before deciding to implement your business logic in T-SQL or in a .NET compliant language.