3.5 minutes and your Web Application is 20 times more faster and responsive!

Caching is one of the tough but essential tasks faced by today’s high end web applications. If you have ever developed or currently developing an e-commerce site you then you may already faced by this question that “Is there any way to make sure that my cached data is 100% synchronized with my database, without polling to database again and again?” Yes, there is a way and you can achieve it by using SQL Server Service Broker mechanism and it is very easy to implement also, in fact you can do it in less than 5 minutes!

Start your stop watch now!

Step 1: Create a new web application in Visual Studio 2005 —30 seconds

Step 2: Add Web.Config file — 20 seconds

Step 3: Add connection string to your database server in web.config as follows: —20 seconds

Change database and server name as appropriate

<connectionStrings>

<add name=“dbConn“ connectionString = “server =.; integrated security = true;database=AdventureWorks; persist security info=true“ providerName=“system.data.sqlclient“/>

</connectionStrings>

Step 4: Add Global.asax — 20 seconds

Step 5: Paste following code for Application Start and End — 20 seconds

void Application_Start(object sender, EventArgs e)

{

//Retreive Connection String

string connectionString =

System.Configuration.ConfigurationManager.

ConnectionStrings["dbConn"].ConnectionString;

//Connect SQL Dependency

System.Data.SqlClient.SqlDependency.Start(connectionString);

}

void Application_End(object sender, EventArgs e)

{

//Retreive Connection String

string connectionString =

System.Configuration.ConfigurationManager.

ConnectionStrings["dbConn"].ConnectionString;

//Disconnect SQL Dependency

System.Data.SqlClient.SqlDependency.Stop(connectionString);

}

 

Step 6: Open Default.aspx and add following 3 controls —50 seconds

GridView — ID= GridView1

TextBox — ID=txtCacheDateTime

Button —-ID=btnLoad

 

 

Step 7: Create the following function in Default.aspx.cs to retrieve the data from database — 30 seconds

private DataTable GetProductList()

{

string connectionString =

ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;

DataTable ProductListData = new DataTable();

using (SqlConnection connection =

new SqlConnection(connectionString))

{

//Data of this command will be cached

SqlCommand command = new SqlCommand(“Select ProductId,Name from Production.Product”, connection);

//Create the necessary dependency object

SqlCacheDependency new_dependency = new SqlCacheDependency(command);

SqlDataAdapter DA1 = new SqlDataAdapter();

DA1.SelectCommand = command;

DataSet DS1 = new DataSet();

//Fill the data set

DA1.Fill(DS1);

ProductListData = DS1.Tables[0];

//Cache the result in ASP.NET Page Cache

Cache.Insert(“ProductList”, ProductListData, new_dependency);

}

//return the data set

return ProductListData;

}

 

 

Step 8: Double click the btnLoad and paste the following code as click handler — 20 seconds

 

protected void btnLoad_Click(object sender, EventArgs e)

{

//Retreive the Cached data

DataTable ProductListData = (DataTable)Cache.Get(“ProductList”);

//If data is not cached yet then get it from database

if (ProductListData == null)

{

ProductListData = GetProductList();

txtCacheDateTime.Text = System.DateTime.Now.ToString();

}

//Display Data

GridView1.DataSource = ProductListData.DefaultView;

GridView1.DataBind();

}

 

Step 9: Hmm…no more steps, run the application. Click “Load” and see yourself.

Grid will be loaded with the data. Now click again and you will see that Time stamp is not changed because data is loaded from Cache directly. Now update Production.Product and click Load again, this time data will be loaded from database and your cache is now again updated. Data will be loaded from cache until your data changed or applications stops.

Enjoy!!!