Of late I have been working with ASP.NET MVC and in general a complete upgrade on my architectural thinking, designing, coding and testing. In this little blip I had, I thought about dependencies and the lack of in my coding. I have some ideas of applications I want to create and performance is really my biggest consideration in the design. The fact that I will be employing good coding practices goes without question.
In this blog I want to show 2 methods, which quite conveniently happen to be the two overloads for the constructor of the SqlCacheDependency class.
Have you got the database ready?
So basically I am assuming you have your database ready. if not go create one, I called mine Forum. If you want to do what i have done to test please run the following sql against your new database to create the example table.
USE [Forum]
GO
/****** Object: Table [dbo].[TestTable] Script Date: 07/12/2008 16:57:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestTable](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [varchar](50) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
The next thing to is actually enable the notification system inside SQL Server 2005. I did not know this previously but the aspnet_regsql.exe can do more than just enable the ASP.NET Providers. The functionality we are looking for is as follows, (http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx):
You need to run a primary command first and this is to enable the database for the Cache Dependency. So run the following command, using the variables which apply to your setup:
- My database server instance : REA_ANDREW-PC\SQLEXPRESSADV
- My database name: Forum
Next we need to enable the SQL Cache Dependency for the individual table. This must be run for each table you wish this to be applied to.
- My database server instance : REA_ANDREW-PC\SQLEXPRESSADV
- My database name: Forum
- My table name : TestTable
Excellent. Now that your database is configured you will notice that you have:
- Five new stored procedures
- One new table
- A trigger assigned to the table which you initialised the SQL Cache Dependency on.
The two methods, SqlCommand or specify the database and table
As mentioned above there are two overloads to instantiate a SqlCacheDependency with. These are:
- public SqlCacheDependency(System.Data.SqlClient.SqlCommand sqlCmd)
Parameters:
sqlCmd: A System.Data.SqlClient.SqlCommand that is used to create a System.Web.Caching.SqlCacheDependency object.
- public SqlCacheDependency(string databaseEntryName, string tableName)
Parameters:
databaseEntryName: The name of a database defined in the databases element of the application's Web.config file.
tableName: The name of the database table that the System.Web.Caching.SqlCacheDependency is associated with.
We must first make sure that we have the correct elements inside the web.config file. This includes the connection string element to identify our database and the caching element.
<connectionStrings>
<clear/>
<add name="ForumConnectionString"
connectionString="Data Source=REA_ANDREW-PC\SQLEXPRESSADV;Initial Catalog=Forum;
Integrated Security=true;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency pollTime="1000" enabled="true">
<databases>
<add connectionStringName="ForumConnectionString" name="ForumDB"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
If you use the method which accepts the SqlCommand as the first parameter, you have to start the SqlDependency notification system simply by calling SqlDependency.Start() inside the application_start method inside the global.asax file.
protected void Application_Start()
{
string connectionString = ConfigurationManager.ConnectionStrings["ForumConnectionString"].ConnectionString;
SqlDependency.Start(connectionString);
}
Make sure you have referenced the System.Configuration which is how you how you can reference the ConfigurationManager.
IMPORTANT NOTE: There are some restrictions which the queries you can write which you will use to grab data which you use inside the Cache. These restrictions can be viewed in their entirety here http://msdn.microsoft.com/en-us/library/ms181122.aspx.
The code for my example contains two static methods which return a list of a custom object I have made called TestObject with the static class called Tester. All I want to do in the first example is retrieve every single row from the database table. While the web application is running I will insert another record into the database table as I will then expect the cache to be invalidated and refreshed. The object which I use in the example is simply:
public class TestObject
{
public string Value;
public DateTime dateUpdated;
}
The stored procedure which I use for this first example is below in a create script. Run against your database to get the same output here.
USE [Forum]
GO
/****** Object: StoredProcedure [dbo].[GetTestData] Script Date: 07/12/2008 18:07:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetTestData]
AS
BEGIN
SELECT TestValue FROM TestTable
END
Below is the first static method which uses the constructor with the two string parameters
public static TestObject[] GetTestData()
{
System.Web.Caching.Cache o = HttpContext.Current.Cache;
//If the cached item is null we need to re-retrieve the data
if(o["TestData"] == null)
{
//create a temporary housing for the list of objects
List<TestObject> items = new List<TestObject>();
//instantiate a new connection
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ForumConnectionString"].ConnectionString))
{
connection.Open();
//For testing purposes every time the cache gets invalidated I want to know the date and time it was last invalidated
DateTime dateUpdated = DateTime.Now;
//instantiate a new SqlCommand
using (SqlCommand command = connection.CreateCommand())
{
//We reference our stored procedure here
string commandText = "GetTestData";
command.CommandText = commandText;
command.CommandType = CommandType.StoredProcedure;
//Create and execute a datareader
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TestObject obj = new TestObject();
obj.dateUpdated = dateUpdated;
obj.Value = reader["TestValue"] as string;
items.Add(obj);
}
reader.Close();
}
//Insert the cache item with the dependancy and the hope to see it work ;-)
HttpContext.Current.Cache.Insert("TestData",
items.ToArray(),
new SqlCacheDependency("ForumDB", "TestTable"),
DateTime.Now.AddDays(1D),
System.Web.Caching.Cache.NoSlidingExpiration);
}
}
}
//finally we return the object
return (TestObject[])o["TestData"];
}
Using the above we can run the app and see it in work. Below you can see that we have returned the values from the database and also filled the cache item. As long as the database table does not change we can refresh this and it will continue to retrieve the information from the cache store. If thje databse is not changed but the cache item expires, it will again fetch the information from the database.
If you now look at the image below, you will see that the time has been updated following an insert into the database. I literally committed the following script to SQL:
use Forum
go
INSERT INTO TestTable (TestValue) VALUES ('A New One, Update the Cache')
The next method is actually supplying a SqlCommand to the SqlCacheDependency object. This must be attached to the command before it is executed and also we must first start the SqlDependcy. To start you must add code to your Global.asax file as shown above. To make this test I have made a stored procedure which will fetch objects from the table whose first letter begins with the character I supply as a parameter, the stored procedure is below:
USE [Forum]
GO
/****** Object: StoredProcedure [dbo].[GetTestDataBeginningWithLetter] Script Date: 07/13/2008 09:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetTestDataBeginningWithLetter]
(
@Letter char(1)
)
AS
BEGIN
SELECT dbo.TestTable.TestValue FROM dbo.TestTable WHERE SubString(dbo.TestTable.TestValue,1,1) = @Letter
END
The code which is below is using the SqlCacheDependency using the SqlCommand in the constructor.
public static TestObject[] GetTestDataBeginningWithLetter(char letter)
{
System.Web.Caching.Cache o = HttpContext.Current.Cache;
//If the cached item is null we need to re-retrieve the data
if (o["TestDataC"] == null)
{
List<TestObject> items = new List<TestObject>();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ForumConnectionString"].ConnectionString))
{
connection.Open();
//For testing purposes every time the cache gets invalidated I want to know the date and time it was last invalidated
DateTime dateUpdated = DateTime.Now;
//instantiate a new SqlCommand
using (SqlCommand command = connection.CreateCommand())
{
string commandText = "GetTestDataBeginningWithLetter";
command.CommandText = commandText;
command.CommandType = CommandType.StoredProcedure;
//Pass in the value of the parameter we want
command.Parameters.AddWithValue("@Letter", letter);
//Attach the SqlCacheDependency to the SqlCommand
SqlCacheDependency depenedency = new SqlCacheDependency(command);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TestObject obj = new TestObject();
obj.dateUpdated = dateUpdated;
obj.Value = reader["TestValue"] as string;
items.Add(obj);
}
reader.Close();
}
//Add the dependency to the Cache with the value.
HttpContext.Current.Cache.Insert("TestDataC",
items.ToArray(),
depenedency, DateTime.Now.AddDays(1D),
System.Web.Caching.Cache.NoSlidingExpiration);
}
}
}
return (TestObject[])o["TestDataC"];
}
}
Below you can see that I have run the function using the letter A to test, which does return me three test results. Because we are using a SqlCommand and not the database name, table name constructor, our cache item will not get invalidated if we insert a value like B2 or C2 etc... It will get invalidated however if we insert a value like A2 or A3 as our commands at the minute fetches records which have a first letter of A. To test this I shall commit two records to the database, the first being B2 and the second being A2. It is after the second which I am expecting to see the cache invalidate and the new values and date displayed.
Commit the following:
use Forum
go
INSERT INTO TestTable (TestValue) VALUES ('B2')
And the data has not changed.
Commit the following:
use Forum
go
INSERT INTO TestTable (TestValue) VALUES ('A2')
And notice how the values and time have updated after the change notification.
There is lots of advice about when not to use caching, I.e. if the cache is going to get invalidated every 2-3hits then it does not actually seem worth it. Rules about when to cache and how long to cache if balanced will be very powerful. A gauge of how much data is currently cached specific to one application would also be kind of handy. I have no doubt this kind of gauge is out but I have not found nor am aware of it yet.
Hope this helps some people!
Cheers,
Andrew