C#:已经有与此命令相关联的打开的DataReader,必须先关闭
问题描述:
String sql = "SELECT * from mybrknElements; ";
String sql2 = "";
String sWord = "" ;
String sNum = "" ;
int nWords = 0;
cnn.Close();
cnn.Open();
SqlCommand command = new SqlCommand(sql, cnn);
cnn2.Close();
cnn2.Open();
SqlDataReader drb;
drb = command.ExecuteReader();
while (drb.Read())
{
sNum = drb["ID"].ToString();
sWord = drb["Element"].ToString();
MessageBox.Show("OUTER loooop sNum = " + sNum + " sWord = " + sWord);
sql2 = "SELECT * from mybrknElements2; ";
String sWord2 = "" ;
String sNum2 = "";
SqlCommand command22 = new SqlCommand(sql2, cnn2);
SqlDataReader drcc;
drcc = command22.ExecuteReader(); //ERROR comes up after this line
while (drcc.Read())
{
sNum2 = drcc["ID"].ToString();
sWord2 = drcc["Element"].ToString();
if (Equals(sWord2,sWord2))
{
nWords = nWords + 1;
MessageBox.Show("sNum2 = " + sNum2 + " sWord2 = " + sWord2);
}
}
//---check occurances--------------
}
以上是我的代码:我已经使用2个SqlDataReaders,一个内其他 我得到的错误在while循环 结束:已经有一个打开的DataReader与此命令相关联,必须先关闭C#:已经有与此命令相关联的打开的DataReader,必须先关闭
有人可以帮忙吗? 谢谢。
答
试试这个代码,
SqlDataReader drb;
drb = command.ExecuteReader();
while (drb.Read())
{
sNum = drb["ID"].ToString();
sWord = drb["Element"].ToString();
MessageBox.Show("OUTER loooop sNum = " + sNum + " sWord = " + sWord);
sql2 = "SELECT * from mybrknElements2; ";
String sWord2 = "" ;
String sNum2 = "";
SqlCommand command22 = new SqlCommand(sql2, cnn2);
SqlDataReader drcc;
drcc = command22.ExecuteReader(); //ERROR comes up after this line
drb.Close();
while (drcc.Read())
{
sNum2 = drcc["ID"].ToString();
sWord2 = drcc["Element"].ToString();
if (Equals(sWord2,sWord2))
{
nWords = nWords + 1;
MessageBox.Show("sNum2 = " + sNum2 + " sWord2 = " + sWord2);
}
}
drcc.Close();
}
答
你应该停止重用你的命令和连接对象,并妥善一旦他们已经用他们的处置。重用对象没有任何好处(.NET会通过连接池重复使用打开的套接字到数据库,即使对于不同的连接对象也是如此),并且这只会导致类似的问题。
所以不重用对象,和那些处置您正确的代码可能最终会是这样的:
string sql = "SELECT ID, Element FROM mybrknElements; ";
string sql2 = "SELECT ID, Element FROM mybrknElements2;";
int nWords = 0;
string connectionString = "Your Connection String";
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(sql1, connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
{
string sWord = reader.GetString(0);
string sNum = reader.GetString(1);
using (var connection2 = new SqlConnection(connectionString))
using (var command2 = new SqlCommand(sql2, connection2))
{
connection2.Open();
using (var reader2 = command2.ExecuteReader())
{
string sWord2 = reader2.GetString(0);
string sNum2 = reader2.GetString(1);
if (Equals(sWord1,sWord2))
{
nWords++;
}
}
}
}
}
注:在每个数据读取器中,您只使用两列,因此我已将这些列添加到每个SQL语句的选择列表中,这样就不会不必要地检索永远不会使用的数据。
整个循环令我毫无意义和低效虽然,假设整个目的是让nWords
了正确的号码,你可以做到这一切在SQL:
SELECT nWords = COUNT(*)
FROM mybrknElements AS e
INNER JOIN mybrknElements AS e2
ON e2.ID = e.ID;
不相关的错误,但你的意思是让'if(Equals(sWord2,sWord2))'?我想你会想'if(Equals(sWord1,sWord2))' – Michael
同时你只能打开1个DataReader –
你不能在一个连接上有多于一个打开的数据读取器。 –