Create CLR Stored Procedure with Visual Studio Express Edition

• Download BooksMgr project - 7.9 KB

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

-- Create the assembly 'BooksMgr'
CREATE ASSEMBLY BooksMgr 
FROM 'F:\Dev\Projects\BooksMgr\BooksMgr\bin\Debug\BooksMgr.dll'
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 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

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. 

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.

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   

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

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