先有一sql有50W。怎么循环去5w,返回
public IList<ProblemCodeRawdataInfo> GetProblemCodeRawdataList()
{
IList< ProblemCodeRawdataInfo> Obj=new List< ProblemCodeRawdataInfo>();
ScheduleTaskMgr task = new ScheduleTaskMgr();
string sqlStr = "";
sqlStr += "select create_date, 'TMSS' DataSource, 'TMSS' Server, country, product_type, ";
sqlStr += "series, sub_series, model, problem_type, problem_type_desc, problem_code, problem_desc, ";
sqlStr += "mail_id case_id, reply_user engineer, subject, problem, reply , reply_mail_id ,reply_flag ";
sqlStr += "from TMSS_PROBLEM ";
sqlStr += “ WHERE create_date >= '1/1/2007 12:00:00 AM' AND create_date <= '1/11/2008 12:00:00 AM' AND product_type = 'Motherboard' "
sqlStr += " union all ";
sqlStr += "select a.create_date, 'eSupport' DataSource, ";
sqlStr += "(case a.site_id when '1000' then 'Taiwan' when '3000' then 'Europe' when '5000' then 'America' when '7000' then 'China' when 'AU' then 'AU' else 'Other' end) Server, ";
sqlStr += "a.country, a.tmss_l1_name product_type, a.tmss_l2_name series, a.tmss_l3_name sub_series, a.tmss_m_name model, ";
sqlStr += "b.problem_type, b.problem_type_desc, b.problem_code, b.problem_desc, cast(a.case_no as varchar) case_id, ";
sqlStr += "d.full_name engineer, b.subject, b.problem, c.solution reply , a.es_guid, b.problem_line_no ";
sqlStr += "from es_header a ";
sqlStr += "join es_problem b on a.es_guid = b.es_guid ";
sqlStr += "left outer join es_solution c on b.es_guid = c.es_guid and b.problem_line_no = c.problem_line_no ";
sqlStr += "left outer join user_v d on b.create_accid = d.cur_id ";
sqlStr += "WHERE a.create_date >= '1/1/2007 12:00:00 AM' AND a.create_date <= '1/11/2008 12:00:00 AM'AND a.tmss_l1_name = 'Motherboard'"; SqlConnection conn = new SqlConnection(ScheduleTaskMgr.CONN_STRING_NON_DTC);
SqlCommand cmd = new SqlCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int i = 1;
int oneBatchSize = 50000;
while (dr.Read())
{
Obj.Add(PopulateProblemCodeRawdataFromDr(dr));
Console.WriteLine(i.ToString());
i++;
}
conn.Close();
dr.Close();
dr.Dispose();
return Obj;
}
求高手,怎么在SqlDataReader dr ,循环取5w
public IList<ProblemCodeRawdataInfo> GetProblemCodeRawdataList()
{
IList< ProblemCodeRawdataInfo> Obj=new List< ProblemCodeRawdataInfo>();
ScheduleTaskMgr task = new ScheduleTaskMgr();
string sqlStr = "";
sqlStr += "select create_date, 'TMSS' DataSource, 'TMSS' Server, country, product_type, ";
sqlStr += "series, sub_series, model, problem_type, problem_type_desc, problem_code, problem_desc, ";
sqlStr += "mail_id case_id, reply_user engineer, subject, problem, reply , reply_mail_id ,reply_flag ";
sqlStr += "from TMSS_PROBLEM ";
sqlStr += “ WHERE create_date >= '1/1/2007 12:00:00 AM' AND create_date <= '1/11/2008 12:00:00 AM' AND product_type = 'Motherboard' "
sqlStr += " union all ";
sqlStr += "select a.create_date, 'eSupport' DataSource, ";
sqlStr += "(case a.site_id when '1000' then 'Taiwan' when '3000' then 'Europe' when '5000' then 'America' when '7000' then 'China' when 'AU' then 'AU' else 'Other' end) Server, ";
sqlStr += "a.country, a.tmss_l1_name product_type, a.tmss_l2_name series, a.tmss_l3_name sub_series, a.tmss_m_name model, ";
sqlStr += "b.problem_type, b.problem_type_desc, b.problem_code, b.problem_desc, cast(a.case_no as varchar) case_id, ";
sqlStr += "d.full_name engineer, b.subject, b.problem, c.solution reply , a.es_guid, b.problem_line_no ";
sqlStr += "from es_header a ";
sqlStr += "join es_problem b on a.es_guid = b.es_guid ";
sqlStr += "left outer join es_solution c on b.es_guid = c.es_guid and b.problem_line_no = c.problem_line_no ";
sqlStr += "left outer join user_v d on b.create_accid = d.cur_id ";
sqlStr += "WHERE a.create_date >= '1/1/2007 12:00:00 AM' AND a.create_date <= '1/11/2008 12:00:00 AM'AND a.tmss_l1_name = 'Motherboard'"; SqlConnection conn = new SqlConnection(ScheduleTaskMgr.CONN_STRING_NON_DTC);
SqlCommand cmd = new SqlCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int i = 1;
int oneBatchSize = 50000;
while (dr.Read())
{
Obj.Add(PopulateProblemCodeRawdataFromDr(dr));
Console.WriteLine(i.ToString());
i++;
}
conn.Close();
dr.Close();
dr.Dispose();
return Obj;
}
求高手,怎么在SqlDataReader dr ,循环取5w
解决方案 »
- Visual studio 2012怎么打包应用程序?
- display image in a full screen window?
- 讨论,做的TCP通讯程序当一个服务器负荷满足不了时,再加一个服务器程序应如何处理?
- 如何把一个picturebox的图像显示到另一个picturebox中
- 怎么设置HttpWebRequset的某个响应状态关闭
- 有一个关于winfrom的简单问题想请教一下
- 使用UDP怎么接受不到消息帮忙看看!
- c#生产的exe程序管理员运行(win7\win8等)
- 求教客户端取request的问题!在线等!!
- 如何将字符型转换为整形
- winform 邮件签名
- 命名空间“FileSetTableAdapters”中不存在类型或命名空间名称“UpFilesTableAdapter”(是缺少程序集引用吗?)怎么改正呢
{
Obj.Add(PopulateProblemCodeRawdataFromDr(dr));
Console.WriteLine(i.ToString());
i++;
if(i>oneBatchSize )
break;
}
SqlServer好像可以用系统方法来变相操作,你可以去google搜一下子
public IList <ProblemCodeRawdataInfo> GetProblemCodeRawdataList(SqlDataReader reader, int count)
{
IList < ProblemCodeRawdataInfo> Obj=new List < ProblemCodeRawdataInfo>();
if(count<=0)
return Obj;
int i = 1;
while (dr.Read())
{
if(i>count)
break;
Obj.Add(PopulateProblemCodeRawdataFromDr(dr));
Console.WriteLine(i.ToString());
i++;
}
return Obj;
}
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int oneBatchSize = 50000;
IList < ProblemCodeRawdataInfo> list1 = GetProblemCodeRawdataList(reader, oneBatchSize );
IList < ProblemCodeRawdataInfo> list2 = GetProblemCodeRawdataList(reader, oneBatchSize );
IList < ProblemCodeRawdataInfo> list3 = GetProblemCodeRawdataList(reader, oneBatchSize );
//...
conn.Close();