best practice for Saving boolean in SQL Server

Florin Bombeanu

Hi,
I would like to save a boolean value into a SQL Server table and retrieve in C# code. I think the best way to do this is to make the column type 'bit' and fill it with 1's and 0's but I would like to avoid explicit cast in my code when retrieving these values.
Which is the best practice in this case? (by best practice I mean the most inexpensive method, from resource, overhead, execution time, etc point of view)

a. save it as bit and use explicit cast?
(bool)row["boolvalue"] == true

b. save it as tinyint and use ToString()
row["boolvalue"].ToString() == "1"

c. better option I'm not thinking of

P.S. I'm new here, I hope I've posted in the right place and the question is not idiotic. Please let me know if any option is just as good as another.
Have a nice day!

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs16y ago
Accepted answer
If you create a data source then the TableAdapter will provide type-safe access to the data.
Sam Hobbs16y ago
There are many ways to create a TableAdapter. Note that you won't find documentation of the TableAdapter class since TableAdapters are created when you create a datasource or use Visual Studio to create a TableAdapter in one of the many other ways to create them. They are generated for a specific table for use in a specific project.

One way to create a TableAdapter is to use the Data Sources window. Look at my Database Table Update in a DataGridView without Writing Code article for instructions for creating a data source except the TableAdapter does not need to be bound to (put onto) a form. I don't know the details of what you need to do so I can't be too specific about what you need to do. If you read my article and create a test project using the instructions in the article then you can look at the generated code. The generated code will by default have a GetData method and a Fill method. You can use one of them in your main program to retrieve the data. They will get the data into a DataTable that is generated with type-safe objects for each of the fields you select for your data source. The DataTable will have a Rows collection. You can change the data in the Rows collection then write the entire table out using TableAdapter.Update(DataTable). There are many other options.
Florin Bombeanu16y ago
Could you please give me a short example on this?
Jiteendra Sampathirao16y ago

Bit data type accepts single digits, So we assign the value to status column and the default value is '1'.
When we are doing delete operation we assign status value as '0' by using Update command.
Ex:
       Update somethingtable set status=0 where Id=1;// when doing delete the row

       Select something from somethingtable where status=1;//to retriving alive rows

          ----------------------------------------------------------------------------------------------------------------------
        If this post helped you, then tick the checkbox above "Do you like this Answer"
Florin Bombeanu16y ago
Sorry, but I didn't quite understood how do you work with the boolean value when retrieving it. Explicit cast? ToString()? Another method? Which one of them? In my datatable it's always an object :)
Jiteendra Sampathirao16y ago
Hi,
Its not at all idiotic and this the fundamental thing. we are facing these concept at least once in a day.
In my working project i used bit data type for booleans. and When i am doing insert operation i set the boolean value to status column as '1' when i'm doing delete operation we are modifying status column value as '0'.

If this post helped you, then tick the checkbox above "Do you like this Answer"


	If this post helped you, then tick the checkbox above "Do you like this Answer"