.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Implementing SQL Notification

Posted By:Ravi Ranjan Kumar       Posted Date: June 28, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This article enable you to learn how to implement SQL Notification service in your application using C#

Sql Notification

Sql Notification or query notifications are useful for applications that need to refresh caches in response to change in underlying data. Microsoft SQL server 2005 allows .Net Framework applications to send a command to SQL server and request notification.

Enabling SQL Notification

Application that Involve SQL notification requires a common set of tasks to be performed. The data source must be configured to support query notification, and the user must have the required Client-Side and Server-side permissions.

To use Query Notification, you need to

Enable Service Broker for the database.

Enable service Broker

SQL server database don't have Service Broker enabled by default. You need to enable Service Broker by using the following SQL statement :

Alter Database YourDatabaseName set Enable_Broker;

Executing SQL Notification

 To Execute SQL notification, you need tp use the SQLDependency class. The SQLDependency class is used to process notifications. This class automatically starts a worker thred to process notification as they are posted to the queue. SqlDependency also parses the Service Broker message and exposes the message as event argument data.

SqlDependency has to be initialized by calling the Start()  method. The Start() method is a static method. It needs to be called only once during application initialization for each database connection required. The stop() method is called at application termination for each connection that was established.

Consider an Example :-

A XYZ company maintain Employee Details of its Employee in the EmpDetails table. XYZ management want an application that will display details. Further, they want that if any changes are made to the address at the database server, the same change should be reflected in the application at that time.

Take a look how to accomplish this work

// Must declare at top of program to use SQLClient namespace for getting functionality of its classes 

Step 1 :-

using System.Data.SqlClient;
Step 2 :-
 // Declaring form level variables
        string connectionstring = "Data Source =.; Initial catalog =Your_Database_Name;uid =sa;pwd =Your_SQL_PassWord;Pooling = false";
delegate void GridDelegate(DataTable table);
        SqlDependency dep;
Step 3 :-
// Creating a UpdateGrid() function which will update data and show at that time in Gridview without invoking any event.
private void UpdateGrid()
            string sql = "select EmpID,Name,City from dbo.EmpDetails";
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(connectionstring))
                using (SqlCommand cmd = new SqlCommand(sql, con))
                    dep = new SqlDependency(cmd); //Passing Command to SQL dependency 
                    dep.OnChange += dep_OnChange;
                    using (SqlDataReader rdr = cmd.ExecuteReader())
            dataGridView1.Invoke((GridDelegate)delegate(DataTable table)
            { dataGridView1.DataSource = table; }, dt);
//Event came in action when data change
  private void dep_OnChange(object sender, SqlNotificationEventArgs e)
            System.Diagnostics.Debug.WriteLine("Received OnCgange Event");
            if (e.Info == SqlNotificationInfo.Invalid)
Step 4 :-
    private void Form1_Load(object sender, EventArgs e)
            // Call the UpdateGrid() method to populate the grid during form load
Step 5 :-
        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
            // Discountinue the Client-Side services

When we execute the given code, we will get the output as below


Now, execute the following SQL statement in SQL server Management studio 

-- Update value and check effect in Ruuning application
update EmpDetails set City ='Delhi' where EmpID ='E0001'

You will notice that the application is also showing updated records as showing given below 

Once More try... 

Now, execute the following SQL statement in SQL server Management studio

-- Insert New value and check effect in Ruuning application
Insert into EmpDetails values('E0002','NameB','StateB','CityB','AddressB','9199999999','abc@xyz.com')

Take a Look 

In the preceding code, the Start() method starts a client-Side process to Communincate with the SQL server. This method takes a valid connection string as a parameter. The SqlCommand objet has a Notification properry that is set by passing an object of SqlCommand of the constructor of SqlDependency object. When the SQL server receives the request for query notification it executes the SQL query. If change of the data affects this query notitication, it executes the SQL query. If changes to the data affects this query result. A message is sent to the service Broker and the subscription is removed from the query notification infrastructure. With the SqlDependency object, this message fires back to the application through the sp_DispatchProc stored procedure.

The message is received by the client application in the form of the OnChange event of the SqlDependency object.

When the form is closed, the Stop() method is called to disconnect the clinet-side services 

 Subscribe to Articles


Further Readings:

Author: amresh pandey         Company URL: http://www.dotnetspark.com
Posted Date: May 06, 2013

I hav 2 questions:
1- Why in asp.net I am getting error in gridview1.invoke

2- why getting invalid in alert.

Pls help me sort out these issues.

Thanks in adv.


Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend