Page 1 of 1

Using SqlDependency To Monitor SQL Database Changes Rate Topic: ***** 2 Votes

#1 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Posted 09 August 2010 - 07:05 AM

In this tutorial, I will use the SqlDependency class and Query notifications to monitor SQL Server 2005 database data changes. Query Notifications allow an application to be notified when data has changed in the database.

The purpose of this class is to save you from having to continuously re-query the database to get new data. You would have probably done this by setting up a timer that executes every X amount of seconds so that your display control would be displaying the most up-to-date information. You will no longer have to do this.

We will be using a Service Broker and a QUEUE in SQL Server 2005. These were new additions to SQL Server 2005. I will assume these are also in SQL Server 2008, but can't guarantee.

My example will be doing something very simple. I have a "Users" table in my database with two fields: FirstName and LastName. I am simply displaying these in a ListBox on one form. On a second form, I have textboxes to insert the data into the database.

So first, we need to create the Queue and the Service broker and assign the privileges to the SQL user.

USE YourDatabaseName;

CREATE QUEUE NameChangeQueue;
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;


You can now see that we have a new queue and a new service.
Attached Image

Now we move on to the code. Add this Imports statement to the top.

Imports System.Data.SqlClient



The first thing you will need to do is to test if the connecting user has the privileges for the query notifications.

    Private Function DoesUserHavePermission() As Boolean

        Try
            Dim clientPermission As SqlClientPermission = New SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)

            ' this will throw an error if the user does not have the permissions
            clientPermission.Demand()

            Return True

        Catch ex As Exception

            Return False

        End Try

        Return True

    End Function



Next, we have our method to get the user names from the database.

Public Sub GetNames()
        If Not DoesUserHavePermission() Then
            Return
        End If

        lbNames.Items.Clear()

        ' You must stop the dependency before starting a new one.
        ' You must start the dependency when creating a new one.
        SqlDependency.Stop(connectionString)
        SqlDependency.Start(connectionString)

        Using cn As SqlConnection = New SqlConnection(connectionString)

            Using cmd As SqlCommand = cn.CreateCommand()

                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]"

                cmd.Notification = Nothing

                ' creates a new dependency for the SqlCommand
                Dim dep As SqlDependency = New SqlDependency(cmd)
                ' creates an event handler for the notification of data changes in the database
                AddHandler dep.onchange, AddressOf dep_onchange

                cn.Open()

                Using dr As SqlDataReader = cmd.ExecuteReader()

                    While dr.Read()

                        lbNames.Items.Add(dr.GetString(0) & " " & dr.GetString(1))

                    End While

                End Using

            End Using

        End Using
    End Sub



THIS IS VERY IMPORTANT.

1. In the previous code, you will notice that my SQL query does not use the "*" wildcard to return all columns. You MUST return the exact columns that you want. If you use the "*", you will have bad consequences.

2. Also in the previous code, you will notice that my SQL query contains the "two-part" table name. This is also REQUIRED. Using just "TableName" instead of "owner.TableName" will also cause unwanted consequences.


Here is the method for the onchange event

    Private Sub dep_onchange(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)

        ' this event is run asynchronously so you will need to invoke to run on the UI thread(if required)
        If Me.InvokeRequired Then

            lbNames.BeginInvoke(New MethodInvoker(AddressOf GetNames))

        Else

            GetNames()

        End If

        ' this will remove the event handler since the dependency is only for a single notification
        Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
        RemoveHandler dep.onchange, AddressOf dep_onchange

    End Sub



You will also need to stop the dependency when the form closes so that it doesn't leave it running.

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

        SqlDependency.Stop(connectionString)

    End Sub



The other events..

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        GetNames()

    End Sub

    Private Sub btnShowForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowForm.Click

        Dim f As Form2 = New Form2
        f.Show()

    End Sub



And my simple second form's code..

Imports System.Data.SqlClient

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Using cn As SqlConnection = New SqlConnection(connectionString)

            Using cmd As SqlCommand = cn.CreateCommand()

                cmd.CommandText = String.Format("INSERT INTO Users VALUES ('{0}', '{1}')", txtFirstName.Text, txtLastName.Text)
                cmd.CommandType = CommandType.Text

                cn.Open()

                cmd.ExecuteNonQuery()

            End Using

        End Using
    End Sub



And that is really all you have to do. Here are a couple of screenshots.

Before clicking "Save"...

Attached Image

After clicking "Save"...

Attached Image

This will work for SQL Server 2005 databases running on the local PC or on a server. This also works from multiple PCs. You can distribute this little app to multiple people and have them insert names, and you can sit back and watch each one that is entered.

To me, this is one of the best functionalities that I have come across. It is amazing how easy it is to get it running. I plan on starting to use it very soon.

Is This A Good Question/Topic? 3
  • +

Replies To: Using SqlDependency To Monitor SQL Database Changes

#2 IBelongToYou  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 13-December 09

Posted 17 September 2010 - 05:04 AM

