DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Fixing Common Oracle Database Problems
  • SAP HANA Triggers: Enhancing Database Logic and Automation
  • The Future of Data Lakehouses: Apache Iceberg Explained
  • NoSQL for Relational Minds

Trending

  • Unlocking AI Coding Assistants Part 4: Generate Spring Boot Application
  • Breaking Bottlenecks: Applying the Theory of Constraints to Software Development
  • Unlocking AI Coding Assistants Part 3: Generating Diagrams, Open API Specs, And Test Data
  • Integrating Security as Code: A Necessity for DevSecOps
  1. DZone
  2. Data Engineering
  3. Data
  4. C# and SQLDependency: Monitoring Your Database for Data Changes

C# and SQLDependency: Monitoring Your Database for Data Changes

By 
Richard Mccutchen user avatar
Richard Mccutchen
·
May. 21, 10 · Interview
Likes (1)
Comment
Save
Tweet
Share
104.9K Views

Join the DZone community and get the full member experience.

Join For Free

Using the  SqlDependency Class is a good way to make your data driven application (whether it be Web or Windows Forms) more efficient by removing the need to constantly re-query your database checking for data changes.

For the purposes of this article we'll be discussing using SqlDependencywith SQL 2005, but I'm sure it works the same, or very similar with SQL 2008. Using SqlDependency to monitor for data changed relies on two things from SQL Server, Service Broker. A Service Broker allows the sending of asynchronous messages to and from your database.

The second item it relies on Queues, your Service Broker will use your Queue to create loose coupling between the sender and receiver. The sender can send its message to the Queue and move on, and relies on the Service Broker to make sure whatever message was put into the Queue is processed and a response is sent back to the sender (your application).


For this article I’m using a sample database I created named SqlDependencyExample and a table named Employee whick has this structure:

CREATE TABLE [dbo].[EmployeeList](    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,    [FirstName] [varchar](25) NOT NULL,    [LastName] [varchar](25) NOT NULL,    [PhoneNumber] [varchar](13) NOT NULL) ON [PRIMARY]

We will be using a stored procedure for querying, but this can also be done with a regular text query in your code. Here’s the stored procedure

CREATE PROCEDURE uspGetEmployeeInformationASBEGIN    -- Insert statements for procedure here    SELECT        EmployeeID,        FirstName,        LastName,        PhoneNumber    FROM        dbo.EmployeeListEND

NOTE: Notice in the stored procedure we have dbo in the table name, when using dependency to monitor data changes you have to have the table in the format [owner].[Tablename] of it can cause unwanted results, so to avoid that just use the said format.


