Click here to Skip to main content
15,885,216 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have written a C# CLR stored procedure and i know that i can deploy it with Visual Studio 2008 but i need to know how to to deploy it in code since i would like to automate the entire process of creating the database, tables, users, content and the UDF and stored procedures.

So far all the articles show how to deploy a normal TSQL based SP but none of them have shown how to deploy the CLR C# SP using SMO. Can anyone help on this?
Posted

1 solution

Create a T-SQL format string, something like:

CREATE ASSEMBLY {0}
AUTHORIZATION dbo
FROM 0x{1}
WITH PERMISSION_SET = SAFE

GO


Then, supply this string with arguments to a Database.ExecuteNonQuery call, e.g.

database.ExecuteNonQuery(string.Format(
   installClrAssemblyFormatString, 
   clrAssemblyName, 
   hexFormattedClrAssemblyContents));


To make deployment easier, I included the CLR assemblies as embedded resources of my database management utility (the one responsible for creating the dataase, scripting tables, setting up permissions, etc).

Once the CLR assemblies have been loaded to the database, you still need to have "CREATE PROCEDURE" calls for the various stored procs. I seem to remember snagging this T-SQL from SSMS, but they're not terribly difficult to code by hand.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900