使用 SQL Server 2008 通过 CLR 启用 (c# dll) 发送电子邮件有多智能？
我的问题是关于抽象软件工程师的观点：
假设我想在每次向表中插入行后发送一封电子邮件。
哪种方法会更聪明并且“按书本”？
- 创建一个将发送邮件的 C#/CLR 程序集
- 在 SQL Server 中启用 CLR
- 在插入后创建触发器，使用该程序集的功能在 C# 应用程序本身中发送邮件
或
- ，在插入到 DB 后 - 发送电子邮件。
My question is about abstract software engineer point of view :
Suppose I want to send an email after each insertion of row to a table.
Which approach will be smarter and "by the book" ?
- create a C#/CLR assembly which will send mail
- enable the CLR in SQL Server
- create trigger after insert, using that assembly's function to send mail
or
- in C# application program itself , after the insertion to DB - send the email.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用 Database Mail，它是 SQL Server 的内置电子邮件解决方案，如果您要从服务器发送电子邮件（而不是编写自己的解决方案）。
这至少可以确保邮件的传递相对于事务的其余部分是异步的，因此从触发器或存储过程中调用是安全的，如果电子邮件传递需要一段时间才能成功，则不会破坏其中任何一个。
至于现在，您是否应该使用它（从触发器或存储过程调用）或在应用程序中执行工作的决定，这取决于您更喜欢哪一个。无论哪种情况，您都需要考虑各种失败条件，而不仅仅是幸福的路径。如果 SQL 部分成功，然后应用程序在发送电子邮件之前崩溃（或通过任务管理器终止），那么失败会有多严重？您必须采取哪些缓解措施？
Use Database Mail, which is a built in emailing solution for SQL Server, if you're going to do the emailing from the server (rather than writing your own solution).
This, at least, ensures that delivery of mail is asynchronous with respect to the rest of your transaction, so would be safe to call from a trigger or stored proc, without breaking either of these if the email delivery takes a while to succeed.
As to, now, the decision of whether you should use this, called from a trigger or stored proc, or do the work in the application, it depends on which you're more comfortable with. In either case, you need to consider the various failure conditions rather than just the happy path. If the SQL portion succeeds, and then the application crashes (or is killed through task manager) before it sends the email, how serious a failure would that be? What mitigating steps would you have to put in place?
我同意 Damien 的观点，如果你真的想在数据库中进行操作，你应该使用数据库邮件。
我的建议是无论如何从您的 C# 应用程序中执行此操作，因为我认为从那里您可以更好地控制。
如果您在某个时刻一次性插入 100.000 条记录或在多个连续事务中插入怎么办？
从您的代码发送电子邮件（使用 .NET 中的 SMTP 邮件非常容易），您可以拥有满足您所有要求的方法，一些方法在一次插入后发送电子邮件，另一些方法在完成一堆插入后发送消息。
一般来说，我也有点反对触发器，我更喜欢单独保留数据库，除了存储过程不让它做太多事情；更容易调试和跟踪/跟踪/记录客户端（或服务器）.NET 应用程序中发生的情况。
这是我的两分钱，每个人都可以有不同的方法。
I agree with Damien that if you really want to do in inside the database you should rather use Database Mail.
My suggestion is to do it anyway from your C# application because I think from there you have way better control.
what if you at some point insert 100.000 records at once or in multiple consecutive transactions?
Doing the emailing from your code (it's very easy with SMTP Mails in .NET), you can have methods which suit all your requirements, some sending email after a single insert and some others sending the message after a bunch of inserts has been completed.
I am also, in general, a bit against triggers, I prefer to leave the database alone and except stored procs not making it to do too much; much easier to debug and follow/trace/log what happens in the client (or server) .NET application.
here were my two cents, everyone could have different approach.
需要考虑的一件事是电子邮件的目的和对象。如果您在发生异常或警告情况时发送一条小型维护消息，那么如果数据量不高，同步机制可能是最简单的解决方案。
但是，如果您需要邮寄高质量的报告（例如带有个性化 HTML 的 PDF），那么建议您将“要发送的邮件”的任务排队，例如在表格中，然后异步创建和发送电子邮件，例如使用 SSRS、Crystal等等。这还将带来更好的审计跟踪和更改渠道（例如打印而不是电子邮件）而不会破坏 SQL“排队”机制的能力的好处。
One thing to consider is for what and for whom the emails are intended. If you are sending a small maintenance message if an exception or warning condition occurs, then a synchronous mechanism might be the simplest solution if volumes aren't high.
But if you need to mail high quality reports (e.g. PDF with personalised HTML) then would recommend that you queue up the tasks for 'mails to be sent' e.g. in a table, and then create and despatch the emails asynchronously e.g. using SSRS, Crystal etc. This will also have benefits of a better audit trail and the ability to change channels (e.g. print instead of email) without breaking your SQL 'queueing' mechanism.