Read boolean values from DB?

Question

In C#, using SqlDataReader, is there a way to read a boolean value from the DB?

while (reader.Read())
{
    destPath = reader["destination_path"].ToString();
    destFile = reader["destination_file"].ToString();
    createDir = reader["create_directory"].ToString();
    deleteExisting = Convert.ToBoolean(reader["delete_existing"]);
    skipIFolderDate = reader["skipifolderdate"].ToString();
    useTempFile = reader["useTempFile"].ToString();
    password = reader["password"].ToString();
}

In the code above, delete_existing is always a 1 or 0 in the DB. I read on MSDN that Convert.ToBoolean() does not accept a 1 or 0 as valid input. It only accepts true or false. Is there an alternative way to convert a DB value to a bool? Or do I need to do this outside of the SqlDataReader?

Also, I can not change the DB values, so please no answers saying, "Change the DB values from 1's and 0's to true's and false's."

Thanks!

Larry · Accepted Answer · 2011-05-09T20:11:49.383

41

If the type of delete_existing is a sqlserver 'bit' type, you can do :

var i = reader.GetOrdinal("delete_existing"); // Get the field position
deleteExisting = reader.GetBoolean(i);

or (but it will crash if delete_existing can be DBNull)

deleteExisting = (bool)reader["delete_existing"];

or better, this onebelow is DBNull proof and returns false if the column is DBNull

deleteExisting = reader["delete_existing"] as bool? ?? false;

Otherwise if the database type is int :

deleteExisting = (reader["delete_existing"] as int? == 1) ? true : false;

or if it is a varchar

deleteExisting = (reader["delete_existing"] as string == "1") ? true : false;

edited May 09 '11 at 20:11

answered May 09 '11 at 19:52

Larry

17,605
9
77
106

@Laurent Hmm... I would rather go with the third option because someone could add a column to the table and then, obviously, the app would break. However, can you explain what exactly is going on in the second option? I am confused by the "as bool? ?? false;" specifically the 3 ?'s... – Mr. Ant May 09 '11 at 19:56
3rd solution: I'm trying to cast reader["delete_existing"] to a nullable bool (bool?). This way, the cast fails and returns null if the field contains DBNull.Value. Because I prefer to get false instead, I am using the ?? which transform a null to any value (ie: null ?? 45 = 45). I uses this a lot. – Larry May 09 '11 at 20:02
2nd solution: I'm doing a direct cast to a bool. This one will throw an exception if the value is not a boolean type. For example, if it is DBNull.Value. That's why it should be used with not null bit database field. Just choose the one that fits your needs ^^ What is the database type of delete_existing btw ? – Larry May 09 '11 at 20:08
@Laurent What is the difference if I use a .ToString()? For example... deleteExisting = (reader["delete_existing"].ToString() == "1") ? true : false; – Mr. Ant May 09 '11 at 20:31
I'm afraid reader["delete_existing"].ToString() will return "True" or "False" instead of 1 or 0 if the underlying database type is bit. Otherwise, if it is a varchar or an int, deleteExisting = reader["delete_existing"].ToString() = "1"; is OK – Larry May 09 '11 at 20:37
@Laurent, Would you agree that as string is always a better option than .ToString()? Or is that debatable? Possibly another SO question... – Mr. Ant May 09 '11 at 20:43
1

http://stackoverflow.com/questions/2099900/difference-between-tostring-and-as-string-in-c This answered my question above. Thanks for all your help! – Mr. Ant May 09 '11 at 20:47
it will crash if delete_existing can be DBNull - gave me clue why my code was not working. Thanks – Goldfish Aug 29 '17 at 12:22
Did I really wrote `? true : false;` ? This is totally redundant! XD – Larry Apr 12 '21 at 10:09

score 3 · Answer 2 · answered May 09 '11 at 19:58

3

Casting works: myVar = (bool)dataReader["myColumn"];

answered May 09 '11 at 19:58

donkz

313
2
12

4

That works if the value returned is actually a bit. If someone lazily selected a 1 or 0 as an integer that cast will fail. – canon May 09 '11 at 20:00

score 1 · Answer 3 · answered Nov 14 '17 at 03:01

1

If you are using CASE in SELECT and want to use GetBoolean then use CAST to change the column to bit before reading.

For eg:

SELECT CAST((CASE WHEN [Condition] THEN 1 ELSE 0 END) as bit) FROM Table_Name

then you can use

reader.GetBoolean(0)

answered Nov 14 '17 at 03:01

sree

2,287
28
26

Jeffrey L Whitledge · Answer 4 · 2011-05-09T20:22:46.987

1

How about this?

deleteExisting = (reader["delete_existing"] as int?) == 1;

Boolean is probably the easist type to convert something to. Here's the 'Y', 'N' version:

deleteExisting = string.Equals(reader["delete_existing"] as string, "Y", StringComparision.OrdinalIgnoreCase);

edited May 09 '11 at 20:22

answered May 09 '11 at 20:15

Jeffrey L Whitledge

58,241
9
71
99

score -1 · Answer 5 · answered May 09 '11 at 19:51

-1

deleteExisting = reader.GetBoolean(reader["delete_existing"]);

answered May 09 '11 at 19:51

slandau

23,528
42
122
184

this statement returns an error. I assume it is because the GetBoolean function wants an Int passed in, and reader["delete_existing"] is not an int yet... – Mr. Ant May 09 '11 at 19:59
Try casting it? It should auto cast to an int I believe but you can try explicitly doing reader.GetBoolean((int)reader["delete_existing"]); – slandau May 09 '11 at 20:00
I'm sorry, in GetBoolean(x), x is not a value to convert to bool, but rather an integer that is the position of the field to read in the datareader. – Larry May 09 '11 at 20:04
2

GetBoolean expects an ordinal = index of the column to get. – jgauffin May 09 '11 at 20:13

Read boolean values from DB?

5 Answers5

Linked

Related