SqlDependency

SqlDependency

In this fast moving world of technologies , there is need  to share data between instances, as in a chat program, or receive constant updates like scores, as in a stock broker application.

Sqldependency will fulfill this, as this provides the instant trigger if the event when ever there is a change in the database entries .

Among the many new features for SQL Server 2012 are Service Broker and Query Notifications. The Service Broker is a queued, reliable messaging mechanism that is built into SQL Server 2012 and provides a robust asynchronous programming model.

Query Notifications allow applications to receive a notice when the results of a query have been changed. This improves performance by not having to periodically query the database for changes. 

To use SqlDependecy, the database must support a Service Broker. If the database was not created with this option enabled, you will need to enable it.

  ALTER DATABASE database SET ENABLE_BROKER

SqlNotificationRequest can also be used to provide the same services, however, it requires a good deal of manual setup. SqlDependency sets up the plumbing for you. While it is simpler to implement, it obviously doesn't allow the degree of customization that may be necessary for some applications, andSqlNotificationRequest would be the best choice.

A dependency is created between the application and the database via a SqlCommand. Before that can be established, the SqlDependency must be started for this session.

Sample code to use sqldependency

  using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    message = reader[0].ToString();
                    stat = reader[1].ToString();
                }
            }

 private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            
            if (e.Info == SqlNotificationInfo.Insert)
            {
               //function code
            }

            if (e.Info == SqlNotificationInfo.Update)
            {
               //function code
            }

        }