Chapter 21. Programming with SQL Server 2005
IN THIS CHAPTER
- Introduction to the SQL Server 2005 CLR Host
- Building C# Stored Procedures
- Building C# User-Defined Functions
- Creating a C# User-Defined Type
- Working with the New Server-Side SQL Library
- Using MARS (Multiple Active RecordSets)
SQL Server 2005 adds a wealth of new features for developers and DBAs alike. One of the biggest new features of SQL Server 2005 of interest to C# developers is the integration of the CLR directly into the server. This gives SQL Server 2005 the ability to host stored procedures, user-defined functions, user-defined types, and much more—all written in managed code.
The ability to reuse your existing .NET and C# skills from within SQL Server 2005, as well as have access to the entire library of .NET Framework classes, creates an incredible new opportunity for both efficiency and productivity.
This chapter shows you how you can get started writing managed code for SQL Server 2005 both within SQL Server and on the client side, with an eye toward making intelligent decisions about when to utilize the new technology and when to avoid it.
Introduction to the SQL Server 2005 CLR Host
SQL Server 2005 is integrated with the Common Language Runtime (CLR), giving the database access to all of the features and benefits of managed code. Developing in C# is a rich, object-oriented environment that allows for faster time to production and more reliable, more scalable code. The code written in C# still works within the confines of Code Access Security (CAS), which provides enhanced security for all managed code executed by SQL Server 2005. In addition to the ability to write stored procedures in C#, the integration of the CLR into SQL Server 2005 also provides you with the ability to write user-defined data types, functions, and aggregates. One of the other benefits of being able to write C# code for SQL Server 2005 is the ability to use the highly productive IDE native to Visual Studio 2005. Finally, the use of C# can potentially provide dramatic performance increases when applied in the appropriate manner. The following sections will show you how all this and more can be accomplished.
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.
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 Type
Figure 21.1 Database reference prompt dialog.
Select Stored Procedure and call it TestProcedure. Visual Studio will create a stored procedure stub that looks as follows:
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.SqlTypesnamespace. - 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:
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.
Building C# User-Defined Functions
A function in SQL Server is just that: a function that can be invoked from within any T-SQL statement that will return a value. SQL Server has many built-in functions for string concatenation, mathematics, data type conversion, and much more. With the integration of the CLR into SQL Server 2005, you can now write your own user-defined functions in C#.
In many circumstances, you need to perform some bit of logic on a column or a couple of columns in order to provide a calculated column. What often happens in that situation is that either the logic is too difficult to represent in T-SQL or the calculation is just too slow, so developers leave the columns unaltered, and then perform the calculated column on the client side in C#. Now you can place that complex logic on the server, and gain a performance boost from not having to perform the calculations on the client.
To illustrate this, without getting bogged down in a complex calculation, the next example will create a C# user-defined function that takes a customer’s first name, last name, and middle initial as parameters, and returns a string containing the appropriate full-name display.
Add a new user-defined function to your project by right-clicking the project, highlighting Add, and then clicking User-Defined Function. You’ll be presented with an empty stub that looks similar to the empty stored procedure stub from the preceding section. However, this method starts off with a return type (functions cannot be void) and is decorated with the Microsoft.SqlServer.Server.SqlFunction attribute. The code in Listing 21.1 shows this name-formatting function.
Listing 21.1 C# User-Defined Function
The important thing to note here isn’t how easy it is to format names. The important thing to note is that you can pass any SQL data type (even the new XML data type) to your own C# function, perform some processing on it, and then return a value that can then be used in a SELECT statement by any code, whether it’s someone running a query directly on the server or whether it is within your own C# client application.
If you modify the Test.sql script to contain the following UDF test code
select dbo.NameDisplay(c.FirstName, c.LastName, c.MiddleInitial)
as FullName FROM Customers c
You will see the following output (assuming you have a Customers table defined):
FullName
------------------------
Doe, John Q
Doe, Jane R
Smith, Bob E Smith, Rob V Smith, Lob L
Customer, Jethro M
Customer, Ringo R
No rows affected.
(7 row(s) returned)
If you have been developing applications based on SQL Server for a long time, you can probably think of several uses for C# user-defined functions that can save you the time and headache of writing them in T-SQL, and also create potential performance improvements.
Creating a C# User-Defined Type
Before we get into the code involved in creating your own user-defined type in SQL Server 2005, it should be pointed out that CLR UDTs are extremely different from any type of custom data structure you may have created for previous versions of SQL Server. A CLR UDT not only has fields for storing data, but it also has behaviors that are defined by C# methods on the UDT class. The larger and more complex a UDT, the more overhead it consumes. As a result, the most efficient use for UDTs is storing simple data over which you want tight control. For example, you could create a UDT in C# that provides a highly customized type of date/time storage, or you could even create one that stores encrypted data. You can provide behaviors (methods) on that type to gain access to the decrypted values.
The SQL Books Online reference that you can install with SQL Server 2005 illustrates the use of CLR UDTs with a type called Point. What we want to do is to create a table in SQL Server that is storing the positions of all the objects in a fictitious strategy game. This fictitious game uses three-dimensional space, so the UDT that we will create will support three-dimensional points, and will contain a method for determining the distance between two 3D points.
To get started, add a user-defined type called Point3D to your project using the same method used to create a user-defined procedure and a user-defined function.
Next, change the default class definition so that it looks like the one shown in Listing 21.2.
Listing 21.2 Point3D, a User-Defined Type
It looks pretty much like any other C# struct, with a few minor differences. The SqlMethod attribute controls the behavior of the method when invoked within a SQL statement.
To use this new data type, first run your project to complete the deployment and registration of your new type. Then, you can use SQL Server 2005’s management console or Visual Studio 2005’s server browser for creating a new table. Figure 21.2 shows the table editor from within Visual Studio 2005. Note that the Point3D data type appears just like any other type in SQL.
Two of the key methods that make this type work are the Parse method and the ToString method. The Parse method is what allows you to enter textual data in a grid or send textual data in a SQL statement and have SQL properly create an instance of the Point3D type. By default, SQL Server 2005 doesn’t invoke the ToString method on your type to display it; it displays the raw serialization data.
Figure 21.2 Creating a table based on the Point3D data type.
Create a new table, GameObjects, with a column for the object’s ID, the object name, and the object’s location. Then, add in some rows of data, making sure to enter the location points in the format x, y, z.
With the data in place, you can start issuing SQL statements to examine the contents of the data, as shown in the following code:
DECLARE @refPoint Point3D
SELECT @refPoint = Location FROM GameObjects WHERE ObjectId=2
select ObjectID, Name, Location.ToString() as CurrentLocation,
Location.DistanceFromPoint(@refPoint)
FROM GameObjects
This SQL statement will give you a list of all the objects in the game, as well as each object’s distance from the object with ID 2. The following are the results from the author’s sample data:
1 Battleship Intrepid 0,0,0 21.6101827849743
2 Starship SAMS 1,5,21 0
3 Battlecruiser DotNettica 9,7,3 19.7989898732233
As expected, the distance from object 2 to object 2 is 0. The other objects are displaying the results of the DistanceFromPoint method on the Point3D struct itself.
With this kind of power in hand, you can do a lot of really interesting things. For example, let’s say that the Starship SAMS has a radar radius of 20 units. We can easily issue a query that will return all game objects within radar range, as shown in the following lines:
select ObjectID, Name, Location.ToString() as CurrentLocation FROM
GameObjects WHERE Location.DistanceFromPoint(@refPoint) < 20 AND ObjectID != 2
You can also access the user-defined type from within your regular .NET code using ADO.NET. All you need to do is add a reference in your client code to the assembly that contains the UDT. Create a console application and then add a reference to SqlProjectDemo.dll by browsing for it. Then you can write the code shown in Listing 21.3.
Listing 21.3 ADO.NET Client Consuming a SQL Server 2005 UDT
When you run this application, you receive the following output:
1 (Battleship Intrepid) is at 0,0,0. Distance from Origin 0
2 (Starship SAMS) is at 1,5,21. Distance from Origin 21.6101827849743
3 (Battlecruiser DotNettica) is at 9,7,3. Distance from Origin 11.7898261225516
Remember that CLR UDTs are designed specifically for small, simple types that might expose a few behaviors. If you attempt to store complex or hierarchical data in a UDT, you might actually be slowing your application down.
Working with the New Server-Side SQL Library
When writing stored procedures, types, functions, and more, you will need access to data and functionality provided by SQL Server. For example, when writing a C# stored procedure you need to be able to send results out on the “pipe.” Also, you will want to be able to run your queries using the existing server-side connection and not have to resort to a client-side ADO.NET connection. All of this is available for you in a few classes, such as SqlContext and SqlPipe.
Table 21.1 shows some of the methods and properties available on the SqlPipe class.
Table 21.1 SqlPipe Properties and Methods
The code in Listing 21.4 illustrates how to use some of the methods of the SqlPipe class to send results to the client as well as how to create a command parameter that is one of your own UDTs.
Listing 21.4 Using the SqlPipe Class and Creating a UDT Command Parameter
When this stored procedure is executed in a query environment, it gives the following results (for Object ID of 2, radar range of 20):
3 Battlecruiser DotNettica 9,7,3 19.7989898732233
These results indicate that, according to the GameObjects table, there is a ship called the “Battlecruiser DotNettica” almost 20 units away, at location (9,7,3). Obviously this procedure could have been written in T-SQL, but if you needed to take into account other factors such as whether that vessel is cloaked, moving, and so on, the C# procedure might prove computationally faster than its T-SQL equivalent.
When you are working with a SQL Server project, you can’t just add references as you see fit. When you go to add a reference, you are limited to other projects, and to assemblies already stored in SQL Server, as shown in Figure 21.3.
Figure 21.3 Adding a reference to a SQL Server project.
Another common task when working with the server-side SQL library is the creation and use of temporary tables. Often developers will need to create a temporary table to store dynamically generated results, and those results are then sent to the client. This same functionality is available to you in C# by giving you the ability to create your own metadata and send records that match that metadata.
The code in Listing 21.5 shows a C# stored procedure that utilizes the SqlPipe class to send its own arbitrary data as well as the accompanying metadata.
Listing 21.5 Sending Arbitrary Tabular Results
Using MARS (Multiple Active RecordSets)
Multiple Active RecordSets is a fairly complex topic, and there are a lot of unseen factors that can determine whether or not you should use this technology. This chapter has a small preview of this technology. For more information on MARS and the impact of using MARS on performance and the database itself, you should consult your favorite SQL Server 2005 reference or the SQL Server 2005 Books Online reference that installs with the server itself.
In previous versions of SQL Server, many developers remember getting a common error message that indicates that there is already an open DataReader on the connection, and that in order to open the current DataReader, the previous reader needs to be closed. This message arose from the limitation that you could only have one open forward-only result set on any given connection at any given time. The workaround developers used for this was just to make sure that they used new connections all the time, or they opted for low-performance options like reading all the data into a DataSet before opening the next DataReader.
With the introduction of SQL Server 2005, developers now have the ability to create multiple active result sets on a single connection. This essentially removes the block created by previous versions of SQL Server. The code in Listing 21.6 contains code that would otherwise fail miserably on previous versions of SQL Server 2005 and the .NET Framework.
Listing 21.6 Using MARS
The preceding code creates one connection and two commands. Each of these commands is then used to create a new SqlDataReader.
MultipleActiveResultSets=true
If you remove this option from the connection string, the behavior resembles the previous versions of SQL Server and the .NET Framework, and you get the following error message:
There is already an open DataReader associated with this
Command which must be closed first.
The downside to MARS is that the execution of the commands on the server is interleaved. This means that a portion of the first command will be executed, and then a portion of the second command will be executed, and so on. You can’t rely on which portion will be executed first. If two commands are executed requesting the same rows on the same connection, you could potentially create a deadlock situation where neither command can complete.
You can learn more about these issues in more detail in other SQL Server 2005 references. The important point here is that with MARS, it is possible to have multiple result sets executed on the same connection at the same time. However, this might not always be such a good idea. If the multiple result sets might be competing for the same information or the same table, you might want to consider using separate connections. Just because it is possible doesn’t mean it should be overused.
Summary
In this chapter, you’ve been taken on a whirlwind tour of the new CLR integration features of SQL Server 2005. You’ve seen how to create stored procedures, user-defined types, and user-defined functions within SQL Server 2005 using the new Visual Studio 2005 “SQL Server Project” template. You also learned that T-SQL is still the fastest way to retrieve data from the database, and now CLR languages are the fastest way to perform complex logic and otherwise computationally expensive operations.
Also, you saw how to use the new SQL Server library for data access on the server side, including the SqlContext and SqlPipe classes. Finally, you were introduced to yet another new feature of SQL Server 2005 that is of interest to C# developers: MARS. MARS allows you to create and iterate through multiple active result sets at any given time on the same connection. This new feature is not without penalty, however, because the server execution of these commands is interleaved and you need to be wary of this before overutilizing MARS.