executereader返回一个值! [英] executereader return one value !!
问题描述
我在使用executereader时遇到问题
我有table1:
id |日期
__________
1 | 27/1/2011
2 | 28/1/2011
1 | 5/8/2011
2 | 6/9/2011
_______________
i have problem when use executereader
i have table1:
id | date
__________
1 |27/1/2011
2 |28/1/2011
1 |5/8/2011
2 |6/9/2011
_______________
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["racon"].ToString());
SqlCommand cmd = new SqlCommand("SELECT id,MAX(date) as enddate from table1 group by id", con);
当我在gridview中获得值时,我得到了这些值
id | date
___________
1 | 5/8/2011
2 | 6/9/2011
____________
当使用以下代码时..it更新了用户(1)而不更新了用户(2)注意:"datetime.now = 10/10/2011"
when I get values in gridview i get these values
id|date
___________
1 |5/8/2011
2 |6/9/2011
____________
when use following code..it updated user(1) and not updated user(2) note:"datetime.now=10/10/2011"
DateTime datenow = new DateTime();
datenow = datetime.Now;
con.Open();
SqlDataReader reader =cmd.ExecuteReader();
if (reader.Read())
{
DateTime enddte = new DateTime();
enddte = Convert.ToDateTime(reader["enddate"]);
if (DateTime.Compare(datenow,enddte)>0)
{
string id = Convert.ToString(reader["id"]);
string sql = "update table2 set type=''expired'' where id=''" + id + "''";
SqlCommand cmd1 = new SqlCommand(sql, con);
reader.Dispose();
cmd1.ExecuteNonQuery();
con.Close();
}
}
推荐答案
阅读器一次读取一条记录,因此,如果要处理阅读器返回的所有记录,则必须在环形.
Reader reads a single record at a time so if you want to handle all the records returned by the reader, you must use the Read method in a loop. Something like
while (reader.Read()) { ... }
之类的东西您正在使用if语句而不是循环.更改代码,使其看起来像这样,以便覆盖所有结果并正确清理连接,读取器和命令对象.
You are using an if statement rather than a loop. Change your code to look something like this so that you cover all of the results and clean up the connection, reader and command objects properly.
DateTime datenow = DateTime.Now;
using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["racon"].ToString()))
{
con.Open();
using(SqlCommand cmd = new SqlCommand("SELECT id,MAX(date) as enddate from table1 group by id", con))
{
using(SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
DateTime enddate = Convert.ToDateTime(reader["enddate"]);
if(DateTime.Compare(datenow,enddate)>0)
{
string id = Convert.ToString(reader["id"]);
string sql = "update table2 set type=''expired'' where id=''" + id + "''";
using(SqlCommand cmd1 = new SqlCommand(sql, con))
{
cmd1.ExecuteNonQuery();
}
}
}
}
}
}
这是Kythen回答的延续.从循环中移出该代码块.
Hi,
This is in continuation to Kythen''s answer. Move out this code block from the loop.
string sql = "update table2 set type='expired' where id='" + id + "'";
using(SqlCommand cmd1 = new SqlCommand(sql, con))
{
cmd1.ExecuteNonQuery();
}
相反,只需在循环中收集ID,然后在循环外执行查询即可.
Instead just collect the ids in the loop and execute the query outside the loop.
//have a string builder instance before the loop
StringBuilder sb=new StringBuilder();
//Inside the loop just append the ids. Take care the last id no need to append a comma.
sb.append(id); sb.append(",");
//outside the loop use like
string sql=@"UPDATE table2 SET type='expired' WHERE id IN ("+sb.ToString()+")";
然后使用nonquery执行它.这样您就不会对sql服务器产生兴趣了.
Then execute it using the nonquery. So you won''t be chatty with the sql server.
这篇关于executereader返回一个值!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!