c# Mysql 已经有一个与此连接关联的开放数据读取器
本文关键字:数据 读取 关联 连接 Mysql 有一个 | 更新日期: 2023-09-27 18:33:30
大家好,我在阅读时尝试做一些事情。我正在尝试做的是编辑刚刚读取的行。但是我得到错误。也许您有一些建议,我应该如何修复它,以使它无需退出数据读取器即可工作。PS:忽略这一点,查询是开放的SQL注入。
string select = "Select * FROM ivykiai WHERE `Ivikio diena` MOD Periodiskumas_d = 0 AND `Ivikio diena` > 0 AND `Ivikio diena` < `Dif dien`";
MySqlCommand command = new MySqlCommand(select, cnn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
db1 = (now - Convert.ToDateTime(reader["Nuo"])).TotalDays;
MessageBox.Show(db1.ToString());
db1 = db1 - Convert.ToInt32(reader["Ivikio diena"]);
MessageBox.Show(db1.ToString());
b = Convert.ToInt32(db1) / Convert.ToInt32(reader["Periodiskumas_d"]);
MessageBox.Show(b.ToString());
a =+ Convert.ToInt32(reader["Suma"]);
MessageBox.Show(a.ToString());
a = a * b;
MessageBox.Show(a.ToString());
string prideti = "Update Lesos Set Grynieji=Grynieji + '"+ a +"'";
MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
string p = prideti_cmd.ExecuteNonQuery().ToString();
string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
MySqlCommand update_cmd = new MySqlCommand(update, cnn);
string u = update_cmd.ExecuteNonQuery().ToString();
}
reader.Close();
cnn.Close();
您不能在
while (reader.Read())
块内使用相同的连接执行prideti_cmd
和update_cmd
,并且reader
仍然打开,但是您可以在while (reader.Read())
块外和关闭reader
后执行此操作。我建议创建以下类
public class MyClass
{
public DateTime Nuo { get; set; }
public int IvikioDiena { get; set; }
public int Periodiskumas_d { get; set; }
public int Suma { get; set; }
}
并按如下方式更改您的代码
string select = "Select * FROM ivykiai WHERE `Ivikio diena` MOD Periodiskumas_d = 0 AND `Ivikio diena` > 0 AND `Ivikio diena` < `Dif dien`";
using (MySqlCommand command = new MySqlCommand(select, cnn))
{
// execute the select query and store the results to list variable
List<MyClass> list = new List<MyClass>();
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MyClass record = new MyClass();
record.Nuo = Convert.ToDateTime(reader["Nuo"]);
record.IvikioDiena = Convert.ToInt32(reader["Ivikio diena"]);
record.Periodiskumas_d = Convert.ToInt32(reader["Periodiskumas_d"]);
record.Suma = Convert.ToInt32(reader["Suma"]);
list.Add(record);
}
}
// enumerate list and execute both the update queries
foreach (var record in list)
{
db1 = (now - record.Nuo).TotalDays;
MessageBox.Show(db1.ToString());
db1 = db1 - record.IvikioDiena;
MessageBox.Show(db1.ToString());
b = Convert.ToInt32(db1) / record.Periodiskumas_d;
MessageBox.Show(b.ToString());
a =+ record.Suma;
MessageBox.Show(a.ToString());
a = a * b;
MessageBox.Show(a.ToString());
string prideti = "Update Lesos Set Grynieji=Grynieji + '"+ a +"'";
MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
string p = prideti_cmd.ExecuteNonQuery().ToString();
string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
MySqlCommand update_cmd = new MySqlCommand(update, cnn);
string u = update_cmd.ExecuteNonQuery().ToString();
}
}
通常,您只能有一个活动命令 - SQL Server MARS 有点例外。
因此,当连接具有打开的阅读器时,您不能使用连接。您首先需要完成阅读,然后可以更新或使用其他连接,这会让您陷入事务隔离麻烦。
试试这个:
using (MySqlConnection cnn = new MySqlConnection(dbConnectionString))
{
cnn.Open();
MySqlCommand command = new MySqlCommand(select, cnn);
using (MySqlDataReader reader = command.ExecuteReader())
{
db1 = (now - Convert.ToDateTime(reader["Nuo"])).TotalDays;
MessageBox.Show(db1.ToString());
db1 = db1 - Convert.ToInt32(reader["Ivikio diena"]);
MessageBox.Show(db1.ToString());
b = Convert.ToInt32(db1) / Convert.ToInt32(reader["Periodiskumas_d"]);
MessageBox.Show(b.ToString());
a = +Convert.ToInt32(reader["Suma"]);
MessageBox.Show(a.ToString());
a = a * b;
MessageBox.Show(a.ToString());
}
string prideti = "Update Lesos Set Grynieji=Grynieji + '" + a + "'";
MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
string p = prideti_cmd.ExecuteNonQuery().ToString();
string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
MySqlCommand update_cmd = new MySqlCommand(update, cnn);
string u = update_cmd.ExecuteNonQuery().ToString();
}
ExecuteNonQuery() 所需的所有变量都是在读取数据时设置的,因此您可以在 MySqlDataReader.ExecuteReader() 函数之外使用它们。