csharp-method

Getting a boolean from SQL in C# – GetBoolean()

One thing that’s always bothered me a little bit about using DataRows via SqlDataReader is getting a boolean value from a bit field in SQL Server.

Yes, it’s easy enough to do but it just seems a little long winded because of the fact that the GetBoolean() method only accepts an index as an ordinal.

bool MyBoolean = false;
using(SqlDataReader dr = cmd.ExecuteReader()){
    if(dr.HasRows){
        while(dr.Read()){
            MyBoolean = dr.GetBoolean(dr.GetOrdinal("MyColumnName"));
        }
    }
}

So I wrote a really simple extension method for when I need to use a SqlDataReader to access DB columns directly:

public static class SqlExtensions{

    public static bool GetBool(this SqlDataReader row, string ColumnName){
        return Convert.IsDBNull(row[ColumnName]) ? false : row.GetBoolean(row.GetOrdinal(ColumnName));
    }

}

Because sometimes you can’t always rely on a bit field having a constraint for 1 or 0, it’s always an idea to check the column to ensure it’s not null, which we do in this method by returning false if IsDbNull() is true. If there is a value, we then simply call the built in GetBoolean() and GetOrdinal() methods to return the boolean value. So by adding this simple little extension method we can now simply call GetBool() and pass a column name.

<-snip->
while(dr.read()){
    MyBoolean = dr.GetBool("MyColumnName")
}
<-snip->

Why this isn’t available for the native GetBoolean() method I have no idea. To me it seems a little bit of an oversight that Microsoft didn’t include the column name ability in the native GetBoolean() method, but I suppose there’s a method to their madness ;)

 

Category: Programming

Tags: