文档结构  
翻译进度:已翻译     翻译赏金:0 元 (?)    ¥ 我要打赏

介绍

电子邮件发送工具 Email Sender Utility 是一个 .NET 应用,该工具最好的诠释了使用 SQLDependency 技术,包括利用 XML 来做批量更新、发送邮件、检测待发邮件等功能。利用 SQL Server 的代理服务,利用 XML 做单个数据库的访问。这种方法让你的要你应用可以感知数据库的变化。当任何数据库记录满足 WHERE 查询条件就会触发事件 "void OnDependencyChange(object sender, SqlNotificationEventArgs e)"。我们可以利用 WHERE 语句和 SQLDependency 对象结合在一起实现。

第 1 段(可获 1.03 积分)

背景

很多开发者都在试图寻找一种推送模型来避免打开数据库连接,有些人会使用定时间进行定期的调用,而 SQL Dependency 则能很好的解决这个问题。

代码使用

首选需要启动 "SqlDependency" 对象(如下所示)。下面代码中的 connectionString 是目标数据库的连接的字符串。如果 SQL 代理服务没有启用的话,这个代码会抛出异常。

步骤#1- 启动 SqlDependency 

SqlDependency.Start(<TARGET_DB_CONNECTION_STRING>);

可以使用 T-SQL 查询来启用和禁用 SQL 代理服务。

启用代理服务:

第 2 段(可获 1.09 积分)
ALTER DATABASE [Database_name] SET ENABLE_BROKER;

如果 SQL Server 代理服务不启用,SQLDependency.start() 将会抛出异常,因此代理服务对 SQLDependency 的自动更改检测是必不可少的。

步骤# 2 初始化 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 段(可获 0.43 积分)

注意:上述查询有部分是存储过程和数据库模型的脚本,详情请参考 "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;
              }
第 4 段(可获 0.56 积分)

邮件实体示例类用于将属性数据从数据访问层传输 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; }
   
    }
第 5 段(可获 0.43 积分)

步骤 # 4  SMTP 配置用于发送邮件

smtpserver  : <邮件服务器的 SMTP 地址>
EmailUserName : <发送邮件的账号>
EmailPwd : <发送邮件的密码>
SenderEmailAddress : <发送邮件的地址>
SmtpServerPort :<SMTP 端口>
EmailSubject : <邮件主题>
EmailBody <邮件内容>

你需要在 [GeneralConfigurations] 表中更新上述配置,或者直接修改发送邮件代码中信息。

最后 : - 核心技术领域

使用 SqlDependency 来做更新检测,一旦我们将命令与 SqlDependency 进行关联,数据库使用代理服务,当自动检测到变更或者更新就会触发 OnDependencyChange 时间。 

第 6 段(可获 0.99 积分)

为了分离多个数据库操作,可以使用 ADO.NET 的 TransactionScope 分布式事务。

 using (TransactionScope scope = new TransactionScope())
                {

// 你在这个对象内对数据库操作是独立的,ado.net 关心这个,用于执行提交或者回滚。代码片段:

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

               }
第 7 段(可获 0.5 积分)

步骤 # 5 - 为了减少往返,避免多次打开数据连接,我们可以使用 XML 和 LINQ,使用 LINQ 生成 XML 并传递给存储过程,代码可参考 DBManager "spUpdateEmailSentStatusAndArchiveXML", 使用如下代码输出 XML。

LINQ 用来生成数据库操作的 XML ,代码如下:

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)

                         ));
第 8 段(可获 0.7 积分)

我们在 XML 中定义的 LINQ 查询

<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 代码片段如下,存放于文件夹 "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
第 9 段(可获 0.34 积分)

步骤 # 6 - 两种测试方法

1- 在 EmailNotificationHistory 表中增加待发记录,其值必须满足 Command 对象的 WHERE 条件,也就是说下列的查询必须能检索上某些记录:

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

2- 手工更新 EmailAddress , SentTime, PatientCode 所有或者某个一个表,但是 SentTime 必须不能为空,因为 WHERE 语句做了限制。如果在更新提交后满足查询条件,那么这个事件就会自动触发。

第 10 段(可获 0.99 积分)
void OnDependencyChange(object sender, SqlNotificationEventArgs e){

 // TODO

}

兴趣点

SQLDependency 用来做数据库更改的自动检测是非常有用的,开发者无需定时查询数据库来判断是否有记录更新, SQLDependency 使用命令检测并通过事件方式写回数据。

XML 可以帮我们一次性执行多个数据库操作,虽然我们也可以使用 DataTable 来实现相同的功能。

第 11 段(可获 0.78 积分)

文章评论