SqlDependency .NET 库可用于自动检测更新
4.63/5 (19投票s)
SqlDependency 可用于在不执行查询的情况下捕获更改，以查看数据库是否有任何更新。WHERE CLAUSE 也可用于缩小范围。它通过 SQL Server 将更改推送到 SQLDependency .NET 库，SQL Server Broker 服务将更新发送给 SQLDependency。
引言
Email Sender Utility 是一个 .NET 应用程序，它展示了如何更好地结合使用 **SQLDependency** 和 XML 进行批量更新，用于发送电子邮件，通过 SQL Server Broker 服务自动检测候选电子邮件，并使用 XML 进行单次数据库往返。通过这种方式，您可以让您的应用程序感知数据库的变化。例如，如果找到任何满足 WHERE CLAUSE 条件的记录，它会触发事件 "void OnDependencyChange(object sender, SqlNotificationEventArgs e)"。我们可以使用带 WHERE CLAUSE 的命令对象与 SQLDependency 对象关联。
背景
许多开发人员正在尝试找到一种实现推送模型的方法，以避免打开数据库连接。有些人使用计时器定期调用。SQL Dependency 解决了这个问题。
使用代码
首先需要启动 "SqlDependency" 对象，如下所示。下面的 connectionString 是目标数据库的连接字符串。如果 SQL Broker 服务未启用，它将抛出异常，
步骤 #1 - 从 C# 代码启动 SqlDependency。
SqlDependency.Start(<TARGET_DB_CONNECTION_STRING>);
如何启用/禁用 Broker 服务，提供 T-SQL 查询帮助，可以使用以下方法。
要启用 Service Broker，请运行
ALTER DATABASE [Database_name] SET ENABLE_BROKER;
如果 SQL Server broker 服务未启用，SQLDependency.start() 将抛出异常，因此 Broker 服务对于 SQLDependency 自动更改检测是必需的。
步骤 #2 初始化 SQLDependency 对象。以下是如何初始化 sqldependency
SqlConnection SqlConnection =new SqlConnection(<TARGET_DB_CONNECTION_STRING>);
SqlConnection.Open();
SqlCommand command = new SqlCommand();
command.Connection = SqlConnection;
command.CommandType = CommandType.Text;
//command.CommandText = " SELECT [PatientCode] ,[EmailAddress] , SentTime FROM [dbo].[EmailNotificationHistory] where [SentTime] is null";
command.CommandText = " SELECT [PatientCode] ,[EmailAddress] , SentTime , NotificationStatus FROM [dbo].[EmailNotificationHistory] where [SentTime] is null ";
// Create a dependency and associate it with the SqlCommand.
//command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
// Maintain the refence in a class member.
// Subscribe to the SqlDependency event. , Its using sql server broker service. for this broker service must be enabled for this database.
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
// Get the messages
command.ExecuteReader();
注意：以上查询包含存储过程和架构脚本的部分。请参阅 "Database Schema" 文件夹
步骤 #3 - 准备一些示例数据，将从脚本中添加一个示例候选电子邮件。请参阅 "Data Scripts"
在代码中，smtpclient 库用于发送电子邮件。
string to = EmailEntity.RecipientEmailAddress;
string from = SmtpClientEntity.SenderEmailAddress;
MailMessage message = new MailMessage(from, to);
message.Subject = SmtpClientEntity.EmailSubject;
message.Body = EmailEntity.EmailBody;
System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient(SmtpClientEntity.SMTPAddress, Convert.ToInt16(SmtpClientEntity.Port));
System.Net.NetworkCredential basicCredential = new System.Net.NetworkCredential(SmtpClientEntity.UserName, SmtpClientEntity.Password);
// Credentials are necessary if the server requires the client
// to authenticate before it will send e-mail on the client's behalf.
client.UseDefaultCredentials = false;
client.Credentials = basicCredential;
// Still working on attachment
try
{
client.Send(message);
// txtStatus.Text = txtStatus.Text.Insert(0, "Email Sent to " + to + "\r\r");
EmailEntity.SentStatus = true;
}
catch (Exception ex)
{
AppendControlText(this.txtStatus, "Email sending process failed , Error" + ex.ToString() + " at " + DateTime.Now.ToString());
DbManager.LogFile(ex.Message, "SendAnEmail", this.FindForm().Text); // ((Control)sender).Name,
throw;
}
示例电子邮件实体类，用于将属性数据传输到 UI 和数据访问层，同样，数据访问层也使用相同的实体来生成 XML 进行批量更新，只需一次数据库往返。
class EmailEntity
{
public string CaseNumber { get; set; }
public string RecipientEmailAddress { get; set; }
public string PatientID { get; set; }
public string NotificationID { get; set; }
public string PatientName { get; set; }
public string PatientAge { get; set; }
public string EmailSubject { get; set; }
public string PatientStatus { get; set; }
public DateTime CaseDate { get; set; }
public object Attachment { get; set; }
public string EmailBody { get; set; }
public double Sender { get; set; }
public string PatientColorCode { get; set; }
public string Priority { get; set; }
public Boolean SentStatus { get; set; }
}
步骤 #4 对于电子邮件，您需要 SMTP 配置
smtpserver : <Mail Server SMTP address> EmailUserName : <Sender Email user> EmailPwd : <Sender Email password> SenderEmailAddress : <Sender Email address> SmtpServerPort :<SMTP Port> EmailSubject : <Email Subject> EmailBody <Email Body>
需要在表 "[GeneralConfigurations]" 中更新这些设置，或者在发送电子邮件的代码行中注释掉
最终确定：核心技术领域
使用 SqlDependency 检测更改，一旦我们将命令与 SqlDependency 关联，数据库将使用 Broker 服务自动检测更改/更新并触发 OnDependencyChange 事件。
为了进行隔离的多个数据库操作，使用 ADO.NET 分布式事务，通过 TransactionScope。
using (TransactionScope scope = new TransactionScope())
{
// Your database opearations within this object are isolated and ado.net cares for that, to make permanent/Commit or rollback. code snippet
using (TransactionScope scope = new TransactionScope())
{
// Load Candidate Emails from Database Table
EmailEntityList = DbManager.GetCandidateForEmail();
// Send Email One by one to all
foreach (EmailEntity EmailEntity in EmailEntityList)
{
if (SendAnEmail(EmailEntity))
{
AppendControlText(this.txtStatus,"Email Sent to " + EmailEntity.RecipientEmailAddress + " at " + DateTime.Now.ToString());
// NotifyingMsg.PropertyChanged
}
else {
AppendControlText(this.txtStatus, "Email sending process failed " + EmailEntity.RecipientEmailAddress + " at " + DateTime.Now.ToString());
// Console.WriteLine("Email sending process failed " + EmailEntity.RecipientEmailAddress + " at " + DateTime.Now.ToString()) ;
}
}
// If some emails are processed then need to update database
if (EmailEntityList != null && EmailEntityList.Count > 0) {
DbManager.UpdateEmailSentStatus(EmailEntityList);
}//using (var scope = new TransactionScope())
scope.Complete(); // To commit must need to call it, otherwise default will be rolled back
}
步骤 #5 - 为了减少往返并避免多次打开数据库连接，如果需要，我们可以使用 XML 和 LINQ。使用 LINQ 创建 XML 并传递给存储过程，代码参考类 DBManager "spUpdateEmailSentStatusAndArchiveXML"，下面的示例 XML 为输出。
LINQ 用于生成用于数据库操作的 XML。代码片段 i
var xEle = new XElement("EmailList",
from emp in EmailList
select new XElement("EmailList",
new XElement("NotificationID", emp.NotificationID),
new XElement("RecipientEmailAddress", emp.RecipientEmailAddress),
new XElement("SentStatus", emp.SentStatus)
));
LINQ 查询的 XML 输出
<EmailList>
<EmailList>
<NotificationID>10011</NotificationID>
<RecipientEmailAddress>xxxx@hot.com</RecipientEmailAddress>
<SentStatus>false</SentStatus>
</EmailList>
<EmailList>
<NotificationID>10012</NotificationID>
<RecipientEmailAddress>abc@hotmail.com</RecipientEmailAddress>
<SentStatus>false</SentStatus>
</EmailList>
</EmailList>
我们可以使用 XML 在一次往返中更新所有候选记录。下面的示例 T-SQL 代码片段。文件夹 "Stored Procedures"
ALTER PROC [dbo].[spUpdateEmailSentStatusAndArchiveXML](
@XML xml
)
AS
BEGIN
SET NOCOUNT ON
-- Place all value into variable table for next update
DECLARE @EmailNotificationUpdate TABLE
(
NotificationID [bigint],
RecipientEmailAddress nvarchar(50),
SentStatus [bit] default(0),
[NeedArchive] int null ,
[SentTime] datetime null
)
Insert into @EmailNotificationUpdate(NotificationID,RecipientEmailAddress,SentStatus, [SentTime])
SELECT Emails.Contact.value('(./NotificationID)[1]','bigint') NotificationID
, Emails.Contact.value('(./RecipientEmailAddress)[1]', 'nvarchar(50)') RecipientEmailAddress -->znawazch@gmail.com</RecipientEmailAddress>
, Emails.Contact.value('(./SentStatus)[1]', 'bit') SentStatus
,Getdate() [SentTime]
FROM @XML.nodes('/EmailList/EmailList') AS Emails(Contact)
-- Update Email Primary table for status and sent Time log
UPDATE ENH
SET
ENH.[SentTime] = Case when SentStatus = 1 then VENU.[SentTime] else ENH.[SentTime] end
,ENH.[NotificationStatus] = Case when SentStatus = 1 then 1 else ENH.[NotificationStatus] end
,ENH.[AuditActionCode] = Case when SentStatus = 1 then 3 else ENH.[AuditActionCode] end
,ENH.[IsActive] = Case when SentStatus = 1 then 0 else ENH.[IsActive] end
,ENH.TimesTryToSend = isnull(ENH.TimesTryToSend,0) +1
,[ModifiedOn] = getdate()
from [dbo].[EmailNotificationHistory] ENH inner join @EmailNotificationUpdate VENU on VENU.NotificationID = ENH.NotificationID
and ENH.EmailAddress = VENU.RecipientEmailAddress
where ENH.[SentTime] is null
END
步骤 #6 - 如何执行测试，有两种方法。
1- 在 "EmailNotificationHistory" 表中添加一行候选记录，其值必须满足与 Command 对象关联的查询中的 WHERE CLAUSE 条件。下面的查询需要选择一些记录。
SELECT [PatientCode] ,[EmailAddress] , SentTime , NotificationStatus FROM [dbo].[EmailNotificationHistory] where [SentTime] is null
2- 您可以手动更新 EmailAddress、SentTime、PatientCode 表中的所有或部分字段，但 SentTime 不能为 null，因为 WHERE 子句会限制它。如果更新提交后满足条件，此事件将自动触发。
void OnDependencyChange(object sender, SqlNotificationEventArgs e){
// TODO
}
关注点
SQLDependency 对于自动检测数据库更改非常有帮助。开发人员无需定期查询是否有更新，而是 SQLDependency 使用命令进行检测并将更新通过事件回传。
XML 有助于我们以原子方式执行多个记录的数据库操作。虽然我们也可以使用 DataTable 作为替代来实现相同的目的。