CLR Integration in SQL Server 2005 Using Visual Studio 2005 – Part 2
page 2 of 8
by Soyuj Kumar Sahoo
Average Rating: 
Views (Total / Last 10 Days): 38649/ 101

Steps for using Visual Studio 2005 for creating Database Projects

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

View Entire Article

User Comments

Title: Update?   
Name: Apuma
Date: 2010-02-03 7:17:37 PM
Can we see an example using VS 2008?
Title: Bout the article..   
Name: ainOmar
Date: 2007-05-13 10:57:53 PM
can u simply attach more samples using .net clr
Title: Nice Article   
Name: Abhishek
Date: 2007-02-21 4:50:26 AM
Nice article to work with .net CLR.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-15 4:39:44 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search