Writting a CLR Stored Proc

Posted Date: 30 Nov 2008 Total Responses: 3

Posted By: g_arora Member Level: Silver Points: 10

Writting a CLR Stored Proc

With the launch of SqlServer2005, we got the ability to write CLR from any .Net language.

Overview:

The following code-snippet tells how to write a simple CLR Stored Proc with the use

of C#.

Step:

1. Start Visual Studio2005 or higher
2. Add new file from File -> New -> File
3. Select Class with C#
4. Mark the name as AStepAheadProc.cs
5. Write the following lines:


/* This Example is a part of different 
 * examples shown in Book: 
 * C#2008 Beginners: A Step Ahead
 * Written by: Gaurav Arora 
 * Reach at : Gaurav Arora */


using System.Data.SqlServer;
using System.Data.SqlTypes;

public class AStepAheadProc
{
    public static void FirstProcProc()
    {
        //Client output buffer

        SqlPipe sqlPipe = SqlContext.GetPipe();

        //send the output

        sqlPipe.Send("This is demo Store Proc Assembly");

    }
}

Explanation :

The sqlContext provides The GetPipe() method returns a SqlPipe that we can use to send results back to the client.

Steps of Use/Working

1. Compile the above code into assembly


csc /target:library c:\AStepAheadProc.cs /r:"D:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Binn\sqlaccess.dll"

2. Load the created assembly in SQL with the use of CREATE PROCEDURE


create procedure AStepAheadProc
as external name AStepAheadProc.AStepAheadProc.FirstProcProc

3. Execute the Created stored procedure in Query Analyzer


exec AStepAheadProc

Note:

The above code-snippet is just describe how to create a CLT Stored Proc.

Responses

Author: Joymon 31 Dec 2008 Member Level: Bronze Rating: Points: 3

Can't we compile from VS without the below line

csc /target:library c:\AStepAheadProc.cs /r:"D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"

Author: g_arora 01 Jan 2009 Member Level: Silver Rating: Points: 4

Hi Joymon,

First of all thanks for your question because this inspires me for a new article.

Ofcourse, you can do the same thing using Visual Studio, without using Console compilation.

Please refer to my another Article "Writing a CLR Stored Proc - Using Visual Studio".

Thanks & regards,
Gaurav Arora

Author: shuby 04 Jan 2009 Member Level: Bronze Rating: Points: 1

very good and knowledgeful article

Post Reply

You must Sign In to post a response.


	Previous : Writting a CLR Stored Proc : Using Visual Studio
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages