469,572 Members | 1,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

How to store a bit field from SQL Server

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.
Sep 15 '06 #1
5 25547
Alan T wrote:
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

Sep 15 '06 #2
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?
"John B" <jb******@yahoo.comwrote in message
news:45**********@news.iprimus.com.au...
Alan T wrote:
>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

Sep 15 '06 #3
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

"Alan T" wrote:
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?
"John B" <jb******@yahoo.comwrote in message
news:45**********@news.iprimus.com.au...
Alan T wrote:
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


Sep 15 '06 #4
Alan T wrote:
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

<...>
Sep 18 '06 #5
On Mon, 18 Sep 2006 16:30:07 +1000, John B <jb******@yahoo.comwrote:
>Alan T wrote:
>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
Sep 18 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Tony Johansson | last post: by
6 posts views Thread by omozali | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.