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?
- column names must be defined in the query.
- Table must be used with a schema name like dbo.TableName.
- Unnamed or duplicate columns are not allowed.
- Reference to a table with Computed Columns are not allowed.
- GROUPBY, Cube, Rollup or Having are not allowed.
- Statement must not contain Pivot or Unpivot operators.
- Union, Intersect and except are not allowed.
- Statement should not contain a reference of a View.
- Query should not contain Distinct or Compute.
- NText,Text,Image Type in the query for notification are not allowed.
- Rowset functions like OpenRowset or OpenQuery are not allowed.
- Top expression is also not allowed in the query.
- Set NoCount ON will invalidate the usage of Query Notification in a stored procedure.
- Reference to server global variables (@@variableName) must also be excluded from the queries.
DEMO
ALTER DATABASE DatbaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
SqlDependency.Start (Connnectionstring);
2. 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.
4. To create SqlCommand, we need two parameters.
- SqlConnection object: This connection must have the same connection string like previously we registered with SqlDepedency.Start method.
- 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();
var dependency new = sqlDependency(command ) ;
dependency.OnChange += Table_Modify;
7. Now we invoke the previously created SqlCommand.
command.ExecuteNonQuery();
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(); }
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.