c# - 当 ExecuteScalar 工作正常时，为什么 ExecuteReader 会崩溃读取某些记录的特定数据字段？

Question

我有一个使用 C# 和 .NET 4.0 的 ASP.NET 网络表单站点。

我创建了一个类，用于将信息从 SQL 表加载到对象中，一段时间以来一直运行良好。最近，一些特定的记录使我用来填充此类的 SqlDataReader 崩溃，并出现数据库超时错误。我找不到任何理由让这些记录让读者崩溃。

我已将崩溃隔离到 [Address] int 类型的数据字段，当从查询中排除该数据字段时，读取器可以正常工作。我检查了数据库，存储的值没有异常，将它们更改为 0、null 或其他工作数据，仍然会导致超时错误。如果我使用 ExecuteScalar() 调用字段，数据会正确填充而不会出错。

是什么导致了这种行为？

错误内容如下:

Server Error in '/' Application.

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

来源方法

    public void Populate(Guid UserId)
    {
        DAL db = new DAL();
        using (SqlConnection con = db.GetConnection())
        {
            SqlCommand RowCount = new SqlCommand("SELECT COUNT([UserId]) FROM aspnet_Membership WHERE [UserId]=@UserId", con);
            RowCount.Parameters.Add(new SqlParameter("@UserId", SqlDbType.UniqueIdentifier));
            RowCount.Parameters["@UserId"].Value = UserId;

            SqlDataReader rdr = null;
            string sqlQuery = "SELECT [ApplicationId],[UserId],[Password],[PasswordFormat],[PasswordSalt],[MobilePIN],"+
                "[Email],[LoweredEmail],[PasswordQuestion],[PasswordAnswer],[IsApproved],[IsLockedOut],[CreateDate],[LastLoginDate],[LastPasswordChangedDate],[LastLockOutDate]," +
                "[FailedPasswordAttemptCount],[FailedPasswordAttemptWindowStart],[FailedPasswordAnswerAttemptCount],[FailedPasswordAnswerAttemptWindowStart],[Comment]," +
                "[FirstName],[LastName],[Address] FROM aspnet_Membership WHERE [UserId]=@UserId";
            SqlCommand sqlCommand = new SqlCommand(sqlQuery, con);
            sqlCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.UniqueIdentifier));
            sqlCommand.Parameters["@UserId"].Value = UserId;
            try
            {
                con.Open();
                int Rows = (int)RowCount.ExecuteScalar();
                if (Rows > 0)
                {
                    rdr = sqlCommand.ExecuteReader();
                    while (rdr.Read())                        
                    {
                        this.ApplicationId  = (Guid)rdr["ApplicationID"];
                        this.UserId = (Guid)rdr["UserId"];
                        this.Password = common.Coalesce(rdr["Password"], "");
                        this.PasswordFormat = common.Coalesce(rdr["PasswordFormat"], 0);
                        this.PasswordSalt = common.Coalesce(rdr["PasswordSalt"], "");
                        this.MobilePIN = common.Coalesce(rdr["MobilePIN"], "");
                        this.Email = common.Coalesce(rdr["Email"], "");
                        this.LoweredEmail = common.Coalesce(rdr["LoweredEmail"], "");
                        this.PasswordQuestion = common.Coalesce(rdr["PasswordQuestion"], "");
                        this.PasswordAnswer = common.Coalesce(rdr["PasswordAnswer"], "");
                        this.IsApproved = (bool)rdr["IsApproved"];
                        this.IsLockedOut = (bool)rdr["IsLockedOut"];
                        this.CreateDate = common.Coalesce(rdr["CreateDate"], DateTime.Now);
                        this.LastLoginDate = common.Coalesce(rdr["LastLoginDate"], DateTime.Now);
                        this.LastPasswordChangedDate = common.Coalesce(rdr["LastPasswordChangedDate"], DateTime.Now);
                        this.LastLockOutDate = common.Coalesce(rdr["LastLockOutDate"], DateTime.Now);
                        this.FailedPasswordAttemptCount = common.Coalesce(rdr["FailedPasswordAttemptCount"], 0);
                        this.FailedPasswordAttemptWindowStart = common.Coalesce(rdr["FailedPasswordAttemptWindowStart"], DateTime.Now);
                        this.FailedPasswordAnswerAttemptCount = common.Coalesce(rdr["FailedPasswordAnswerAttemptCount"], 0);
                        this.FailedPasswordAnswerAttemptWindowStart = common.Coalesce(rdr["FailedPasswordAnswerAttemptWindowStart"], DateTime.Now);
                        this.Comment = common.Coalesce(rdr["Comment"], "");
                        this.FirstName = common.Coalesce(rdr["FirstName"], "");
                        this.LastName = common.Coalesce(rdr["LastName"], "");
                        this.Address = common.Coalesce(rdr["Address"], 0);
                    }
                    rdr.Close();
                }
                con.Close();
            }
            catch (Exception ex) { if (con != null) { con.Close(); } throw ex; }
            finally { if (con != null) { con.Close(); } }
        }
    }

