SQL Server 中的 CLR 程序集 C#

本文介绍了SQL Server 中的 CLR 程序集 C#的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

• 那么，是否可以用 C# 制作一个大项目(很多功能)，
• 为它创建 CLR 程序集，然后，
• 在 SQL Server IN A STORED PROC 中，调用程序集中的函数，

• 该表(我将传递给 ASSEMBLY)在其他存储过程中计算...

• Is it possible to make a big project in C# (lots of functions), then,
• Create CLR Assembly for it, then,
• In SQL Server IN A STORED PROC, call a function that is in the assembly,

• The table (which I would pass to ASSEMBLY) is computed in other stored proc...

• 如果是，步骤是什么?

我在想这样的事情.

-- I have a stored proc that gets a table, let it be myStoredProcTable --FIST ENABLE DATA ACCESS EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE --main.sql calls yStoredProcTable.sql and the calls functionAssembly SELECT * INTO #tmpTable FROM OPENQUERY(SERVERNAME, 'EXEC test.dbo.myStoredProcTable 1') -- pass the table to assembly -- how would i pass the table to assembly code?, Is this POSSIBLE? EXEC functionAssembly #tmpTable

------------------------------------------编辑

按照 @faester 回答:- 我怎么能在代码中使用 XML，我建议使用 numberTOstring 东西，但我猜 XML 选项是最好的......

Following @faester answer: - How could I use XML in the code, I suggested to use the numberTOstring thing, but I guess XML option is the best here...

再说一次，我真的这样做了，即使这不是最好的选择...

Again, I really do this, even if is not the best choice...

推荐答案

是的，您可以注册程序集，但由于性能问题，这很少是一个好主意.

Yes you can register assemblies, but it is rarely a good idea due to performance issues.

但是如果您对标量值进行复杂的数值计算或类似操作，它可以为您提供很大的灵活性.但问题仍然是 SQL 本身是面向设置的，而 C# 则不是，因此您很容易遇到不匹配的情况.

But if you make complex numeric calculations or similar operations on scalar values it can give you a lot of flexibility. But the problem remains that SQL is natively set oriented which C# isn't, so you will easily run into mismatches.

您还应该注意，您只能在静态类上导入静态成员.

You should also be aware that you can only import static members on static classes.

举个例子这个类 - 故意没有命名空间，因为在命名空间中导入类似乎是不可能的.

But an example This class - which intentionally doesn't have a namespace since it seems to be impossible to import classes in a namespace.

public static class Math { [Microsoft.SqlServer.Server.SqlFunction] public static int Add(int a, int b) { return a + b; } [Microsoft.SqlServer.Server.SqlProcedure] public static void Void(int a, int b) { } }

需要一些 SQL 才能使服务器准备就绪，您可能需要成为管理员.

It takes some SQL to get the server ready and you probably need to be admin.

EXEC SP_CONFIGURE 'clr enabled', 1 GO RECONFIGURE GO -- CONSIDER: DROP ASSEMBLY SqlClr GO CREATE ASSEMBLY SqlClr FROM 'pathtoassembly' WITH PERMISSION_SET = SAFE; GO SELECT * FROM sys.assemblies GO CREATE FUNCTION [MathAdd] (@a int, @b int) RETURNS INT AS EXTERNAL NAME [SqlClr].Math.[Add] GO CREATE PROCEDURE [Void] @a INT, @b INT AS EXTERNAL NAME [SqlClr].Math.[Void] GO SELECT dbo.MathAdd (1, 2) EXEC void 1, 2

再次:你真的应该确信你需要这个，这很少是一个好主意！(我曾经将它用于电子邮件验证、dns 查找等，但那是在所有业务逻辑都是用 SQL 编写的系统上使用的.这很糟糕！)

AGAIN: You really should be confident that you need this, it is rarely a good idea! (I have used it once for email validation making dns lookups etc, but that was on a system where all business logics was written in SQL. And that is bad!)

一些有用的参考:

msdn.microsoft/en-us/library/ms189524.aspx

www.codeproject/KB/database/CLR_in_Sql_Server_2005.aspx