我用的是Datagrid空间来绑定数据,数据经过层层处理保存到泛型Dictionary<string, ArrayList>中
等于就是Datagrid读取泛型Dictionary中的数据,但是打开页面速度非常慢,大概5-6分钟读取完毕,成功显示页面。
我研究了很久很久,不知道是什么原因所以向大家询问,希望大家详细解答一下,我提供源代码,希望大家看看什么地方需要改善!!!public DataTable dt = new DataTable();//用来保存读取的数据
protected SqlCommon conn = new SqlCommon();
public string strKBH = string.Empty;
public string strDate = string.Empty;
public string strKMC = string.Empty;
public bool bTime=false;//用来判断时间是否超过当前的日期
public int iHangNum = 0;//用来保存行数
public List<string> listLie = new List<string>();//用来保存标题列
public List<string> listHeaderName = new List<string>();//用来保存标头
public Dictionary<string, List<string>> HasList = new Dictionary<string, List<string>>();//保存表内容protected void Page_Load(object sender, EventArgs e)
{
strKBH = Request.QueryString["kbh"];
strDate = Request.QueryString["date"];
strKMC= Request.QueryString["kmc"];
listHeaderName = this.getHeaderName();
HasList = this.getDateTable();
BindGrid();
}#region 获得DataGrid的标头,将表头的内容保存到泛型List<string>中
public List<string> getHeaderName()
{
List<string> listHeaderName = new List<string>();
try
{
listHeaderName.Add("时间");
string strSql = "select distinct[SensorNum] from RealTimeHistory where MineNum='" + strKBH + "'";
conn.open();
DataSet myDs = conn.ExceDS(strSql);
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
listLie.Add(myDs.Tables[0].Rows[i][0].ToString().Trim());
try
{
string sSql = "select SensorName from Sensor where MineNum='" + strKBH + "' and SensorNum='" + myDs.Tables[0].Rows[i][0].ToString().Trim() + "'";
DataSet myDss = conn.ExceDS(sSql);
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + myDss.Tables[0].Rows[0][0].ToString().Trim();
listHeaderName.Add(sName);
}
catch
{
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + "未设置";
listHeaderName.Add(sName);
}
}
}
catch (Exception e)
{
Response.Write("在取标头的时候出现错误!" + e.Message);
}
finally
{
conn.close();
}
return listHeaderName;
}
#endregion#region 建立Datagrid控件的数据源
ICollection CreateDataSource()
{
try
{
DataRow dr;
foreach (string sHeader in listHeaderName)
{
dt.Columns.Add(sHeader, typeof(string));
}
foreach (KeyValuePair<string, List<string>> kvp in HasList)
{
dr = dt.NewRow();
dr[0] = kvp.Key.ToString();
List<string> listValue = (List<string>)kvp.Value;
int i = 0;
foreach (string sValue in listValue)
{
i = i + 1;
dr[i] = sValue;
}
dt.Rows.Add(dr);
} }
catch
{
}
DataView dv = new DataView(dt);
return dv;
}
#endregion#region 获得每个时间列所对应的值
public string getHangInfo(Dictionary<string, ArrayList> oscar, string slieTime, string skey)
{
string sHang = string.Empty;
try
{
ArrayList listA = (ArrayList)oscar[skey];
for (int i = 0; i < listA.Count; i++)
{
ArrayList listB = (ArrayList)listA[i];
DateTime timeA = Convert.ToDateTime(listB[0].ToString().Trim());
DateTime timeB = Convert.ToDateTime(slieTime);
DateTime thisTime = this.TimeAdd(listB[0].ToString().Trim(), listB[3].ToString().Trim());
if ((timeB >= timeA) && (timeB <= thisTime))
{
if (bTime == true)
{
sHang = "0";
}
else
{ if (listB[1].ToString().Trim() == "0" && listB[2].ToString().Trim() == "0")
{
sHang = "关";
break;
}
else if (listB[1].ToString().Trim() == "1" && listB[2].ToString().Trim() == "0")
{
sHang = "开";
break;
}
else
{
sHang = listB[1].ToString().Trim();
break;
}
}
}
else
{
sHang = "0";
}
}
}
catch (Exception e)
{
Response.Write("在取列值的时候出现错误!" + e.Message);
}
return sHang;
}
#endregion#region 获得Datagrid整个表格中的内容,保存到泛型Dictionary<string, List<string>>中
public Dictionary<string, List<string>> getDateTable()
{
Dictionary<string, List<string>> HasDate = new Dictionary<string, List<string>>();
try
{
Dictionary<string, ArrayList> oscar = this.getHashtable();
List<string> lieTime = this.getLieTime();
foreach (string Ltime in lieTime)
{
List<string> listSensors = new List<string>();
string sKey = Ltime;
for (int i = 0; i < listLie.Count; i++)
{
string sCkey = listLie[i].ToString();
string svalue=string.Empty;
if (oscar.ContainsKey(sCkey))
{
svalue = this.getHangInfo(oscar, sKey, sCkey);
}
else
{
svalue = "无";
}
listSensors.Add(svalue);
}
HasDate.Add(sKey,listSensors);
}
}
catch(Exception e)
{
Response.Write("在取整个表格的时候出现错误!" + e.Message);
}
return HasDate;
}
#endregion#region 将1个完整的日期时间+上1个时间段(秒),最后获得1个DateTime类型的时
public DateTime TimeAdd(string strNowTime,string sTime)
{
string sDate = string.Empty;
DateTime getTime = new DateTime();
DateTime nowTime = Convert.ToDateTime(strNowTime);
TimeSpan a = new TimeSpan(nowTime.Ticks);
TimeSpan t = new TimeSpan(Convert.ToInt64(sTime) * (1000 * 10000));
TimeSpan ts = a.Add(t);
int iDay = a.Days - ts.Days;
if (iDay != 0)
{
bTime = true;
sDate = nowTime.Year.ToString() + "-" + nowTime.Month.ToString() + "-" + nowTime.Day.ToString() + " " + ts.Hours.ToString() + ":" + ts.Minutes.ToString() + ":" + ts.Seconds.ToString();
}
else
{
bTime = false;
sDate = nowTime.Year.ToString() + "-" + nowTime.Month.ToString() + "-" + nowTime.Day.ToString() + " " + ts.Hours.ToString() + ":" + ts.Minutes.ToString() + ":" + ts.Seconds.ToString();
}
getTime = Convert.ToDateTime(sDate);
return getTime;
}
#endregion#region 将指定的值,以编号为键,将内容保存到泛型Dictionary<string, ArrayList>里
public Dictionary<string, ArrayList> getHashtable()
{
Dictionary<string, ArrayList> oscar = new Dictionary<string, ArrayList>();//用来保存指定煤矿传感器的信息
try
{
string sQuerySql = "select SensorNum,TimeVal,RTData,RTStatus,ValidTime from RealTimeHistory where MineNum='" + strKBH + "' and TimeVal>='" + strDate + " 00:00:00' and TimeVal<='" + strDate + " 23:59:59' order by TimeVal";
conn.open();
DataSet myQueryDs = conn.ExceDS(sQuerySql);
conn.close();
for (int j = 0; j < myQueryDs.Tables[0].Rows.Count; j++)
{
string s1 = myQueryDs.Tables[0].Rows[j][1].ToString().Trim();
string s2 = myQueryDs.Tables[0].Rows[j][2].ToString().Trim();
string s3 = myQueryDs.Tables[0].Rows[j][3].ToString().Trim();
string s4 = myQueryDs.Tables[0].Rows[j][4].ToString().Trim();
ArrayList list1 = new ArrayList();
list1.Add(s1);
list1.Add(s2);
list1.Add(s3);
list1.Add(s4);
string strKey = myQueryDs.Tables[0].Rows[j][0].ToString().Trim();
if (oscar.ContainsKey(strKey))
{
ArrayList list3 = (ArrayList)oscar[strKey];
list3.Add(list1);
oscar.Remove(strKey);
oscar.Add(strKey, list3);
}
else
{
ArrayList list2 = new ArrayList();
list2.Add(list1);
oscar.Add(strKey, list2);
}
}
}
catch (Exception em)
{
Response.Write(em.Message);
}
return oscar;
}
#endregion#region 将数据绑定到DataGrid控件上
private void BindGrid()
{
try
{
MyDataGrid.DataSource = CreateDataSource();
MyDataGrid.DataBind();
}
catch
{
//提示错误信息
}
}
#endregion
请大家主要是看看我的代码为什么执行速度那么慢,说出原因,最好有解决方案!
希望大家解答一下,这个问题困扰了我很久,分不够可以加,只要解决我万分感谢!
等于就是Datagrid读取泛型Dictionary中的数据,但是打开页面速度非常慢,大概5-6分钟读取完毕,成功显示页面。
我研究了很久很久,不知道是什么原因所以向大家询问,希望大家详细解答一下,我提供源代码,希望大家看看什么地方需要改善!!!public DataTable dt = new DataTable();//用来保存读取的数据
protected SqlCommon conn = new SqlCommon();
public string strKBH = string.Empty;
public string strDate = string.Empty;
public string strKMC = string.Empty;
public bool bTime=false;//用来判断时间是否超过当前的日期
public int iHangNum = 0;//用来保存行数
public List<string> listLie = new List<string>();//用来保存标题列
public List<string> listHeaderName = new List<string>();//用来保存标头
public Dictionary<string, List<string>> HasList = new Dictionary<string, List<string>>();//保存表内容protected void Page_Load(object sender, EventArgs e)
{
strKBH = Request.QueryString["kbh"];
strDate = Request.QueryString["date"];
strKMC= Request.QueryString["kmc"];
listHeaderName = this.getHeaderName();
HasList = this.getDateTable();
BindGrid();
}#region 获得DataGrid的标头,将表头的内容保存到泛型List<string>中
public List<string> getHeaderName()
{
List<string> listHeaderName = new List<string>();
try
{
listHeaderName.Add("时间");
string strSql = "select distinct[SensorNum] from RealTimeHistory where MineNum='" + strKBH + "'";
conn.open();
DataSet myDs = conn.ExceDS(strSql);
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
listLie.Add(myDs.Tables[0].Rows[i][0].ToString().Trim());
try
{
string sSql = "select SensorName from Sensor where MineNum='" + strKBH + "' and SensorNum='" + myDs.Tables[0].Rows[i][0].ToString().Trim() + "'";
DataSet myDss = conn.ExceDS(sSql);
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + myDss.Tables[0].Rows[0][0].ToString().Trim();
listHeaderName.Add(sName);
}
catch
{
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + "未设置";
listHeaderName.Add(sName);
}
}
}
catch (Exception e)
{
Response.Write("在取标头的时候出现错误!" + e.Message);
}
finally
{
conn.close();
}
return listHeaderName;
}
#endregion#region 建立Datagrid控件的数据源
ICollection CreateDataSource()
{
try
{
DataRow dr;
foreach (string sHeader in listHeaderName)
{
dt.Columns.Add(sHeader, typeof(string));
}
foreach (KeyValuePair<string, List<string>> kvp in HasList)
{
dr = dt.NewRow();
dr[0] = kvp.Key.ToString();
List<string> listValue = (List<string>)kvp.Value;
int i = 0;
foreach (string sValue in listValue)
{
i = i + 1;
dr[i] = sValue;
}
dt.Rows.Add(dr);
} }
catch
{
}
DataView dv = new DataView(dt);
return dv;
}
#endregion#region 获得每个时间列所对应的值
public string getHangInfo(Dictionary<string, ArrayList> oscar, string slieTime, string skey)
{
string sHang = string.Empty;
try
{
ArrayList listA = (ArrayList)oscar[skey];
for (int i = 0; i < listA.Count; i++)
{
ArrayList listB = (ArrayList)listA[i];
DateTime timeA = Convert.ToDateTime(listB[0].ToString().Trim());
DateTime timeB = Convert.ToDateTime(slieTime);
DateTime thisTime = this.TimeAdd(listB[0].ToString().Trim(), listB[3].ToString().Trim());
if ((timeB >= timeA) && (timeB <= thisTime))
{
if (bTime == true)
{
sHang = "0";
}
else
{ if (listB[1].ToString().Trim() == "0" && listB[2].ToString().Trim() == "0")
{
sHang = "关";
break;
}
else if (listB[1].ToString().Trim() == "1" && listB[2].ToString().Trim() == "0")
{
sHang = "开";
break;
}
else
{
sHang = listB[1].ToString().Trim();
break;
}
}
}
else
{
sHang = "0";
}
}
}
catch (Exception e)
{
Response.Write("在取列值的时候出现错误!" + e.Message);
}
return sHang;
}
#endregion#region 获得Datagrid整个表格中的内容,保存到泛型Dictionary<string, List<string>>中
public Dictionary<string, List<string>> getDateTable()
{
Dictionary<string, List<string>> HasDate = new Dictionary<string, List<string>>();
try
{
Dictionary<string, ArrayList> oscar = this.getHashtable();
List<string> lieTime = this.getLieTime();
foreach (string Ltime in lieTime)
{
List<string> listSensors = new List<string>();
string sKey = Ltime;
for (int i = 0; i < listLie.Count; i++)
{
string sCkey = listLie[i].ToString();
string svalue=string.Empty;
if (oscar.ContainsKey(sCkey))
{
svalue = this.getHangInfo(oscar, sKey, sCkey);
}
else
{
svalue = "无";
}
listSensors.Add(svalue);
}
HasDate.Add(sKey,listSensors);
}
}
catch(Exception e)
{
Response.Write("在取整个表格的时候出现错误!" + e.Message);
}
return HasDate;
}
#endregion#region 将1个完整的日期时间+上1个时间段(秒),最后获得1个DateTime类型的时
public DateTime TimeAdd(string strNowTime,string sTime)
{
string sDate = string.Empty;
DateTime getTime = new DateTime();
DateTime nowTime = Convert.ToDateTime(strNowTime);
TimeSpan a = new TimeSpan(nowTime.Ticks);
TimeSpan t = new TimeSpan(Convert.ToInt64(sTime) * (1000 * 10000));
TimeSpan ts = a.Add(t);
int iDay = a.Days - ts.Days;
if (iDay != 0)
{
bTime = true;
sDate = nowTime.Year.ToString() + "-" + nowTime.Month.ToString() + "-" + nowTime.Day.ToString() + " " + ts.Hours.ToString() + ":" + ts.Minutes.ToString() + ":" + ts.Seconds.ToString();
}
else
{
bTime = false;
sDate = nowTime.Year.ToString() + "-" + nowTime.Month.ToString() + "-" + nowTime.Day.ToString() + " " + ts.Hours.ToString() + ":" + ts.Minutes.ToString() + ":" + ts.Seconds.ToString();
}
getTime = Convert.ToDateTime(sDate);
return getTime;
}
#endregion#region 将指定的值,以编号为键,将内容保存到泛型Dictionary<string, ArrayList>里
public Dictionary<string, ArrayList> getHashtable()
{
Dictionary<string, ArrayList> oscar = new Dictionary<string, ArrayList>();//用来保存指定煤矿传感器的信息
try
{
string sQuerySql = "select SensorNum,TimeVal,RTData,RTStatus,ValidTime from RealTimeHistory where MineNum='" + strKBH + "' and TimeVal>='" + strDate + " 00:00:00' and TimeVal<='" + strDate + " 23:59:59' order by TimeVal";
conn.open();
DataSet myQueryDs = conn.ExceDS(sQuerySql);
conn.close();
for (int j = 0; j < myQueryDs.Tables[0].Rows.Count; j++)
{
string s1 = myQueryDs.Tables[0].Rows[j][1].ToString().Trim();
string s2 = myQueryDs.Tables[0].Rows[j][2].ToString().Trim();
string s3 = myQueryDs.Tables[0].Rows[j][3].ToString().Trim();
string s4 = myQueryDs.Tables[0].Rows[j][4].ToString().Trim();
ArrayList list1 = new ArrayList();
list1.Add(s1);
list1.Add(s2);
list1.Add(s3);
list1.Add(s4);
string strKey = myQueryDs.Tables[0].Rows[j][0].ToString().Trim();
if (oscar.ContainsKey(strKey))
{
ArrayList list3 = (ArrayList)oscar[strKey];
list3.Add(list1);
oscar.Remove(strKey);
oscar.Add(strKey, list3);
}
else
{
ArrayList list2 = new ArrayList();
list2.Add(list1);
oscar.Add(strKey, list2);
}
}
}
catch (Exception em)
{
Response.Write(em.Message);
}
return oscar;
}
#endregion#region 将数据绑定到DataGrid控件上
private void BindGrid()
{
try
{
MyDataGrid.DataSource = CreateDataSource();
MyDataGrid.DataBind();
}
catch
{
//提示错误信息
}
}
#endregion
请大家主要是看看我的代码为什么执行速度那么慢,说出原因,最好有解决方案!
希望大家解答一下,这个问题困扰了我很久,分不够可以加,只要解决我万分感谢!
自己手写html 然后插入,有例子吗,给个可以吗?
获得每个时间列所对应的值
public string getHangInfo(Dictionary<string, ArrayList> oscar, string slieTime, string skey)
用fill又怎么来替代for?
??
list<>效率 很低? 比dataset datatable 低? 看用法的,别一口 就定死了。
狂汗~~~ 误导新人~~~
conn.open();
DataSet myDs = conn.ExceDS(strSql);
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
listLie.Add(myDs.Tables[0].Rows[i][0].ToString().Trim());
try
{
string sSql = "select SensorName from Sensor where MineNum='" + strKBH + "' and SensorNum='" + myDs.Tables[0].Rows[i][0].ToString().Trim() + "'";
DataSet myDss = conn.ExceDS(sSql);
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + myDss.Tables[0].Rows[0][0].ToString().Trim();
listHeaderName.Add(sName);
}
catch
{
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + "未设置";
listHeaderName.Add(sName);
}
}============就上面这一段代码 就进行N此数据库查询,晕哦难道不开可以直接查出来在 通过程序匹配相等?
====================================
看了楼主的代码 考虑欠佳,效率不高
(1) 从数据库A中先取出不重复的时间(1天里),作为表的第一列,也就是时间列(大概有3000多个时间)
(2) 然后在从数据库A中取出在这一天里出现的所有值(大概5-6千条)
此值包括:传入时间,编号,值,持续时间
然后用传入时间+持续时间=time,在用这个time是否小于(1)中取出的时间,小于就取值填入
如果很简单我问个屁呀,我有毛病我,哎,就没有人能真正的帮我呀,我死了......不想说了
你的具体业务需求我还不是十分清楚,按照我的理解写了一个简单的处理过程!--返回表
create table #tbl_result(
编号 int,
值 varchar(20),
时间 datetime,
传入时间 datetime,
持续时间 datetime
)--不重复时间临时表
create table #tbl_temp(时间 datetime)
insert into #tbl_temp
select 时间 from 表A
group by 时间--申明游标对时间临时表进行循环操作
declare my_cur cursor for select 时间 from #tbl_temp
open my_curdeclare @时间 datetime
fetch next from my_cur into @时间
while @@fetch_status=0
begin
--将满足条件的记录插入返回表当中去
insert into #tbl_result
select 编号,值,时间,传入时间,持续时间 from 表A
where 传入时间+持续时间<@时间
fetch next from my_cur into @时间
end
--关闭释放游标
close my_cur
deallocate my_cur
--返回结果集
select * from #tbl_result
写得很好,但是传入时间和持续时间要和不重复时间临时表得时间进行比较,才能得到最后的值,你没有看清楚我的代码的意思,不好意思哈,我想问问,
listHeaderName.Add("时间");
string strSql = "select distinct[SensorNum] from RealTimeHistory where MineNum='" + strKBH + "'";
conn.open();
DataSet myDs = conn.ExceDS(strSql);
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
listLie.Add(myDs.Tables[0].Rows[i][0].ToString().Trim());
try
{
string sSql = "select SensorName from Sensor where MineNum='" + strKBH + "' and SensorNum='" + myDs.Tables[0].Rows[i][0].ToString().Trim() + "'";
DataSet myDss = conn.ExceDS(sSql);
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + myDss.Tables[0].Rows[0][0].ToString().Trim();
listHeaderName.Add(sName);
}
catch
{
string sName = myDs.Tables[0].Rows[i][0].ToString().Trim() + "<br>" + "未设置";
listHeaderName.Add(sName);
}
}
上面两句SQL语句合成1句SQL语句,并写成存储过程,该怎么写?
不是吧,这么简单的一个SQL语句不会写,这不就两张表做内连接吗!
select s.SensorNum, s.SensorName from RealTimeHistory r inner join Sensor s on r.SensorNum=r.SensorNum group by s.SensorNum, s.SensorName