up vote 0 down vote favorite
1

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!

share|improve this question

3 Answers

up vote 3 down vote accepted

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)
{
    //...
}
share|improve this answer
2  
Doesn't that go for any variable type? Whether Greg needs a nullable bool would depend on how he setup his table and whether the bit column allows nulls. – sneakthief Jul 13 '11 at 15:34
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 Jul 13 '11 at 15:46
@Greg updated to show how to use it – Joel Coehoorn Jul 13 '11 at 15:50
@Joel Coehoorn thanks. That looks like what I need. Definitely need that isDBNull as well. Good stuff. – ImGreg Jul 13 '11 at 15:56
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. – Guillaume Massé Jul 13 '11 at 18:34
up vote 4 down vote

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"];
share|improve this answer
up vote 2 down vote

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?

share|improve this answer
I'm assuming you are using the GetBoolean() method to retrieve the value from the db? – ImGreg Jul 13 '11 at 15:49
I'm using linq and subsonic; the bit value from my database table is automatically retrieved as a boolean variable... – sneakthief Jul 13 '11 at 16:02

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.