.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> ASP.NET >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Writting a CLR Stored Proc : At a look

Posted By:Gaurav Arora       Posted Date: April 26, 2009    Points: 25    Category: ASP.NET    URL: http://www.dotnetspark.com  
 

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.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend