Creating SQL Server Stored Procedures using C#

Introduction

Traditionally developers used TSQL to create SQL server stored procedures, functions and triggers. SQL Server 2005 has integrated support for .NET Common Language Runtime (CLR). That means you can now use .NET languages such as C# and VB.NET to create SQL server stored procedures, functions and triggers. This CLR integration provides various advantages ranging from compiled execution, security, type safety and enhanced programming model. In this article I am going to show how these features can be used for creating and consuming stored procedures.

Background

SQL server stored procedures are possibly the most commonly used construct for taking the data in and out of the database. Stored procedures can be broadly classified as follows:

• Stored procedures that simply execute some logic but don't return anything. Not even output parameters
• Stored procedures that execute some logic and return some result as one or more output parameters
• Stored procedures that execute some logic and return one or more records from table
• Stored procedures that execute some logic and return one or more rows. These rows need not be from any table as such. They can be arbitrary rows of data.

In order to create each of these types of stored procedures the SQL Server integration features provide a way. One by one we will see each of the scenarios with an example.

Enabling CLR Integration

Before you proceed any further you must enable CLR integration feature for your SQL Server. By default this feature is turned off. To turn it on open SQL Server Management Studio and execute the following script.

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO 

Here, we execute sp_configure system stored procedure by passing two parameters viz. clr_enabled and 1. To disable this feature again you call the same stored procedure with second parameter as 0. Also, remember to call RECONFIGURE so that the new settings are in effect.

SQL Server Project

Now open Visual Studio and choose "New Project" option from File menu. In the "New Project" dialog select C# language and Database project types. Then select "SQL Server Project" template.

Give some project name and click on OK button.

As soon as you create the project you will be prompted for selecting the SQL Server database.

Doing so at this stage is optional and even if you select Cancel at this stage you can assign the database at later stage using Project - Properties dialog. For this example we assume that you have Northwind database installed on your machine. Select it using the "Add Database Reference" dialog and click OK. This database information is used by the SQL Server project to deploy our stored procedures into the database (this will be clear as we proceed).

Next, right click on the newly created project in the solution explorer and choose Add - Stored Procedure menu option. Doing so will open a dialog as shown below:

Make sure that Stored Procedure template is selected and give some suitable name to the new file being added.

Once you add a stored procedure to you project you will observe that it actually creates a class with certain namespaces already imported for you.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

Notice the namespaces marked in bold letters. The System.Data.SqlTypes namespace contains various types (mainly structures) that represent SQL Server data types. The Microsoft.SqlServer.Server namespace contains various types responsible for CLR integration of SQL Server.

Stored procedure that returns nothing

In this section we will see how to write a stored procedure that executes some logic but doesn't return anything. Not even output parameters. As an example we will create a stored procedure called ChangeCompanyName inside the stored procedure class that is supposed to change the CompanyName column value of Custoemrs table to the supplied value. The stored procedure will accept two parameters - CustomerID whose company name is to be changed and CompanyName, the new company name. The complete code for ChangeCompanyName stored procedure is given below:

[SqlProcedure]
public static void ChangeCompanyName
(SqlString CustomerID, SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "update customers set 
companyname=@p1 where customerid=@p2";
SqlParameter p1 = new SqlParameter("@p1", CompanyName);
SqlParameter p2 = new SqlParameter("@p2", CustomerID);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
int i=cmd.ExecuteNonQuery();
cnn.Close();
SqlContext.Pipe.Send(i.ToString());
}

Observe the ChangeCompanyName() method carefully. The method is static and returns nothing (void). It takes two parameters namely CustomerID and CompanyName. Note that data type of these two parameters is SqlString. The SqlString structure represents nvarchar data type of SQL Server. The ChangeCompanyName() method is decorated with[SqlProcedure] attribute. The [SqlProcedure] attribute marks the ChangeCompanyName() method as a SQL Server stored procedure.

Inside we create a new SqlConnection object and set its connection string to "context connection=true". The context connection allows you to execute your code in the same context in which your code was invoked in the first place. In this case ChangeCompanyName() is a stored procedure residing in Northwind database. So context connection here is a connection to Northwind database itself. This way you need not specify security credentials for your database connection.

The code then opens the database connection. It then configures a SqlCommand object to execute an UPDATE query by setting Connection and CommandText properties. It also fills the Parameters collection with two parameters. The UPDATE query is then executed with the help of ExecuteNonQuery() method. The Sqlconnection is then closed.

Finally the return value of ExecuteNonQuery() method is sent to the client. Doing so is of course optional. We are adding that step just to understand the use of SqlContext class. The SqlContext class is used to communicate results of processing between server and the client. In this case it uses Send() method to send a string value back to the caller.

Stored procedures that return one or more records from a table

Often stored procedures need to SELECT one or more rows. In order to create such stored procedures you can adopt two approaches. Both of these approaches are illustrated below.

First we create a method called GetAllCustomers() that looks as shown below:

