如何在不使用 Visual Studio 的情况下生成 SQL CLR 存储过程安装脚本

Question

我正在使用 VS2010 开发 CLR 存储过程。我需要生成独立的部署脚本以在客户服务器上安装此过程。现在我正在使用 Visual Studio，当我按 F5 并尝试在数据库服务器上调试 SP 时，它会生成此类脚本。该脚本位于 bin\Debug\MyStoredProcedure.sql 文件中。它看起来像这样：

USE [$(DatabaseName)]

GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping [dbo].[spMyStoredProcedure]...';


GO
DROP PROCEDURE [dbo].[spMyStoredProcedure];


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Dropping [MyStoredProcedure]...';


GO
DROP ASSEMBLY [MyStoredProcedure];


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Creating [MyStoredProcedure]...';


GO
CREATE ASSEMBLY [MyStoredProcedure]
    AUTHORIZATION [dbo]
-- here should be long hex string with assembly binary
    FROM 0x4D5A90000300000004000000FFFCD21546869732070726F6772616D...000000000000000000 
    WITH PERMISSION_SET = SAFE;


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Creating [dbo].[spMyStoredProcedure]...';


GO
CREATE PROCEDURE [dbo].[spMyStoredProcedure]
@reference UNIQUEIDENTIFIER, @results INT OUTPUT, @errormessage NVARCHAR (4000) OUTPUT
AS EXTERNAL NAME [MyStoredProcedure].[MyCompany.MyProduct.MyStoredProcedureClass].[MyStoredProcedureMethod]


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'The transacted portion of the database update succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT N'The transacted portion of the database update failed.'
GO
DROP TABLE #tmpErrors
GO

我想知道，是否可以在没有 Visual Studio 的情况下生成这样的脚本？例如，如果我使用 MSBuild 构建解决方案，然后使用某些工具生成此脚本会怎样？我相信，如果我将程序集读取为字节数组，然后将其序列化为十六进制字符串并插入到脚本模板中 - 它可以工作，但也许有一些更简单的标准解决方案？

谢谢。

Answer 1

或者，假设您已将程序集直接从 Visual Studio 部署到某个测试 SQL 服务器；通过右键单击 SSMS（管理工作室）中的程序集来创建独立部署脚本，然后选择：

将汇编脚本编写为 ->创建到...

这将为您在 SQL 脚本中写入代表 DLL 的十六进制字符串，您可以将其用于单个文件部署。

Answer 2

好吧，似乎唯一的方法是将程序集作为二进制文件读取，然后使用上面的模板生成脚本。类似的事情：

            using (var str = File.OpenRead(pathToAssembly))
            {
                int count = 0;
                do
                {
                    var buffer = new byte[1024];
                    count = str.Read(buffer, 0, 1024);

                    for (int i = 0; i < count; i++)
                    {
                        hexStringBuilder.Append((buffer[i] >> 4).ToString("X"));
                        hexStringBuilder.Append((buffer[i] & 0xF).ToString("X"));
                    }
                } while (count > 0);
            }
            // generate script using template from initial question

我已经检查过这种方法并且它有效。

Answer 3

您所描述的内容应该可以正常工作，但正如 部署 CLR 数据库对象 所描述的那样仅仅引用编译后的 Dll 似乎更容易。

使用以下方式部署程序集
Transact-SQLTransact-SQL

从源代码编译程序集
使用命令行编译器的文件
包含在 .NET Framework 中。

对于 Microsoft Visual C# 源文件：

csc /target:library C:\helloworld.cs

对于 Microsoft Visual Basic 源
文件：

vbc /target:库 C:\helloworld.vb

这些命令启动 Visual C# 或
Visual Basic 编译器使用
/target 选项指定构建
库 DLL。

解决所有构建错误和警告
在将程序集部署到之前
测试服务器。

打开 SQL Server Management Studio
测试服务器。创建一个新查询，
连接到合适的测试数据库
（例如 AdventureWorks2008R2）。

通过以下方式在服务器中创建程序集
将以下 Transact-SQL 添加到
查询。

从以下位置创建程序集 HelloWorld
'c:\helloworld.dll' 带有
PERMISSION_SET = 安全

过程、函数、聚合、
用户定义的类型，或触发器必须
然后在SQL实例中创建
服务器。如果HelloWorld程序集
包含一个名为 HelloWorld 的方法
程序类，如下
可以将 Transact-SQL 添加到查询中
创建一个名为 hello 的过程
SQL 服务器。

创建过程你好

AS

外部名称
HelloWorld.Procedures.HelloWorld

Answer 4

阅读 这篇帖子，而不是使用/Action:Publish使用/Action:Script /OutputPath:D:\deploymentScript.sql

Answer 5

您可以从 sys. assembly_files 获取程序集二进制文件。也许你可以用它做点什么。

select *
from sys.assembly_files