SqlDataReader Index Out Of Range Exception when correct column count is returned

Question

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

TOP without an explicit ORDER BY is pointless - which TOP 1 row do you expect to get?? Without an explicit ordering, it's arbitrary - you'll just get back one, more or less randomly chosen row from your table ...... — marc_s
– marc_s, Commented Aug 2, 2016 at 20:55
You probably need SET NOCOUNT ON in your stored proc. What might be happening is that the number of records is being returned and not the result of the SELECT. — Igor
– Igor, Commented Aug 2, 2016 at 20:55
@Kendo - wouldn't dr.Read() return false though in that scenario. — Igor
– Igor, Commented Aug 2, 2016 at 21:01
Please post your actual and complete stored procedure script for GetUser; Your C# code looks fine — techspider
– techspider, Commented Aug 2, 2016 at 21:06

granadaCoder · Accepted Answer · 2016-08-02 21:07:09Z

0

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

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

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

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

answered Aug 2, 2016 at 21:07

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pingpong Over a year ago

Only one schema exists, which is dbo

Pingpong Over a year ago

I need to know why the exception occurs.

granadaCoder Over a year ago

Did you run the c# code (my version) .. without the schema name?

granadaCoder Over a year ago

Note, I'm not using an overloaded .ExecuteReader() method. And the code is looking at the FieldCount and the datatypes of the returned rows. If you get the above code to work (which I think it will).......then you need to focus on the overload. System.Data.Commandbehavior.SingleRow would be my next step.

Will Young · Accepted Answer · 2016-08-02 21:36:43Z

0

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

A.Value2 Ad LastName

Should be: A.Value2 as LastName

answered Aug 2, 2016 at 21:36

Will Young

866 bronze badges

2 Comments

techspider Over a year ago

I think it is a typo in question

Pingpong Over a year ago

it is a typo like ~techspider said

Suyash Kumar Singh · Accepted Answer · 2016-08-02 22:28:22Z

0

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

answered Aug 2, 2016 at 22:28

Suyash Kumar Singh

1731 gold badge2 silver badges10 bronze badges

9 Comments

Pingpong Over a year ago

as in the comment, it is commented out for simplicity. sorry about that.

Suyash Kumar Singh Over a year ago

Have you tried again to check what result it shows after removing CommandBehavior.SingleResult may be metadata contain 0 value

Suyash Kumar Singh Over a year ago

if again throwing exception then you have to re define schema of the table because your code looks good

Pingpong Over a year ago

Why metadata contains 0 value after CommandBehavior.SingleResult is removed?

Pingpong Over a year ago

Why I have to re define schema of the table? Please tell me the reasoning behind it.

|

paparazzo · Accepted Answer · 2016-08-02 23:36:10Z

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;

Collectives™ on Stack Overflow

SqlDataReader Index Out Of Range Exception when correct column count is returned

Source Code below:

4 Answers 4

4 Comments

2 Comments

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Source Code below:

4 Answers 4

4 Comments

2 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related