SqlDataReader Reading a bit datatype from SQL Server 2008?

Question

I am using SQL Server 2008 in a asp.net/c# program. I am trying to use SqlDataReader to fetch the data form the db, but I'm not sure what to use for the datatype "bit".

//these are the assemblies i added manually
using System.Web.Script.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ucsConnectionString"].ConnectionString);
SqlDataReader rdr2 = null;
conn2.Open();

SqlCommand cmder = new SqlCommand("usp_Device_GetBy_DeviceID", conn2);
cmder.Parameters.AddWithValue("@ID", id);
cmder.CommandType = CommandType.StoredProcedure;
rdr2 = cmder.ExecuteReader();
rdr2.Read();

*insert datatype & var* = rdr2.GetSqlBit(rdr2.GetOrdinal("Line_Name"));

I found a couple sites that referenced the above "GetSqlBit" but apparently it is not part of the assemblies I'm using. Any suggestions how I can read this "bit" datatype from SQL?

I found a similar datatype using "GetSqlBinary" but I don't fully understand how it works or if it would be appropriate for this situation?

Everyone's ongoing help is appreciated!

Joel Coehoorn · Accepted Answer · 2011-07-13 15:50:44Z

11

A bit stored in a database can actually have three states, not just two: 0, 1, and NULL. For this reason, the type you want is a Nullable<bool>, or the bool? shorthand.

However, it looks like you want the .GetBoolean() method. That method requires you to check for null before calling the method. The code might look like this:

bool? Line_Name = rdr2.IsDBNull(rdr2.GetOrdinal("Line_Name"))?null:rdr2.GetBoolean(rdr2.GetOrdinal("Line_Name"));
if (Line_Name != null && Line_Name.Value)
{
    //...
}

edited Jul 13, 2011 at 15:50

answered Jul 13, 2011 at 15:33

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ImGreg Over a year ago

I do need to allow nulls (more just for testing..). However, I'm getting errors for using the 'bool?' datatype. I'm using it in an if statement and it says it can't convert from 'bool?' to 'bool'. Do I have to do anything different to use this datatype?

ImGreg Over a year ago

@Joel Coehoorn thanks. That looks like what I need. Definitely need that isDBNull as well. Good stuff.

Guillaume Massé Over a year ago

bool cannot be null. Use Nullable<bool> or Nullable<Boolean>. In sql bit has 3 states: {1,0,NULL}. You can also use false or true instead of null depending on the logic.

Bryan Crosby · Accepted Answer · 2011-07-13 15:58:24Z

11

A bool is what you are looking for. Depending on whether the database table allows a null value, it will be bool or bool? for a nullable type.

(if the bit column allows nulls -- many ways you can do this)

bitValue = reader["MyBitColumn"] as bool? ?? null;

if not, then:

bitValue = (bool)reader["MyBitColumn"];

answered Jul 13, 2011 at 15:58

Bryan Crosby

6,5643 gold badges39 silver badges56 bronze badges

Comments

Grant Winney · Accepted Answer · 2011-07-13 15:28:45Z

2

A bit can be retrieved and stored in a boolean. I use a bit (0 or 1) to indicate whether a record is active. When I retrieve it into my C# app, I store it in a boolean variable. Is that you're asking?

answered Jul 13, 2011 at 15:28

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

1 Comment

ImGreg Over a year ago

I'm assuming you are using the GetBoolean() method to retrieve the value from the db?

Brandon · Accepted Answer · 2014-06-13 17:13:02Z

1

Just wanted to add something to Joel's answer... you have to cast the null to a bool? or you get errors with that statement:

var myvar = reader.IsDBNull(reader.GetOrdinal("field_name")) 
            ? (bool?)null
            : reader.GetBoolean(reader.GetOrdinal("field_name"));

answered Jun 13, 2014 at 17:13

Brandon

9081 gold badge17 silver badges38 bronze badges

Comments

GURU · Accepted Answer · 2013-11-15 13:29:56Z

0

Boolean datatype is sufficient to read bit datatype from sql server 2008

answered Nov 15, 2013 at 13:29

GURU

251 silver badge5 bronze badges

Collectives™ on Stack Overflow

SqlDataReader Reading a bit datatype from SQL Server 2008?

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related