Query Notification & Service Broker

Do you ever encounter a system requirement which require you to periodically monitor a database table to check if there is any data change within it, then you need to perform some code work?

For example, the requirement is to call a web service when there is any data change in the database. What would you do in this scenario?

Option 1:

Are you going to use Trigger? When there is a record being inserted or updated, you write some code logic in the Trigger to perform some work and then call a web service? However, you do realize that code is restricted to the database area only. I mean, you are only able to write T-SQL to perform the work including calling a web service which is not possible.

Option 2:

However, there is an alternate way to do it by using SQL CLR to perform the work and calling a web service with C# code. Later, I realize that the web service client require System.ServiceModel.dll reference, and this assembly is not available in the SQL Server. Therefore, in order to make this work, I need to configure my SQL Server to grant permission to my SQL CLR Trigger to be able to call unsafe assembly and also making my SQL Server to trustworthy. However for this case, some companies rules do not allow us to change any database setting, we have to skip this idea.

Option 3:

When Trigger is out from the options, we have to do database table polling, which mean every second or every specific time interval, I am going to query my database to see any data change in my table. I can write a console application or a windows service to perform the polling job. Once my application detect a change, I will perform the required work and call a web service. This option is commonly used but not efficient because keep polling the database is going to stress the database.

Option 4:

In stead of using trigger and polling, we request the database to notify us when there is a change in the database by using Service Broker + Query Notification. And, this post topic is about how to setting it up and use it.

But before that, we need to know the difference between Query Notification and Polling. Is your problem really can be solved by using Query Notification?

Source from MSDN:

Query Notification	Polling
Query notification is initiated by SQL Server. The notification statement issued by the adapter just instructs the database to initiate notification in case there is a change in the result set of the statement.	Polling is initiated by the adapter. The adapter executes a statement to validate whether data is available for polling, and then initiates polling by executing the polling statement if some data is available for polling.
You can use the query notification statement to only read data in a SQL Server database table.	You can use the polling statement to read or update data in a SQL Server database table.
Query notification informs only about the type of change in the data such as Insert, Update, and Delete.	Polling informs you about the actual data that has changed.
The data-change notification is instantaneous.	The data-change notification depends on the polling interval, and the adapter clients are informed about the data changes at the end of every polling interval. Polling can give you better throughput in scenarios where the data changes are happening continuously, and you do not want to be notified of each change as and when it happens. Instead, you specify a polling interval after which you want to be notified of all the changes that have happened since the last change notification.

The concept is we provide a query for SQL Server to monitor. Then, we enable Service Broker in the SQL Server. We subscribe a Query Notification request, so that if there is any change to the provided query, a notification will be sent to a queue by the Service Broker.

Assume I have a database call NotificationDB. In the database, I have a table call dbo.Leaves, and I need to subscribe query notification to that table.

Note: The query notification subscription need to meet the requirement mentioned here.

So, the following are the steps to make the mentioned concept work:

Database side:

1. Enable Service Broker in the SQL Server by running the following script:
   
   
   USE NotificationDB
   ALTER DATABASE NotificationDB SET ENABLE_BROKER
   
   
2. Create a queue for Service Broker to drop notification message into it:
   
   
   CREATE QUEUE MyNotificationQueue
   
   
3. Create a service that drop message to the above queue.
   
   
   CREATE SERVICE MyNotificationService
     ON QUEUE MyNotificationQueue
     ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
   
   
4. Create a route for the service to point back to my local database
   
   
   CREATE ROUTE
     NotificationMessagesRoute
     WITH SERVICE_NAME = 'MyNotificationService',
          ADDRESS = 'LOCAL'
   
   

Application side:

1. Start a listener to the queue by using SqlDependency :
   
   
   SqlDependency.Start(
                   ConfigurationManager.ConnectionStrings["default"].ConnectionString,
                   "MyNotificationQueue");
   
   
   
