Click here to Skip to main content
15,860,861 members
Articles / Database Development / SQL Server

Create CLR Stored Procedure with Visual Studio Express Edition

Rate me:
Please Sign up or sign in to vote.
4.86/5 (17 votes)
23 May 2012CPOL4 min read 65.4K   743   26   14
Create CLR Stored Procedure with Visual Studio Express edition
 
Introduction 

Visual studio Express edition does not provide a project type (template) for creating CLR Stored Procedures as the professional and higher editions. But the principle of creating a CLR Stored Procedure is very simple and we can accomplish it with the express edition. 

CRL Stored Procedure creation steps 

The CLR Stored Procedure creation involves the following steps: 

  1. Create a class library project.
  2. Define the stored procedure as a static method of a class. This method is decorated with SqlProcedureAttribute attribute. 
  3. Deploy the class library:
    • Register the class library in SQL server using CREATE ASSEMBLY statement.
    • Create the stored procedure that references the registered assembly using CREATE PROCEDURE statement. 
  4. Test the stored procedure.  

Let us fulfill these steps using Visual studio 2010 Express edition:

Step 1: Create a class library project  

  1. Select File->New Project…
  2. Select Class Library project and name it BooksMgr
  3. Click OK button 

Image 1 

This will create a class library project with a default class named Class1. Rename this class to StoredProcedures. This class will hold our stored procedure (of course you can define more than one stored procedure in this class).

Be sure to set the target framework for this project to .Net Framework 3.5, because we will use SQL server 2008R2 which requires that SQL CLR assemblies target version 2.0, 3.0, or 3.5 of the .NET Framework (not version 4). This can be done in the project properties under Application tab:

 

Image 2

 

Step2: Define the stored procedure 

Create a public static method in the StoredProcedures class and name it AddBook. Decorate this method with SqlProcedureAttribute.   

C#
namespace BooksMgr
{
    public class StoredProcedures
    {
        [SqlProcedure()]
        public static void AddBook(string bookName, string authorName)
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"INSERT INTO [BooksLibrary].[dbo].[Books]
                                        ([Name],[Author])
                                        VALUES
                                        (@Name,@Author)";
 
                command.Parameters.AddWithValue("@Name", bookName);
                command.Parameters.AddWithValue("@Author", authorName);                             
                conn.Open();
                command.ExecuteNonQuery();                
            }
        }
    }
}    

This stored procedure will insert a book in the Books table. This simple table has two columns: the book name and the author name. The SqlProcedureAttribute mark the AddBook method as a stored procedure. Compile the project.  

Step3: Deploy the assembly 

To deploy the assembly, we need to register it in the SQL server for a given database. The database that we will use is a simple one. Here is a script that will create a database named BooksLibrary with its unique table Books, run the following script in SQL server Management Studio:

SQL
CREATE DATABASE [BooksLibrary]
Go
USE [BooksLibrary]
GO
CREATE TABLE [dbo].[Books](
[Name] [nvarchar](1000) NOT NULL,
[Author] [nvarchar](1000) NOT NULL
) ON [PRIMARY]
GO 

In order to deploy the created assembly, we need to create a SQL script deployment file. So, add a new Item (Text file) to the project BooksMgr. Rename this new file to Deploy.sql and set the property Copy to output Directory to Copy if newer.  This will copy the script file to the output directory which gives us a full package (binaries and deployment script).   


 Image 3

In the script file we will test if this assembly and its procedure already exist in the database, if so, then we drop them. This is done by the following script: 

SQL
-- Deploy to 'BooksLibrary' database
USE [BooksLibrary]
 
-- Drop the stored procedure 'AddBook' if it exists
IF OBJECT_ID ('AddBook') is not null
BEGIN
	DROP PROCEDURE AddBook
END
GO
 
-- Drop the assembly 'BooksMgr' if it exists
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'BooksMgr') 
BEGIN
	DROP ASSEMBLY BooksMgr
END
GO

After that, we create the assembly in the SQL server (notice the full path to our class library):

SQL
-- Create the assembly 'BooksMgr'
CREATE ASSEMBLY BooksMgr 
FROM 'F:\Dev\Projects\BooksMgr\BooksMgr\bin\Debug\BooksMgr.dll'
GO 
Note that this assembly’s path is in my computer, you must adapt it to point to the assembly location in your computer.
And the last step is to create the stored procedure in the SQL server:  
SQL
-- Create the stored procedure 'AddBook' with its parameters
CREATE PROCEDURE AddBook(@bookName nvarchar(1000), @authorName nvarchar(1000))
WITH EXECUTE AS CALLER AS EXTERNAL NAME BooksMgr.[BooksMgr.StoredProcedures].[AddBook]
GO

Put all above script snippets in the Deploy.sql file which we have created before, here is the full script (some print statements have been added to trace the deployment execution):

SQL
PRINT N'Deploying BooksMgr assemply to [BooksLibrary] database'
 
-- Deply to 'BooksLibrary' database
USE [BooksLibrary]
 
-- Drop the stored procedure 'AddBook' if it exists
IF OBJECT_ID ('AddBook') is not null
BEGIN
	DROP PROCEDURE AddBook
	PRINT N'Stored procedure AddBook dropped'
