To deploy the assembly, we need to register it in the SQL server for a given database. The SqlProcedureAttribute mark the AddBook method as a stored procedure. Compile the project. This simple table has two columns: the book name and the author name. This stored procedure will insert a book in the Books table. Using (SqlConnection conn = new SqlConnection( " context connection=true"))Ĭommand.CommandText = INSERT INTO " bookName) Ĭ( " authorName) Public static void AddBook( string bookName, string authorName) Decorate this method with SqlProcedureAttribute. This can be done in the project properties under Application tab:Ĭreate a public static method in the StoredProcedures class and name it AddBook. 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. This class will hold our stored procedure (of course you can define more than one stored procedure in this class).īe sure to set the target framework for this project to. This will create a class library project with a default class named Class1. Select Class Library project and name it BooksMgr.
Let us fulfill these steps using Visual studio 2010 Express edition: Step 1: Create a class library project Create the stored procedure that references the registered assembly using CREATE PROCEDURE statement.Register the class library in SQL server using CREATE ASSEMBLY statement.
This method is decorated with SqlProcedureAttribute attribute. Define the stored procedure as a static method of a class.The CLR Stored Procedure creation involves the following steps: But the principle of creating a CLR Stored Procedure is very simple and we can accomplish it with the express edition.
Visual studio Express edition does not provide a project type (template) for creating CLR Stored Procedures as the professional and higher editions.