As I discussed in my previous article, CLR is the heart of Microsoft .Net framework. In SQL Server 2005 we can create a database object inside an instance of SQL Server that programmed in an assembly created by .Net framework. Assemblies are DLL files used in an instance of SQL Server to deploy functions, stored procedures, triggers, etc. that are written in one of the managed code languages hosted by Microsoft .NET Framework.

In this article I am going to discuss how to use Visual Studio 2005 for creating CLR integrated functions. In Visual Studio 2005 there is an option for creating database projects, which is very helpful in this objective.

Let us go through an example with creating CLR integrated functions using Visual Studio 2005 and debugging it.

·         Creating a New SQL Server Project

·         Enabling database references

·         Creating the required database object by adding a template

·         Implementing the codes for CLR Functions

·         Using of Test.sql file

Creating a New SQL Server Project

Open Visual Studio 2005 IDE, create a New SQL Server Project and establish a connection to a database on an instance of SQL server 2005.

File >> New Project >> Visual Basic >> SQL Server Project >> Project Name. I have given here a SQL_CLRFunctions for creating CLR integrated function using SQL Server project.

Enabling database references

Here it requires creating a database reference.

Now, Add New Reference >> Give your Server Name >> Database Name >> Credentials... >> Test Connection for checking the connection.

Figure 1 (Creating Database reference)

Creating the required database object by adding template

Then we have to choose the database object we want to create (here we need to create User-Defined functions...).

Solution Explorer (right click) >> Add >> New Item... >> User-Defined Function >>  will give the Name of the source.

Figure 2 (Adding User-Defined Function)

Implementing the codes for CLR Functions

By default, all the required namespaces are added. They are System, System.Data, System.Data.Sql, System.Data.SqlTypes, and Microsoft.SqlServer.Server.

You need to add the code for our new type to the text editor. These codes are given in the next section, just copy that.

Using of Test.sql file

Then we need to add the SQL scripts for testing the created type. For this follow the steps:

Solution Explorer >> Test Scripts >> expand >> Open Test.Sql >> Add the scripts given below.

Listing 1

SELECT dbo.CheckUsers('Username’,’ Password')

There are two types of user defined functions supported by SQL Server: 1- Scalar-Valued function (SVF), which returns a single value, and 2- Table-valued function (TVF), which returns a set of rows. Here, we choose for SVF, which returns a single value, such as a string, integer or bit value. In Microsoft SQL Server 2005 we can create scalar-valued user-defined functions in managed code using any Microsoft .NET Framework programming language. These functions are accessible to Transact-SQL or other managed code.

The following are the codes for CLR-integrated user defined SVF.

Listing 2

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
 
Partial Public Class CLRFunctions
    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function CheckUsers(ByVal UserName As String, _
ByVal PassWord As String) _
 As Boolean
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim sCmdString As String = _
"SELECT COUNT(*) FROM AccountTable where        Username= '" & _
UserName & "' AND Password='" & _
PassWord & "'"
            Dim cmd As New SqlCommand(sCmdString, conn)
            If CType(cmd.ExecuteScalar(), Integer) > 0 Then
                Return True
            Else
                Return False
            End If
        End Using
    End Function
End Class

Here the custom attribute SqlFunction makes the target Static function (i.e. Shared in VB.Net) as a User-defined SVF, which is found in the Microsoft.SqlServer.Server namespaces. The custom attribute indicates whether or not the user-defined function (UDF) uses the in-process provider to read data in the server. This is indicated by setting DataAccessKind to DataAccessKind.Read or DataAccessKind.None. This is set in the above code as:

SqlFunction(DataAccess:=DataAccessKind.Read)

·         To Build: Go to Build >> Build SQL_CLRFunctions (Project Name)

·         To deploy the assembly in SQL Server 2005 : Go to Build >> Deploy SQL_CLRFunctions (Project Name)

·         To verify this deployment open the SQL Server Management Studio. Navigate to Server Name >> Databases >> DB Name >> Programmability >> Assemblies >> you will then see the assembly "SQL_CLRFunctions" deployed there.

Figure 3

Debugging is a highly privileged operation and, therefore, only members of the sysadmin fixed server role are allowed to do so in SQL Server 2005.

Steps for debugging

·         In the Debug menu, select Start Debugging to compile, deploy, and test the project. The test script in Test.sql will be run and the managed database object will be invoked.

·         When the yellow arrow designating the instruction pointer appears at the breakpoint code, execution pauses and you can begin debugging your managed database object. The Locals window is used to observe the state of the objects currently highlighted by the instruction pointer. Variables can be added to the Watch window.

The CLR function is checked in Query analyzer by navigate to Server Name >> Databases >> DB Name >> New Query and then execute the following.

Listing 3

SELECT dbo.CheckUsers('Username’,’Password')

If any error occurs in the above process like:

Server: Msg 325, Level 15, State 1, Procedure Object Name, Line 4 Incorrect syntax near 'external'. You may need to set the compatibility level of the current database to a higher value to enable this feature.

Then we have to set the compatibility level to 90 from 80, which is default, before we create a CLR object in the database.

Listing 4

EXEC sp_dbcmptlevel 'DatabaseName',90

The above discussion describes an easier way of creating CLR integrated database objects than the previous one, discussed in my first article. It uses the Visual Studio 2005 which is very familiar to all of us. This is very helpful when we develop an application in Remote machine. Again, CLR integrated user defined functions (UDFs) have some added advantage over T-SQL UDFs. In the case of Table-Valued Functions (TVFs), Transact-SQL materializes the results of calling the function into an intermediate table, while CLR TVFs represent a streaming alternative.

By

Soyuj Kumar Sahoo

Mindfire Solutions