best practice for Saving boolean in SQL Server

Question

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!

Sam Hobbs · Answer

If you create a data source then the TableAdapter will provide type-safe access to the data.

Sam Hobbs · Answer

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 Bombeanu · Answer

Could you please give me a short example on this?

Jiteendra Sampathirao · Answer

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 Bombeanu · Answer

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 Sampathirao · Answer

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