将Mysql数据填充到asp日历需要太长时间才能加载 [英] Populating Mysql Data to asp calendar takes too long to load
本文介绍了将Mysql数据填充到asp日历需要太长时间才能加载的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在为员工创建一个假期日历，为此，我要使用dataset在日历中填充一些数据，但是加载数据花费的时间太长.
I'm creating a leave calendar for employees, And for that I'm populating some data onto the calendar using dataset, but it takes too long to load the data.
我正在使用多个MySqlDataReader和connections从MySql表中读取日历表每一行的数据.也许使用多个连接和读取器可能是导致速度变慢的原因，但我不确定.以下是我用来填充数据的代码.
I'm using multiple MySqlDataReader and connections to read the data from MySql table for each row of the calendar table. Maybe using multiple connections and readers might be the cause of slowing down but I'm not sure. The below is the code I use to populate the data.
class Sample
{
public DateTime Date { get; set; }
public string SlotAvailable { get; set; }
public string Pending { get; set; }
public string HeadCount { get; set; }
}
DateTime firstDate { get; set; }
DateTime lastDate { get; set; }
List<Sample> samples = new List<Sample>();
protected DataSet dsleaveplanner;
protected void FillLeaveplannerDataset()
{
cal2.VisibleDate = cal2.TodaysDate;
DateTime firstDate = new DateTime(cal2.VisibleDate.Year, cal2.VisibleDate.Month, 1).AddDays(-6);
DateTime lastDate = new DateTime(cal2.VisibleDate.Date.AddMonths(1).Year, cal2.VisibleDate.Date.AddMonths(1).Month, 1).AddDays(7);
dsleaveplanner = GetCurrentMonthData(firstDate, lastDate);
}
protected DateTime GetFirstDayOfNextMonth()
{
int monthNumber, yearNumber;
if (cal2.VisibleDate.Month == 12)
{
monthNumber = 1;
yearNumber = cal2.VisibleDate.Year + 1;
}
else
{
monthNumber = cal2.VisibleDate.Month + 1;
yearNumber = cal2.VisibleDate.Year;
}
DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
return lastDate;
}
protected DataSet GetCurrentMonthData(DateTime firstDate, DateTime lastDate)
{
string site = lblsite.Text;
string skill = lblskill.Text;
string shift = lblshift.Text;
DataSet dsMonth = new DataSet();
string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
MySqlConnection con = new MySqlConnection(MyConString);
string caldate = "Select * From setshrinkage Where date >= @firstDate And date <= @lastDate And site=@site And skill=@skill And shift=@shift Group By date";
MySqlCommand cmd = new MySqlCommand(caldate, con);
cmd.Parameters.AddWithValue("@firstDate", firstDate);
cmd.Parameters.AddWithValue("@lastDate", lastDate);
cmd.Parameters.AddWithValue("@site", site);
cmd.Parameters.AddWithValue("@skill", skill);
cmd.Parameters.AddWithValue("@shift", shift);
MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(cmd);
try
{
mysqlDataAdapter.Fill(dsMonth);
con.Close();
}
catch { }
return dsMonth;
}
public void caldisp(DayRenderEventArgs e)
{
Environment.NewLine.ToString();
e.Cell.ForeColor = System.Drawing.Color.Red;
e.Cell.Font.Size = 9;
e.Cell.Controls.Add(new LiteralControl("<p></p>Slot available:"));
e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().SlotAvailable.ToString()));
e.Cell.Controls.Add(new LiteralControl("<p></p>Pending:"));
e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().Pending.ToString()));
}
protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
DateTime nextDate;
//e.Day.IsSelectable = false;
if (dsleaveplanner != null)
{
foreach (DataRow dr in dsleaveplanner.Tables[0].Rows)
{
nextDate = (DateTime)dr["date"];
var hcount = (dr["headCount"].ToString());
Int32 hcount1 = Convert.ToInt32(hcount);
string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
MySqlConnection conn = new MySqlConnection(MyConString);
string cntdate = "SELECT COUNT(date) FROM approved WHERE date = @date And site=@site And skill=@skill And shift=@shift And status=@status";
string cntdate2 = "SELECT COUNT(date) FROM approved WHERE date = @date And site=@site And skill=@skill And shift=@shift And status=@status";
MySqlCommand cmd2 = new MySqlCommand(cntdate, conn);
MySqlCommand cmd3 = new MySqlCommand(cntdate2, conn);
cmd2.Parameters.AddWithValue("@date", nextDate);
cmd2.Parameters.AddWithValue("@site", lblsite.Text);
cmd2.Parameters.AddWithValue("@skill", lblskill.Text);
cmd2.Parameters.AddWithValue("@shift", lblshift.Text);
cmd2.Parameters.AddWithValue("@status", "auto-approved");
cmd3.Parameters.AddWithValue("@date", nextDate);
cmd3.Parameters.AddWithValue("@site", lblsite.Text);
cmd3.Parameters.AddWithValue("@skill", lblskill.Text);
cmd3.Parameters.AddWithValue("@shift", lblshift.Text);
cmd3.Parameters.AddWithValue("@status", "pending");
string chklog = "SELECT date FROM approved WHERE date = @date And agentlogin=@login And status=@stat";
MySqlCommand cmd1 = new MySqlCommand(chklog, conn);
cmd1.Parameters.AddWithValue("@date", nextDate);
cmd1.Parameters.AddWithValue("@login", Label1.Text);
cmd1.Parameters.AddWithValue("@stat", "auto-approved");
conn.Open();
string count = cmd2.ExecuteScalar().ToString();
string count2 = cmd3.ExecuteScalar().ToString();
var slot2 = Convert.ToInt32(count);
Int32 slot3 = hcount1 - slot2;
string slot4 = slot3.ToString();
MySqlDataReader dr1 = cmd1.ExecuteReader();
MySqlConnection con = new MySqlConnection(MyConString);
string chklog1 = "SELECT date FROM approved WHERE date = @date And agentlogin=@login And status=@stat";
MySqlCommand cmd4 = new MySqlCommand(chklog1, con);
cmd4.Parameters.AddWithValue("@date", nextDate);
cmd4.Parameters.AddWithValue("@login", Label1.Text);
cmd4.Parameters.AddWithValue("@stat", "pending");
con.Open();
MySqlDataReader dr2 = cmd4.ExecuteReader();
MySqlConnection con2 = new MySqlConnection(MyConString);
string chklog2 = "SELECT date FROM approved WHERE date = @date And agentlogin=@login And status=@stat";
MySqlCommand cmd5 = new MySqlCommand(chklog2, con2);
cmd5.Parameters.AddWithValue("@date", nextDate);
cmd5.Parameters.AddWithValue("@login", Label1.Text);
cmd5.Parameters.AddWithValue("@stat", "rejected");
con2.Open();
MySqlDataReader dr3 = cmd5.ExecuteReader();
MySqlConnection con3 = new MySqlConnection(MyConString);
string chklog3 = "SELECT date FROM approved WHERE date = @date And agentlogin=@login And status=@stat";
MySqlCommand cmd6 = new MySqlCommand(chklog3, con3);
cmd6.Parameters.AddWithValue("@date", nextDate);
cmd6.Parameters.AddWithValue("@login", Label1.Text);
cmd6.Parameters.AddWithValue("@stat", "agent-withdrawn");
con3.Open();
MySqlDataReader dr4= cmd6.ExecuteReader();
if (nextDate == e.Day.Date)
{
if (dr1.HasRows)
{
e.Cell.BackColor = System.Drawing.Color.LightGreen;
}
else if (dr2.HasRows)
{
e.Cell.BackColor = System.Drawing.Color.Gold;
}
else if (dr3.HasRows)
{
e.Cell.BackColor = System.Drawing.Color.Tomato;
}
else if (dr4.HasRows)
{
e.Cell.BackColor = System.Drawing.Color.DarkTurquoise;
}
}
conn.Close();
con.Close();
con2.Close();
con3.Close();
samples.Add(new Sample { Date = nextDate, SlotAvailable = slot4, Pending = count2 });
}
if (samples.Any(x => x.Date == e.Day.Date))
{
string weekoff = lblweekoff.Text;
List<string> offday = (lblweekoff.Text).Split(',').ToList();
if (offday.Contains(e.Day.Date.ToString("ddd")))
{
e.Cell.Font.Size = 9;
e.Cell.Controls.Add(new LiteralControl("<p>Week-Off </p>"));
}
else
{
caldisp(e);
}
}
else
{
e.Cell.ForeColor = System.Drawing.Color.Red;
e.Cell.Font.Size = 9;
e.Cell.Controls.Add(new LiteralControl("<p>Target not set! </p>"));
}
}
}
如何使此过程更快?感谢您的帮助，谢谢！
How can I make this process faster? Any help is appreciated, Thanks in advance!
推荐答案
您在foreach语句中有6条SQL查询，如果dsleaveplanner中有10行，则程序将执行60条SQL查询.此数量的SQL查询将对性能产生负面影响.
尝试在您的foreach语句之前检索所有数据，并将数据存储到列表(内存)中，然后在您的foreach中使用它
You have 6 SQL queries into foreach statement, if you have 10 rows in dsleaveplanner the program will execute 60 SQL queries. this number of SQL queries will have a negative impact on performance.
try to retrieve all data before your foreach statement and stock data into lists (memory) then use it within your foreach
这篇关于将Mysql数据填充到asp日历需要太长时间才能加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
