SQL Server 2005-2008 and managed code

In this post I will try with a step by step example to help you understand how we can use Visual studio 2005 (2008) and SQL Server 2005 (2008) to develop managed code within a SQL Server database.

SQL Server 2005 includes the framework which means we can use Visual Studio to create managed code applications that reside and run within SQL Server environment.

So we need to have a database first. i am going to use good old Northwind.

you can find the installation scripts for Northwind in the link below

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Within Northwind we are going to create a managed code application that will return the contact name and company name for the Customers table.after you install the database

1) Launch Visual Studio

2) Create a new Project

3) Select Database Project-SQL Server Project

4) Then you will be asked to specify a database reference. We need to tell it where we will deploy our application. All applications that run in  the SQL environment are deployed as assemblies.The .Net framework handles these assemblies.

5) Right-Click in your project in the Solutions Explorer and add a New Item-> stored procedure-call it getcustomers. A new class is created

 Partial Public Class StoredProcedures<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub  getcustomers ()

‘add your code here

 End Sub

End Class

 

6) this code goes inside the getcustomers sub, using the SqlCommand object

Dim myCommand As New SqlCommand()

myCommand.CommandText = “Select contactname,companyname from Customers”

 ‘ Execute the command and send back the results

SqlContext.Pipe.ExecuteAndSend(myCommand)

 

7)To deploy the assembly in the sql server we just go to the project and right-click and then hit “Deploy”

8) If we fire up Sql Server Management studio and navigate to the Northwind database we will see under “Assemblies” and “Programmability” we will see “getcustomers”

9) If we try to execute this, it will fail. In order to do it we need to “enable CLR”

10) Type “exec sp_configure” in the query window-CLR is equal to zero

11) Type “sp_configure ‘clr enabled’, ‘1’” and then type “reconfigure”. execute this in the query window

12) Type “exec sp_configure” again. now the value has changed to 1.

13) type “exec getcustomers”

now it works. It does not mean that T-SQL will be replaced.

It is just a way to do things. now if we need to use this stored procedure to get some results back to the user we can do that by building a data access layer with a dataset.

1) Launch Visual studio

2) Create a new asp.net (vb) web application project

3)Add a new item and select Data->Dataset. Leave the name as Dataset1.xsd

4)Drag and drop a Tableadapter object in the designer area. In the wizard that will come up, define the connection string. In my case we connect to the Northwind database.

5) In the next step of the wizard select “Use existing stored procedures”

6) In the next step in the “Select” dropdown list choose the stored procedure, in my case “getcustomers”

7)Then we click “Next” and in the next step of the wizard we do not change anything

8) We press finish and the wizard closes

9) In our default.aspx we drag and drop a Gridview web server control

10) In the datasource configuration options we choose “Object”

11) In the next step we choose the business object we created before, we then click “Next” and “Finish” 

12) We save and run our application by hitting F5.

If everything is ok then we will see the results of the managed stored procedure we initially created,through the business object in our web page.