SqlDependency in .NET language

Query Notification in SQL

In parallel programming, It became very hard in DBMS to track when records are modified (add,update,delete) of a table. We may require some notification system which notify us that a particular table record is changed so we can update the UI accordingly. Before SqlDependency, we had dependent on checked records based on some regular interval basis, it was creating unnecessary query and lots of unnecessary traffic in DBMS engine. But due to SqlDependency, it became easy to handle.

What is Query Notification?

Query Notification is a new feature which lets the application wait for the notification when the underlying query changes its result. The Database Engine uses a service broker to deliver notification messages. So, once you run the command that has notification associated with it, the Database Engine registers the notification request to it and maps it with the existing command. Hence the notification gets executed once the result is changed.

What is SQLDependecy?

SqlDependency is a special .NET class which lets us subscribe for an event and later on when the subscribed command gets notification from SQLServer, the OnChange event of the class gets called

Which criteria needs to be considered before we start?

  1. column names must be defined in the query.
  2. Table must be used with a schema name like dbo.TableName.
  3. Unnamed or duplicate columns are not allowed.
  4. Reference to a table with Computed Columns are not allowed.
  5. GROUPBY, Cube, Rollup or Having are not allowed.
  6. Statement must not contain Pivot or Unpivot operators.
  7. Union, Intersect and except are not allowed.
  8. Statement should not contain a reference of a View.
  9. Query should not contain Distinct or Compute.
  10. NText,Text,Image Type in the query for notification are not allowed.
  11. Rowset functions like OpenRowset or OpenQuery are not allowed.
  12. Top expression is also not allowed in the query.
  13. Set NoCount ON will invalidate the usage of Query Notification in a stored procedure.
  14. Reference to server global variables (@@variableName) must also be excluded from the queries.

DEMO

Before we move forward to demo, we are required to enable a service broker for a particular database which will enable us to work with the notification system.

ALTER DATABASE DatbaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
 
  1. Before we start to work with SqlDependency (available in System.Data.SqlClient), We are required to register a dependency service. We can do that call the static start method of SqlDependency class. Start method accepts the argument as ConnectionString that needs to be passed as argument.
SqlDependency.Start (Connnectionstring);
  1. Now we want to listen to a particular table and some of the columns on modification & changes. For that we are required to create one SqlDepedency object.
  2. SqlDependency object accepts SqlCommand as an argument. So, before we create SqlDepdency object, we need an SqlCommand object.
  3. To create SqlCommand, we need two parameters.
    1. SqlConnection object: This connection must have the same connection string like previously we registered with SqlDepedency.Start method.
    2. Command query: Require query to register with command (This query must be followed above mentioned criteria)
var connection = new Sqlconnection (Connnectionstring);
connection.open ();
var Query = "Select coll, col2, col3 from schema.tableName";
var command = new SqlCommand (Query , connect1on ) ;
command.CommandType = CommandType . Text ;
command.Parameters.Clear();
  1. After we created SqlCommand object, we can populate SqlDepedendency object by pass the SqlCommand object as an argument.
var dependency new = sqlDependency(command ) ;
  1. To notify our program, we need an event. SqlDependecy has one event called OnChange which we can subscribe to and notify our program. OnChange can be registered via OnChangeEventhandler delegate. OnChangeEventHandler accepts 2 arguments. Object(Sender) & SqlNotificationEventArgs (e). So we need to create one method that has the same signature and register it with an OnChange event.
dependency.OnChange += Table_Modify;
  1. Now we invoke the previously created SqlCommand.
command.ExecuteNonQuery();
  1. We will create one method and put all the things (from step 4 to step 6) inside the one method so we can reuse it because we need to again register the same method.
private static void RegisterEvent () 
{
    var connection = new SqlConnection (ConnnectionString);
    connect1on.Open ();
    var Query = "Select col1, col2, col3 from schema . tableName " ;
    var command = new SqlCommand (Query, connection) ;
    command.CommandType = Command Type. Text;
    command. Parameters.clear();
    var dependency new SqlDependency(command)
    dependency .OnChange + Table_Modify;
    command. ExecuteNonQuery();
}
  1. After finishing the registration, we need to close the SqlDepedency service. We can stop the SqlDependecy using the static stop method of SqlDepedency class.
sqlDependency.Stop(connnect1onString);

Full Code:

sqlDependency.Stop(connnect1onString);

static Void Main (string[] args)
{
    try
    {
        SqlDependency . Start (ConnnectionString) ;
        RegisterEvent ();
    }
    finally
    {
        SqlDependency.Stop(ConnnectionString);
    }
}
2 reterences
private static void RegisterEvent()
{
    var connection = new SqlConnection (ConnnectionString);
    connection.open();
    var Query "Select coll, col2, col3 from schema. tableName " ;
    var command = new Sqlcomand (Query, connection) ;
    command.commandType CommandType. Text;
    command. Parameters. clear();
    var dependency= new SqlDependency (command );
    dependency . OnChange += Table_Modify;
    command . ExecuteNonQuery();
}
2 reterences
private static void Table_Modify (object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;
    dependency. OnChange -= Table_Modify;
    RegisterEvent () ;
}

Note: Query notifications were introduced in SQL Server 2005 (9.x) and also available in Azure SQL.

Mr. Shailesh Sakaria