1

I am trying to read a boolean value from ODBCDataReader by using ODBCDataReader.GetBoolean(i) in theory it should work fine as the column we have read is a boolean column and the data coming in is either 1 or 0

so when I read it like this -

return reader.IsDBNull(col) ? null : reader.GetBoolean(col);

I get InvalidCastException

Then I tried to fetch reader as 

return reader.IsDBNull(col) ? null : Convert.ToBoolean(reader.GetValue(col));

The above line complains about The string is not recognized as a valid bool value.(value is "1")

Then I wrote the following method --

public static bool? GetDbSafeNullableBit(this IDataRecord reader, string propertyName, bool? defaultBool = null)
    {
        var returnValue = defaultBool;
        var col = reader.GetOrdinal(propertyName);
        if (reader.IsDBNull(col))
        {
            return returnValue;
        }

        var stringValue = reader[col].ToString();

        switch (stringValue)
        {
            case "1":
                returnValue = true;
                break;
            case "0":
                returnValue = false;
                break;
            default:
                throw new Exception($"Value read from reader is not defined as proper boolean, value is {stringValue}");
        }

        return returnValue;
    }

and Voila, I find this works fine.

Can someone please guide me on why the first two ways are not giving the expected result.

PS: Data is coming from PostgresSql (makes any difference ?)

Muds
  • 4,006
  • 5
  • 31
  • 53
  • What is a data type of column? It can't cast int into boolean. You say, it has only "1 and 0", but user can not be trusted :) In my previous job we had to cast varchar into bool, so we had something like: case "true": case "1": case "yes": case "y": return true; break; Because admins kept putting stuff like this into this pure column. – Arkadiusz Raszeja Jul 11 '18 at 09:32
  • Thanks for your reply @ArkadiuszRaszeja, but probably you missed that I mentioned that we are tring to read value from a `bit` column – Muds Jul 11 '18 at 09:36
  • The main difference between "boolean" and "bit" is that bit is literally one bit, but boolean is the shortcut for tinyint(1), so theoretically it could contain numbers other than 1 and 0. If you put 0 it will be aliased as "false" but every non-zero values are aliased as "true". That is why it is hard to cast them directly :( – Arkadiusz Raszeja Jul 11 '18 at 09:47
  • if its just one bit it cant be anything but 1 or 0, if it tries to truncate a tinyInt it will still end up being 1 or 0, i have a strong feeling this has something to do with odbc as I have never encountered this error working with sqlDataReader – Muds Jul 11 '18 at 09:50
  • Maybe I am wrong, but tinyint(1) is a normal tinyint with displaywidth = 1. So theoretically you can expect one byte to one bit conversion. (eg. 128 into one bit). Just guessing, not saying you are wrong :) https://stackoverflow.com/questions/12839927/mysql-tinyint-2-vs-tinyint1-what-is-the-difference – Arkadiusz Raszeja Jul 11 '18 at 10:02
  • Out of curiosity, why ODBC and why not NpgSql? NpgSql handles so much more natively and even handles things like array datatypes and custom PostgreSQL data types. – Hambone Jul 20 '18 at 19:42

0 Answers0