END
GO
 
-- Drop the assembly 'BooksMgr' if it exists
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'BooksMgr') 
BEGIN
	DROP ASSEMBLY BooksMgr
	PRINT 'Assembly BooksMgr.dll dropped'
END
GO
 
-- Create the assembly 'BooksMgr'
CREATE ASSEMBLY BooksMgr 
FROM 'F:\Dev\Projects\BooksMgr\BooksMgr\bin\Release\BooksMgr.dll'
PRINT 'Assembly BooksMgr.dll created'
GO
 
-- Create the stored procedure 'AddBook'with its parameters
CREATE PROCEDURE AddBook(@bookName nvarchar(1000), @authorName nvarchar(1000))
WITH EXECUTE AS CALLER AS EXTERNAL NAME BooksMgr.[BooksMgr.StoredProcedures].[AddBook]
GO
PRINT 'Stored procedure AddBook created'
Go

In order to execute this script we use the command line utility SqlCmd.exe. The command line syntax for executing a script file is: 

SqlCmd.exe -S <SqlServerName> -i <FullSqlFileName> 

It will be nice if we can execute this command (automatically) directly after a successful build of our project. To realize this wish:
  • Open project properties window.
  • Click on BuildEvents tab.
  • Type the following command in the Post-build event command line field: 

SqlCmd.exe -S iDevHawk\SQLEXPRESS2008R2 -i $(TargetDir)\Deploy.sql

Note that you must adapt the server name to what’s appropriate to your SQL server. 

  • Be sure that On successful build is selected in Run the post-build event combo box.

This action will execute the script file Deploy.sql after a successful build, i.e. deploy the assembly to SQL Server.  

Image 4

Try to rebuild the project now; you will notice the deployment traces are printed in the output windows. 

 Image 5

To verify that the deployment has been done correctly:

  1. Open SQL server Management Studio.
  2. Expand databases node.
  3. Expand BooksLibrary node (our database).
  4. Expand Stored Procedures node.
Here you see that our stored procedure AddBook is a child item of the Stored Procedures node.

Image 6

Notice that there is a padlock icon next to the stored procedure. This means it’s not editable outside Visual Studio project. 

 

Step4: Test the stored procedure   

The moment for testing the stored procedure is arrived. If CLR integration hasn’t been enabled before, then you will need to enable it in SQL server because it’s disabled by default. Try to run the following script in SQL Server Management Studio: 
SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

To execute our stored procedure: Click with right mouse button on the stored procedure and select Execute Stored Procedure…. 

Image 7 

This will open a window asking to enter parameters values; type a book and author names in their respective fields.

Image 8

Click OK; a script for executing the stored procedure will be created and executed:

Image 9 

We can see the inserted book in Books table:  

Image 10 

Conclusion 

It’s very simple to create an assembly having stored procedures and deploy it using Visual studio 2010 express edition. 

History 

  • May 23, 2012 - Initial Version 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
France France
Software Architect, Consultant and Solution Initiator with over 20 years of industry experience designing and developing software in different domains: semiconductor manufacturing, electrical energy distribution, solar energy production, medical device manufacturing, PC manufacturing etc.

Comments and Discussions

 
PraiseThanks! Pin
alex_perea28-Jun-16 7:53
alex_perea28-Jun-16 7:53 
QuestionAlso a Useful Fix for SQL Server CLR Function Libraries Broken by the 2013 Visual Studio Upgrade Pin
ClarkFrazier28-Jun-15 15:20
ClarkFrazier28-Jun-15 15:20 
GeneralGreat! Pin
yorky00112-Mar-15 9:52
yorky00112-Mar-15 9:52 
GeneralExcellent! Pin
lpitadmin29-Oct-14 21:55
lpitadmin29-Oct-14 21:55 
QuestionDebug ? Pin
VIctorPerez291114-Jul-14 8:24
VIctorPerez291114-Jul-14 8:24 
AnswerRe: Debug ? Pin
PIEBALDconsult14-Jul-14 8:39
mvePIEBALDconsult14-Jul-14 8:39 
Questionhow to deploy on to a server Pin
Member 891454012-Mar-14 16:00
Member 891454012-Mar-14 16:00 
Questionmy vote of 5 Pin
Shambhoo kumar20-Feb-14 18:24
professionalShambhoo kumar20-Feb-14 18:24 
QuestionThis works for Visual Studio 2012 Express and SQL Server 2012, too Pin
freedom_nut26-Aug-13 14:40
freedom_nut26-Aug-13 14:40 
AnswerYou rock!!! Pin
Marcello Antonucci20-Dec-12 3:51
Marcello Antonucci20-Dec-12 3:51 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:49
professionalKanasz Robert24-Sep-12 5:49 
QuestionIt is working perfectily Pin
Lepa10-Sep-12 20:43
Lepa10-Sep-12 20:43 
GeneralMy vote of 5 Pin
AmitGajjar12-Aug-12 23:27
professionalAmitGajjar12-Aug-12 23:27 
i haven't tried it but article looks promising.
GeneralMy vote of 5 Pin
cjb11023-May-12 21:03
cjb11023-May-12 21:03 

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

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