Sqldependency Cache Usage

Series directory:

Sqldependency Cache Usage

Asp.net uses sqldependency

Sqlcachedependency use command notifications to invalidate Cache

Cachedependency usage

Aggregatecachedependency usage

Sqlcachedependency uses Round Robin to invalidate Cache

Bytes ----------------------------------------------------------------------------------------------

 

 

When the data in the Microsoft SQL Server database changes, the sqldependency class can provide a method to notify the application of this change. Program . You can use this notification to terminate the cache and reload the data on the database server.
A notification is a resource-consuming task, and data with a low change frequency should be selected.
For Web applications or intermediate layer components that use a few customers (Web servers or component servers) to provide services to hundreds of users at the same time, it is best to use the sqldependency class. Therefore, large tables can be cached. However, when an application receives a modification notification, it terminates the cache to incorrectly re-load these tables. Only when the user references data again, to reload the cache.

Sqldependency is a type encapsulated by. net2.0 and can be used in sql2005 or later versions.

Usage:

1. You must grant the send permission to the guest user on the querynotificationservice service in the MSDB database. The method is as follows. Be case sensitive. (I can run this step without performing real-time verification. It may have been the same before)

Use MSDB
Grant Send On Service ::
[ Http://schemas.microsoft.com/ SQL /Notifications/QueryNotificationService ]
To Guest

 

2. Enable CLR
When a message arrives in a service queue,CodeSp_dispatcherproc will use a queue to send messages. Therefore, you must enable the CLR function in SQL Server. You can use the following method to enable the Service:

Use Master
Exec Sp_configure ' CLR Enabled ' , 1
Reconfigure

 

3. The sqldependency object uses service broker to send messages to querynotificationservice. Therefore, you must enable service broker. You can use the following statement to check whether the feature is enabled. Select Databasepropertyex ( ' Northwind ' , ' Isbrokerenabled ' )
-- 1 indicates true, 0 indicates false

 

The Service Broker startup statement is as follows:

Use Master
Alter   Database Northwind Set Enable_broker

 

 

4. Use sqldependency.

Create a winfrom project named formdependency and place a dview in the window. Write the following code.

Using System. Data. sqlclient;
Using System. configuration;

Namespace Sqldependencyuse
{
Public   Partial   Class Formdependency: Form
{
Private   Static   String Connectstring = Configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Tostring ();

private DeleGate void griddelegate (datatable table);
private sqldependency Dep;
Public formdependency ()
{< br> initializecomponent ();
}

private void formdependency_load ( Object sender, eventargs e)
{< br> // Start a client process that communicates with SQL Server
sqldependency. start (connectstring);
updategrid ();
}

Private VoidFormdependency_formclosed (ObjectSender, formclosedeventargs E)
{
//Terminate communication with SQL Server
Sqldependency. Stop (connectstring );
}

Private   Void Updategrid ()
{
String SQL =   " Select [customerid], [firstname], [lastname], [version] from [DBO]. [Customer] " ;
Datatable dt =   New Datatable ();
Using (Sqlconnection CN =   New Sqlconnection (connectstring ))
{
Using (Sqlcommand cmd =   New Sqlcommand (SQL, CN ))
{
CN. open ();
// The sqlcommand object contains a notification attribute. You can pass the sqlcommand object to the constructor of the sqldependency object to set this attribute.
Dep =   New Sqldependency (CMD );
// When there is a DML operation, the onchange event will receive messages sent from SQL Server to the application through the sq_dispatcherproc stored procedure.
Dep. onchange + =   New Onchangeeventhandler (dep_onchange );
Using (Sqldatareader RDR = Cmd. executereader ())
{
DT. Load (RDR );
}
}
}
Dgvview. Invoke ( New Griddelegate (dgvdelegatemethod), DT );
}
Private   Void Dgvdelegatemethod (datatable dtval)
{
Dgvview. datasource = Dtval;
}

///   <Summary>
/// Sqldependency onchange event processing
///   </Summary>
///   <Param name = "sender"> </param>
///   <Param name = "E"> </param>
Void Dep_onchange ( Object Sender, sqlnotificationeventargs E)
{
System. Diagnostics. Debug. writeline ( " Events received " );

If (E. Info = Sqlnotificationinfo. Invalid)
{
MessageBox. Show ( " Invalid statement " );
Return ;
}
Updategrid ();
}

}
}

Note: The query statement in the above Code must be strictly written. You must provide the query statement for rule-making according to SQL Server Service Broker. Generally, it must be a simple SQL query statement (not *, not top, not function, including aggregate function, you cannot use subqueries, including subqueries after where, external connections, and self-connections. You cannot use temporary tables, variables, views, or databases, before the table name, you must add a prefix similar to the DBO database owner. For example, select * From Table1, select column1 from Table1, and select count (*) from Table1 are all incorrect SQL query statements, select column1 from DBO. table1 is the correct statement.

During the test, I found that the onchange event could not be triggered for a long time. Later I found that [DBO] was not added to the SQL statement.

 

5. Test. Open sqlserver and modify the data. The data of the datagridview interface is also updated.