2. Create another method that use SqlDependency to poll the Service Broker:
   
   
   
   

   public void RegisterDependency()

   {

       using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))

       {

           try

           {

               SqlCommand command = new SqlCommand("Select [ID],[ApplicantName], [Status] from dbo.Leaves WHERE [Status] = 'Approved' AND HasScheduled = 0", connection);

   
   

               //Monitor the Service Broker, and get notified if there is change in the query result

               SqlDependency dependency = new SqlDependency(command, "Service=MyNotificationService;local database=NotificationDB", int.MaxValue);

   
   

               //Fire event when message is arrived

               dependency.OnChange += this.dependency_OnChange;

   
   

               connection.Open();

                   

               SqlDataReader reader = command.ExecuteReader();

   
   

               while (reader.Read())

               {

                   Leave leave = new Leave();

                   leave.ID = Convert.ToInt32(reader["ID"]);

                   leave.ApplicantName = reader["ApplicantName"].ToString();

                   leave.Status = reader["Status"].ToString();

   
   

                   Console.WriteLine(string.Format("{0}\t{1}\t{2}", leave.ID, leave.ApplicantName, leave.Status));

               }

   
   

               connection.Close();

           }

           catch (SqlException ex)

           {

               Console.WriteLine(string.Format("Error: {0}", ex.Message));

           }

           catch (Exception ex)

           {

               Console.WriteLine(string.Format("Error: {0}", ex.Message));

           }

       }

   }
   
   
3. Create an event handler to handle what to do when a message is arrived. It is required to re-register the dependency every time a notification message has been received:
   
   
   void dependency_OnChange(object sender, SqlNotificationEventArgs e)
   {
       var info = e.Info;
       var source = e.Source;
       var type = e.Type;
              
       SqlDependency dependency = sender as SqlDependency;
   
   
       dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
       RegisterDependency(); //Re-register dependency is required after a notification is received everytime
   
   
       //Do whatever you like here after message arrive
       //Can be calling WCF service or anything supported in C#
   }

FYI: The notification messages are in XML format. The detail message format can be found here: http://schemas.microsoft.com/SQL/Notifications/QueryNotification/QueryNotification.xsd

The above SqlNotificationEventArgs event argument contain the content of the notification message. I am not able to sniff the message content, however, base on the above XML schema, the notification message should be look like this:

<QueryNotification Type="change" Source="data" Info="insert">
    <Message>bla bla bla…</Message>
</QueryNotification>

So, whenever there is a new record has been inserted to the table, then the monitored query will be affected with the new row, then the notification message will be look like above, and finally the SqlNotificationEventArgs.Type = insert. For any row of record has been updated, the Type will be "update".

Also, you can see how many active query subscriptions currently have in the database with the following statement:

SELECT * FROM sys.dm_qn_subscriptions

Then, you will see something like this:

In summary, instead of polling a table by keep sending query to the database frequently with a timely interval to look for any data change, we can ask the database to notify us. Theoretically, it sound like better performance, however I am not too sure about that, how SqlDedepency handle the database connection may be a concern because in order to get message notifications, it require to constantly open a connection. I have not have a chance to dig deep enough, probably will share more info in the future when I encounter any performance problem with SqlDependency.


If you are interested with my complete source code, feel free to download from HERE.

WCF REST Service + HttpClient vs WCF Proxy Class - Part 2

Continue from the previous post, I would like to share how to make HTTP GET call to a WCF REST Service.

Following is my operation contract setup:

[OperationContract]

[WebGet(UriTemplate = "ListResults", ResponseFormat = WebMessageFormat.Json)]

List<Result> ListResults();

WCF Service Client

Normally, when we want to call that web service, we can simply perform the "Add Service Reference" from the Visual Studio, and the WCF client aka the proxy class will be auto-generated. Therefore, the following is the code that you normally would use to call a WCF service:

ResultServiceClient proxy = new ResultServiceClient();

List<Result> results = proxy.ListResults();

Well, this code cannot work with the WCF REST service where the operation contract is decorated with WebGet attribute. The reason is WebGet attribute actually make the data retrieval operation expect a GET method call. But, the above code is actually making a POST method call. See the following RAW data which is made by the proxy class:

POST http://127.0.0.1:65000/ResultService.svc/ListResults HTTP/1.1

Content-Type: application/xml; charset=utf-8

VsDebuggerCausalityData: uIDPo8z4w4PqwblMjjlSjLko010AAAAA6fc873e/5U+GAxRCKLz7Mps+z0ILaFhMs1wQZ9g/XOwACQAA

E2EActivity: DUiXbbtRBk6NgLuSr8X7+A==

Host: 127.0.0.1:65000

Content-Length: 42

Expect: 100-continue

Accept-Encoding: gzip, deflate

Connection: Keep-Alive

<ListResults xmlns="http://tempuri.org/"/>

You would get the response HTTP 405 - Method Not Allowed.

HTTP/1.1 405 Method Not Allowed

Server: ASP.NET Development Server/11.0.0.0

Date: Fri, 17 May 2013 00:40:32 GMT

X-AspNet-Version: 4.0.30319

Allow: GET

Content-Length: 1565

Cache-Control: private

Content-Type: text/html; charset=UTF-8

Connection: Close

Therefore, we have to use WebChannelFactory to make the WCF REST service call with HTTP GET method. Here is the code:

var behavior = new WebHttpBehavior();

behavior.DefaultBodyStyle = WebMessageBodyStyle.Wrapped;

//Note: the IResultService is not the one generated from the svcutil.exe

//It should be your Service Contract

using (var factory = new WebChannelFactory<LayeredWebApi.Services.Contracts.IResultService>(

    new WebHttpBinding(),

    new Uri("http://ipv4.fiddler:65000/ResultService.svc")

))

{

    factory.Endpoint.EndpointBehaviors.Add(behavior);

    var channel = factory.CreateChannel();

    //Note: the Result object is not the one generated from the svcutil.exe

    List<LayeredWebApi.Entities.Result> results = channel.ListResults();

}

And, here is the HTTP RAW content made by the WebChannelFactory:

GET http://127.0.0.1:65000/ResultService.svc/ListResults HTTP/1.1

Content-Type: application/xml; charset=utf-8

VsDebuggerCausalityData: uIDPo6WsSaH5x2NJta5xXQVoGTcAAAAA5M7pLj2DXEOHIOoTRG4R1t5jyJvtJCVLlnuRlW/5RqcACQAA

E2EActivity: BOiPxzfbnUCLg4OkbNlTDw==

Host: 127.0.0.1:65000

Accept-Encoding: gzip, deflate

Connection: Keep-Alive

HttpClient 

There is another way to call the WCF REST service which is by using HttpClient. Here is the code:

using (HttpClient client = new HttpClient())

{

    client.BaseAddress = new Uri("http://ipv4.fiddler:65000");

    client.DefaultRequestHeaders.Accept.Add(

        new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

    HttpResponseMessage response = client.GetAsync("ResultService.svc/ListResults").Result;

    Assert.IsTrue(response.IsSuccessStatusCode, "Failed to call WCF service.");

    string json = response.Content.ReadAsStringAsync().Result;

    JObject jObj = JsonConvert.DeserializeObject(json) as JObject;

               

    //Here is the tricky part.

    //WCF REST service return the real result object inside an object property

    //The object property name is prefixed with service: e.g. <ServiceName>Result

    //You know that your result is a generic list, you have to convert the result to JArray first

    //Then convert the JArray to generic List

    List<Result> result = jObj.GetValue("ListResultsResult")

                              .ToObject<JArray>()

                              .ToObject<List<Result>>();

}

See the following RAW content made by the HttpClient:

GET http://127.0.0.1:65000/ResultService.svc/ListResults HTTP/1.1

Accept: application/json

Host: 127.0.0.1:65000

Connection: Keep-Alive

Simple and let's look at the result. Note: There is a tricky part when deal with WCF REST service return result. Look at the following RAW content return from the service:

HTTP/1.1 200 OK

Server: ASP.NET Development Server/11.0.0.0

Date: Fri, 17 May 2013 03:35:08 GMT

X-AspNet-Version: 4.0.30319

Content-Length: 286

Cache-Control: private

Content-Type: application/json; charset=utf-8

Connection: Close

{"ListResultsResult":[{"<ID>k__BackingField":1,"<Name>k__BackingField":"Ah Beng","<Score>k__BackingField":50},{"<ID>k__BackingField":2,"<Name>k__BackingField":"Ah Lian","<Score>k__BackingField":72},{"<ID>k__BackingField":3,"<Name>k__BackingField":"Ah Boon","<Score>k__BackingField":1}]}

The List<Result> type has been serialized into JSON as you can see above, however, it is assigned to one property call ListResultsResult as highlighted above. Therefore, in my code with HttpClient, after deserialize the whole JSON string into a JObject (from Newtonsoft), I have to have this code jObj.GetValue("ListResultsResult") to get the real result return by the service method. And then, since the data type is a generic list, it must be a JArray type. I have to convert the result into JArray first, then only convert it into List<Result> type.

Compare to WCF client that use WebChannelFactory, the serialization and deserialization is done at the back with the .net serialization library. It is transparent to you with this one line of code: List<LayeredWebApi.Entities.Result> results = channel.ListResults();

Well, it is up to your call which WCF REST client you wish to use.