[SqlProcedure]
public static void GetAllCustomers()
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers";
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
cnn.Close();
}

The GetAllCustomers() method is marked with [SqlProcedure] attribute as before. Inside it creates a SqlConnection and SqlCommand objects. It then fires a SELECT statement using ExecuteReader() method. The data retrieved as SqlDataReader is sent to the client application using Send() method. The reader and connection is then closed. In this approach we ourselves created the SqlDataReader. However, you can leave this task on the SqlContext class itself as shown below.

[SqlProcedure]
public static void GetCustomerByID
(SqlString CustomerID)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers 
where customerid=@p1";
SqlParameter p1 = new SqlParameter("@p1", CustomerID);
cmd.Parameters.Add(p1);
SqlContext.Pipe.ExecuteAndSend(cmd);
cnn.Close();
}

The GetCustomerByID() method takes CustomerID as a parameter and is supposed to return a record for that customer from Customers table. Most of the code inside the GetCustomerByID() method should be familiar to you except the ExecuteAndSend() method. The ExecuteAndSend() method accepts SqlCommand object, executes it and then returns the returned data back to the client application.

Stored procedures that has output parameters

Stored procedures often return data in the form of calculated values via output parameters. So let's see how to create a stored procedure that takes one or more output parameters.

[SqlProcedure]
public static void GetCompanyName
(SqlString CustomerID,out SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select companyname from 
customers where customerid=@p1";
SqlParameter p1 = new SqlParameter
("@p1", CustomerID);
cmd.Parameters.Add(p1);
object obj = cmd.ExecuteScalar();
cnn.Close();
CompanyName = obj.ToString();
}

Here we have a method called GetCompanyName() that takes two parameters. The first parameter CustomerID is a normal input parameter whereas the second parameter CompanyName is an output parameter as indicated by the out keyword. Both of these parameters are of type SqlString. The GetCompanyName() this accepts a CustomerID and returns its CompanyName in the form of an output parameter.

The code inside the GetCompanyName() method configures an SqlConnection and SqlCommand. This time the SELECT statement is executed using ExecuteScalar() method. The return value of ExecuteScalar() is an object containing the company name. The CompanyName output parameter is then set to this value.

Stored procedures that return one or more custom rows of data

Most of the times we need to return data from some table. However, in some cases our data may not reside in any table. For example, you might be generating tabular data based on some calculation. Since the data is not residing in any table none of the above techniques can be used. Fortunately, the CLR integration features provide a way out. Have a look at the method below:

[SqlProcedure]
public static void GetCustomRow()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar,50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar,50);
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.Send(record);
}

The GetCustomRow() method returns a row filled with arbitrary data to the client application. The method declares an array of SqlMetaData objects. The SqlMetaData class is used to represent metadata of an arbitrary column. In our example we created two columns of type NVarChar and length 50. The code then creates a SqlDataRecord. The SqlDataRecord class represents a custom row in memory. The constructor of SqlDataRecord accepts SqlMetaData arrary. Then SetString() method of SqlDataRecord is called to set respective column values. Just like SetString() there are various other methods for different data types. Finally, Send() method is called by passing the SqlDataRecord object.

In the above example we returned a single row to the caller. What if we wish to return multiple rows? The following examples explains just that.

[SqlProcedure]
public static void GetMultipleCustomRows()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar, 50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar, 50);
SqlDataRecord record = new SqlDataRecord(metadata);
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.SendResultsRow(record);
record.SetString(0, "ANATR");
record.SetString(1, "Ana Trujillo Emparedados y helados");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}

The GetMultipleCustomRows() method returns multione SqlDataRecord objects to the client. The part of creating metadata and assigning column values is exactly same as before. However, we initiate the transfer using SendResultsStart() method. Then SendResultsRow() is called multiple times to send individual SqlDataRecord back to the client. Finally, SendResultsEnd() method is called to mark completion of the data transfer operation.

We just completed creating our stored procedures. Now build the project so as to get an assembly (.DLL). Our work doesn't end here. We also need to deploy this assembly and stored procedures therein to the SQL Server. There are two ways to accomplish this - manual and automated. In the manual way you use T-SQL statements to register your assembly and stored procedures in the underlying SQL Server. Though this approach gives full control on the process it is time consuming (especially if you have dozens of stored procedures). For our example we will use automated way of deploying the stored procedures in the SQL Server.

Right click on the project in the Solution Explorer and click on Deploy menu option.

Doing so will register the assembly and stored procedures in the underlying database. Note that the Deploy option will work only if you added a database reference while creating the project. If for some reason you didn't added any database reference in the beginning you can do so via Project Properties dialog.

If you browse to the Northwind database via SQL Server Management Studio then you should see something similar to the following figure.

Notice that all our methods appear under Stored Procedures node (with lock icon on them) and the assembly appears under Assemblies node.

That's it! Now you can call these stored procedures from your client applications. You can also test them by executing them in Management Studio itself.