Deploying SqlDependency in the WCF service itself to leverage the server's Query Notification is a relatively easy process, simply follow the SqlDependency usage guidelines for any application: start SqlDependency, then attach a SqlDependency object instance to your SqlCommand objects, use this object's OnChange event to react to changes, and subscribe again once notified. This will provide the WCF service with a callback from the SqlDependency infrastructure when the database server tables have changed.

The only thing challenging here is how to communicate from the WCF service back to the front end. The answer is a duplex channel and a callback that has to be supplied by the client. Here is how such a contract would be defined in WCF:

    [ServiceContract(

        Name="WCFQNTableSubscribe",

        Namespace="http://rusanu.com/Samples/WCFQN",

        CallbackContract=typeof(IWCFQNTableCallback),

        SessionMode=SessionMode.Required)]

    public interface IWCFQNTableSubscribe

    {

        [OperationContract(IsOneWay=true)]

        void Subscribe(string cookie, string sql);

    }

    public interface IWCFQNTableCallback

    {

        [OperationContract(IsOneWay = true)]

        void Callback(string cookie, DataSet data);

The important things here are the CallbackContract attribute specifying the contract the WCF service should use to callback on the client and the IsOneWay attribute on the two contracts to specify an asynchronous bidirectional contract.

Since we’re going to callback the client each time the data is changed, I thought to actually send the new dataset with each callback. The client subscribes to the data and does not get back any data in the Subscribe call, but the WCF service will immediately call him back on the supplied callback, providing the dataset with the current state of the data. After that each time the data is changed the WCF service will callback again the client with a new dataset. In my example the SQL query that is being submitted to retrieve the data is actually supplied by the client, in a real world scenario of course the client would request some business data, not some arbitrary SQL.

The WCF service runs the query, fills a dataset with the result set and calls back the client passing back the resulted dataset:

        public void SubmitDataRequest()

        {

            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(Settings.Default.connectionString);

            scsb.Pooling = true;

            SqlConnection conn = new SqlConnection(scsb.ConnectionString);

            conn.Open();

            SqlCommand cmdGetTable = new SqlCommand(_sql, conn);

            // Create the associated SqlDependency

            SqlDependency dep = new SqlDependency(cmdGetTable);

            dep.OnChange += new OnChangeEventHandler(dep_OnChange);

            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmdGetTable);

            da.Fill(ds);

            // Send the new dataset back to the client

            // on it's own callback

            _callback.Callback(_cookie, ds);

        }

When the SqlDependency is notified, all it does it invokes again this very same method, causing a new dataset to be sent back to the client (and in the process subscribing again to be notified):

     void dep_OnChange(object sender, SqlNotificationEventArgs e)

        {

            if (e.Type == SqlNotificationType.Change)

            {

                // Submit the SQL back to the server,

                SubmitDataRequest();

            };

        }

The client reacts to the callback by loading the supplied dataset, in my case the client simply attaches the dataset to a DataGridView object displayed on the form:

       #region WCFQNTableSubscribeCallback Members

        public void Callback(Callback request)

        {

            Invoke(new CallbackInvokeHandler(CallbackInvoke), request.data);

        }

        #endregion

        private void CallbackInvoke(DataSet ds)

        {

            grdViewer.DataSource = ds;

            grdViewer.DataMember = ds.Tables[0].TableName;

        }

        private delegate void CallbackInvokeHandler(DataSet ds);

Because UI elements on the form have to be updated on the main form thread, the callback in my example is calling using the Invoke method to pass the received dataset to the CallbackInvoke method and this one, in turn, attaches the grid to the new dataset.

The details are in how to get a WCF duplex contract up and running. Not every binding supports such channels, in my example I use a wsDualHTTPBinding:

  <system.serviceModel>

    <services>

      <service name="wcfService.WCFQNTableSubscription">

        <endpoint contract="wcfService.IWCFQNTableSubscribe" binding="wsDualHttpBinding"/>

      </service>

    </services>

  </system.serviceModel>

There are other binding that support duplex contracts, I based my example on the wsDualHTTPBinding just like the MSDN example does: http://msdn2.microsoft.com/en-us/library/ms731184.

I have uploaded my demo project here: http://rusanu.com/wp-content/uploads/2007/11/wcfqn.zip