重新部署包含 CLR 存储过程的 .NET 程序集
我编写了一个位于程序集中的 CLR 存储过程。
我有一个构建系统，可以从我们的源代码控制存储库自动构建和部署 .net 应用程序。
我希望这两件事能够协同工作，以便我可以重新部署托管 CLR 存储过程的程序集。
然而，与 IIS 不同，简单地替换二进制文件似乎不起作用。看来您必须在数据库上删除组件。为了做到这一点，您需要删除引用该程序集的所有对象。
从某种意义上说，这似乎是合理的（即从数据库完整性的角度来看），而从另一种意义上来说，这似乎是不合理的——通常适用于 .NET 依赖关系的运行时评估的 JIT 方法。
那么，是否可以做一些事情，以便我可以替换二进制文件，然后给 SQL Server 一个踢，并使其弄清楚新程序集满足所有要求（即，具有正确的公共命名空间、类型、方法等来满足存储过程）与其绑定的）。
I have written a CLR stored procedure that is in an assembly.
I have a build system that can auto-build and deploy .net application from our source control repository.
I want the two things to work together so I can redeploy the assembly that hosts the CLR stored proc.
However it looks like, unlike IIS, simply replacing the binaries doesn't work. It seems like you have to DROP ASSEMBLY on the database. In order to do that you need to drop all the objects that reference that assembly.
That seems reasonable in one way -- i.e., from a database integrity point of view-- and unreasonable in another -- the JIT approach that applies for runtime evaluation of dependencies for .NET in general.
So, is it possible to do something so I can replace the binary then give SQL server a kick and make it figure out that the new assembly satisfies all the requirements (i.e., has the right public namespaces, types, methods etc to satisfy the sprocs that are bound to it).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
CLR 程序集存储在数据库中，而不是磁盘上，因此您不能简单地替换某些二进制 dll。要刷新它们，请使用
ALTER ASSEMBLY [程序集名称] FROM 'disklocation'。The CLR assemblies are stored in the database, not on disk, so you cannot simply replace some binary dll. To refresh them you use
ALTER ASSEMBLY [assemblyname] FROM 'disklocation'.简短的回答是“不，这样行不通”。正如 Remus 所指出的，SQL Server 将程序集存储在数据库中，而不是文件系统中的某个位置。因此，不存在服务器监控的地方以及您应该放置更新的二进制文件的地方。
将更新的程序集上传到数据库应该是部署过程中不可或缺的一部分。执行此操作的唯一方法是显式执行以下操作：
步骤 1 实际上可以在 T-SQL 中以通用方式实现方式（因此您不必显式列出对象）。但是 p.4 没有这样的方法，除了自定义工具（它将使用反射来发现程序集内容并生成适当的 T-SQL 来创建所有对象）。
Short answer is 'No, it will not work this way'. As it was pointed by Remus, SQL Server stores assemblies inside your database and not somewhere in a file system. Thus there's no such place that is monitored by the server and where you should place updated binaries.
Uploading an updated assembly(ies) to the database should be an integral part of your deployment process. And the only way of doing it to perform the following actions explicitly:
Step 1 can actually be implemented in T-SQL in a generic way (so you don't have to list objects explicitly). But there's not such way for p.4 except custom tool (which will use reflection in order to discover assembly content and generate appropriate T-SQL for creating all objects).
正如Remus的回答所述，您可以使用
ALTER ASSEMBLY ...来更新程序集。从 MSDN 页面 ALTER ASSEMBLY (Transact-SQL)对于 SQL Server 2008 R2 [强调我的]：
因此，如果引用程序集的函数、存储过程等没有更改，您可以简单地更新程序集。此外，这样做不会中断当前正在运行的会话；来自上述相同的 MSDN 页面：
但是，您可以相当轻松地自动重新部署程序集及其依赖对象，但通常要这样做，您需要删除并重新创建它。如果这样做，您可能会发现通过首先将程序集文件的字节转换为十六进制数字，然后将其包含在相关的
CREATE ASSEMBLY 声明。As Remus's answer states, you can use
ALTER ASSEMBLY ...to update an assembly.From the MSDN page ALTER ASSEMBLY (Transact-SQL) for SQL Server 2008 R2 [emphasis mine]:
So, if the functions, stored procedures, etc. that reference the assembly haven't changed, you can simply update the assembly. Also, doing so doesn't disrupt currently running sessions; from the same MSDN page as mentioned above:
However, you could fairly easily re-deploy an assembly and its dependent objects automatically, but to do so generally, you would need to drop and re-create it. If you do so, you may find it easier to deploy the assembly by 'embedding' it in a script by first converting the bytes of the assembly file to hexadecimal digits which can then be included in the relevant
CREATE ASSEMBLYstatement.除了最后一句之外，我同意 AlexS 的建议。
首先，反射不会真正起作用，因为 CLR 函数中使用的数据类型不一定决定 SQL 数据类型。例如，您可以在 CLR 端使用 SqlString，但在 SQL 端使用 NVARCHAR(50) 或 NVARCHAR(MAX) 而不是 NVARCHAR(4000)。
然而，仍然可以将其自动化。您应该使用源代码存储库来存储指向 CLR 代码的存储过程和函数定义，就像存储任何存储过程或函数一样。因此，您可以获取所有这些定义并运行所有 CREATE PROCEDURE 和 CREATE FUNCTION 语句作为步骤 4。
此外，步骤 1 和 2 可以是单个 SQL 脚本。
本质上，整个过程可以自动化:)。
I agree with what AlexS suggested except the last sentence.
First off, reflection will not truly work as the datatypes used in the CLR functions do not necessarily determine the SQL datatypes. For example, you could have SqlString on the CLR side but use NVARCHAR(50) or NVARCHAR(MAX) instead of NVARCHAR(4000) on the SQL side.
However, it is still possible to automate this. You should be using the source code repository to be storing the Stored Proc and Function definitions that point to the CLR code, just as you would any Stored Proc or Function. So you could grab all of those definitions and run all of the CREATE PROCEDURE and CREATE FUNCTION statements as Step 4.
Also, Steps 1 and 2 can be a single SQL script.
Essentially, this entire process can be automated :).