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

Create CLR Stored Procedure with Visual Studio Express Edition

, 23 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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 

 

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:

 

 

Step2: Define the stored procedure 

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

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:

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


 

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: 

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

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

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.  

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

 

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.

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: 
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…. 

 

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

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

 

We can see the inserted book in Books table:  

 

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)

Share

About the Author

Manar Ezzadeen
Architect
France France
Software Architect
 
Twitter:
@ManarEzzadeen
Follow on   Twitter

Comments and Discussions

 
GeneralExcellent! Pinmemberlpitadmin29-Oct-14 22:55 
QuestionDebug ? PinmemberVIctorPerez291114-Jul-14 9:24 
AnswerRe: Debug ? PinmemberPIEBALDconsult14-Jul-14 9:39 
Questionhow to deploy on to a server PinmemberMember 891454012-Mar-14 17:00 
Questionmy vote of 5 PinprofessionalShambhoo kumar20-Feb-14 19:24 
QuestionThis works for Visual Studio 2012 Express and SQL Server 2012, too Pinmemberfreedom_nut26-Aug-13 15:40 
AnswerYou rock!!! PinmemberMarcello Antonucci20-Dec-12 4:51 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 6:49 
QuestionIt is working perfectily PinmemberLepa10-Sep-12 21:43 
GeneralMy vote of 5 Pinmember@amitgajjar13-Aug-12 0:27 
GeneralMy vote of 5 Pinmembercjb11023-May-12 22:03 

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 | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 23 May 2012
Article Copyright 2012 by Manar Ezzadeen
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid