Writing CLR Stored Procedures in C# - Introduction to C# (Part 1)
By Bill Graziano
on 06 June 2005
| 15 Comments
| Tags: Stored Procedures, CLR
Article Series Navigation:
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.
