Jorge F Jorge F - 24 days ago 12
C# Question

Code not synced throwing Index was out of bounds array?

Before you mark this question as a duplicate, here is the tricky part I don't understand. This error is sporadic, I believe the code is correct and it's always working and I'm handling the possible mistakes with an if else condition inside the Reader part. Here is the code:

public static Tuple<int, string> GetIDAndString(string term)
{
try
{
using (SqlConnection con = GetConnection())
using (cmd = new SqlCommand())
using (myReader)
{
int ID = 0;
string status = string.Empty;
cmd.Connection = con;
con.Open();
cmd.CommandText = @"SELECT t.TableID, t.Status
FROM Table t WITH (NOLOCK) /* I know NOLOCK is not causing the mistake as far as I know */
WHERE t.Term = @term";
cmd.Parameters.AddWithValue("@term", term);

myReader = cmd.ExecuteReader();
while(myReader.Read())
{
ID = myReader.IsDBNull(0) ? 0 : myReader.GetInt32(0);
status = myReader.IsDBNull(1) ? string.Empty : myReader.GetString(1).Trim();
}

myReader.Close();

return new Tuple<int, string>(ID, status);
}
}
catch (Exception)
{
throw;
}
}


I know I should be using a class instead of a Tuple, but I can't change that existing code and as you can see. So the main problem is that in the production server there was a
Index out of bounds array exception
in that method but I can't identify what's the problem.

Even if the term is not found in the query, the myReader will not enter and I'll return the ID = 0, status = string.Empty. Sometimes when I'm debugging code and working on the
develpment server
, my code starts to crash everywhere, showing me exceptions where is tested code and I have to reopen the solution to avoid that (I haven't found a solution to that, not even cleaning the solution).

So I hope someone have experience with something like that in a
production server
. I don't have specifications to the production server so I don't know anything about the server.

Answer

First you don't need the try/catch block, you don't do anything with it. After that don't share SqlDataReader in the class, this could bring problems and probably the problem comes from this. You are overwriting the value of ID and Status all the time in your while. Probably a good idea will be to call Top 1 on your query and order it by with correct field. Also there is no need to Dispose() the SqlCommand, the Constructor of SqlCommand is calling SupressFinalization().

Why this problem can happen: Imagine your query returns 1000 records with TableID and Status column and you are entering the while loop. In this moment some other user is going in your application and executing another method which overwrites the SqlDataReader and return 5 records with only one column. On the next iteration of you while loop you will receive your exception. Because of that you should never define your Readers as static for the whole class. Static variables are shared between all of application users.

public static Tuple<int, string> GetIDAndString(string term)
{
    int ID = 0;
    string status = string.Empty;

    using (SqlConnection con = GetConnection())
    {

        cmd.Connection = con;
        con.Open();

        cmd.CommandText = @"SELECT t.TableID, t.Status
                            FROM Table t WITH (NOLOCK) /* I know NOLOCK is not causing the mistake as far as I know */
                            WHERE t.Term = @term";
        cmd.Parameters.AddWithValue("@term", term);


        using(SqlDataReader myReader = cmd.ExecuteReader())
        {
            while(myReader.Read())
            {
                 ID = myReader.IsDBNull(0) ? 0 : myReader.GetInt32(0);
                 status = myReader.IsDBNull(1) ? string.Empty : myReader.GetString(1).Trim();
            }

        }

    }

    return new Tuple<int, string>(ID, status);
}
Comments