AJAX Timer And GridView Control Using SqlDependency

Hi Coder,

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack == true)
{
tmTime.Interval = 100000;
Session["Flag"] = false;

connstr = "Data Source=NIHONGOW2003;Initial Catalog=DataWatcher;User ID=testlogin;pwd=testlogin";
string ssql = "select Id,Name from dbo.tbl_P ";
SqlDependency.Stop(connstr);
SqlDependency.Start(connstr);
if (connection == null)
connection = new SqlConnection(connstr);
if (command == null)
command = new SqlCommand(ssql, connection);

if (myDataSet == null)
myDataSet = new DataSet();
myDataSet.Clear();
command.Notification = null;

//Label1.Text= System.DateTime.Now.ToString();
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
adapter = new SqlDataAdapter(command);
adapter.Fill(myDataSet, "dbo.tbl_P");
GridView1.DataSource = myDataSet;
GridView1.DataMember = "dbo.tbl_P";
GridView1.DataBind();

}
}

private DataSet myDataSet = null;
private SqlConnection connection = null;
private SqlCommand command = null;

private string connstr;
SqlDataAdapter adapter;

SqlDependency dependency;
protected void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
Session["Flag"] = true;
tmTime.Interval = 1000;
UpdatePanel1.Update();
tmTime_Tick(sender, e);
//tmTime.Enabled = true;

}
protected void tmTime_Tick(object sender, EventArgs e)
{


if (Convert.ToBoolean(Session["Flag"]) == true)
{
tmTime.Interval = 4000;
connstr = "Data Source=NIHONGOW2003;Initial Catalog=DataWatcher;User ID=testlogin;pwd=testlogin";
string ssql = "select Id,Name from dbo.tbl_P ";
if (connection == null)
connection = new SqlConnection(connstr);
if (command == null)
command = new SqlCommand(ssql, connection);

if (myDataSet == null)
myDataSet = new DataSet();
myDataSet.Clear();
command.Notification = null;
//Label1.Text= System.DateTime.Now.ToString();
adapter = new SqlDataAdapter(command);
adapter.Fill(myDataSet, "dbo.tbl_P");
GridView1.DataSource = myDataSet;
GridView1.DataMember = "dbo.tbl_P";
GridView1.DataBind();
Session["Flag"] = false;
}
else
{
timer.Interval = 1000;
}

Session["Flag"] = false;
}

Now The Problem Is That,In every timer interval the page_load get called.First time when page get load so the grid will bind with data and after timer interval change the page load again called and page is displaying nothing.Now when i changed somthing in database so it will again rebind the grid and show the data.So this logic is going well.

I want grid will always displays and when data changes in databses grid will rebind.

Please help me.

I know it's not exactly what you're asking, but you might try doing it this way: http://encosia.com/2007/07/25/display-data-updates-in-real-time-with-ajax/

It's alot faster than using a Timer.

Hi ,

I have used your giving link and trying to access current data in grid.It is not giving me the fresh data from database.You know in my application i can see fresh data from database but my problem is only that when timer again going to the Page_Load so it will display nothing if there are changes in database so it will again display the grid.

Can yoy please give me any idea to display grid even timer goes to the page_load again and display data,

Thanks for your reply.

Hi,

It's necessary to bind the GridView upon each request. Otherwise, you will not see anything.

If your intention is to avoid the flicker, the best solution is to place the GridView inside a UpdatePanel, and set the Timer to be the trigger for it.