Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Tip/Trick

Monitoring Real-time Database Changes in SQL Server in C#

Rate me:
Please Sign up or sign in to vote.
3.00/5 (4 votes)
8 Sep 2022CPOL2 min read 17.9K   10   4
Tutorial to monitor database changes without using tools and by using a simpler trick
There are many methods for monitoring database changes, one of which is SQL Dependency and Service Broker. But in this tutorial, we are going to do this without using the above tools and using a much simpler trick.

Introduction

In this article, I want to explain to you how to monitor the database without the need for SQL Dependency and Service Broker.

There are many methods to monitor database changes, one of these methods is SQL Dependency and Service Broker, but in this tutorial, we are going to do this without using the above tools and by using a much simpler trick. At first, I must explain that SQL Dependency will use a trigger in nature, and we are actually going to simulate a tool similar to SQL Dependency through this trick. For example, we have a database named Shop, which contains a table named Product, we intend to make any changes in the table, including inserting, editing, and deleting table records, automatically and without the need for timer control as well. Thread, the gridview of our program should be updated or so called Refresh. To do this, we will first create a trigger for the Insert action on the Product table in SQL Server as shown below:

Image 1

Consider creating triggers for deletes and updates as well. Inside our trigger, we used SP (stored procedure) xp_cmdshell. This stored procedure was provided by Microsoft in order to access operating system commands and is defined in the master database. What you're doing inside has us using the echo command to create a file called AppTextFile on the C drive. Note that to activate the xp_cmdshell procedure, you must execute the following trigger commands in SQL Server before creating them.

Image 2

Now, in the Visual Studio environment, we will use the FileSystemWatcher class to automatically update the gridview. This class will monitor the file that was created by trigger. Note that we have only explained the automatic gridview update here, with this trick that you can fully monitor even changes in fields and records.

Image 3

This trick was a very easy way to monitor the data of the tables. If you have any questions or other uncertainties in this field, please leave a comment below.

History

  • 7th September, 2022: Initial version

License

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


Written By
Iran (Islamic Republic of) Iran (Islamic Republic of)
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSqlDependency , CDC Pin
kiquenet.com18-Sep-22 6:03
professionalkiquenet.com18-Sep-22 6:03 
GeneralIt does the trick but... Pin
Rene Balvert8-Sep-22 23:59
Rene Balvert8-Sep-22 23:59 
GeneralRe: It does the trick but... Pin
Telegram:@ArastoAhmadi9-Sep-22 1:04
Telegram:@ArastoAhmadi9-Sep-22 1:04 
GeneralMy vote of 4 Pin
LightTempler8-Sep-22 10:03
LightTempler8-Sep-22 10:03 
In a low change rate scenario this could be a cheesy solution. Smile | :)

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.