How to get a bit value with SqlDataReader and convert it to bool?

Question

I am retrieving user information from a database using a simple query.

select * from dbo.[User] u where u.Email = @email

I then try to get the value of a column, called IsConfirmed (which is represented as a bit type column in the database) and convert it to bool.

bool isConfirmed = int.Parse(sqlDataReader["IsConfirmed"].ToString()) == 1;

I then get an FormatException error, stating that "Input string was not in a correct format.".

I saw a similar question with an answer providing this code:

bool isConfirmed = sqlDataReader.GetBoolean(0);

But this won't work with my case, because I don't know the index of the IsConfirmed column and I don't want to know it. I want to use the column name.

Answer 1

The value returned from the data reader indexer property is of type object but can be cast to the data type it has been stored as.

Try this:

bool isConfirmed = (bool)sqlDataReader["IsConfirmed"]

Answer 2

If you want to use the column name you can use

bool isConfirmed = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal("IsConfirmed"));

Answer 3

Your code should work if you don't have any null value in your column IsConfirmed.
Usually these bit columns have a NOT NULL property and/or a default value of 0, but it could happen to have a null value and, in this case, your code will fail with the error mentioned.

You could fix it in this way (You will need the column position for this check however)

int colPos = sqlDataReader.GetOrdinal("IsConfirmed");
bool isConfirmed = sqlDataReader.IsDBNull(colPos) ? false : sqlDataReader.GetBoolean(colPos));

If you really dislike to have a call to find the Column Position you could create an extension method that allow you to hide the call

public static class ReaderExtensions
{
    public static bool IsDBNull(this SqlDataReader reader, string colName)
    {
        int colPos = reader.GetOrdinal(colName);
         return reader.IsDBNull(colPos);
    }
}

and call it with

bool isConfirmed = int.Parse((sqlDataReader.IsDBNull("IsConfirmed") 
                    ? "0" : sqlDataReader["IsConfirmed"].ToString())) == 1;

Answer 4

Try this: Convert.ToBoolean(reader["Columnname"]) or with Ordinal for example: Convert.ToBoolean((3))

Answer 5

For nullable boolean (if you may have null boolean values), you can try

bool? isConfirmed = sqlDataReader["IsConfirmed"] as bool?;