返回正确的列数时，SqlDataReader索引超出范围异常 [英] SqlDataReader Index Out Of Range Exception when correct column count is returned

问题描述

我有一个存储过程返回正确的列数，但是下面的代码在大多数情况下都有效，但是 RANDOMLY 会在下面引发异常.我们最近已升级到.NET 4.6，此后我们注意到了该异常.

I have a stored procedure that returns correct column count, but the code below works most of the times, but RANDOMLY throws exception below. We had upgraded to .NET 4.6 recently, and we notice the exception afterwards.

问题:

1异常在何处发生，为什么发生?

1 Where and why does the exception occur?

2根据底部的源代码，SQL客户端怎么可能从服务器端接收空的metaData?

2 Based on source code at the bottom, how is it possible that SQL client receives empty metaData from server side?

存储过程 GetUser :

CREATE PROCEDURE [dbo].[GetUser]    
    @UserID int
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @UserIDChar NVARCHAR(255);
    SET @UserIDChar = convert(nvarchar(255), @UserID);

    SELECT TOP 1
        A.Value1 As FirstName, 
        A.Value2 As LastName
       -- three more columns omitted here
    FROM dbo.Activity as A      
    WHERE A.ActivityValue = @UserIDChar  --ActivityValue is NVARCHAR(255) type
    ORDER BY A.DateCreated DESC 

    SET NOCOUNT OFF;

END 

C#Web层:

using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetUser";  //the proc returns one row that consists of two columns
    cmd.Connection = cn;
    cmd.Parameters.AddWithValue("@UserID", userId);

    cn.Open();

    using (IDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleResult))
    {
        if (dr.Read())  //check if row is available
        {
            string firstName = (string)dr[0];
            string lastName = (string)dr[1];                        
            // three more columns omitted here
            return firstName + " " + lastName;
        }
    }
}

错误:

异常类型:System.IndexOutOfRangeException
消息:索引超出了数组的范围.
数据:System.Collections.ListDictionaryInternal
TargetSite:无效CheckDataIsReady(Int32，Boolean，Boolean，System.String)
来源:System.Data

Exception Type: System.IndexOutOfRangeException
Message: Index was outside the bounds of the array.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void CheckDataIsReady(Int32, Boolean, Boolean, System.String)
Source: System.Data

下面的源代码:

private void CheckDataIsReady(int columnIndex, bool allowPartiallyReadColumn = false, bool permitAsync = false, string methodName = null) {
            if (_isClosed) {
                throw ADP.DataReaderClosed(methodName ?? "CheckDataIsReady");
            }
            if ((!permitAsync) && (_currentTask != null)) {
                throw ADP.AsyncOperationPending();
            }
            Debug.Assert(!_sharedState._dataReady || _metaData != null, "Data is ready, but there is no metadata?");
            if ((!_sharedState._dataReady) || (_metaData == null)) {
                throw SQL.InvalidRead();
            }
            if ((columnIndex < 0) || (columnIndex >= _metaData.Length)) {
                throw ADP.IndexOutOfRange();
            }
            if ((IsCommandBehavior(CommandBehavior.SequentialAccess)) &&                                    // Only for sequential access
                ((_sharedState._nextColumnDataToRead > columnIndex) || (_lastColumnWithDataChunkRead > columnIndex) ||   // Read past column
                ((!allowPartiallyReadColumn) && (_lastColumnWithDataChunkRead == columnIndex)) ||           // Partially read column
                ((allowPartiallyReadColumn) && (HasActiveStreamOrTextReaderOnColumn(columnIndex))))) {      // Has a Stream or TextReader on a partially-read column
                    throw ADP.NonSequentialColumnAccess(columnIndex, Math.Max(_sharedState._nextColumnDataToRead, _lastColumnWithDataChunkRead + 1));
            }
        }

http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlDataReader.cs,577d642dce99ed0d

推荐答案

尝试一下
可能无法解决，但您会获得信息，至少它会正常失败

Give this a try
Might not fix but you will get information and at least it will fail gracefully

try
{ 
    cn.Open();
    string firstName = "not";
    string lastName = "found";
    using (SQLDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleResult))
    {
        if (dr.Read())  //if there is a row, there are two columns. Thus index is used below
        {
            Debug.WriteLine(dr.FieldCount);
            firstName = rdr.IsDBNull(0) ? string.Empty : rdr.GetString(0);
            lastName = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1);                         
        }
    }
} 
catch (SqlException ex)
{
    Debug.WriteLine("GetUser " + Environment.NewLine + ex.Message);
}
catch (Exception ex)
{
    Debug.WriteLine("GetUser" + Environment.NewLine + ex.Message);
    throw ex;
}
finally
{
    cn.Close();
}
return firstName + " " + lastName;

这篇关于返回正确的列数时，SqlDataReader索引超出范围异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！