CLR大会C＃SQL Server内部 [英] CLR Assembly C# inside SQL Server
问题描述
- 是否有可能使在C＃中的一个大项目（很多功能），那么，
- 为它创建CLR大会，那么，
- 在SQL Server中创建存储过程，调用一个函数，在装配时，
-
（我会通过组装）的表计算在其他存储过程...
- 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 /库/ ms189524.aspx
的 http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx
这篇关于CLR大会C＃SQL Server内部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
