代码:SqlConnection cnSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ESMS"].ToString());
cnSql.Open();
SqlCommand cm=new SqlCommand("select id from customer",cnSql);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
string sql = "update customer set checkamt=100 where checkamt<100 and id='" + dr[0].ToString() + "'";
SqlCommand cm2 = new SqlCommand(sql, cnSql);
cm2.ExecuteNonQuery();
}
dr.Close();
cnSql.Close(); //执行cm2.ExecuteNonQuery(); 时出错:当前已有sqldatareader没有关闭。
难道一个connection只能创建一个sqlcommand?
如果这样,除了创建多个connection,还有别的办法吗?
注:我知道通过SQL语句完成不需要运行这个循环,但我为了说明问题,故意这样写的语句。
cnSql.Open();
SqlCommand cm=new SqlCommand("select id from customer",cnSql);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
string sql = "update customer set checkamt=100 where checkamt<100 and id='" + dr[0].ToString() + "'";
SqlCommand cm2 = new SqlCommand(sql, cnSql);
cm2.ExecuteNonQuery();
}
dr.Close();
cnSql.Close(); //执行cm2.ExecuteNonQuery(); 时出错:当前已有sqldatareader没有关闭。
难道一个connection只能创建一个sqlcommand?
如果这样,除了创建多个connection,还有别的办法吗?
注:我知道通过SQL语句完成不需要运行这个循环,但我为了说明问题,故意这样写的语句。
所以不能用dr.Close();
cnSql.Close();
人为关闭.他会在引用他的页面关闭后释放!
SqlConnection cnSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ESMS"].ConnectionString);
SqlCommand cm = new SqlCommand();
cm.Connection = cnSql;
cnSql.Open();
cm.CommandText = "select id from customer";
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Response.Write(dr["name"].ToString());
} cm.CommandText = "update customer set checkamt=100 where checkamt <100 and id='" + dr[0].ToString() + "'";
cm.ExecuteNonQuery(); dr.Close();
cnSql.Close();
应该是这样:
SqlConnection cnSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ESMS"].ConnectionString);
SqlCommand cm = new SqlCommand();
cm.Connection = cnSql;
cnSql.Open();
cm.CommandText = "select id from customer";
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
cm.CommandText = "update customer set checkamt=100 where checkamt <100 and id='" + dr[0].ToString() + "'";
cm.ExecuteNonQuery();
} dr.Close();
cnSql.Close();
cnSql.open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("select id from customer", cnSql );
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cnSql.close();
foreach(DataRow dr int ds.Tables[0].Rows)
{
string sql = "update customer set checkamt=100 where checkamt <100
and id='" + dr[列名/索引].ToString() + "'";
SqlCommand cm2 = new SqlCommand(sql, cnSql);
cm2.ExecuteNonQuery();
}
利用DataSet断开式的特性实现
要不你用DataSet脱机访问,要不先用List把你的数据行缓存起来。要不,开两个connection