What will C# Look Like in SQL Server?

By Bill Graziano on 15 April 2002 | Tags: SQL Server 2005 , CLR

Since this article was complete speculation you really shouldn't read it for any information about how C# is handled in SQL Server. It is kind of entertaining though. Please see our CLR category for information on the CLR. Articles abound on the Internet about the .NET Common Language Runtime (CLR) being incorporated into the next version of SQL Server codenamed "Yukon". My assumption is that we'll be able to write stored procedures, user-defined functions and possibly database level classes in languages such as C#, Visual Basic.NET, Perl, COBOL, Python, Ruby, J# (JScript?) and any other language that compiles to the Microsoft Intermediate Language (MSIL). What does this mean to you? This article is my rampant speculation about what this might look like.

My first caveat is that I haven't seen anything from Microsoft that isn't publicly available. I haven't seen any stored procedures written in C#. I haven't seen any pre-Alpha versions of Yukon. This article is just my wild guesses on what it might look like. And what better place for uninformed speculation than the Internet! And I'll probably be wrong too.

I'm going to focus on C#. I've been doing some ASP.NET pages in C# and I've come to know a little bit about the language. I'm by no means a great C# programmer. I've done nothing with inheritance, polymorphism, structures, interfaces, threading, delegates or events or any of the other really cool things. But I have been able to write very functional web pages without using those advanced features. My background is T-SQL and Visual Basic and I found it very easy to use C#. I like it so much that I'm hoping I never have to write any VBScript or Visual Basic again!

C# is a deceptively simple language. One book I've read said it only has 90 keywords. It has no history of previous versions to support. The designers, Anders Hejlsberg and Scott Wiltamuth, were able to take the best features of all other languages and incorporate them into C#. Hejlsberg was previously responsible for creating Turbo Pascal and Borland Delphi.

Classes

C# is primarily designed around creating classes. Instead of creating subroutines or functions you create classes with methods and properties. I'm not sure how classes will be integrated into stored procedures and user-defined functions. I would love to see both of those (sprocs and UDF's) replaced with a hierarchial namespace of user defined classes. Instead of calling spAddClaimLineItem you might invoke the Claim.LineItem.Add() method. I think this would allow better grouping and modularity of code.

I think you'll also see a System hierarchy of classes to replace the system stored procedures. Instead of calling sp_who you'll call SqlServer.CurrentUsers to return a record set of current users. sp_addsubscriber might be replaced with SqlServer.Replication.Subscribers.Add(). My hope is that the entire SQL-DMO API will be incorporated into this SQL Server class library. I'm curious how Transact-SQL will work in this environment. Will they provide extensions to T-SQL that allow it to call these new classes? In the past we've been able to look through the source code of system stored procedures and learn all kinds of things about SQL Server. Will we still be able to do that?

The C# Language

For the most part I'm going to skip that part of the discussion and focus on the C# language itself. I'm going to focus on creating variables, looping, handling errors, etc. Declaring a variable in C# might look something like this:

SqlInt32 @myVar = 17;
int @nextVar = 19;

The .NET Framework has a set of classes to declare SQL Server specific variables. SqlInt32 is really a class and correspnds to int in SQL Server. It has properties such a Value and IsNull and methods such as ToSqlString.

Notice that C# uses the ; character to separate commands. I'm not sure how variables will be named inside Yukon. Will they still need to be prefixed with the @ sign? Currently in C# you don't use the @ sign but in SQL we'll need something to make this work:

select	v1 = column
from	table

Currently v1 can be either a variable or a column alias and the @ sign tells us which. Maybe Yukon will finally force us to use the ANSI standard

select	column as v1
from	table

in order to alias column names. Then we'll know the first SELECT statement is always for variable assignment and won't need @ signs for variable names. Of course that gets tricky in WHERE clauses where variables have the same name as columns. Another option is to require columns to always be prefixed with a table name or table alias (thanks Rob). I'm going to use the @ sign in this article just for clarity's sake.

