介绍

电子邮件发件人实用程序是一个.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查询。

启用Service 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>xxxx@hot.com</RecipientEmailAddress> <SentStatus>false</SentStatus> </EmailList> <EmailList> <NotificationID>10012</NotificationID> <RecipientEmailAddress>abc@hotmail.com</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 -->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不能为空，因为where子句将ristrict它。如果在更新提交后满足其条件，则会自动触发此事件。

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

兴趣点

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

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