Events and CacheDependency with the database

• Download source and demo projects - 25 Kb

Introduction

Most applications in .NET use database. This is why I made this code, because .NET 1.1 doesn't support events with database. With this code, you can simple implement events like OnChange, OnInsert, OnUpdate or OnDelete with a table in database. The spectrum of using is big, it is useful in every project, where you need to get know that data in the database was changed or if some query was executed on the database. If you use some data from the database more often, it is bad to make a query to the database in every case you need this data. It is why many programmers use application cache in their web projects. They put some data (most often is DataSet) from database to the cache. And now, what will they do if the data in the database was changed and they need to update the data in cache? It is bad to make a lot of queries to know if the data was changed! Good idea is to use class CacheDependency, but unfortunately it supports only files and doesn't support tables in database. It is also why there is my code. There is a method that returns CacheDependency object, prepared to make dependency with the object in the cache and the table in database. Now, your object from the database in the application cache is updated when data in the table in database was changed. This code is specially written for use with Microsoft SQL Server, but you can apply this method to any database system that supports triggers and user defined procedures.

Before using

• Create directory "C:\Signalfiles\" and grant to it a read access for “ASPNET” user. If you want to use another directory, you have to rewrite in SqlEvents.cs static member DefaultPathToSignalFiles to path to your directory. And in xsp_proc.cpp macro PATH_TO_SIGNAL_FILES. Compile this dll library! Remember, ASPNET user has to be able to read from this directory.
• Copy xsp_proc.dll to "C:\Program Files\Microsoft SQL Server\MSSQL\Binn" or to some other directory, where Windows automatically look for Dll libraries.
• Execute on your database InstallMyXSP.sql. For example, you can do it like this: osql -U sa -P -i InstallMyXSP.sql (This will be work only if you have user "sa" with blank password). This file will install extended stored procedure (user-defined procedure) to your SQL server and grant it necessary permissions.
• If you want to try WebDemoProject that illustrates using of CacheDependency with the application cache, you must make virtual directory named "WebDemoProject" for the application directory "WebDemoProject" on your web server.

InstallMySQL.sql

/*It is necessary to install extended stored procedure to master database*/
USE master 


/* Add external procedure xsp_UpdateFile to Microsoft SQL Server*/
EXEC sp_addextendedproc 'xsp_UpdateFile', 'xsp_proc.dll'


/* Everybody can execute xsp_UpdateFile now*/
GRANT EXECUTE ON xsp_UpdateFile TO PUBLIC



/*Execute*/
GO

How it works

When the sql query (only queries INSERT, UPDATE and DELETE are allowed, because others are not supported by triggers) is executed on a table, which is monitoring with trigger. This trigger calls for extended stored procedure xsp_UpdateFile in xsp_proc.dll that changes (or creates) empty signal file. This signal file is monitored by FileSystemWatcher or CacheDependency, it depends on what you want to do with the information.

There are two interesting SQL commands used in SqlEventsObject class. First one makes sure if a trigger named "SomeTrigger" exists in system. For the interest, with this query you can look for another objects in the database, you only have to do is to change the type. You can find this types in the help of your database server.

SELECT COUNT(name) FROM sysobjects WHERE name = SomeTrigger AND type = 'TR'

Next one creates a trigger called "SomeTrigger", whitch will be watching "TableName" for changes caused by queries INSERT, UPDATE, DELETE. If some of these queries is executed on a table "TableName", extended stored procedure xsp_UpdateFile is called and this procedure creates a file named "file.signalfile".

CREATE TRIGGER SomeTrigger ON TableName FOR INSERT, UPDATE, 
  DELETE AS EXEC master..xsp_UpdateFile 'file.signalfile'

How to use it

Events with Database

