|
Build SQL CLR Objects With .NET
Learn how to develop SQL procedures and user-defined functions (UDFs) in .NET 2.0 and SQL Server 2005.
by Doug Thews
October 11, 2004
Technology Toolbox: VB.NET, Visual Studio .NET 2005, SQL Server 2005
One of the most anticipated features in SQL Server 2005 (code-named Yukon) and Visual Studio .NET 2005 (code-named Whidbey) is the capability to develop stored procedures, user-defined functions (UDFs), and user-defined data types in .NET 2.0. SQL Server 2005 now supports developing UDFs, user-defined procedures (UDPs), user-defined triggers, and user-defined data types in any .NET 2.0 Common Language Runtime (CLR)-compliant language.
In this article, I'll provide an introduction to writing stored procedures and functions in .NET 2.0. While the scope of CLR integration with SQL Server 2005 could take up an entire book, I'll focus on how the CLR works within SQL Server 2005 and how to start writing and deploying CLR code to SQL Server 2005.
The code in this article is based on beta versions for both Visual Studio .NET 2005 (beta 1) and SQL Server 2005 (beta 2). As with any beta product, there is a slight chance that some of the functionality or namespaces might change before the final products are released. I've tried to be as careful as possible: I've worked with the internal beta teams at Microsoft and have purposely stayed away from various features and internals that are likely to change between now and both products' final release.
Getting both SQL Server 2005 and VS.NET 2005 installed on the same machine is pretty straightforward, with one exception. The beta 2 build of SQL Server 2005 uses a minor-version upgrade of the .NET 2.0 Framework. Therefore, you should install SQL Server 2005 first so that the higher .NET Framework is installed. (The VS.NET 2005 install won't overwrite it because it detects that the .NET 2.0 Framework is installed already.) If you install VS.NET 2005 first, you'll need to uninstall the .NET 2.0 Framework and then install SQL Server 2005. Microsoft says that both versions will use the same framework by the time they're released, so this should only be an issue for developers who work with the betas.
Once you've completed the installations, it's time to write some code to see what you can do. To test out how the CLR inside of SQL Server 2005 actually works, I'll show you how to write a simple stored procedure that just returns the current version of SQL Server 2005.
First, bring up VS.NET 2005 and create an empty solution. I've called mine VSM122004 and included it with the downloadable source code for this article. Next, select Add | Project for the solution, and select SQL Server Project from under the SQL Server project types (see Figure 1).
For purposes of this exercise, I'll be using the SampleProcedures project to hold all user-defined stored procedures, the SampleFunctions project to hold all UDFs, and the SampleTriggers project to hold all user-defined triggers.
Once you add a new project, VS.NET 2005 asks you to create a database reference for that specific SQL Server project (see Figure 2). It's not mandatory you add a database reference because assemblies run in-process with SQL Server 2005 (meaning they don't need their own connections), but when you add one, it helps you browse for SQL Server objects in the IDE.
Create a Stored Procedure Class File
Now that you've created the project, create a simple stored procedure class file by selecting Add | Stored Procedure and call it SimpleStoredProc.vb. Notice the IDE creates a stub:
Partial Public Class StoredProcedures
<SqlProcedure()> _
Public Shared Sub SimpleStoredProc()
' Add your code here
End Function
Any Function or Sub must be declared as Public Shared within the class to be deployed as a SQL CLR object. The IDE also generates a Partial Public Class called Stored Procedures. If you create another stored procedure using the previous steps, you'll get another Partial Class. When they're compiled, these classes are merged into a single class called StoredProcedures, which enables you to keep your stored procedures in a single class file, or separate them into functionality-specific class files. The end result is a single assembly per project to deploy to SQL Server 2005.
Back to top
|