Query notification in Ado.net 2.0

Overview

Any relational database application that has a real value can not be separated from a large pile of query tables. If you are an expert in developing a graphical user interface, you know that these query tables will be used to process lists in a Drop-down list box. I split the query table into two types: a read-only table and a rewritable read-only table. The difference is what causes the table to change. I think that if you need to convene an employee meeting or a user meeting to modify the contents of a table, the table is read-only. A good example is the company's product Category list. The contents of the table will not change until the company develops and launches new products to the market, or the company restructures. A read-only table that can be rewritten is a relatively fixed list of content, but can be modified by end users, often using a combo box instead of a Drop-down list box. An example of a read-only table can be rewritten is the appellation term list. Application designers can take into account the most commonly used titles, such as Ms., Mr, Mrs, and Dr, but there is always a user's title that you have never considered before and want to add to it. As an example of how common this situation is, one of the last midsize products I worked on had a relational database that was well designed to conform to the third paradigm, which contained 350-400 tables, and I estimated that about 250 tables were read-only or could overwrite read-only tables.

Traditional Web applications (a classic example of a three-tier architecture application) want to cache this type of table as much as possible. This will not only reduce the number of round-trip databases, but also reduce the query load on the database server and improve the responsiveness of certain scenarios, such as accepting new orders. Read-only tables are easy to cache; You can always put tables in the cache and occasionally reload the tables, and the database administrator (DBA) reloads the cache in some way. I hope your organization rarely holds meetings that modify the basic structure and content of the database. Re-refreshing the middle-tier cache to overwrite a read-only table is a bit of a hassle. If the cache refresh rate is too much to get the effect you expect, the user cannot immediately see other users ' modifications to the data. A support person can use another application to add a new item, and then send an instant message to a companion who intends to use the item, but the companion's selection list box does not contain the newly added item. Worse, if the second user tries to add this "missing list item" At this point, he or she will receive a database error informing that the item already exists. Because of a similar problem, if you can overwrite a read-only table to allow for multiple-point updates, you typically do not cache.

In the past, programmers had to develop their own solutions by hand, using Message Queuing, triggers for file output, or Out-of-band protocols to notify caching, if some users outside the application were updated to overwrite read-only tables. This "notification" solution simply notifies the cache that a row has been added or modified, indicating that the cache must be refreshed. How to tell which rows of the cache have changed or which rows have been added has always been a challenge, as is the issue of distributed databases and distributed transactions or merge replication. A LOW-COST notification solution is to flush the entire cache as long as the program receives a "cached invalid" message.

SqlDependency provides a caching solution

If you are using SQL Server 2005 and Ado.net 2.0, a new notification solution called query notification is built into the SqlClient data vendor and database. The problem finally comes with a built-in and easy-to-use solution! ASP.net 2.0 also has built-in features to support query notifications. Asp. NET cache objects can register notifications, and can even be used to combine notifications with ASP.net page caching or page fragment caching.

The architecture that implements this feature includes the SQL Server 2005 query engine, SQL Server Service Broker, sp_dispatcherproc system stored procedures, ADO. NET of Sqlnotification (System.Data.Sql.SqlNotificationRequest) and SqlDependency class (System.Data.SqlClient.SqlDependency), and ASP.net cache class (system.web . Caching.cache) in short, it works in the following ways:

1. Each ado.net sqlcommand contains a notification attribute that represents a request for notification.

When the SqlCommand is executed, if a notification property exists, a network protocol packet (TDS) that represents the notification request is appended to the network request.

2.SQL Server registers the subscription for the requested notification by using the query notification schema, and then executes the command.

3.SQL Server "Monitors" any SQL DML statement that might cause a change in the result set that was originally returned. Sends a message to the Service Broker service when a change occurs.

4. Messages can be:

A. Raises the notification and returns the notification to the registered client.

B. Residing in service Broker's services queues, Advanced Clients can implement their own processing mechanisms.

Figure 1. Notification Service Overview