C# 使用 2 个不同的命令时出现错误“已经有一个与此命令关联的打开的 DataReader 必须先关闭”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18475195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands
提问by alejandro carnero
I have this legacy code :
我有这个遗留代码：
private void conecta()
{
if (conexao.State == ConnectionState.Closed)
conexao.Open();
}
public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
{
List<string[]> historicos = new List<string[]>();
conecta();
sql =
@"SELECT *
FROM historico_verificacao_email
WHERE nm_email = '" + email + @"'
ORDER BY dt_verificacao_email DESC, hr_verificacao_email DESC";
com = new SqlCommand(sql, conexao);
SqlDataReader dr = com.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string[] dados_historico = new string[6];
dados_historico[0] = dr["nm_email"].ToString();
dados_historico[1] = dr["dt_verificacao_email"].ToString();
dados_historico[1] = dados_historico[1].Substring(0, 10);
dados_historico[2] = dr["hr_verificacao_email"].ToString();
dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
sql =
@"SELECT COUNT(e.cd_historico_verificacao_email) QT
FROM emails_lidos e
WHERE e.cd_historico_verificacao_email =
'" + dr["cd_historico_verificacao_email"].ToString() + "'";
tipo_sql = "sele??o";
conecta();
com2 = new SqlCommand(sql, conexao);
SqlDataReader dr3 = com2.ExecuteReader();
while (dr3.Read())
{
//quantidade de emails lidos naquela verifica??o
dados_historico[4] = dr3["QT"].ToString();
}
dr3.Close();
conexao.Close();
//login
dados_historico[5] = dr["cd_login_usuario"].ToString();
historicos.Add(dados_historico);
}
dr.Close();
}
else
{
dr.Close();
}
conexao.Close();
return historicos;
}
I have created two separates commands to correct the issue, but it still continues: "There is already an open DataReader associated with this Command which must be closed first".
我创建了两个单独的命令来纠正这个问题，但它仍然继续：“已经有一个与此命令关联的打开的 DataReader，必须先关闭它”。
An additional info: the same code is working in another app.
附加信息：相同的代码正在另一个应用程序中工作。
采纳答案by Jeroen van Langen
I suggest creating an additional connection for the second command, would solve it. Try to combine both queries in one query. Create a subquery for the count.
我建议为第二个命令创建一个额外的连接，可以解决它。尝试将两个查询合并为一个查询。为计数创建一个子查询。
while (dr3.Read())
{
dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verifica??o
}
Why override the same value again and again?
为什么要一次又一次地覆盖相同的值？
if (dr3.Read())
{
dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verifica??o
}
Would be enough.
就足够了。
回答by Mauricio Gracia Gutierrez
I bet the problem is being shown in this line
我敢打赌这个问题正在显示在这一行
SqlDataReader dr3 = com2.ExecuteReader();
I suggest that you execute the first reader and do a dr.Close();and the iterate historicos, with another loop, performing the com2.ExecuteReader().
我建议您执行第一个读取器并执行 adr.Close();和 iterate historicos，以及另一个循环，执行com2.ExecuteReader().
public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
{
List<string[]> historicos = new List<string[]>();
conecta();
sql = "SELECT * FROM historico_verificacao_email WHERE nm_email = '" + email + "' ORDER BY dt_verificacao_email DESC, hr_verificacao_email DESC";
com = new SqlCommand(sql, conexao);
SqlDataReader dr = com.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string[] dados_historico = new string[6];
dados_historico[0] = dr["nm_email"].ToString();
dados_historico[1] = dr["dt_verificacao_email"].ToString();
dados_historico[1] = dados_historico[1].Substring(0, 10);
//System.Windows.Forms.MessageBox.Show(dados_historico[1]);
dados_historico[2] = dr["hr_verificacao_email"].ToString();
dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
dados_historico[5] = dr["cd_login_usuario"].ToString();
historicos.Add(dados_historico);
}
dr.Close();
sql = "SELECT COUNT(e.cd_historico_verificacao_email) QT FROM emails_lidos e WHERE e.cd_historico_verificacao_email = '" + dr["cd_historico_verificacao_email"].ToString() + "'";
tipo_sql = "sele??o";
com2 = new SqlCommand(sql, conexao);
for(int i = 0 ; i < historicos.Count() ; i++)
{
SqlDataReader dr3 = com2.ExecuteReader();
while (dr3.Read())
{
historicos[i][4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verifica??o
}
dr3.Close();
}
}
return historicos;
回答by Csaba Toth
- The optimal solution could be to try to transform your solutioninto a form where you don't need to have two readers open at a time. Ideally it could be a single query. I don't have time to do that now.
If your problem is so special that you really need to have more readers open simultaneously, and your requirements allow not older than SQL Server 2005 DB backend, then the magic word is MARS (Multiple Active Result Sets). http://msdn.microsoft.com/en-us/library/ms345109%28v=SQL.90%29.aspx. Bob Vale's linked topic's solution shows how to enable it: specify
MultipleActiveResultSets=truein your connection string. I just tell this as an interesting possibility, but you should rather transform your solution.- in order to avoid the mentioned SQL injection possibility, set the parameters to the SQLCommand itself instead of embedding them into the query string. The query string should only contain the references to the parameters what you pass into the SqlCommand.
- 最佳解决方案可能是尝试将您的解决方案转换为不需要同时打开两个阅读器的形式。理想情况下，它可以是单个查询。我现在没有时间这样做。
如果您的问题非常特殊，您确实需要同时打开更多读取器，并且您的要求允许不早于 SQL Server 2005 DB 后端，那么神奇的词是MARS (Multiple Active Result Sets)。http://msdn.microsoft.com/en-us/library/ms345109%28v=SQL.90%29.aspx。Bob Vale 的链接主题的解决方案展示了如何启用它：
MultipleActiveResultSets=true在您的连接字符串中指定。我只是说这是一个有趣的可能性，但您应该改变您的解决方案。- 为了避免提到的 SQL 注入可能性，请将参数设置为 SQLCommand 本身，而不是将它们嵌入到查询字符串中。查询字符串应该只包含对您传递给 SqlCommand 的参数的引用。
回答by Jeroen van Langen
Try to combine the query, it will run much faster than executing an additional query per row. Ik don't like the string[] you're using, i would create a class for holding the information.
尝试组合查询，它会比每行执行一个额外的查询运行得快得多。我不喜欢你使用的 string[]，我会创建一个类来保存信息。
public List<string[]> get_dados_historico_verificacao_email_WEB(string email)
{
List<string[]> historicos = new List<string[]>();
using (SqlConnection conexao = new SqlConnection("ConnectionString"))
{
string sql =
@"SELECT *,
( SELECT COUNT(e.cd_historico_verificacao_email)
FROM emails_lidos e
WHERE e.cd_historico_verificacao_email = a.nm_email ) QT
FROM historico_verificacao_email a
WHERE nm_email = @email
ORDER BY dt_verificacao_email DESC,
hr_verificacao_email DESC";
using (SqlCommand com = new SqlCommand(sql, conexao))
{
com.Parameters.Add("email", SqlDbType.VarChar).Value = email;
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
string[] dados_historico = new string[6];
dados_historico[0] = dr["nm_email"].ToString();
dados_historico[1] = dr["dt_verificacao_email"].ToString();
dados_historico[1] = dados_historico[1].Substring(0, 10);
//System.Windows.Forms.MessageBox.Show(dados_historico[1]);
dados_historico[2] = dr["hr_verificacao_email"].ToString();
dados_historico[3] = dr["ds_tipo_verificacao"].ToString();
dados_historico[4] = dr["QT"].ToString();
dados_historico[5] = dr["cd_login_usuario"].ToString();
historicos.Add(dados_historico);
}
}
}
return historicos;
}
Untested, but maybee gives some idea.
未经测试，但maybee 给出了一些想法。
回答by LCJ
You can get such a problem when you are two different commandson same connection - especially calling the second command in a loop. That is calling the second command for each record returned from the first command. If there are some 10,000 records returned by the first command, this issue will be more likely.
当您处于two different commands同一连接时，您可能会遇到这样的问题- 特别是在loop. 即为从第一个命令返回的每个记录调用第二个命令。如果第一个命令返回了大约 10,000 条记录，则更有可能出现此问题。
I used to avoid such a scenario by making it as a single command.. The first command returns all the required data and load it into a DataTable.
我曾经通过将其作为单个命令来避免这种情况。第一个命令返回所有必需的数据并将其加载到数据表中。
Note: MARSmay be a solution - but it can be risky and many people dislike it.
注意：MARS可能是一个解决方案 - 但它可能有风险并且很多人不喜欢它。
Reference
参考
回答by Ankit
Just add the following in your connection string:
只需在您的连接字符串中添加以下内容：
MultipleActiveResultSets=True;
回答by Rousonur Jaman
Add MultipleActiveResultSets=trueto the provider part of your connection string. See the example below:
添加MultipleActiveResultSets=true到连接字符串的提供程序部分。请参阅下面的示例：
<add name="DbContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=dbName;Persist Security Info=True;User ID=userName;Password=password;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
