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

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

Select isnull(MyColumn,'No') as MyColumn

Answer 2

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 3

This is easily done without modifying data first. You can bind directly to your checkbox, then in DataBindings | Advanced you can specify what to use for a null value.

Sorry, I did not notice you needed to convert varchar Yes and No. Dathan's answer will get your data correctly, but I'm pretty sure you are going to have to manually update the values with a custom save method.

Answer 4

This code converts the string into a bool? that can be used. Nullabe types have a 'HasValue' property that indicates if they are null or not.

public bool TryParseNullableBool (string value, out bool? result)
{
  result = false;
  if (string.IsNullOrEmpty (value))
  {
    result = null;
    return true;
  }
  else
  {
    bool r;
    if (bool.TryParse (value, out r))
    {
      result = r;
      return true;
    }
  }
  return false;
}

Answer 5

I originally used Dathan's solution however my checkbox would not push its updates to the data table it was bound to, while trying to solve that issue I found out about Binding.Parse and Binding.Format I now leave the query normal and use this:

Public Form1()
{
    InitializeComponent();
    cbxKeepWebInfinityChanges.DataBindings["Checked"].Parse += new ConvertEventHandler(cbxKeepWebInfinityChanges_Parse);
    cbxKeepWebInfinityChanges.DataBindings["Checked"].Format += new ConvertEventHandler(cbxKeepWebInfinityChanges_Format);
}

void cbxKeepWebInfinityChanges_Parse(object sender, ConvertEventArgs e)
{
    if ((bool)e.Value == true)
        e.Value = "Yes";
    else
        e.Value = "No";
}
void cbxKeepWebInfinityChanges_Format(object sender, ConvertEventArgs e)
{
    if ((string)e.Value == "Yes")
        e.Value = true;
    else
        e.Value = false;
}