Writing CLR Stored Procedures in C# - Introduction to C# (Part 1)

By Bill Graziano on 6 June 2005 | Tags: Stored Procedures , CLR

This is the first article in a series on writing stored procedures using the Common Language Runtime (CLR). This article focuses on basic C# syntax and using Visual Studio to build a stored procedure. It's targeted at DBA's and anyone else who primarily writes in Transact-SQL and hasn't had much exposure to .NET yet. (Update: Fixed the title.)

As SQL Server 2005 rolls out DBA's are going to be forced to learn either C# or Visual Basic or both. Until now these were client side languages and not knowing them had little impact on your job. And if you write code in these languages your going to have to learn to use Visual Studio. This article covers the basics of C# and Visual Studio using a basic stored procedure as an example. It's written using the April CTP of SQL Server 2005.

C# vs. Visual Basic

When I first started working in .NET I was faced with the choice of a language. The two most popular are C# and Visual Basic. The debate on these two languages has ben on-going in the .NET community -- sometimes friendly, sometimes not. I've done Visual Basic development in the past and could find my way around the language. All the web sites I've built were written in VBScript. C# is similar to C, C++ and Java. My skills in C are old and rusty and I don't have any experience in C++ or Java. On the other hand C# is a brand new language designed from the ground up for .NET. It's simple and not very verbose (less typing!). Visual Basic carries the baggage of numerous previous versions and needed some fairly significant changes to make it play well in the .NET world. Many of the .NET features such as class declarations and object inheritence seemed more elegant in C#. I chose C#.

I've been very happy with this decision. The language wasn't difficult to learn. After a few weeks of using it I've never looked back. That's not to say C# isn't quirky for an ex-Visual Basic developer. The language itself and its string comparisons are case-sensitive. A comparison of equality (==) is different than an assignment (=). Those are two of the biggest areas that caused me problems.

If you're faced with choosing a language to write stored procedures I'd encourage you to choose C#. Anyone with a strong Transact-SQL background will have no problem picking it up. I'm fairly certain there isn't a right answer to which language to choose -- only one that's right for you. All my examples in this article and the ones that follow will be written in C#.

Visual Studio

In SQL Server 2000 DBA's primarily used Query Analyzer and Enterprise Manager. SQL Server Managment Studio combined these tools in SQL Server 2005. However if you want to build CLR-based database objects or Integration Services packages (formerly DTS) you'll have to learn Visual Studio. (You can build CLR-based objects without Visual Studio but it's much more difficult. I'll cover that in a future article.)

Visual Studio uses Solutions and Projects to group its work. A Solution is just a group of one of more Projects. A Solution can have different types of Projects in it. For example, you can have a web project, a class library (DLL) project and an Integration Services project all in the same solution.

After launching Visual Studio 2005 choose File -> New Project. In the dialog box under Project Type choose Visual C# -> Database and then choose SQL Server Project on the right side. I named my project CSharpPart1 and I cleared the checkbox to create a directory for the solution. This creates a solution and a project both named CShartPart1. If I'm working on a larger project I usually create a blank solution and then add projects to it as needed. Visual Studio will also ask you to create a database reference or use an existing one. I created one for the AdventureWorks database for this article.

A key area to understand in Visual Studio is the Solution Explorer. This lists all the projects and files in your solution.

Creating a Stored Procedure

We'll start by having Visual Studio create a shell of a stored procedure for us. In the Solution Explorer, right click on the project and choose Add. From the submenu choose Stored Procedure and name the file "PrintToday.cs". Visual Studio will add the file to your project and create a stored procedure shell for you like the one shown below.

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


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PrintToday()
    {
        // Put your code here
    }
};

The basic structure of this code is a series of using directives, a class (StoredProcedures) and method (PrintToday) within the class. You'll notice that C# uses curly brackets to define the start and end of "things". In Transact-SQL we use BEGIN and END for this. The class itself is contained in these curly brackets as is the PrintToday method

The first keyword that we see is using. .NET is an object-oriented development environment and uses the concept of a hierarchical namespace to group classes. The root of the namespace on most of the classes that ship with .NET is System. The classes that are used for data access are grouped under System.Data. The using keyword allows us to reference classes in the particular namespace without having to fully qualify them.

The class definition is next. The template created a class called StoredProcedures for us. The public keyword is an access modifier. It defines what other types of classes can instantiate objects of this type. Some type of access modifier is required. The partial keyword says we may use multiple physical files to store the definition for this class. This is new in .NET 2.0. The partial keyword is optional and is only really needed when we want to split the class up. The class keyword says we're defining a class.

Next we define a method in the class. This method is called PrintToday and doesn't take any parameters. (I'll talk about the text in the square brackets in a minute.) In the case of a method, the public keyword doesn't restrict who can call this method. Another common access modifier is private which says that this method can only be called from within this class. The PrintToday method doesn't return anything back so its return type is void. If it returned a number its return type might be int. The static keyword says we can call this method without having to instantiate an instance of this class first.

The text in the brackets above the method declaration is called an attribute. Attributes can be used to "decorate" certain entities such as methods and properties (which we haven't discussed yet). This is used by Visual Studio to deploy this stored procedure to SQL Server. This attribute tells Visuall Studio that this is a stored procedure for SQL Server.

Compiling and Deploying our Stored Procedure

The first step to using our stored procedure is to compile it. You do this by choosing Build -> Build Solution from the Visual Studio menu (or pressing control-shift-B). That will build (compile) all the projects in our solution. You can also build individual projects in a solution by right-clicking them in the Solution Explorer and choosing Build. This compiles our source code into a DLL. If you look in the directory for the project you'll see a directory called bin. Under this directory you'll find a Debug directory (and possibly a Release directory). On the top Visual Studio toolbar (right below the menu) you'll see a drop down with choices for Debug and Release. These are the two default build configuartions. The debug build configuration adds debugging information to the compiled DLL. If you'll look in bin/Debug in the project directory you should see a file called CSharpPart1.DLL. That's our compiled DLL. When you choose Release, the DLL will be placed in the Release directory and will be built without the debugging information.

Next we need to put this DLL inside SQL Server and create a stored procedure that references it. This is called Deploying. You can deploy a stored procedure by right-clicking the solution or the project and choosing Deploy. This will compile the DLL, copy it into SQL Server and create a stored procedure called PrintToday. You have the same options by using the Build menu. At this point you can start SQL Server Management Studio and execute our stored procedure:

USE [AdventureWorks]
GO
EXEC [dbo].[PrintToday]
GO

It doesn't really do anything but at least it doesn't generate an error. In future articles I'll cover the Transact-SQL commands to deploy assemblies and create stored procedures from them.

Writing Code

Now let's add some actual code to our stored procedure. All I want to do is have it print today's date. Add the following lines after it says "Put your code here" but inside the curly brackets for the method.

SqlPipe p; 
p = SqlContext.Pipe; 
p.Send( System.DateTime.Today.ToString() );

In Transact-SQL a variable declaration uses the DECLARE keyword and has the variable name prefixed with an "@" sign followed by the datatype and you can't assign a value to a variable when you declare it. In C# you declare a variable by starting with the type of variable you want to declare and then listing the variable. The first line above declares a variable p of type SqlPipe (which I'll discuss in a second). Each statement in C# must be following by a semi-colon and statements can be split over multiple lines. SqlPipe is in the namespace Microsoft.SqlServer.Server and we can reference it directly because indicated we're using this namespace is in a using directive at the top of the procedure.

Next we assign a value to our variable. There is an object called SqlContext (also in the Microsoft.SqlServer.Server namespace) that has a property called Pipe. A property is referenced (or set) as Object.Property. This CLR DLL is going to run inside the SQL Server process space. The SqlContext object is our "hook" into that process. The Pipe object (which is a property of the SqlContext object) is what we'll use to send data back to the calling program (our query in Management Studio that called this stored procedure). So p is now defined as the "pipe" back.

In C# you can combine a variable declaration and assignment. The first two lines of our custom code could be combined into this line:

SqlPipe p = SqlContext.Pipe;

A SqlPipe object has a series of methods and properties. One of these methods is the Send method. There are three different versions of the Send method but we're only concerned with one of them for now. It accepts a string as a parameter and returns it to the calling program. We pass it a string parameter inside the parenthesis. This has the same result as doing a PRINT inside Transact-SQL.

The parameter we're going to pass to the Send method is today's date. In Transact-SQL we get the date using GETDATE(). In any one of the .NET languages we get the current date by referencing System.DateTime.Today. Today is a property of the DateTime class. It's a static property so we don't have to instantiate an instance of DateTime to call the property. It returns a DateTime datatype. Nearly every datatype in .NET can be converted to a string using the ToString method. Since this is a method it has to be called with parenthesis. System.DateTime.Today returns a DateTime and that can be converted to a string so we can just do it directly in a single statement:

System.DateTime.Today.ToString()

This will return the current date as a string.

We pass our string into the Send method and it will be "printed" by the stored procedure. Now we can build, deploy and test the stored procedure again:

USE [AdventureWorks]
GO
EXEC [dbo].[PrintToday]
GO

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

6/5/2005 12:00:00 AM

Notice that it strips out the time but displays it anyway. I believe this is the result of the build I'm using. In a later article I'll talk about string conversions and format strings.

Summary

This article discussed basic C# syntax and using Visual Studio to write CLR stored procedures. Future articles will cover database access, programmitically deploying assemblies and more complex logic in .NET.