Introduction
Hi All,<o:p>
<o:p>
Here is some cool stuff related to CLR Store Procedure, I will cover here how to create simple CLR store procedure using VS 2005.<o:p>
I suggest before reading this article please read my previous article named “CLR store procedure vs. T-SQL store procedure” Click here.<o:p>
Please perform following steps sequentially, to create CLR store procedure.<o:p>
1. Open VS 2005 and click on “New Project”, then select project type of “Database” as shown below.
<o:p>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"><v:f eqn="sum @0 1 0"><v:f eqn="sum 0 0 @1"><v:f eqn="prod @2 1 2"><v:f eqn="prod @3 21600 pixelWidth"><v:f eqn="prod @3 21600 pixelHeight"><v:f eqn="sum @0 0 1"><v:f eqn="prod @6 1 2"><v:f eqn="prod @7 21600 pixelWidth"><v:f eqn="sum @8 21600 0"><v:f eqn="prod @7 21600 pixelHeight"><v:f eqn="sum @10 21600 0"><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"><o:lock aspectratio="t" v:ext="edit"> <o:p>
2. Select location for your project and say “OK”. If any connection is been created previously following screen will appear to select available database connection.
<o:p>
3. You can add another connection by clicking “Add New Reference…” following screen will appear on click of button. The same screen will appear if you do not have any connection created previously. <o:p>
<o:p>
<o:p>
4. Type machine name or IP address of your SQL Server. Select type of authentication you required (here I am using windows authentication), then select database name where you would like to deploy CLR store procedure.<o:p>
5. Click on “Test Connection” if you got message “Test Connection Succeeded” then click on “OK”, else make sure you have selected proper SQL Server, database and you hold account on SQL Server for defined database.<o:p>
6. On click of “OK” you will see message window shown below.
<o:p>
<o:p>
7. Say “Yes” and proceed, I will cover how to debug and set permissions, related to CLR store procedure in my next article named “Deploy and Debug CLR store procedure”.<o:p>
8. Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”. (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)
<o:p>
<o:p>
9. Now in solution click on project and say “Add New Item”, following window appears where as select item of type “Store Procedure” and click on “ADD”. (I will cover what “Test Script” folder hold in my next article named “Deploy and Debug CLR store procedure”)<o:p>
10. Cheer up, you are about to write your first store procedure. Here I will show you simple demo of how to select records using CLR store procedure, and it goes here.<o:p>
[Microsoft.SqlServer.Server.SqlProcedure]<o:p>
public static void GetRole()<o:p>
{<o:p>
// Put your code here<o:p>
SqlConnection conn = new SqlConnection("Context Connection=true");<o:p>
SqlCommand cmd = new SqlCommand(@"Select * from dbo.Roles", conn);<o:p>
<o:p>
conn.Open();<o:p>
<o:p>
SqlDataReader rdr = cmd.ExecuteReader();<o:p>
SqlContext.Pipe.Send(rdr);<o:p>
<o:p>
rdr.Close();<o:p>
conn.Close();<o:p>
}<o:p>
11. Now select project from solution explorer and say deploy. Once deployed on SQL Server, go to SQL Server and fire following query and see the output. <o:p>
USE [IRSDev]<o:p>
exec GetRole<o:p>
Output :
<o:p>
<o:p>
12. You may not able to deploy CLR store procedure in single go, since lot of permission issues. If so please refer my next article named “Deploy and Debug CLR store procedure”.<o:p>
<o:p>
