Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

SqlDependency .NET library can work to detect updates automatically

Rate me:
Please Sign up or sign in to vote.
4.63/5 (19 votes)
24 May 2017CPOL3 min read 45.8K   32   25   9
SqlDependency ,can be used to pick changes without executing query to see there is any update from database , WHERE CLAUSE can also be applied for narrowing scope, Its way to push changes from SQL Server to SQLDependency , .NET library , SQL Server Broker service sends updates to SQLDependency.

Introduction

Email Sender Utility is a .NET application that 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 assossciate 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 conenctions, 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 connectionString 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. here is how to initialize 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();

Note: For Above queries there is part of Stored procedures and schema script. Refer "Database Schema" Folder

STEP # 3 - Prepare some sample data, Sample one candidate email will be added 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 udpate these settings, in table "[GeneralConfigurations]" OR comment line of code where sending email

Finalization: Core Technical Areas

Using SqlDependency for detecting changes , once we assosciate 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 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

                }

Step # 5 - To reduce round trips and avoid openning 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 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)

                         ));

Out put of LINQ Query in 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>

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 fullfill 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 ristrict it. If meets their criteria after update commit, This event will be automatically fired.

void OnDependencyChange(object sender, SqlNotificationEventArgs e){

 // TODO

}

Points of Interest

SQLDependency is enough helpfull for auto detecing changes from database, Developer need not to query periodically if there is some update for me, but SQLDependency using command detecting and updates back to through event fire

XML comes to help us perform mutliple record database operation in atomic. Although we can also use datatable for the same purpose as alternative.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Advanced Programs Trd. Co. Ltd
Saudi Arabia Saudi Arabia
Motivated software Engineer in web/mobile application and windows development, architecture design, and multi-layer implementation. Well-versed in multiple programming languages and platforms. Demonstrated success in UI, integration, Service {REST, SOAP}. Highly skilled at assessing legacy code, identifying defects, promptly designing resolution strategies and performing corrective measures.

Comments and Discussions

 
QuestionDouble transactionScope ?? Pin
Herman<T>.Instance13-Jun-17 4:15
Herman<T>.Instance13-Jun-17 4:15 
AnswerRe: Double transactionScope ?? Pin
Zahid Nawaz Ch.28-Aug-17 4:26
professionalZahid Nawaz Ch.28-Aug-17 4:26 
GeneralRe: Double transactionScope ?? Pin
Herman<T>.Instance28-Aug-17 9:23
Herman<T>.Instance28-Aug-17 9:23 
GeneralRe: Double transactionScope ?? Pin
Zahid Nawaz Ch.28-Aug-17 19:57
professionalZahid Nawaz Ch.28-Aug-17 19:57 
GeneralMy vote of 5 Pin
Ehsan Sajjad10-Jun-17 9:38
professionalEhsan Sajjad10-Jun-17 9:38 
GeneralFurther Details Pin
martinrj3030-May-17 20:57
martinrj3030-May-17 20:57 
Hi Zahid, I too have been working in this area, here are some points to add to your article.

To setup the database there is a good article: Minimum Database Permissions Required for SqlDependency[^]

Which suggests to use specific logins to control access to 2 separate roles. 1 a Subscriber that watches the 'dependant table', and the other a User to act on the data.

Also, if you make a fatal mistake and have to undo, then cleaning up the DB is hard. Making a backup is important.

This script helped me cleanup:

/*

	-- KILL ANY CONNECTIONS
	
	-- * * * * * * run on both DB with broker enabled and broker to be enabled  * * * * *
	DECLARE @dbname sysname
	SET @dbname = 'DBNAME' --TODO change this to your DB name
	DECLARE @spid int
	SELECT spid from master.dbo.sysprocesses where dbid = db_id(@dbname)
	SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
	WHILE @spid IS NOT NULL
	BEGIN
	  EXECUTE  ('KILL ' + @spid)
	  SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
	END

*/
/*
--DELETE leftover SP's from badly closed SqlDependency ExE code:
declare @procName varchar(255)
declare cur cursor 
for select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 and name like '%SqlQueryNotificationStoredProcedure%'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
	print @procName
	print('drop procedure [dbo].[' + @procName + ']')
	fetch next from cur into @procName
end
close cur
deallocate cur

--check how many ServiceBroker SP's remain;
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 
  and name like '%SqlQueryNotificationStoredProcedure%'
*/



And to disable the Broker you have to put the DB in single user mode:

-- DISABLE BROKER
	use master
	alter database [DBNAME] 
	set single_user with rollback immediate;
	GO
	alter database [DBNAME] 
	set disable_broker
	go

	alter database [DBNAME] 
	set multi_user
	go


I have some nice C# delegate code to share if you'd like to use it in your article.
QuestionNice Articlet Pin
javedcodeproject4-May-17 3:53
javedcodeproject4-May-17 3:53 
AnswerRe: Nice Articlet Pin
Zahid Nawaz Ch.4-May-17 5:59
professionalZahid Nawaz Ch.4-May-17 5:59 
QuestionVery good article! Pin
Irfan Baig4-May-17 2:45
Irfan Baig4-May-17 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.