ASP.NET SqlDataReader 抛出错误：阅读器关闭时调用 Read 的尝试无效

Question

这个把我难住了。以下是相关代码：

    public AgencyDetails(Guid AgencyId)
    {
        try
        {
            evgStoredProcedure Procedure = new evgStoredProcedure();
            Hashtable commandParameters = new Hashtable();
            commandParameters.Add("@AgencyId", AgencyId);
            SqlDataReader AppReader = Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", commandParameters);
            commandParameters.Clear();

            //The following line is where the error is thrown. Errormessage: Invalid attempt to call Read when reader is closed.
            while (AppReader.Read())
            {
                AgencyName = AppReader.GetOrdinal("AgencyName").ToString();
                AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString();
                AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString();
                AgencyState = AppReader.GetOrdinal("AgencyState").ToString();
                AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString();
                AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString();
                AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString();
            }
            AppReader.Close();
            AppReader.Dispose();
        }
        catch (Exception ex)
        {
            throw new Exception("AgencyDetails Constructor: " + ex.Message.ToString());
        }
    }

以及 ExecuteReaderProcedure 的实现：

    public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters)
    {
        SqlDataReader returnReader;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                SqlParameter param = new SqlParameter();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                foreach (DictionaryEntry keyValue in Parameters)
                {
                    cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value);
                }

                conn.Open();
                returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message.ToString());
            }
        }
        return returnReader;
    }

连接字符串正常工作，而同一类中的其他存储过程运行良好。唯一的问题似乎是从该方法返回 SqlDataReaders 时！他们在标题中抛出错误消息。任何想法都将不胜感激！提前致谢！

Answer 1

DataReader 通常直接连接到数据库。在这种情况下，当您从该方法返回时，您将从创建 SqlConnetion 对象的 using 语句内部返回。这将调用 SqlConnection 上的 Dispose，并使 SqlDataReader 无用。

试试这个：

public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters)
{
    SqlConnection conn = new SqlConnection(connectionString);
    using(SqlCommand cmd = new SqlCommand(ProcedureName, conn))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        foreach(DictionaryEntry keyValue in Parameters)
        {
            cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value);
        }

        conn.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
}

这样调用：

public AgencyDetails(Guid AgencyId)
{
    evgStoredProcedure Procedure = new evgStoredProcedure();
    Hashtable commandParameters = new Hashtable();
    commandParameters.Add("@AgencyId", AgencyId);
    using(SqlDataReader AppReader = 
        Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", 
                                         commandParameters))
    {
        commandParameters.Clear();

        while(AppReader.Read())
        {
            AgencyName = AppReader.GetOrdinal("AgencyName").ToString();
            AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString();
            AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString();
            AgencyState = AppReader.GetOrdinal("AgencyState").ToString();
            AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString();
            AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString();
            AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString();
        }
    }
}

在 AppReader 的 using 语句末尾，将调用 AppReader.Dispose。由于您使用 CommandBehavior.CloseConnection 调用了 ExecuteReader，因此处置读取器也将关闭连接。

请注意，我也摆脱了你糟糕的异常处理。切勿使用 ex.Message，除非可能是为了向最终用户显示。其他人都会想要完全例外。此外，如果您允许传播完整的异常，则无需将方法名称作为异常消息的一部分打印。方法名称将出现在堆栈跟踪中。