修改后的方法不报错

    public void Populate(Guid UserId)
    {
        DAL db = new DAL();
        using (SqlConnection con = db.GetConnection())
        {
            SqlCommand RowCount = new SqlCommand("SELECT COUNT([UserId]) FROM aspnet_Membership WHERE [UserId]=@UserId", con);
            RowCount.Parameters.Add(new SqlParameter("@UserId", SqlDbType.UniqueIdentifier));
            RowCount.Parameters["@UserId"].Value = UserId;

            SqlDataReader rdr = null;
            string sqlQuery = "SELECT [ApplicationId],[UserId],[Password],[PasswordFormat],[PasswordSalt],[MobilePIN],"+
                "[Email],[LoweredEmail],[PasswordQuestion],[PasswordAnswer],[IsApproved],[IsLockedOut],[CreateDate],[LastLoginDate],[LastPasswordChangedDate],[LastLockOutDate]," +
                "[FailedPasswordAttemptCount],[FailedPasswordAttemptWindowStart],[FailedPasswordAnswerAttemptCount],[FailedPasswordAnswerAttemptWindowStart],[Comment]," +
                "[FirstName],[LastName] FROM aspnet_Membership WHERE [UserId]=@UserId";
            SqlCommand sqlCommand = new SqlCommand(sqlQuery, con);
            sqlCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.UniqueIdentifier));
            sqlCommand.Parameters["@UserId"].Value = UserId;
            try
            {
                con.Open();
                int Rows = (int)RowCount.ExecuteScalar();
                if (Rows > 0)
                {
                    rdr = sqlCommand.ExecuteReader();
                    while (rdr.Read())                        
                    {
                        this.ApplicationId  = (Guid)rdr["ApplicationID"];
                        this.UserId = (Guid)rdr["UserId"];
                        this.Password = common.Coalesce(rdr["Password"], "");
                        this.PasswordFormat = common.Coalesce(rdr["PasswordFormat"], 0);
                        this.PasswordSalt = common.Coalesce(rdr["PasswordSalt"], "");
                        this.MobilePIN = common.Coalesce(rdr["MobilePIN"], "");
                        this.Email = common.Coalesce(rdr["Email"], "");
                        this.LoweredEmail = common.Coalesce(rdr["LoweredEmail"], "");
                        this.PasswordQuestion = common.Coalesce(rdr["PasswordQuestion"], "");
                        this.PasswordAnswer = common.Coalesce(rdr["PasswordAnswer"], "");
                        this.IsApproved = (bool)rdr["IsApproved"];
                        this.IsLockedOut = (bool)rdr["IsLockedOut"];
                        this.CreateDate = common.Coalesce(rdr["CreateDate"], DateTime.Now);
                        this.LastLoginDate = common.Coalesce(rdr["LastLoginDate"], DateTime.Now);
                        this.LastPasswordChangedDate = common.Coalesce(rdr["LastPasswordChangedDate"], DateTime.Now);
                        this.LastLockOutDate = common.Coalesce(rdr["LastLockOutDate"], DateTime.Now);
                        this.FailedPasswordAttemptCount = common.Coalesce(rdr["FailedPasswordAttemptCount"], 0);
                        this.FailedPasswordAttemptWindowStart = common.Coalesce(rdr["FailedPasswordAttemptWindowStart"], DateTime.Now);
                        this.FailedPasswordAnswerAttemptCount = common.Coalesce(rdr["FailedPasswordAnswerAttemptCount"], 0);
                        this.FailedPasswordAnswerAttemptWindowStart = common.Coalesce(rdr["FailedPasswordAnswerAttemptWindowStart"], DateTime.Now);
                        this.Comment = common.Coalesce(rdr["Comment"], "");
                        this.FirstName = common.Coalesce(rdr["FirstName"], "");
                        this.LastName = common.Coalesce(rdr["LastName"], "");
                    }
                    rdr.Close();

                    string sqlQuery2 = "SELECT [Address] FROM aspnet_Membership WHERE [UserId]=@UserId";
                    SqlCommand sqlCommand2 = new SqlCommand(sqlQuery2, con);
                    sqlCommand2.Parameters.Add(new SqlParameter("@UserId", SqlDbType.UniqueIdentifier));
                    sqlCommand2.Parameters["@UserId"].Value = UserId;
                    this.Address = common.Coalesce(sqlCommand2.ExecuteScalar(), 0);
                }
                con.Close();

            }
            catch (Exception ex) { if (con != null) { con.Close(); } throw ex; }
            finally { if (con != null) { con.Close(); } }
        }

    }

Answer 1

您应该向我们展示比这更多的代码，我认为这只是一个间接错误。

通常打开已经打开的连接或关闭已经关闭的连接会导致无效操作异常，这就是您在这里所做的。

try{
    con.Open();
    //do something
    con.Close(); //will be closed when no error was raised
}catch (Exception ex){
    if (con != null){
        // this will close if the error was raised in "do something"
        con.Close(); 
    } 
    throw ex; // you better thow the exception by throw (instead of throw ex) to keep the stacktrace
}finally {
    if (con != null) {
        // this will definitely cause an Invalid Operation Exception since the connection was already closed
        con.Close(); 
    }
}

您应该改为使用 using-statement隐含地关闭和处理连接。如果你想手动关闭它，你还应该检查它的 ConnectionState :

if (con != null && con.State != System.Data.ConnectionState.Closed){con.Close();}

使用 using 语句规避此问题的示例:

try{
    using (var con = new System.Data.SqlClient.SqlConnection(conString)) { 
        using(var cmd = new System.Data.SqlClient.SqlCommand(command, con)){
            con.open();
            var reader = cmd.ExecuteReader();
            while (reader.Read()) { 
                //do something
            }
        }
    }//will automatically close connection
}
catch (Exception ex) { 
    //log exception and/or throw
    throw;
}