.NET CLR Programming with SQL Server 2005 Made Simple
This article explains how to create .NET based CLR sub-routines in SQL Server 2005 in the simplest way possible. It is mainly targeted at .NET developers who want to work with SQL Server 2005 at server side.
A downloadable file for this article is available here.
The following are the topics covered in this article:
Creating a new database, table and a few rows.
Creating, coding and testing a .NET CLR stored procedure.
Creating, coding and testing a .NET CLR user-defined function.
Creating, coding and testing a .NET CLR aggregate function.
The entire source code for this contribution is freely available for download.
Creating your own database in SQL Server 2005
This section mainly covers the creation of a database and tables to be used in this series. Go through the following steps:
Go to Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio and connect to your SQL Server 2005 instance with the necessary credentials.
Once connected, open the SQL Server 2005 instance using the “Object Explorer," right click on “databases” and select “New Database” (Fig 1).
You will be presented with the “New Database” dialog. Provide the database name “Sample” and click on “OK” to create the new database within the same instance.
Once the database is created, you must be able to watch the same in “Object explorer.”
Using the “Object explorer,” open “Sample” database, right click on “tables” and select “new table" (Fig. 2).
Create the structure of the table as shown in the following figure (Fig 3) and save the table with the name “emp.”
Once you have created the table “emp,” open it (Fig 4) and fill with rows as shown (Fig 5).
Once you have created the database and tables as shown in the previous section, complete the following steps to create a .NET CLR stored procedure in a SQL Server 2005 database:
Go to Start -> Programs -> Microsoft Visual Studio 2005 -> Microsoft Visual Studio 2005.
Go to File -> New -> Project.
Within the “New Project” dialog box, select "database" for the "Project Type" in the “Visual Basic” tree and select “SQL Server Project” as the template. Provide the name “SampleAllInOne.” By this time, the dialog box should look something like the following (Fig 6).
Once you click “OK”, you will be provided with the “New Database Reference” dialog. Provide your own instance name, select the database “Sample” (created in previous section) and test the connection (Fig 7).
Once testing is successfully completed, click on “OK” to create the project.
You will be provided with a dialog box, which asks you to enable CLR debugging or not. Please click on “Yes” for that (Fig 8).
Using the “Solution explorer,” right click on the “SampleAllInOne” Project, and go to Add-> Stored Procedure (to create a new stored procedure) (Fig 9).
You will be presented with the “Add New Item” dialog box. Within that dialog box, select “Stored Procedure” as template and provide the name as “IncSalaries” and finally click on “Add” (Fig 10).
Before proceeding through this section, make sure that you have completed everything in the previous section.
Once the “IncSalaries.vb” is created, modify your code in such a way that it looks something like the following:
ImportsSystem
ImportsSystem.Data
ImportsSystem.Data.SqlClient
ImportsSystem.Data.SqlTypes
ImportsMicrosoft.SqlServer.Server
PartialPublicClass StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
PublicSharedSub IncSalaries(ByVal IncVal As SqlDouble)
' Add your code here
Using cn AsNew SqlConnection("context connection=true")
Dim cmd AsNew SqlCommand("update sample.dbo.emp set sal = sal + " & IncVal.ToString, cn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
EndUsing
EndSub
EndClass
The above is a simple CLR based stored procedure written using Visual Basic.NET 2005 to get it deployed in SQL Server 2005. The name of the stored procedure is “IncSalaries;” it accepts a single parameter, “IncVal.” The above stored procedure simply increases the salaries of every employee in the table “emp” with the value provided as the parameter.
To test the above stored procedure, follow the following steps:
Using “Solution Explorer,” open “test.sql” in “test scripts” (Fig 11).
Go to the last line and modify the code as follows:
--select 'To run your project, please edit the Test.sql file in your project. This file is located in the Test Scripts folder in the Solution Explorer.'
exec IncSalaries 50
Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.
Within the window, click on “Surface Area Configuration for Features” (Fig 12).
Enable the CLR integration as shown (Fig 13).
Press F5 to execute the whole solution. If it executed with any errors, everything gets displayed in the “Output” window.
If the stored procedure executes successfully, you should be able to see all the salaries increased by 50.
Now we shall extend the previous sections to add a stored function to our existing solution.
Using the “Solution explorer,” right click on the “SampleAllInOne” Project and go to Add-> User-defined Function (to create a new stored function) (Fig 14).
You will be presented with the “Add New Item” dialog box. Within that dialog box, select “User defined function” as the template, provide the name “getNetSalary” and finally click on “Add” (Fig 15).
Once the “getNetSalary.vb” is created, modify your code in such a way that it looks something like the following:
PublicSharedFunction getNetSalary(ByVal empno As SqlString) As SqlDouble
' Add your code here
Dim sal AsDouble
Using cn AsNew SqlConnection("context connection=true")
Dim cmd AsNew SqlCommand("select sal from sample.dbo.emp where empno='" & empno.ToString & "'", cn)
cmd.Connection.Open()
sal = CType(cmd.ExecuteScalar, Double)
cmd.Dispose()
EndUsing
Dim hra AsDouble = sal * 10 / 100
Dim ta AsDouble = 200
Dim gross AsDouble = sal + hra + ta
Dim epf AsDouble = sal * 5 / 100
Dim net AsDouble = gross - epf
Return net
EndFunction
EndClass
To test the above stored procedure, follow the following steps:
Using “Solution Explorer,” open “test.sql” in “test scripts” (Fig 11).
Go to the last line and modify the code as follows:
--select 'To run your project, please edit the Test.sql file in your project. This file is located in the Test Scripts folder in the Solution Explorer.'
--exec IncSalaries 50
select dbo.getNetSalary(empno) from sample.dbo.emp
Press F5 to execute the function and you should be able to see the result.
Now we shall extend the previous sections to add a stored function to our existing solution.
Using the “Solution explorer,” right click on the “SampleAllInOne” Project and go to Add-> Aggregate (to create a new aggregate function) (Fig 16).
You will be presented with the “Add New Item” dialog box. Within that dialog box, select “Aggregate” as the template, provide the name “NetSal” and finally click on “Add” (Fig 17).
Once the “NetSal.vb” is created, modify your code in such a way that it looks something like the following:
To test the above stored procedure, follow the following steps:
Using “Solution Explorer,” open “test.sql” in “test scripts.”
Go to the last line and modify the code as follows:
--select 'To run your project, please edit the Test.sql file in your project. This file is located in the Test Scripts folder in the Solution Explorer.'
--exec IncSalaries 50
--select dbo.getNetSalary(empno) from sample.dbo.emp
select dbo.NetSal(sal) from sample.dbo.emp
Press F5 to execute the function and you should be able to see the result.
The next article will follow with .NET CLR based User-Defined Types (UDT) and triggers in SQL Server 2005. This entire solution was developed using Microsoft SQL Server 2005 Developer edition along with Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition. I didn’t really test the solution in any other version.
Any feedback, bugs, errors, suggestions, doubts etc., are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
Implementing Managed code in SQL Server 2005 using Visual Studio.NET 2005
