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();

如何启用/禁用代理服务，可以帮助下面的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();
            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  : 
EmailUserName : 
EmailPwd : 
SenderEmailAddress : 
SmtpServerPort :
EmailSubject : 
EmailBody 

需要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中的输出

隐藏 复制代码

 



    10011

    [e-mail]

    false

  



    10012

    [e-mail]

    false

  

我们可以使用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]
    , 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不能为空，因为where子句将ristrict它。如果在更新提交后满足其条件，则会自动触发此事件。

隐藏 复制代码

void OnDependencyChange(object sender, SqlNotificationEventArgs e){

 // TODO

}

兴趣点

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

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