You don't have to query SQL every second to change the second counters in your textboxes. You should use Javascript to automatically adjust the seconds left from the start time.
The start time should only get changed when the start time in the SQL tables has been reset. You can use AJAX to update the Textboxes.
You do not have to query SQL once a second to tell when your timers have been reset. You should use a SQL cache dependency. Basically, a SQL Dependency tells you when when it's data has changed, instead of you having to ask SQL every second if anything has changed.
The workflow is as follows:
- Make initial SQL query, with a SQL Dependency object, specifying a "callback" method
- Cache the result
- Return the cached result to your pages until the SQL data has changed
- When the SQL data has changed, the SQL Dependency calls the "callback" method you defined
- Callback method clears your SQL cache, and re-queries SQL
- Repeat steps 2 - 6.
It's a little complicated if you're not used to working with Delegates and dependencies, but it's well worth the time spent because it eliminates repetitive querying.
Sql Cache Dependency Example C# ASP.Net 2.0
In your Global.aspx page, add this code:
<%@ Application Language="C#" %>
<script runat="server">
string mySqlConnection = "<<Enter your connection string here.>>";
void Application_Start(object sender, EventArgs e)
{
// Start subscribing to SQL Server 2005 Notification Services.
System.Data.SqlClient.SqlDependency.Start(mySqlConnection);
}
void Application_End(object sender, EventArgs e)
{
// Stop subscribing to SQL Server 2005 Notification Services.
System.Data.SqlClient.SqlDependency.Stop(mySqlConnection);
}
</script>
In your Default.aspx page, add this code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblCacheDisplay" runat="server" />
<br />
<asp:Label ID="lblWasQueryExecuted" runat="server" />
</div>
</form>
</body>
</html>
The Default.aspx page will display your data, and will say whether the data came from the Application Cache, or from SQL.
In your Default.aspx.cs page, add this code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
private static SqlDependency sqlDependency;
private static bool cacheIsValid = false; // cacheIsValid is set to "false" when the SQL data is changed.
private static string mySqlConnection = "<<Enter you connection string here. It MUST use a different user than the Global.aspx connection string.>>";
protected void Page_Load(object sender, EventArgs e)
{
string myCachedData = (string)HttpContext.Current.Cache.Get("myCachedData");
if (myCachedData == null || !cacheIsValid) // Remember that cached objects can be removed from the cache at any time by the garbage collector, you cannot assume that they exist!
{
myCachedData = GetMyDataFromSql();
cacheIsValid = true;
lblWasQueryExecuted.Text = "SQL was queried for this data.";
}
else
{
lblWasQueryExecuted.Text = "This data came from the Application-level cache. It should be deleted if the SQL data changes.";
}
lblCacheDisplay.Text = myCachedData;
}
public static string GetMyDataFromSql()
{
string returnSqlData = String.Empty;
string storedProcedureName = "<<Enter you stored procedure name here.>>";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.Connection = new SqlConnection(mySqlConnection);
cmd.Connection.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
returnSqlData = sdr.GetString(1);
}
// Only one Sql dependency needs to be created per application start. Since each Sql dependency keeps a connection
// to the Sql database open at all times, we want to make sure we have only one Sql dependency at any given time.
if (!cacheIsValid)
{
HttpContext.Current.Cache.Remove("myCachedData");
}
object hasSqlDependency = HttpContext.Current.Cache.Get("myCachedData");
if (hasSqlDependency == null)
{
CreateSqlDependency();
HttpContext.Current.Cache.Add("myCachedData", returnSqlData, null, DateTime.MaxValue,
TimeSpan.Zero, System.Web.Caching.CacheItemPriority.Normal, null);
}
cmd.Connection.Close();
return returnSqlData;
}
public static void SqlDependency_OnChange(Object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
// The Sql data has changed, so the current cache is out-dated. Therefore, mark it as being invalid.
cacheIsValid = false; // We do not have access to HttpContext.Current right now, so we cannot clear the cache ourselves right now. Instead, we have to mark it as being invalid, and let other code update it.
// Recreate the Sql dependency, since it disappears after sqlDependency_OnChange is called. This will keep the
// connection to the Sql database open, so we can continue to be notified if the SQL data changes.
CreateSqlDependency();
}
}
private static void CreateSqlDependency()
{
SqlConnection sqlConn = new SqlConnection(mySqlConnection);
sqlConn.Open();
// If any tables in this query are modified (data changes, table definition changes, etc.), SqlDependency_OnChange will be called.
SqlCommand cmdDependency = new SqlCommand("<<SELECT column FROM myTable>>", sqlConn);
sqlDependency = new SqlDependency(cmdDependency);
sqlDependency.OnChange += new OnChangeEventHandler(SqlDependency_OnChange);
// Even though we don't do anything with the results of this query, it still needs to be executed in order to set the Sql dependency.
// If you comment out this code, the Sql dependency will not be properly created, and SqlDependency_OnChange will never be notified of
// changes to the SQL data.
SqlDataReader objReader = cmdDependency.ExecuteReader();
objReader.Close();
sqlConn.Close();
}
}
Make the appropriate changes marked by the "<<" and ">>", and you should be good to go.
Note that the two SQL user accounts must have very specific SQL 2005 permissions. Here's how you can setup the SQL user permissions.