How to store a bit field from SQL Server

[Alan T]

Currently our SQL Server 2000 database table field is using char to store as
boolean, ie. "T" or "F".
Now we change the field from char to bit field. I am not sure how it has
impact on the C# code.

For example, the stored procedure returns
SELECT *
FROM employee

There is a field "ismale", "T" is male, "F" is female.
Now 1 is male, 0 is female.

I am not sure should I change the C# code or change the stored procedure?

1) If I leave the C# code unchanged
eg. to add an employee
- in C# code:
exec sp_add_employee 'alan', 'T'

- in SQL Server
so I need to change the stored procedure to test

create proc sp_add_emplyee
@name varchar(255),
@gender char
as
if @gender = 'T' then
insert into employee (empName, gender)
values (@name, 1)
else
insert into employee (empName, gender)
values (@name, 0)

But if I retrieve the data from database, I still need to modify the stored
procedure to return 1 or 0.
eg. in stored procedure
select empName, case gender.....1 else 0
from employee

2) if I leave the stored procedure unchanged, I need to modify the C# code,
if I have a record type to store the details of an employee, I need to
change the field type of the gender from char to 'bit'(or integer)
GUI binding also need to change, eg. check box, I use if it is "T" then
checked the box. Now I need to change the code if it is 1 then checked the
box.

 

[John B]

Currently our SQL Server 2000 database table field is using char to store as
boolean, ie. "T" or "F".
Now we change the field from char to bit field. I am not sure how it has
impact on the C# code.

For example, the stored procedure returns
SELECT *
FROM employee

There is a field "ismale", "T" is male, "F" is female.
Now 1 is male, 0 is female.

I am not sure should I change the C# code or change the stored procedure?

Change the sp.

1) If I leave the C# code unchanged
eg. to add an employee
- in C# code:
exec sp_add_employee 'alan', 'T'

- in SQL Server
so I need to change the stored procedure to test

create proc sp_add_emplyee
@name varchar(255),
@gender char

@gender bit

when you create your command, if you use a parameterized query, you can
just add the @gender parameter and it will map bool straight to bit.

as
if @gender = 'T' then
insert into employee (empName, gender)
values (@name, 1)
else
insert into employee (empName, gender)
values (@name, 0)

But if I retrieve the data from database, I still need to modify the stored
procedure to return 1 or 0.
eg. in stored procedure
select empName, case gender.....1 else 0
from employee

2) if I leave the stored procedure unchanged, I need to modify the C# code,
if I have a record type to store the details of an employee, I need to
change the field type of the gender from char to 'bit'(or integer)
GUI binding also need to change, eg. check box, I use if it is "T" then
checked the box. Now I need to change the code if it is 1 then checked the
box.

Another question is why you have changed.
If it isn't broken and you don't have time to improve it then leave it.

JB

 

[Alan T]

We think it is proper to change a boolean field from a char field to a bit
field.
Yes, we are using parameterizes query:

SqlParameter objParameter1 = new SqlParameter("@active", SqlDbType.Int);

objCommand.Parameters.Add(objParameter1);

objParameter1.Direction = ParameterDirection.Input;

objParameter1.Value = 1; // or 0 if "inactive"



Can I use like this?

 

[Guest]

I would change the code if you can. The sql bit field maps to a boolean in
C#. you can set the parameter value in C# to be 1 or True.

Ciaran O'Donnell

 

[John B]

We think it is proper to change a boolean field from a char field to a bit
field.
Yes, we are using parameterizes query:

SqlParameter objParameter1 = new SqlParameter("@active", SqlDbType.Int);

objCommand.Parameters.Add(objParameter1);

objParameter1.Direction = ParameterDirection.Input;

objParameter1.Value = 1; // or 0 if "inactive"

This would work but you could more easily do:

objParameter1.Value = myClass.Active;

Then when you load from the database

myClass.Active = (bool)Reader["Active"];

(where reader is an idatareader and Active is the name of the column)



JB

<...>

 

[Otis Mukinfus]

We think it is proper to change a boolean field from a char field to a bit
field.
Yes, we are using parameterizes query:

SqlParameter objParameter1 = new SqlParameter("@active", SqlDbType.Int);

objCommand.Parameters.Add(objParameter1);

objParameter1.Direction = ParameterDirection.Input;

objParameter1.Value = 1; // or 0 if "inactive"

This would work but you could more easily do:

objParameter1.Value = myClass.Active;

Then when you load from the database

myClass.Active = (bool)Reader["Active"];

(where reader is an idatareader and Active is the name of the column)



JB

<...>

JB's solution will work very well indeed, but in the parameter declaration there
is a mistake. If Alan T is using MS SQL Server the declaration of the
parameter's type should be SqlDbType.Bit instead of SqlDbType.Int.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com