Click here to Skip to main content
Click here to Skip to main content

How to create CLR store procedure

By , 15 Oct 2006
Rate this:
Please Sign up or sign in to vote.

Introduction

Hi All,

 

Here is some cool stuff related to CLR Store Procedure, I will cover here how to create simple CLR store procedure using VS 2005.

I suggest before reading this article please read my previous article named “CLR store procedure vs. T-SQL store procedure” Click here.

Please perform following steps sequentially, to create CLR store procedure.

1.      Open VS 2005 and click on “New Project”, then select project type of “Database” as shown below.

Sample screenshot

 

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.

Sample screenshot

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.

Sample screenshot

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.

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.

6.      On click of “OK” you will see message window shown below.

Sample screenshot

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”.

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”)

Sample screenshot

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”)

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.

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetRole()

    {

        // Put your code here

SqlConnection conn = new SqlConnection("Context    Connection=true");

SqlCommand cmd = new SqlCommand(@"Select * from dbo.Roles", conn);

 

        conn.Open();

 

        SqlDataReader rdr = cmd.ExecuteReader();

        SqlContext.Pipe.Send(rdr);

 

        rdr.Close();

        conn.Close();

  }

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.

     USE [IRSDev]

exec GetRole

Output :

Sample screenshot

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”.

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Malpani Amol
Web Developer
India India
I am a .NET developer cum designer. I work for Zensar Technologies Ltd. Pune (INDIA), I consult, I develop, I debug and besides that I speak at conferences and user groups and I also write. I have written some articles on WCF, WF, SSIS, Compression decompression, CLR Store Procedure, SQL Server Reporting Services 2005, Extended grid and lot more to count.
Reach to me on:-a.malpani@zensar.com

Comments and Discussions

 
QuestionHow to generate CRL desplyment script PinmemberJan Prochazka alias jena20-Oct-11 10:36 
If you can't use Deploy function of Visual Studio:

http://www.datadmin.com/articles/how-generate-crl-installation-script
QuestionStore procedure or Stored procedure? PinmemberSam Hobbs10-Dec-10 8:59 
GeneralExcellent Article PinmemberWashington600074-Nov-06 19:53 
GeneralI thought it was a good article Pinmembercykophysh3916-Oct-06 1:22 
QuestionWhat hasn't been covered before PinmemberMark Nischalke15-Oct-06 5:40 
AnswerRe: What hasn't been covered before PinmemberMalpani Amol15-Oct-06 18:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 15 Oct 2006
Article Copyright 2006 by Malpani Amol
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid