Convert Yes/No/Null from SQL to True/False in a DataTable

Question

I have a Sql Database (which I have no control over the schema) that has a Column that will have the varchar value of "Yes", "No", or it will be null. For the purpose of what I am doing null will be handled as No.

I am programming in c# net 3.5 using a data table and table adapter to pull the data down. I would like to directly bind the column using a binding source to a check box I have in my program however I do not know how or where to put the logic to convert the string Yes/No/null to boolean True/False;

Reading a null from the SQL server and writing back a No on a update is acceptable behavior.

Any help is greatly appreciated.

EDIT -- This is being developed for windows.

Answer 1

Modify the query that populates your DataTable to include the appropriate logic:

SELECT col1, col2, CAST(CASE YesNoNullCol WHEN 'yes' THEN 1 WHEN 'no' THEN 0 ELSE 0 END AS BIT) FROM SomeTable

EDIT: Forgot that you have to supply Insert / Update / Delete commands on DataAdapter as well.

In order to get commits to work using the above, you need to specify custom commands for performing updates to the DB:

SqlCommand insert_cmd = connection.CreateCommand();
insert_cmd.CommandText = "INSERT INTO SomeTable(col1, col2, YesNoNullCol) VALUES (@col1, @col2, CASE @yesnonullcol WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE 'No' END)";
var yesno_col = insert_cmd.CreateParameter();
yesno_col.Name = "@yesnonullcol";
insert_cmd.Parameters.Add(col1_parm);
myAdapter.InsertCommand = insert_cmd;

And, of course, you need to provide parameters for @col1 and @col2 as well. And then you need to create commands for update and delete if you want to support those operations.

Answer 2

If you can modify the SQL you are using to retrieve the data do something like..

Select isnull(MyColumn,'No') as MyColumn