Calendar display of information from database too slow

[Mark Schaver]

I'm using the calendar control to display information from a SQL
Server database. I use a stored procedure to execute seven select
statements, then use a datareader to display the information on the
calendar. But it takes several minutes for the calendar to load, even
though if I run the SQL for the stored procedure in query analyzer it
takes only a few seconds to complete the query. I'm wondering if
anyone has any suggestions on how I can make this calendar display
faster. (I'm only an amateur coder, I'll admit, and I've been unable
to find anything in the documentation or on the Web to help with
this.) Thanks in advance.

Here's my code:

private void calFeatures_DayRender(object sender,
System.Web.UI.WebControls.DayRenderEventArgs e)
{
SqlCommand sqlCmd = new SqlCommand("stp_featurecal", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

System.Data.SqlClient.SqlDataReader DayReader;
sqlConn.Open();
DayReader = sqlCmd.ExecuteReader();
string theDate;

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();
aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - A1";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{

Label aLabel = new Label();
aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - FF";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();

aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - H&F";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();

aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - WEx";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();

aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - Scene";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();

aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - H&G";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.NextResult();

if (DayReader.Read())
{
while (DayReader.Read())
{
theDate = DayReader[1].ToString();
if (theDate == e.Day.Date.ToString())
{
Label aLabel = new Label();

aLabel.Text = "<br><strong><a href=update.asp?ID=" +
DayReader[0] + ">" + DayReader[2] + "</a></strong> - " + DayReader[3]
+ " - A&L";
e.Cell.Controls.Add(aLabel);
}
}
}

DayReader.Close();
sqlConn.Close();

}

 

[Scott Mitchell [MVP]]

I'm using the calendar control to display information from a SQL

Server database. I use a stored procedure to execute seven select
statements, then use a datareader to display the information on the
calendar. But it takes several minutes for the calendar to load, even
though if I run the SQL for the stored procedure in query analyzer it
takes only a few seconds to complete the query.

Mark, I'd wager the problem is because the DayRender event fires for
every day created by the calendar, so you are running the stored
procedure like 35 times!

What you want to do is run a procedure ONCE that gets ALL of the events
for the selected month, ordered by the event date ascending. You can
store this in a DataTable or DataSet.

Then, in the DayRender event handler you can check to see if the current
day matches up to the current event in the DataTable you are looking at.
If it does, you display the event information and move ahead in the
DataTable until you reach a record for a future date.

This way, you have just one SQL access for the entire page. Hope this
helps...

--

Scott Mitchell
(e-mail address removed)
http://www.4GuysFromRolla.com
http://www.ASPFAQs.com
http://www.ASPMessageboard.com

* When you think ASP, think 4GuysFromRolla.com!

 

[Mark Schaver]

As I said, I am an amateur ;-) (who has been trying to teach himself by
reading your site and your books, by the way. You do great work).

Thanks, that helps a lot. I didn't realize I was firing the stored
procedure that many times. I had thought it was only firing once. Live
and learn...