获得"无效试图调用时，读者是封闭&QUOT阅读; [英] Getting "Invalid attempt to call Read when reader is closed"

问题描述

我（与pseudovalues​​为便于阅读在这里），其中第一连接返回大量的数据（数千行）的有下面的代码。读取SqlDataReader的逐一由 reader.Read（），然后打开一个新的连接来更新每一行用新值：

I've got the following code (here with pseudovalues for readability), where the first connection returns a lot of data (thousands of rows). SqlDataReader reads them one by one by the reader.Read() and then opens a new connection to update each row with new values:

using (SqlConnection conn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand("sp1", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@param1", param1);
        cmd.Connection.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                try
                {
                    string hash= utils.SHA256.Hashing((string)reader["firstRow"], saltValue);
                    using (SqlConnection conn2 = new SqlConnection(connString))
                    using (SqlCommand cmd2 = new SqlCommand("sp2", conn2))
                    {
                        cmd2.CommandType = CommandType.StoredProcedure;
                        cmd2.Parameters.AddWithValue("@param1", param1);
                        cmd2.Parameters.AddWithValue("@param2", param2);
                        cmd2.Connection.Open();
                        cmd2.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    //something
                }
            }
        }
    }

但它抛出一个错误：

[InvalidOperationException: Invalid attempt to call Read when reader is closed.]
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +640
System.Data.SqlClient.SqlDataReader.Read() +9

在开发环境中能正常工作，但这里只是有几百行。这立即引发了错误，因此它并不直接看起来像某种超时，但嘿 - 我不知道...

In development environment it works fine, but here there's only a few hundred rows. It throws the error immediately, so it doesn't directly look like some kind of timeout, but hey - I don't know...

推荐答案

不知道为什么会发生，但它的确是一个糟糕的主意，而迭代到同一个数据库的实时连接，执行查询。

Don't know why it happens, but it's really a bad idea to execute queries while iterating a live connection to the same database. Keep in mind that as long as you iterate records with a DataReader, the connection is alive.

更​​糟糕的开，然后结束的连接数千个在一个快速连续倍。仅这能带来任何数据库停机瘫痪。

Much worse is opening then closing a connection thousands of times in a quick succession. This alone can bring any database down to its knees.

更改你的逻辑，你保存在一个局部变量需要（结构并不重要）的值，则使用一个连接只有执行所有你所需要的存储过程。

Change your logic, store the values you need in a local variable (structure doesn't matter) then use one connection only to execute all the stored procedures you need.

例如：

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    List<string[]> values = new List<string[]>();
    using (SqlCommand cmd = new SqlCommand("sp1", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@param1", param1);
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                try
                {
                    string hash= utils.SHA256.Hashing((string)reader["firstRow"], saltValue);
                    string anotherValue = (string)reader["secondRow"];
                    values.Add(new string[] { hash, anotherValue });
                }
                catch (SqlException ex)
                {
                    //something
                }
            }
            reader.Close();
        }
    }

    if (values.Count > 0)
    {
        using (SqlCommand cmd2 = new SqlCommand("sp2", conn))
        {
            cmd2.CommandType = CommandType.StoredProcedure;
            cmd2.Parameters.AddWithValue("@param1", null);
            cmd2.Parameters.AddWithValue("@param2", null);
            values.ForEach(items =>
            {
                cmd2.Parameters["@param1"].Value = items[0];
                cmd2.Parameters["@param2"].Value = items[1];
                cmd2.ExecuteNonQuery();
            });
        }
    }
    conn.Close();
}

一个连接，一个命令来执行所有存储过程。真的不需要比这更多。

One connection, one command to execute all stored procedures. Really don't need more than that.

这篇关于获得"无效试图调用时，读者是封闭&QUOT阅读;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！