SqlDataReader Index Out Of Range Exception when co

问题:

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.

Questions:

1 Where and why does the exception occur?

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

Stored procedure 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 layer:

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;
        }
    }
}

Error:

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

Source Code below:

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

回答1:

The lack of a schema name could be messin' with you.

Also, some debugging tips in the C# code below.

1. Supply a schema name for the stored procedure.

   IF EXISTS (
               SELECT * FROM INFORMATION_SCHEMA.ROUTINES
               WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'GetUser'  
           )   
   BEGIN
       DROP PROCEDURE [dbo].[GetUser]
   END
   
   GO
   
   CREATE PROCEDURE dbo.GetUser()
   
   AS 
   
   SELECT TOP 1
       FirstName, LastName
   FROM 
       User
   --omitted WHERE userid = @id
   
   
   GO
   

2. Change your c# to:

      using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
     {
          SqlCommand cmd = new SqlCommand();
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "dbo.GetUser";  
          cmd.Connection = cn;
   
          cn.Open();
   
          using (IDataReader dr = cmd.ExecuteReader())
          {
          while (dr.Read()) 
                              string temp = string.Empty;
   
       int fc = dr.FieldCount;    
       if (fc>0)
       {
                       object val2 = dr[0];
                       temp = val1.GetType().ToString();
       }
   
       if (fc>1)
       {
                       object val2 = dr[1];
                       temp = val2.GetType().ToString();
       }
   
   
   
       temp = "did I get here?";
   
       string firstName = (string)dr[0];
       string lastName = (string)dr[1];                        
   
       return firstName + " " + lastName;
   }
          }
          }
   

回答2:

I think this line is messing you up in your stored procedure:

A.Value2 Ad LastName

Should be: A.Value2 as LastName

回答3:

You haven't specify parameter userid in C# code please use CommandParameter and then try to do

回答4:

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;