SQL Server 中的 CLR 程序集 C# [英] CLR Assembly C# inside SQL Server
问题描述
- 那么,是否可以用 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!)
一些有用的参考:
http://msdn.microsoft.com/en-us/library/ms189524.aspx
http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx
这篇关于SQL Server 中的 CLR 程序集 C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!