First thing we need to do is create your Service Broker and Queue so we can send messages back & forth between our database. In my example I have a simple table that holds employee names & phone numbers. This is how we create the Service Broker & Queue (My database name is SqlDependencyExample but change that with your database name (We also need to give our SQL user permission to access it)

USING [SqlDependencyExample]CREATE QUEUE NewEmployeeAddedQueue;CREATE SERVICE NewEmployeeAddedService ON QUEUE NewEmployeeAddedQueue([http://schemas.microsoft.com/sql/notifications/postquerynotification]); GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlDependencyExampleUser;

Now we move on to the fun part, the code (I know that's what you're waiting for). Before this can work we need to check and make sure the connected user has the proper permissions for the notifications. We can do this with creating a simple method CheckUserPermissions which uses the SqlClientPermissions Class to check the permissions of the currently connected user. So here’s the simple method for accomplishing this:

private bool CheckUserPermissions(){    try    {        SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);        //if we cann Demand() it will throw an exception if the current user        //doesnt have the proper permissions        permissions.Demand();        return true;    }    catch    {        return false;    }}

One thing to know, SqlDependency relies on the OnChangeEventHandler Delegate which handles the SqlDependency.OnChange Event, which fires when any notification is received by any of the commands for the SqlDependency object.


Now for getting the employee list, in this method we will query our EmployeeList table to get the employees information. We will also set the OnChange event of our SqlDependency object so that it can let us know when data has changed in our table and re-populate it with the latest employee list. Before trying to access our database we will call the method CheckUserPermissions method we created earlier to make sure the current user has the proper permissions, if not we display a message otherwise we move on to getting the employee list and populating a ListView with the ID, first & last name of the employee and their phone number.

Here’s the GetEmployeeList method, which expects a parameter of type ListView (which is what will be displaying our employee list)

/// <summary>/// method for querying our database to get an employee list/// </summary>/// <param name="lview">the ListView we want to display the employee list in</param>private void GetEmployeeList(ListView lview){    //the connection string to your database    string connString = "YourConnectionString";    //the name of our stored procedure    string proc = "uspGetEmployeeInformation";    //first we need to check that the current user has the proper permissions,    //otherwise display the error    if (!CheckUserPermissions())        MessageBox.Show("An error has occurred when checking permissions");    //clear our ListView so the data isnt doubled up    lview.Items.Clear();    //in case we have dependency running we need to go a head and stop it, then    //restart it    SqlDependency.Stop(connString);    SqlDependency.Start(connString);    using (SqlConnection sqlConn = new SqlConnection(connString))    {        using (SqlCommand sqlCmd = new SqlCommand())        {            sqlCmd.Connection = sqlConn;            sqlCmd.Connection.Open();            //tell our command object what to execute            sqlCmd.CommandType = CommandType.StoredProcedure;            sqlCmd.CommandText = proc;            sqlCmd.Notification = null;            SqlDependency dependency = new SqlDependency(sqlCmd);            dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);            sqlConn.Open();            using (SqlDataReader reader = sqlCmd.ExecuteReader())            {                while (reader.Read())                {                    ListViewItem lv = new ListViewItem();                    lv.Text = reader.GetInt32(0).ToString();                    lv.SubItems.Add(reader.GetString(1));                    lv.SubItems.Add(reader.GetString(2));                    lv.SubItems.Add(reader.GetString(3));                    lview.Items.Add(lv);                }            }        }    }}

Notice we set the OnChangeEventHandler of our SqlDependency object to dependency_OnDataChangedDelegate, this is the method that will re-query our table and send notifications when the data has changed in our EmployeeList table. In this method we invoke the work onto the main UI thread, this will help us avoid the dreaded cross—thread exception when we go to re-populate the ListView control when any notifications are sent to our application.

Since our method (GetEmployeeList) required a parameter we cannot use the standard MethodInvoker delegate (as this cannot accept parameters). So what we will do is create our own Delegate which can accept our parameter. Here’s the delegate (very simple):

private delegate void getEmployeeListDelegate(ListView lv);

Our dependency_OnDataChangedDelegate requires a SqlNotificationEventArgs in the signature. Here we check the control being used to make sure InvokeRequired is true, if no then we use Invoke to invoke the work onto the main UI thread for re-populating our ListView, otherwise we just call our method to re-query:

private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e){    //avoid a cross-thread exception (since this will run asynchronously)    //we will invoke it onto the main UI thread    if (listView1.InvokeRequired)        listView1.Invoke(new getEmployeeListDelegate(GetEmployeeList), listView1);    else        GetEmployeeList(listView1);    //this example is for only a single notification so now we remove the    //event handler from the dependency object    SqlDependency dependency = sender as SqlDependency;    dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);}


NOTE: One thing to remember is you have to stop the dependency from querying your database when your form is closed, to do this use the forms FormClosing event to stop the work in the form you are using the dependency work in.

That’s how you use the SqlDependency Class for monitoring data changes in your database without having to use something like a timer control to re-query at certain intervals. Thanks for reading and happy coding :)

Database Data (computing)

Opinions expressed by DZone contributors are their own.

Related

  • Fixing Common Oracle Database Problems
  • SAP HANA Triggers: Enhancing Database Logic and Automation
  • The Future of Data Lakehouses: Apache Iceberg Explained
  • NoSQL for Relational Minds

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!