SqlDataReader Index Out Of Range Exception when returning the correct column

Question

I have a stored procedure that returns the correct column count, but the code below works most of the time, but INCIDENTALL throws an exception below. We recently updated .NET 4.6, and we notice this exception.

Questions:

1 Where and why does the exception occur?

2 Based on the source code below, how is it possible that the SQL client receives empty metadata from the 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:

: System.IndexOutOfRangeException
: .
: System.Collections.ListDictionaryInternal
TargetSite: Void CheckDataIsReady (Int32, Boolean, Boolean, System.String)
: System.Data​​p >

:

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

Answer 1

messin ' .

, # .

• .

  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
  

• # :

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

Answer 2

, :

A.Value2 Ad LastName

: A.Value2 LastName

Answer 3

You did not specify a parameter useridin C # code, please use CommandParameterand then try to do

Answer 4

Try it. I can’t fix it, but you will get the 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;