试执行包含几下代码的程序
string str = @"select ci.cityname,a.areaname,i.name,i.invno,c.*
from invoice2014 i, invoice_cont2014 c,area a ,city ci where i.cinvid=c.cinvid
and i.areaid=a.areaid and ci.citycode=a.citycode and i.usestatus='1' and c.etime is not null
order by
ci.cityname,a.areaname ";
SqlCls sql=new SqlCls() ;
DataTable dt = new DataTable();
dt = sql.QuerytoDT(str);
int a = dt.Rows.Count;
int i = 0;
FeeClass f ;
foreach (DataRow dr in dt.Rows)
{
i=i+1;
f = new FeeClass(Convert.ToDateTime(dr["stime"]), Convert.ToDateTime(dr["etime"]), Convert.ToDecimal(dr["fees"]), Convert.ToDateTime(dr["optime"]));
dr["pastyears"] = f.FeePastYears;
dr["thisyear"] = f.FeeThisYear;
dr["lastyears"] = f.FeeNextYears;
}
当a的值大于50万(此数为大约,此数可能应与系统内存、表的字段数、字段的类型及大小有关,对于不同的情况可能会发生变化)时,执行到dr["pastyears"] = f.FeePastYears语句时会报错。将第1条语句改成下语句:
string str = @"select ci.cityname,a.areaname,i.name,i.invno,c.*
from invoice2014 i, invoice_cont2014 c,area a ,city ci where i.cinvid=c.cinvid
and i.areaid=a.areaid and ci.citycode=a.citycode and i.usestatus='1' and c.id>= 0 and c.id<500000 and c.etime is not null
order by
ci.cityname,a.areaname ";
亦即使表的行数不大于50万,则不会报错。
由此可知要处理大于50万条数据的表格时,须分段处理,每段为50万条。