.NET SQL Dependency Auto Detecting changes using WHERE CLAUSE in command
Introduction
Email Sender Utility is .NET Small application with real implementation to depicts the better use of SQLDependency in combination with XML for bulk updates, to send emails , detecting candidate email[s] automatically using SQL Server Broker Service, using XML for single DB round trip, Its way you make your application ,aware of database changes. Like it fires the event "void OnDependencyChange(object sender, SqlNotificationEventArgs e)" if any record found which satisfy WHERE CLAUSE . We can associate command object using WHERE CLAUSE with SQLDependency object.
Background
Lot of developers are trying to find a way to implement push model to avoid opening DB connections, Some are using timer to call periodically, SQL Dependency solves this problem.
Using the code
First need to start "SqlDependency" object as show below. below connection String is connection string of target database. It will throw exception, if SQL Broker Service is not enabled,
STEP #1- Start SqlDependency from C# code.
SqlDependency.Start(<TARGET_DB_CONNECTION_STRING>);
How to enable / disable broker service, helping T-SQL queries , below can be used.
To enable Service Broker run:
ALTER DATABASE [Database_name] SET ENABLE_BROKER;
If SQL Server broker service is not enabled , SQLDependency.start() will throw exception, so Broker service is mandatory for SQLDependency auto change detection.
Step# 2- Initialize SQLDependency object. I will give you code walk through fir its initialize.
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();
Note: For above queries, there is part of Stored procedures and schema script. Refer "Database Schema" folder.
STEP # 3 - Prepare some sample data, It will add one candidate email from script. Reference "Data Scripts"
In Code , **smtpclient **library is used for sending email.
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;
}
Sample Email Entity class for attributed data transfer to UI and Data Access layer, also from Data Access layer same entity is used to generate XML for bulk Update, in single database round trip.
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; }
}
STEP # 4 - For email, you need SMTP configurations
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>
Need to update these settings, in table "[GeneralConfigurations]" OR comment line of code where sending email
Core Technical Areas
Using SqlDependency for detecting changes, once we associate our command with SqlDependency, database using broker service, automatically detect changes/ updates and fires OnDependencyChange event.
To make isolated multiple Database operations, using ADO.NET distributed transaction using TransactionScope.
using (TransactionScope scope = new TransactionScope())
{
// Your database operations 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
}
Step # 5 - To reduce round trips and avoid opening database connection multiple time, if required, we can use XML and LINQ, using LINQ make XML and pass to stored procedure, Code Reference Class DBManager "spUpdateEmailSentStatusAndArchiveXML", Sample XML as output is as given below.
LINQ is used to generate XML for Database operations., Code snippet to illustrate how to generate 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)
));
LINQ Query output
<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>
we can update all candidate records in round trip using XML. Sample TSQL code snippet as below. Folder "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
Step # 6 - How to perform testing, there are two approaches
1- Add one candidate row in table "EmailNotificationHistory" , its value must need to fulfill WHERE CLAUSE criteria in query associated with Command object. below query need to select some record.
SELECT [PatientCode] ,[EmailAddress] , SentTime , NotificationStatus FROM [dbo].[EmailNotificationHistory] where [SentTime] is null
2- You can update manually in table EmailAddress , SentTime,PatientCode all OR either, but "SentTime" should not be null because where clause will restrict it. If meets their criteria after update commit, This event will be automatically fired.
void OnDependencyChange(object sender, SqlNotificationEventArgs e){
// TODO
}
Conclusion -
SQLDependency is enough helpful for auto detecting changes from database, Developer need not to query periodically if there is some update for me, but SQLDependency using command detecting and updates back through event fire. XML comes to help us perform multiple record database operation in atomic. Although we can also use data table for the same purpose as alternative.