Introduction 

If you have ever tried to create a push mechanism, originating from the database to the services and then to the browser - it can be complicated enough, but having a Silverlight embedded browser application, makes it that little more difficult. 

If you do a online search for that type of application you will find numerous articles on Silverlight and Duplex or Silverlight and Sql Notifications - but Silverlight with Duplex and SQL Notations - so little content to be found. 

 Download Code.zip - 3.02 MB

Background  

Initially this arose out of the frustration of not being able to find an article online to do just this scenario. Some came very close, but just not the complete article. Silverlight will be around for a while yet (or until HTML5 is fully accepted and matured) and with such a push mechanism will only aid other developers to use such technologies.  

Using the code  

Project Structure Layout  

Fig 1 

Silverlight    

  This method makes the initial call to the "DBNotificationService"service to register this browsers interest in the SQL server table notifications.  

        /// Subscribes this instance of the browser,
        void Subscribe()
        {            
            client.SubscribeToNotificationsCompleted += (sender, e) => { };
            client.SubscribeToNotificationsAsync();
        } 

This is the method that will register the browsers and store their session id's in a static list. each session will have it's own closure events associated with it. 

 public void SubscribeToNotifications()
		{
			IDBNotificationCallbackContract ch = OperationContext.Current.GetCallbackChannel<IDBNotificationCallbackContract>();
			string sessionId = OperationContext.Current.Channel.SessionId;
 
			//Any message from a client we haven't seen before causes the new client to be added to our list			
			lock (syncRoot)
			{
				if (!SilverlightClientsList.IsClientConnected(sessionId))
				{
					SilverlightClientsList.AddCallbackChannel(sessionId, ch);
					OperationContext.Current.Channel.Closing += new EventHandler(Channel_Closing);
					OperationContext.Current.Channel.Faulted += new EventHandler(Channel_Faulted);
				}
			}
		} 

Duplex 

This is the method that is used as the callback, for when there is a database change. 

private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type != SqlNotificationType.Change) return;
 
            ObservableCollection<Employee> myAuthors = this.GetEmployeeList(DateTime.Now.AddYears(-20), DateTime.Now.AddYears(1));
 
            string c = SerializeObject(myAuthors);
            this.SendTriggerAuditData(c);
 
            //the notification is now used up - so now we remove the event handler from the dependency object (another one will have been bound to it again)
            SqlDependency dependency = sender as SqlDependency;
            dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);
        }  

This is the method that will initiate the call back to the browsers with the new data. 

public void SendTriggerAuditData(string data)        
        {
            // loop through channels (browsers) and make a call to their callback method
            if (SilverlightClientsList.GetCallbackChannels().Count() > 0)
            {
                lock (syncRoot)
                {
                    IEnumerable<IDBNotificationCallbackContract> channels = SilverlightClientsList.GetCallbackChannels();
                    channels.ToList().ForEach(c => c.SendNotificationToClients(data));
                }
            }
        } 

Sql Notifications \ SqlDependency 

 The following method is the general purpose method used to query the database and create the Sql dependency 

 public ObservableCollection<WcfSqlNotifications.DuplexServiceReference.Employee> GetEmployeeList(DateTime startDate, DateTime endDate)
        {
            employees = new ObservableCollection<WcfSqlNotifications.DuplexServiceReference.Employee>();

            //the connection string to your database
            string connString = @"Data Source=YouDatabaseServer;Initial Catalog=dependencyDB;Persist Security Info=True;User ID=NotificationsUser;Password=password";

            string proc = "SelectCredentialsWithinDOBRange"; //the name of our stored procedure

            if (!CheckUserPermissions()) return null;  //first we need to check that the current user has the proper permissions, otherwise display the error

            this.employees = new ObservableCollection<WcfSqlNotifications.DuplexServiceReference.Employee>();
            bool success = SqlDependency.Start(connString); // create the dependency reference on the database

            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmd = new SqlCommand())
                {

                    SqlParameter myParm1 = sqlCmd.Parameters.Add("@StartDate", SqlDbType.DateTime, 20);
                    myParm1.Value = startDate;
                    SqlParameter myParm2 = sqlCmd.Parameters.Add("@EndDate", SqlDbType.DateTime, 20);
                    myParm2.Value = endDate; 

                    sqlCmd.Connection = sqlConn;
                    sqlCmd.Connection.Open();

                    //tell our command object what to execute
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = proc;
                    sqlCmd.Notification = null;

                    SqlDependency dependency = new SqlDependency(sqlCmd);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);                    

                    if (sqlConn.State != ConnectionState.Open) sqlConn.Open();

                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            WcfSqlNotifications.DuplexServiceReference.Employee author = new WcfSqlNotifications.DuplexServiceReference.Employee();
                            author.ID = reader.GetInt32(0);
                            author.FirstName = reader.GetString(1);
                            author.SecondName = reader.GetString(2);
                            author.Address = reader.GetString(3);
                            author.DOB = reader.GetDateTime(4).ToString();
                            this.employees.Add(author);
                        }
                    }
                }
                return this.employees;
            }
        } 

A method is needed to make sure that the user is able to initiate a query on the server. 

 private bool CheckUserPermissions()
        {
            try
            {
                SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);
                permissions.Demand(); //if we cannot Demand() it will throw an exception if the current user doesnt have the proper permissions
                return true;
            }
            catch { return false; }
        } 

USE dependencyDB;
CREATE QUEUE dependencyDBQueue;
CREATE SERVICE dependencyDBService ON QUEUE dependencyDBQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO GeneralUser;
ALTER DATABASE dependencyDB SET ENABLE_BROKER;  

Testing The Application

1. Make sure (dependencyDB) database has been enabled as a broker. 
2. Make sure you have created the user (in connection string) within the database (with enough permissions) - should already be created if just attaching database to Server. 
3. Run the Silverlight application within VS2010 (with modified connection string) - a grid should appear with details. 
4. Go to the database and open the credentials table for editing. 
5. Edit one of the table rows. 
6. Result: The edited row's details should be reflected in the browser's grid. 
7. Further testing: Open multiple browsers and do the same - changes will be pushed to all browsers. 

Pros and Cons  

Upside  

1. It is cross database platform - there is an OracleDependency object.(http://download.oracle.com/docs/cd/B19306_01/win.102/b14307/OracleDependencyClass.htm) 
2. High Scalability - using background thread to call WCF and using Win2008 server with IIS7 - connections can run into 1000's.
3. The code can be tailored to be more efficient - in that - only the updated records are passed back to the clients - not all the records and rebound. 
4. Can greatly aid an applications caching mechanism - the wcf can be notified when an update has occurred and refresh it's cache.   

 Downside  

1. Mono WCF is at .Net 2 - SqlDependency is in .Net 3.5+, so not able to port this solution to a Linux environment.   
2. There are limitations in the SQL that can be used within a stored procedure (http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx) 

History