Thanks Yaarr..........
Was This Post Helpful? 0
  • +
  • -

#3 Guest_Victor Santos*


Reputation:

Posted 05 November 2010 - 06:15 PM

Can you please publish the solution to download it?
Was This Post Helpful? 0

#4 Guest_Adriano*


Reputation:

Posted 15 January 2011 - 07:38 AM

I tried this sample, it works great in windows forms,
when I tried the same inside windows service, it doesn't listen for new records,
any ideas why???

thanks a lot,

Adriano
Was This Post Helpful? 0

#5 chrisw  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 03-February 12

Posted 03 February 2012 - 01:13 PM

I also was able to get this working, but one thing I noticed is that the listbox keeps flickering.
When I move the mouse around on either form it looks like the list box keeps refreshing itself, but if i stop moving the mouse on the form, the listbox stops flickering. Is it suppose to do that or is it because there isnt very many entries that I put in the list box. I only tested with 3 entries.

I am running windows 7 enterprise, using visual studio 2008 pro., my database is on a seperate box running sql 2008 r2.

View PostAdriano, on 15 January 2011 - 07:38 AM, said:

I tried this sample, it works great in windows forms,
when I tried the same inside windows service, it doesn't listen for new records,
any ideas why???

thanks a lot,

Adriano

Was This Post Helpful? 0
  • +
  • -

#6 prajkta.bedarkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 13-February 12

Posted 13 February 2012 - 11:58 PM

Hi Team,

Thanks for such a wonderful article dude !!

Just, I do not understand why we create a queue and service.
I mean if we did similar coding for DB2 database where queue and service are not there, will the code work?

-- Prajkta Bedarkar
Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Posted 14 February 2012 - 05:59 AM

View Postprajkta.bedarkar, on 14 February 2012 - 02:58 AM, said:

Hi Team,

Thanks for such a wonderful article dude !!

Just, I do not understand why we create a queue and service.
I mean if we did similar coding for DB2 database where queue and service are not there, will the code work?

-- Prajkta Bedarkar


I don't know DB2, so I can't answer that question.
Was This Post Helpful? 0
  • +
  • -

#8 premprakash.gsp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-June 12

Posted 06 June 2012 - 07:12 AM

Hi ,
I am not able to use this code for windows service bez its not triggering the onchange Event . do you have any solutions for this issues.

Regards,
PremPrakash.S

View Postprajkta.bedarkar, on 13 February 2012 - 11:58 PM, said:

Hi Team,

Thanks for such a wonderful article dude !!

Just, I do not understand why we create a queue and service.
I mean if we did similar coding for DB2 database where queue and service are not there, will the code work?

-- Prajkta Bedarkar



Did u Find any solutions for this issues . Thanks in advance if i got the solution .... :)
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9199
  • View blog
  • Posts: 34,567
  • Joined: 12-June 08

Posted 06 June 2012 - 07:13 AM

Quote

I am not able to use this code for windows service bez its not triggering the onchange Event . do you have any solutions for this issues.

Did you add the reference to the event handler and the method?
Was This Post Helpful? 0
  • +
  • -

#10 premprakash.gsp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-June 12

Posted 06 June 2012 - 07:17 AM

View Postmodi123_1, on 06 June 2012 - 07:13 AM, said:

Quote

I am not able to use this code for windows service bez its not triggering the onchange Event . do you have any solutions for this issues.

Did you add the reference to the event handler and the method?