First thing you need to do is to add reference to a dynamic library "SqlEvents.dll", after that include namespace MiloslavBeno.Data.SqlEvents. Than create a new instance of SqlEventsObject class. Constructor initializes a new instance of this class with a SqlConnection object. There is also one overloaded constructor with 2 parameters, the first is a SqlConnection object and the second is path to the directory with signal files. This parameter is necessary for using the code in web farms (see below). When you have a instance of SqlEventsObject, call CreateSqlEvent with a name of the database, a name of the table and a string with events you want to catch. You can set this parameter not only to "INSERT, UPDATE, DELETE", but also for its others combinations and it doesn't depend on any order. So, this method returns you identification of the event( it is name of signal file ). Now, it is time for the AddHandler method. As first parameter use identification returned by CreateSqlEvent and as second parameter you create an EventHandler delegate, which identifies the method that will handle the event. And when you want remove delegate from SqlEventsObject use RemoveHandler. And that's all:)

using System;
using System.Data.SqlClient;
using MiloslavBeno.Data.SqlEvents;

class Demo

{
 static void Main(string[] args)
 {

  SqlConnection conn = new SqlConnection(
   "server=localhost;database=TestingDatabase;uid=sa;pwd=");


  SqlEventsObject eo;
  
  try
  {
   conn.Open();


   eo = new SqlEventsObject(conn);



   //This method returns identificator of event with 
   //database table you want to watch for changes.
   string identification = eo.CreateSqlEvent(
    "TestingDatabase","DemoTable","INSERT, UPDATE, DELETE");


   //With identificator returned by method above you can add
   //EventHandler to your method. And you can add 
   //as much EventHandlers as you want
   eo.AddHandler(identification,new EventHandler(OnSqlEvent));
   eo.AddHandler(identification,new EventHandler(OnSqlEvent2));
   //There is also method EventsObject.RemoveHandler 
   //that removes a delegate from the event

 
  // This 2 rows
   Console.WriteLine("Now if you execute query INSERT, UPDATE or "+
    "DELETE on TestingDatabase..DemoTable, event will be called.");
   Console.WriteLine("---===Press a key to quit===---");
   Console.ReadLine();
  }
  catch(Exception e)
  {
   Console.WriteLine(e);
  }
  finally
  {
   conn.Close();
  }
 }



 protected static void OnSqlEvent(Object sender,EventArgs e)
 {
  Console.WriteLine(
   "Table DemoTable in database TestingDatabase was changed!!!");
 }


 protected static void OnSqlEvent2(Object sender,EventArgs e)
 {
  Console.WriteLine("--- Second delagate also works!");
 }
}

This demonstration code is a console application, but you can make web applications with SqlEventsObject as easy like that. You only have to initialize SqlEventsObject in Application_Start at global.asax file instead of in Main. And ensure that SqlEventsObject will not be removed, you can do this by using a SqlEventsObject instance which will be variable of Global class in global.asax file.

Using in Web farms

Everything is ok when a web server and the database is on same computer. But does it work if database is on another computer? and what about web farms? The answer is yes, it is possible, but the computer with a web server must have access to remote machine's Win32 file system, where the database is. It is necessary, because these events database system is based on Win 32 file change notifications.

So, what next? In constructor of a SqlEventsObject class, set second parameter to path to remote machine directory, where the signal files are located. For example: SqlEventsObject(conn,@"\\RemoteMachineName\\Signalfiles\\"). And other things are same like in console application above.

CacheDependency with Database

Context.Cache.Insert("DataSet",
     ds,
     EventsObject.CreateCacheDatabaseDependency(
      "TestingDatabase","DemoTable",true),

     Cache.NoAbsoluteExpiration,
     Cache.NoSlidingExpiration,
     CacheItemPriority.Default,
     new CacheItemRemovedCallback(RefreshDataSet));

As you can see, the thing you have to only do in this case is call CreateCacheDependency instead of new CacheDependency(). Don’t forget that in first call of this method in application is needful to set a third parameter on true. It means that method has to make sure if the necessary trigger is in the database. If it isn’t here, it creates a new one. In next cases use overload function without third parameter or set it to false, because the trigger is already in the system. If you want to see a whole example of this, see WebDemoProject.