Getting values from C# SqlDataReader into an integer variable

Question

I am using SqlCommand to run a simple query against my database:

SELECT MIN(ID), MAX(ID) 
FROM DocumentImage

Obviously the query should return one row with two columns a minID (1) and a maxID (12xxxxxx). I am trying to get those two values into integer variables that I can use in a while loop.

int[] minMax = new int[2];

DataTable dT = new DataTable();

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = databaseName;
myCommand.CommandText = "SELECT MIN(ID), MAX(ID) FROM DocumentImage";

databaseName.Open();

using (SqlDataReader dr = myCommand.ExecuteReader())
{
    while (dr.Read())
    {
        minMax[0] = dr.GetInt32(0);
        minMax[1] = dr.GetInt32(1);
    }
}

databaseName.Close();

If I insert a breakpoint at the while (dr.Read()), dr shows the values I expect. But when I run minMax[0] = dr.GetInt32(0), the code throws an exception

Invalid Attempt to read when no data is present

If I drill down down to the SqlDataReader results, I get

Enumeration yielded no results

I've tried writing to a dataTable then moving it to an array as well as different cast and to-arrays. I am at a bit of a loss.

Answer 1

You’re trying to read data that might not be available when attempting to access it. To address this, consider checking whether the SqlDataReader has any rows before trying to read the data. Here’s a modified version of your code with error handling to ensure you’re reading the data correctly:

int[] minMax = new int[2];
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = databaseName;
myCommand.CommandText = "Select MIN(ID), MAX(ID) FROM DocumentImage";
databaseName.Open();
using (SqlDataReader dr = myCommand.ExecuteReader())
{
    if (dr.HasRows)
    {
        dr.Read(); // Move to the first row

        if (!dr.IsDBNull(0)) // Check for DBNull before reading
            minMax[0] = dr.GetInt32(0);

        if (!dr.IsDBNull(1)) // Check for DBNull before reading
            minMax[1] = dr.GetInt32(1);
    }
}
databaseName.Close();