Building C Stored Procedures

Building C# Stored Procedures

When creating managed code that will reside within SQL Server as a stored procedure, you are basically creating a static method on a class. That static method is then decorated with the Microsoft.SqlServer.Server.SqlProcedure attribute. When your assembly is deployed to SQL Server and stored within the database, this attribute allows SQL to create a CLR routine for the method.

By default, SQL Server 2005 doesn't allow you to execute CLR code, so you'll have to enable it by executing the following command inside a SQL query window (make sure you're connected with sufficient privileges to perform this command):

sp_configure 'clr enable', 1 

After executing this, SQL Server will inform you that the option has changed, but it will not take effect until you issue the following command:

reconfigure 

Now you're ready to start coding. Ordinarily, you would have to create an assembly and then go over to SQL Server and issue several commands within the query window to deploy the assembly and then create a managed stored procedure. However, with Visual Studio 2005, you can create a special type of project called a SQL Server project.

Before you create a SQL Server project, you will need to have an instance of SQL Server handy, as well as a database against which you are planning on developing.

When you first create a SQL Server Project, you will be asked for a database reference if you haven't already created one, as shown in Figure 21.1.

Figure 21.1. Database reference prompt dialog.

With a new SQL Server project in your solution, you are ready to go. Simply right-click the project and highlight the Add submenu. You will see the following list of SQL Server objects appear:

• User-Defined Function

• Stored Procedure

• Aggregate

• Trigger

• User-Defined

Select Stored Procedure and call it TestProcedure. Visual Studio will create a stored procedure stub that looks as follows:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void TestProcedure() {      // Put your code here } }; 

Note that the class is a partial class called StoredProcedures. Whenever you add a new stored procedure to your SQL Server project, it will be part of the partial class StoredProcedures and the static method representing the procedure will be in its own file.

When building C# static methods that will be used as stored procedures, you need to remember the following rules:

• The return type of the method is used as the return value of the stored procedure or function.

• The parameter list of the method is the parameter list of the stored procedure. As such, you should only use data types from the System.Data.SqlTypes namespace.

• Keep in mind that your method has no user interface, so any debugging or tracing you do can't go to a console window. You can still print debug messages the same way you could with stored procedures, however.

Now we make a small modification to the "stub" method provided for us, and we're left with this:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void TestProcedure(out SqlString outVal) {     // Put your code here     outVal = "Hello World"; } }; 

When you build this project, the assembly is compiled into a DLL, but that's it. In order to get your stored procedure onto the server, you need to debug your application. This will deploy your assembly to SQL Server, register your stored procedure, and then execute a test script found in the Test Scripts folder of your solution called Test.sql. To execute just this stored procedure without running a test script, open your Server Explorer pane, browse to the stored procedure you just created, right-click the procedure, and choose Execute. Finally, select <NULL> for the input to the @outVal parameter. When you execute the stored procedure, the following text will appear in your output window:

Running [dbo].[TestProcedure] ( @outVal = <NULL> ). No rows affected. (0 row(s) returned) @outVal = Hello World @RETURN_VALUE = Finished running [dbo].[TestProcedure]. 

This is just the beginning. As you will see in the section on utilizing the new server-side SQL library, accessing data and returning data to the caller are both extremely easy tasks managed by powerful tools.