CODEWITHSUNDEEP
c#booleanbitsqldatareader
Yulian
Yulian

Reputation: 6759

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

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.

Upvotes: 28

Views: 55283

Answers (5)

KRM
KRM

Reputation: 1405

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

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

Upvotes: 1

Yaina Villafañes
Yaina Villafañes

Reputation: 181

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

Upvotes: 2

Steve
Steve

Reputation: 216293

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;

Upvotes: 8

dotnetom
dotnetom

Reputation: 24901

If you want to use the column name you can use

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

Upvotes: 16

Slade
Slade

Reputation: 2453

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"]

Upvotes: 29

Related Questions