SQL Server 内的 CLR 汇编 C#

发布于 2024-11-15 15:27:54 字数 936 浏览 3 评论 0原文

是否可以用 C# 创建一个大项目（很多函数），然后
为其创建 CLR 程序集，然后
在 SQL Server 中的存储过程中调用程序集中的函数，
表（其中我会传递给 ASSEMBLY) 在其他存储过程中计算...
- 如果是的话，步骤是什么？

我在想这样的事情。

-- 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 选项在这里是最好的......

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

原文

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...
- If so, What would be the steps?

I am thinking something like this.

-- 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

------------------------------------------edit

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...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空袭的梦i 2024-11-22 15:27:54

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

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

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

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

    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 来让服务器准备好，并且您可能需要成为管理员。

    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 编写的系统上。这很糟糕！）

一些有用的参考：

http://msdn.microsoft.com/en-us/library/ms189524.aspx

http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx

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

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)
        {
        }
    }

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

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!)

Some useful references:

http://msdn.microsoft.com/en-us/library/ms189524.aspx

http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx

回复收藏 0 原文