5 Steps for Developing and Deploying CLR Code in SQL Server

1. Prepare a database. Create the database in SQL Server where the objects will be deployed unless the target database already exists. For our examples, you've already created the sample database,TSQLCLR.

Main article: CLR or Not CLR: Is That the Question?

2. Create a project in Visual Studio 2005. To develop CLR code, you can use any environment—even Notepad. However, it's convenient to develop CLR code in Visual Studio 2005 Professional Edition or higher because it's a feature-rich environment that lets you debug your code and deploy it in SQL Server with minimal effort. Create a new C# or VB project by using the Database, SQL Server Project template. (Note that if you're using a lower VS edition, you'll need to use the Class Library project template.) In the New Project dialog box, name the project CLRRoutines. Specify C:CLR (or a folder of your choice) as the target location, and confirm.

3. Prepare a database reference. If you used the SQL Server Project template, the Add Database Reference dialog box will pop up.VS will use the database reference you provide to deploy the CLR objects in the target database. Create a new database reference to the TSQLCLR database (unless you already created one), and choose it. A new dialog box will ask whether you want to enable SQL/CLR debugging. I won't discuss debugging in this article, so you can select No.

4. Add items to the project. At this point, you add to the project items (files) that will contain your code.To add items, choose the Project menu option, then choose the type of item to develop (e.g., UDF, stored procedure). Depending on the item type you choose,VS 2005 will use the appropriate template and prepare a code skeleton for you. For our examples, you'll use the code that I provide in Listing 3 or Listing 4 in the main article, so choose the Add Class... option. Name the file CLRRoutines.cs if you prefer to use C# code, and CLRRoutines.vb if you prefer to use VB code. If you used the Class Library project template, simply rename the file Class1.cs (or Class1.vb) to CLRRoutines.cs (or CLRRoutines.vb).

5. Build and deploy the solution. Building the solution creates in disk a .dll file that contains the result assembly. Deploying the solution loads the intermediate language (IL) code from the file to the target database and registers the routines in the database. Once the deployment process is finished, you no longer need the .dll file on disk because the IL code has become an integral part of the database.

Deploy the solution to SQL Server by choosing the Build > Deploy CLRRoutines menu option. Note that if you used the Class Library project template, you'll need to first build the solution by choosing Build > Build CLRRoutines, then deploy it manually in SQL Server by using the appropriate T-SQL code (the CREATE ASSEMBLY, CREATE FUNCTION, and CREATE PROCEDURE commands). Now, the routines are ready for use.The rest of the demonstrations will take place in SQL Server.