Yes i added reference FYI i have added my code. Please a quick look on it

  protected override void onstart(string[] args)
    {
        try
        {
           Trace.WriteLine(DateTime.Now.ToString() + " Cache Service Started");
           var serviceType = typeof(Service);
           var uri = new Uri("http://localhost:3399/");
           host = new ServiceHost(serviceType, new[] { uri });
           var behaviour = new ServiceMetadatabehavior() { HttpGetEnabled = true };
           host.Description.behaviors.Add(behaviour);
           host.AddServiceEndpoint(serviceType, new BasicHttpBinding(), "Hello");
           host.AddServiceEndpoint(typeof(IMetadataExchange), new BasicHttpBinding(), "mex");
           host.Open();           
            SQLNotification.strHello = "PremPrakash from windows service";            
            //SQLNotification.dtMetro = SetMetrosCache();
            SQLNotification.dtobject = SetMetrosCacheobject();
            DataTable dtt = (DataTable)SQLNotification.dtobject;
            dtt.TableName = "Metro";
            Trace.WriteLine(DateTime.Now.ToString() +"Record count"+ SQLNotification.dtMetro.Rows.Count ); 
            Trace.WriteLine(DateTime.Now.ToString() + " Cache Service Started and completed");

        }
        catch (Exception ex)
        {
            Trace.WriteLine(DateTime.Now.ToString() + " Service start issue "+ ex.ToString());
        }
    }

    protected override void onstop()
    {
        Trace.WriteLine(DateTime.Now.ToString() + "Changes Occured Metro");
        SqlDependency.Stop(SQLNotification.mStarterConnectionString);
        host.Close();
    }
    private bool DoesUserHavePermission()
    {
        try
        {
            SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);

            // will throw an error if user does not have permissions
            clientPermission.Demand();
            Trace.WriteLine(DateTime.Now.ToString() + " DoesUserHavePermission access grant");
            return true;
        }
        catch(Exception ex)
        {
            Trace.WriteLine(DateTime.Now.ToString() + " DoesUserHavePermission Error" +ex.ToString());
            return false;
        }
    }

    private DataTable SetMetrosCache()
    {
        DataTable DtRet = new DataTable();
        try
        {
            if (DoesUserHavePermission())
            {
                SQLNotification.dtMetro.Rows.Clear();
                Trace.Write(DateTime.Now.ToString() + "Start SetMetrosCache");
                SqlDependency.Stop(SQLNotification.mStarterConnectionString);
                SqlDependency.Start(SQLNotification.mStarterConnectionString);
                using (SqlConnection cn = new SqlConnection(SQLNotification.mStarterConnectionString))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        //cmd.CommandText = "SELECT [MetroCD],[MetroName],[CountryCD],[StateName],[LastUpdUID],[LastUpdTS],[IsDeleted] FROM [Metro]";
                        cmd.CommandText = "SELECT Message, Person_ID from Message";
                        cmd.Notification = null;
                        SqlDependency dep = new SqlDependency(cmd);
                        dep.onchange += new onchangeEventHandler(dep_onchange);
                        cn.Open();
                        DtRet.Load(cmd.ExecuteReader());
                    }
                }
                Trace.WriteLine(DateTime.Now.ToString() + "End SetMetrosCache");
            }
        }
        catch (Exception Ex)
        {
            Trace.WriteLine(DateTime.Now.ToString() + "Error Occured SetMetrosCache()" + Ex.ToString() + " " + Ex.Message);

        }

        return DtRet;
    }


    private object SetMetrosCacheobject()
    {
        DataTable DtRet = new DataTable();
        object Dobject = new object();
        try
        {
            if (DoesUserHavePermission())
            {
                SQLNotification.dtMetro.Rows.Clear();
                Trace.Write(DateTime.Now.ToString() + "Start SetMetrosCache");
                SqlDependency.Stop(SQLNotification.mStarterConnectionString);
                SqlDependency.Start(SQLNotification.mStarterConnectionString);
                using (SqlConnection cn = new SqlConnection(SQLNotification.mStarterConnectionString))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        //cmd.CommandText = "SELECT [MetroCD],[MetroName],[CountryCD],[StateName],[LastUpdUID],[LastUpdTS],[IsDeleted] FROM [Metro]";
                        cmd.CommandText = "SELECT Message, Person_ID from Message";
                        cmd.Notification = null;
                        SqlDependency dep = new SqlDependency(cmd);
                        dep.onchange += new onchangeEventHandler(dep_onchange);
                        cn.Open();
                        DtRet.Load(cmd.ExecuteReader());
                        Dobject = DtRet;
                    }
                }
                Trace.WriteLine(DateTime.Now.ToString() + "End SetMetrosCache");
            }
        }
        catch (Exception Ex)
        {
            Trace.WriteLine(DateTime.Now.ToString() + "Error Occured SetMetrosCache()" + Ex.ToString() + " " + Ex.Message);

        }

        return Dobject;
    }


    void dep_onchange(object sender, SqlNotificationEventArgs e)
    {
        Trace.WriteLine(DateTime.Now.ToString() + "Changes Occured Metro");
        SetMetrosCache();
        // this will remove the event handler since the dependency is only for a single notification
        SqlDependency dep = sender as SqlDependency;
        dep.onchange -= new onchangeEventHandler(dep_onchange);
       
    }

    [OperationContract]
    public DataTable GetMetoCity(string name)
    {
        Trace.WriteLine("Getmetrocity " + SQLNotification.dtMetro.Rows.Count);
        DataTable dttemp = new DataTable();
        dttemp.TableName = "Metro";
        dttemp = (DataTable)SQLNotification.dtobject; ;
        try
        {
            if (dttemp.Rows.Count > 0)
            {
                Trace.WriteLine("Record Found with Table name");
            }
            else
            {
                Trace.WriteLine("No Record found ");
                //GetMetoCityObject("premprakash");
            }
        }

        catch (Exception Ex)
        {
            Trace.WriteLine(DateTime.Now.ToString() + "GetMetoCity() functions with Erroe" + Ex.ToString() + " " + Ex.Message);

        }
        return dttemp;
    }

This post has been edited by modi123_1: 06 June 2012 - 07:24 AM
Reason for edit:: highlight the lines THEN click the code tags button in the 'format text' box of the post.

Was This Post Helpful? 0
  • +
  • -

#11 TimDawg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-October 12

Posted 15 October 2012 - 02:12 PM

This is great! It's exactly what I was looking for!!
:yes:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1