·         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')