CODEWITHSUNDEEP
c#sqlsql-serverdatatable
Scott Chamberlain
Scott Chamberlain

Reputation: 127573

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

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.

Upvotes: 4

Views: 10705

Answers (5)

Scott Chamberlain
Scott Chamberlain

Reputation: 127573

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;
}

Upvotes: 0

Dathan
Dathan

Reputation: 7446

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.

Upvotes: 9

crauscher
crauscher

Reputation: 6618

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;
}

Upvotes: 1

jac
jac

Reputation: 9726

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.

Upvotes: 1

CResults
CResults

Reputation: 5105

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

Select isnull(MyColumn,'No') as MyColumn

Upvotes: 1

Related Questions