如何在C#中使用SMO部署基于CLR的存储过程 [英] How to deploy CLR based Stored Procedures with SMO in C#

问题描述

我已经编写了一个C#CLR存储过程，我知道可以在Visual Studio 2008中进行部署，但是我需要知道如何在代码中进行部署，因为我想自动化创建数据库，表的整个过程，用户，内容以及UDF和存储过程.

到目前为止，所有文章都展示了如何部署基于常规TSQL的SP，但是没有一篇文章展示了如何使用SMO部署CLR C#SP.有人可以帮忙吗?

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?

推荐答案

创建T-SQL格式的字符串，例如:

Create a T-SQL format string, something like:

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

GO

然后，为该字符串提供Database.ExecuteNonQuery调用的参数，例如

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

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

为了简化部署，我将CLR程序集作为数据库管理实用程序的嵌入式资源(负责创建数据库，脚本表，设置权限等)包含在内.

将CLR程序集加载到数据库后，您仍然需要对各种存储的proc进行"CREATE PROCEDURE"调用.我似乎还记得从SSMS抓取到该T-SQL，但是用手工编写它们并不是很难.

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.

这篇关于如何在C#中使用SMO部署基于CLR的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！