Below is the code sample for Data Caching
ExDatacaching.aspx.cs
ExDatacaching.aspx.cs
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Caching;
public partial class ExDatacaching : System.Web.UI.Page
{
SqlConnection connObj = new SqlConnection();
DataSet ds = new DataSet();
SqlDataAdapter objAdapter;
CacheItemRemovedCallback onRemove;
protected void Page_Load(object sender, EventArgs e)
{
onRemove = new CacheItemRemovedCallback(this.RemovedCallback);
if (Cache["Student"] == null)
{
connObj.ConnectionString = ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString();
connObj.Open();
SqlCommand objCommand = new SqlCommand("Select * from student", connObj);
objAdapter = new SqlDataAdapter(objCommand);
objAdapter.Fill(ds);
SqlCacheDependency dep = new SqlCacheDependency(objCommand);
connObj.Close();
Cache.Insert("Student", ds, dep, Cache.NoAbsoluteExpiration, System.TimeSpan.FromMinutes(1),
CacheItemPriority.Default, onRemove);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
lblStatus.Text = "Fresh From DB";
}
else
{
GridView1.DataSource = Cache["Student"];
GridView1.DataBind();
}
}
void RemovedCallback(string key, Object value,
CacheItemRemovedReason reason)
{
Cache["Status"] = "Cache item: " + key + " value: " +
value.ToString() + " was " + reason.ToString();
}
protected void btnRemove_Click1(object sender, EventArgs e)
{
Cache.Remove("Student");
}
public void ExDatacaching_PreRender(object sender, EventArgs e)
{
// Display status.
if (Cache["Status"] != null)
lblStatus.Text = Cache["Status"].ToString();
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExDatacaching.aspx.cs" Inherits="ExDatacaching" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server" onprerender ="ExDatacaching_PreRender">
<div>
<asp:Button ID="btnRemove" runat="server" Text ="Remove" OnClick="btnRemove_Click1" />
<asp:Label ID="lblStatus" runat="server" Text ="" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Above SqlCacheDependency works only when SQLSERVER is allowed to Notify about the changes made and Application is ready to receive the notifications
For this
First write below code in global.asax file
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString());
}
void Application_End(object sender, EventArgs e)
{
// Code that runs on application shutdown
System.Data.SqlClient.SqlDependency.Stop (ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString());
}
Plus make sure Database is also configured to notify for the changes
to do that
execute below command on sqlserver
SELECT NAME, IS_BROKER_ENABLED
FROM SYS.DATABASES
If it is not enabled (IS_BROKER_ENABLED=0)execute below command to enable notification
ALTER DATABASE <database> SET ENABLE_BROKER
great article...
ReplyDelete