SQL Server CLR 用于嵌入业务逻辑并使用 SQL Server 代理安排执行
我在 C# 类库中有类似工作流的应用程序的业务逻辑，但在每个月末都需要调用库中的某些流程，我认为调用此计划执行的理想机制是使用 Sql 服务器代理（最好是sql作业，尽管如果绝对需要的话我愿意进入SSIS地狱），但问题是如何从sql作业调用应用程序库的进程？
应用程序库也从asp.net页面使用，因此它通常有一个带有配置的输入和输出数据库的Web应用程序上下文（web.config）
我知道这些步骤或多或少是
1）将类库嵌入到sql服务器中CLR 组装、强键签名并部署 2）调用库
我不清楚的是，当部署这样的CLR程序集时，Web应用程序上下文会发生什么？我该如何使用适当的 app.config 来部署 CLR？如果我需要更改连接字符串，是否必须重新部署 CLR？
我也不确定，因为 CLR 需要连接回调用它的数据库（用于读取和写入表的目的），但我猜测必须格外小心，以避免自调用之类的事情。
I have the business logic of an workflow-like application in a C# class library, but at end of each month certain process in the library needs to be invoked, i thought that the ideal mechanism to invoke this scheduled execution is using the Sql server agent (sql job preferably, althought i'm open to even go into SSIS hell if its absolutely required), but the question is how do i invoke the process of the application library from the sql job?
the application library is also used from asp.net pages, so it usually has a web application context (web.config) with configured input and output databases
I understand that the steps are more or less
1) embed the class library in a sql server CLR assembly, strong key sign and deploy it
2) call the library
the thing i am not clear is, when deploying such a CLR assembly, what happens with the web application context? how do i exactly go to deploy the CLR with the appropiate app.config? do i have to redeploy the CLR if i need to change the connection strings?
I am unsure also because the CLR needs to connect back to the database which is invoking it (for read and write table purposes) but i'm guessing one has to be extra cautious to avoid self-invoking and that sort of thing.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，这篇文章以“使用 SSIS”结束：-P
只要您的库包含 System.Web.dll 的链接，我认为您就无法将其在 SQL Server 中注册为 SQLCLR 程序集。 SQLCLR 汇编非常受限制，不能包含任何引用。
您可以将业务逻辑的前端特定部分移至单独的表示层库中。这通常是比混合业务逻辑和表示类（如 Web 应用程序上下文）更好的解决方案。之后，您可以尝试将程序集部署到 SQL Server。
现在您可以创建第二个 SQLCLR 库，其中包含编排业务对象的 .NET 存储过程。
但是，我在这里并没有真正看到 SQLCLR 的优势。这将需要对源代码进行巨大的更改，并对整个应用程序造成一些限制。
我的个人偏好是
要么使用 SSIS 包。在这里您可以引用任何其他 DLL 并使用其功能。
或者编写一个简单的 .NET 控制台应用程序，该应用程序将由 SQL 代理启动。
在这两种情况下，实现几乎是相同的。
SQLCLR 专门设计用于处理纯粹的数据库特定任务，这些任务会遇到可能的（和建议性的）T-SQL 功能的限制。
First, this post ends up in "use SSIS" :-P
As long as your library contains links to System.Web.dll I don't think that you will be able to register it in SQL Server as SQLCLR assembly. SQLCLR assembles are very restricted and cannot contain any reference.
You could move front-end specific parts of your business logic into a separate presentation layer library. This is generally a better solution than mixing business logic and presentation classes like a web application context. After that you can try to deploy your assembly to SQL Server.
Now you are able to create a second SQLCLR library, containing the .NET stored procedures that orchestrate your business objects.
However, I don't really see the advantage of SQLCLR, here. It would require huge changes in your source code and causes several restrictions for your whole application.
My personal preferences would be
Either use a SSIS package. Here you can refer any other DLL and consume its functionality.
Or write a simple .NET console application which will be started by SQL Agent.
In both cases, the implementation is almost equal.
SQLCLR is especially designed to handle purely database specific tasks that hit the wall of possible (and suggestive) T-SQL features.