C#, like SQL Server, is a strongly typed language. Each variable needs to have a datatype associated with it. C# is case-sensitive but Transact-SQL isn't. SQL Server itself can either be case-sensitive or not. I'm not exactly sure how this will play out. Does that mean variable names but not column and table names will be case-sensitive? Since C# keywords such as while are case-sensitive does that mean SQL keywords like SELECT will also be case-sensitive? Here's hoping not :)

C# uses { ... } rather than BEGIN ... END so you might see code like this:

if ( @var1 == 17 )
{
    @var2 = 19;
    @var3 = 23;
}

That's certainly less typing :). Notice that the condition in the if statement needs to be surrounded by parenthesis. The comparison operator is == while the assignment operator is =. And if you only have one statement after the if you can skip the curly braces.

C# also includes a switch command that functions like a CASE. You'll be able to use this functionality outside a SELECT statement. Woo hoo! C# includes all the standard loops including while, do ... while and for.

C# supports string variables. These don't have a length defined at the time they are declared. My hope is that we'll finally have a way to manipulate TEXT dataypes programmatically inside a stored procedure. In fact I'm hoping that TEXT datatypes are finally upgraded to full parity with all the other SQL Server datatypes. C# also supports regular expressions. I'm desparately hoping these will be available in a WHERE clause. 4Guys has a good section on regular expressions if you'd like more information.

Arrays

C# also supports arrays. In fact C# supports the a whole bunch of "array like" classes including Array, ArrayList, NameValueCollection, StringCollection, Queue, Stack and BitArray. These are part of the System namespace in the Framework and I'm assuming they'll be available inside SQL Server. It will be so nice to not have to trick SQL Server into using a CSV string to mimic an array. I'd also like to see a ToRecordSet method for an array.

int MyArray = new int[5];
MyArray[3] = 17;

This declares an array of five integers and sets the fourth item to 17. Note that arrays are zero-based so the first item is MyArray[0]. Arrays have a variety of useful methods and properties including Sort(), Length and GetUpperBound(). There is also a new command called foreach that will iterate through all the items in an array or collection. You might have code like this:

int MyArray = new int[5];
// some code here to assign values
foreach ( int i in MyArray )
{
    WriteLine ( i.ToString() );
}

The // designate a comment in C# and WriteLine replaces the PRINT statement. You can use foreach to loop through anything that C# thinks is a collection. Who knows, it might even work with cursors. Maybe we'll even be able to use an array in a WHERE clause using the IN statement! I wouldn't hold my breath on that one though.

Error Handling

The last topic I want to cover is error handling. SQL Server uses RAISERROR to generate user defined error messages and @@ERROR to check for errors in a previous statement. C# uses the throw statement to throw an exception which is basically similar to RAISERROR. C# uses try ... catch blocks to handle errors.

try
{
    // some code that may cause an error
}
catch
{
    // some error handling code
}

You can put as many statements as you'd like in the try or catch block. If any of the code in the try block generates an error C# will execute the code in the catch block. A better example might be

try
{
    Insert Table1 ( userID, UserName )
    Values ( 17, 'Smith' );
}
catch
{
    WriteLine( 'Duplicate Key' );
    return -1;
}

You can also catch specific errors in a catch block. For example you might have multiple catch blocks for a statement.

try
{
    // some code that may cause an error
}
catch (SqlServer.InsertDuplicateKey)
{
    // some duplicate key error handling code
}
catch (SqlServer.ForeignKeyViolation)
{
    // some FK error handling code
}
catch 
{
    // some generic error handling code
}

This way you could write specific code for each type of potential error.

Conclusion

I'm excited about using C# to write stored procedures. I think T-SQL has been hamstrung by starting out as SQL and being extended. I think a great programming language that supports SQL will really help the programmability of SQL Server. I also think it will be possible to write really horrible code using C#. Probably easier than it was in T-SQL. Keep in mind I've only covered the most basic aspects of C#. So there's my rampant speculation. I'm curious to see any feedback you have on my thoughts.