SQL命令数据读取器while循环不工作
本文关键字:循环 工作 while 命令 数据 读取 SQL | 更新日期: 2023-09-27 18:07:22
我有一个关于SQL命令的问题。我似乎无法得到在"while(dr.read())"下运行的while循环。下面是我在c# Windows窗体的示例代码。谢谢你。
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM network";
MySqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string datasource = dr[1].ToString();
string datadestination = dr[2].ToString();
if (source == datasource && destination == datadestination)
{
int newcounter;
newcounter = Convert.ToInt32(dr[4]) + 1;
cmd.CommandText = "UPDATE network set counter = @nnnewcounter";
cmd.Parameters.AddWithValue("@nnnewcounter", newcounter);
}
else
{
cmd.CommandText = "INSERT INTO network(source,destination,length,counter) VALUES (@sssource,@dddestination,@lllength,@cccounter)";
cmd.Parameters.AddWithValue("@sssource", source);
cmd.Parameters.AddWithValue("@dddestination", destination);
cmd.Parameters.AddWithValue("@lllength", length);
cmd.Parameters.AddWithValue("@cccounter", 1);
}
}
您有一些问题,首先您重用相同的命令并使用不同的参数,此时您应该清除它们。也不清楚您是否正在为数据读取器打开连接。因此,我将把插入和更新操作移到SQLDataReader之外,因为这样可以使代码更易于阅读。
using (SqlConnection connection = new SqlConnection(ConnString))
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM network";
cmd.Connection.Open();
using (MySqlDataReader dr = cmd.ExecuteReader())
{
if (dr .HasRows)
{
while (dr.Read())
{
string datasource = dr[1].ToString();
string datadestination = dr[2].ToString();
if (source == datasource && destination == datadestination)
{
int newcounter;
newcounter = Convert.ToInt32(dr[4]) + 1;
Updateddos_network(newcounter);
}
else
{
Savedoss_network(source,destination, length, 1);
}
}
else
{
//No rows found
}
}
}
}
然后在同一个类的方法之外。
private void Updateddos_network(int newcounter)
{
using (SqlConnection connection = new SqlConnection(ConnString))
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "UPDATE ddos_network set counter = @nnnewcounter";
cmd.Parameters.AddWithValue("@nnnewcounter", newcounter);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
}
private void Insertddos_network(string source, string destination, int length, int counter)
{
using (SqlConnection connection = new SqlConnection(ConnString))
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO ddos_network(source,destination,length,counter) VALUES (@sssource,@dddestination,@lllength,@cccounter)";
cmd.Parameters.AddWithValue("@sssource", source);
cmd.Parameters.AddWithValue("@dddestination", destination);
cmd.Parameters.AddWithValue("@lllength", length);
cmd.Parameters.AddWithValue("@cccounter", counter);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
}
如果你要进一步重构你的代码,你可以有一个保存方法为你的ddos_network对象,然后可以用来确定你的对象是否被更新或插入基于当前对象是否有一个Id。