SQL Server integration with CLR aka SQL CLR (Part II – Creating a stored procedure in SQL CLR)

Vishvvas
Posted by in .NET Framework category on for Advance level | Points: 300 | Views : 4708 red flag
Rating: 5 out of 5  
 1 vote(s)

In the previous article ,http://www.dotnetfunda.com/articles/article1727-sql-server-integration-with-clr-aka-sql-clr-part-i-introduction-and-back.aspx we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. This article discusses the steps for creating a stored procedure, deployment and testing the same. This is second write-up of multipart series on this subject.

Introduction and background

Now,we have know-how of what SQL CLR is and its benefits and when to choose it. In this write-up we would learn to create a stored procedure, deploy and test the same.

Objective

To learn how to create a stored procedure in SQL CLR and use it. 

Description

The tools for development are VS 2010 and SQL server 2008 R2. The database “AdventureWorks” is referred. The first step in learning the SQL CLR database items is to create a SQL CLR project.




Visual Studio provides a template for creating such project which is named as SQL CLR Database project. Please create a project as shown above and name it as “SqlServerProject”. The snapshot for project organization is as below.



The folder named “Test Script” contains the file “Test.sql” where in the test script can be included.

Now, we are targeting to create a store procedure. The step is same as creating any new item and select the option for “Stored Procedure”. Have a look at other options like aggregates, triggers etc

Let’s name the class as “StoredProcedures”. We are going to have SELECT queries in envisaged stored procedure. As SELECT queries are used, we need to use “SqlDataReader” and also SqlContext pipe to send the results. Following code snippet demonstrates the code for creating a stored procedure with SELECT queries.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

/// <summary>

/// Following code creates a stored procedure named SQLCLRStoredProcedure_CS in the Adventure Works sample database.

/// This stored procedure contains SELECT queries

/// </summary>

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SQLCLRStoredProcedure_CS()

{

using (SqlConnection conn = new SqlConnection("context connection=true"))

{

SqlCommand SQLCLRStoredProcedureCommand = new SqlCommand();

SQLCLRStoredProcedureCommand.CommandText = "SELECT TOP 2 * from Production.Product;" +

"SELECT TOP 2 * from Sales.Customer;";

SQLCLRStoredProcedureCommand.Connection = conn;

conn.Open();

SqlDataReader sqlDataReader = SQLCLRStoredProcedureCommand.ExecuteReader();

SqlContext.Pipe.Send(sqlDataReader);

conn.Close();

}

}

};

Yes, we have created our first stored procedure. As VS 2010 provided facility of building, deploying and running the test script in one go, we can follow this route. VS 2010 also allows building and deploying separately. Refer to following figure


The another way to accomplish this is --> 1. Build 2. Deploy in SQL Server with T SQL.

For deployment through T SQL, following statements needs to be executed in SQL query window (complete path needs to be provided for the assembly)

CREATE ASSEMBLY SqlServerProject

FROM '…… \SqlServerProject\bin\Debug\SqlServerProject.dll'

WITH PERMISSION_SET = SAFE;

CREATE PROCEDURE [dbo].[SQLCLRStoredProcedure_CS]

AS

EXTERNAL NAME [SqlServerProject].[StoredProcedures].[SQLCLRStoredProcedure_CS]

GO

These statements create an assembly in SQL server and also create a stored procedure referring the assembly just created.

We are done with creating a stored procedure in SQL CLR and its deployment. Hurray, definitely it is easy than we expected. Isn’t it?

When we browse the stored procedure in management studio, we get to see following


Isn't it surprising that no T SQL code is seen? As the stored procedure is secured and hence not editable in management studio. This is not going to make database developers/ administrators happy but has it is part of assembly; this is the way it should behave.

Now we will have to test it. Wait, one most important step is still not performed and what is it? Yes, the SQL server has a configurable option for enabling CLR without which the database items created through SQL CLR won’t execute. Let’s see how to do it.

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

Testing

For testing through VS 2010, please add following code in “Test.sql”.


When one chooses to build, deploy and test through VS 2010, the output of testing is displayed in output window as “Database output”.


The same script i.e. EXEC .... can be used in SQL Query window in management studio for SQL server and we can confirm the results.

Summary and Conclusion

We are through our first exercise of creating a stored procedure in SQL CLR along-with deployment and testing. In retrospection, it seems easy to implement SQL CLR  contrary to the situation before one make his/ her hands dirty, the task seems daunting.

Hope this helps to help to create SQL CLR stored procedure and start exploring the SQL CLR world.

HAPPY PROGRAMMING!!!

Reference

Please see this link

Page copy protected against web site content infringement by Copyscape

About the Author

Vishvvas
Full Name: Vishwas Sutar
Member Level: HonoraryPlatinum
Member Status: Member,MVP
Member Since: 5/30/2011 2:13:10 AM
Country: India

http://www.dotnetfunda.com
Extensive and rich experience across gamut of technologies and programming languages like PB,VB,C++,VB.NET, C#, Classic ASP,ASP.NET, ASP.NET MVC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)