Post
Ask Question

Query Notification in SQL Server

Have you ever thought of a scenario in an ASP.NET website that if something is inserted, updated or deleted from the database the ASPX page should auto-reflect it without explicitly refreshing the page? Well reading the preceding lines you will think of using an ajax timer and database polling can easily accomplish the mission, right? Give it a second thought; is it really a good practice to poll the database periodically ? No, its not, because every database call will increase the load on the website. What if we put the data into a cache and poll it periodically for a change in data? A question then comes of how will the cache know when the data in the database has changed? The answer of the question is QueryNotification and SQLCacheDependency. To use them we first need to set up our database by executing the following scipts:

  1. ALTER DATABASE Ahmar_DB SET ENABLE_BROKER  
  2. GRANT SUBSCRIBE QUERY NOTIFICATIONS TO Mst;  
  3. GRANT RECEIVE ON QueryNotificationErrorsQueue TO Mst;  
  4. GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO Mst; 

Note

  1. Ahmar_DB is the database name
  2. Mst is the Schema Name

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions among instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Query notification is a feature that lets us subscribe to a query such that when the results of the query change a message is passed to the subscriber.

To read more about Service Broker and query notification please refer to the MSDN.

Let's get back to C#. Until now we have set up query notification that will provide notificationof a change in data residing in the cache. Let's see how to implement sqlcachedependency.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Web.Caching;  
  10.   
  11. public partial class _Default : System.Web.UI.Page  
  12. {  
  13.     string con = "Data Source=IKSL_2-PC;Initial Catalog=Ahmar_DB;User ID=Junk;Password=JunkPassword";  
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.         if (!IsPostBack)  
  17.         {  
  18.             getdata();  
  19.         }     
  20.     }  
  21.      
  22.     public void getdata()  
  23.     {  
  24.         if (HttpRuntime.Cache["Hello"] == null)  
  25.         {  
  26.             SqlConnection c = new SqlConnection(con);  
  27.             SqlCommand cmd = new SqlCommand("SELECT  [Sno],[Name]  FROM [Mst].[EMP_Name]", c);          
  28.             cmd.Notification = null;  
  29.             cmd.NotificationAutoEnlist = true;  
  30.             SqlCacheDependency cac1 = new SqlCacheDependency(cmd);   
  31.             c.Open();  
  32.             DataTable dt = new DataTable();  
  33.             SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  34.             sda.Fill(dt);   
  35.             c.Close();   
  36.             HttpRuntime.Cache.Insert("Hello", dt, cac1);                 
  37.         }  
  38.         Load();       
  39.     }  
  40.     public void Load()  
  41.     {   
  42.         DataTable dt = (DataTable)HttpRuntime.Cache.Get("Hello");  
  43.         GridView1.DataSource = dt;  
  44.         GridView1.DataBind();           
  45.     }   
  46.     protected void Timer1_Tick(object sender, EventArgs e)  
  47.     {  
  48.         getdata();  
  49.     }  
  50. } 

 

  1. ---------------------------ASPX--------------------------  
  2. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  3.   
  4. <!DOCTYPE html>   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">   
  7.     <title></title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <asp:ScriptManager runat="server"></asp:ScriptManager>  
  12.           
  13.         <asp:UpdatePanel runat="server" ID="upd" UpdateMode="Conditional">  
  14.             <ContentTemplate>  
  15.                 <asp:Timer ID="Timer1" runat="server" Interval="1500" OnTick="Timer1_Tick"></asp:Timer>  
  16.                 <div>  
  17.                     <asp:GridView ID="GridView1" runat="server"></asp:GridView>  
  18.                 </div>  
  19.             </ContentTemplate>  
  20.         </asp:UpdatePanel>   
  21.     </form>   
  22. </body>  
  23. </html>  

The preceding written code is simple enough to understand but I will try to briefly explain what I am doing here. In the getdata() function I am executing a select command and populating the data table with the results and assigning it to a cache. If you will see I have created an object of sqlCacheDependency and passed a command object into its constructor. Later this dependency object has been passed to the Cache.Insert method. A timer is periodically checking whether the cache is empty or not. If the cache is empty it will be populated back from the database.Query Notifier that we enabled will notify whether the data has changed or not as soon as the data is changed our the cache will be Null and the freshly changed data will be shown in the grid.

There are the following limitations of SQL Cache Dependency:

  1. Works on plain query not SP
  2. No support of * in the query
  3. Query should not contain UNION, INTERSECT, EXCEPT operators
  4. Query should not contain Distinct Keyword
  5. Limited use of AGGREGATE functions


Similar Articles