SqlDependency .NET库可以自动检测更新

SqlDependency可用于选择更改而不执行查询，以查看是否存在来自数据库的更新，WHERE CLAUSE也可用于缩小范围，将其从SQL Server推送到SQLDependency的方式，.NET库，SQL Server Broker服务发送更新到SQLDependency。

介绍

电子邮件发件人实用程序是一个.NET应用程序，描述了更好地使用SQLDependency与XML结合批量更新，使用SQL Server代理服务自动发送电子邮件，自动检测候选电子邮件，使用XML进行单个DB往返，其方式您使您的应用程序，了解数据库更改。如果发现符合“WHERE CLAUSE”的记录，则会触发事件“ ”。我们可以使用带有SQLDependency对象的WHERE CLAUSE来组织命令对象。void OnDependencyChange(object sender, SqlNotificationEventArgs e)

背景

许多开发人员试图找到一种实现推模型的方法，以避免开启DB的操作，有些使用定时器定期调用，SQL依赖解决了这个问题。

使用代码

首先需要启动“ SqlDependency”对象，如下所示。以下connectionString是目标数据库的连接字符串。它会抛出异常，如果没有启用SQL代理服务，

STEP＃1-从C＃代码启动SqlDependency。

隐藏   复制代码

SqlDependency.Start(<TARGET_DB_CONNECTION_STRING>);

如何启用/禁用代理服务，可以帮助下面的T-SQL查询。

启用S​​ervice Broker运行：

隐藏   复制代码

ALTER DATABASE [Database_name] SET ENABLE_BROKER;

如果SQL Server代理服务未启用，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();

注意：对于上述查询，存在存储过程和模式脚本的一部分。参考“数据库架构”文件夹

步骤3 - 准备一些样本数据，将从脚本中添加示例一个候选电子邮件。参考“数据脚本”

在代码中，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>

需要udpate这些设置，在表“[ GeneralConfigurations]”或注释行的代码发送电子邮件

最终确定：核心技术领域

使用SqlDependency检测更改，一旦我们使用SqlDependency协调我们的命令，使用代理服务的数据库，自动检测更改/更新和触发OnDependencyChange事件。

要进行隔离多个数据库操作，请使用TransactionScope使用ADO.NET分布式事务。

隐藏   收缩    复制代码

 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 make 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>[e-mail]</RecipientEmailAddress>    <SentStatus>false</SentStatus>  </EmailList><EmailList>    <NotificationID>10012</NotificationID>    <RecipientEmailAddress>[e-mail]</RecipientEmailAddress>    <SentStatus>false</SentStatus>  </EmailList></EmailList>

我们可以使用XML更新往返中的所有候选记录。样本TSQL代码片段如下。文件夹“存储过程”

隐藏   收缩    复制代码

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 -->[e-mail]</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 logUPDATE 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 nullEND

步骤6 - 如何执行测试，有两种方法。

1 - 在表“ EmailNotificationHistory”中添加一个候选行，其值必须在与Command对象关联的查询中填满WHERE CLAUSE条件。以下查询需要选择一些记录。

隐藏   复制代码

SELECT [PatientCode] ,[EmailAddress] , SentTime , NotificationStatus FROM [dbo].[EmailNotificationHistory] where  [SentTime] is null

2 -您可以手动更新表EmailAddress，SentTime，PatientCode全部或任，但SentTime不能为空，因为where子句将ristrict它。如果在更新提交后满足其条件，则会自动触发此事件。

隐藏   复制代码

void OnDependencyChange(object sender, SqlNotificationEventArgs e){ // TODO}

兴趣点

SQLDependency足够有助于自动检测数据库的变化，开发人员不需要定期查询是否有一些更新，但SQLDependency使用命令检测和更新返回到事件触发

XML来帮助我们以原子方式执行多个记录数据库操作。虽然我们也可以使用datatable作为替代的相同目的