Run SQL Query Against DataTable

Question

I am populating a DataTable like this

static DataTable GetTable()
{
    DataTable table = new DataTable();
    table.Columns.Add("name", typeof(string));
    table.Columns.Add("exam1date", typeof(date));
    table.Columns.Add("exam1complete", typeof(date));
    table.Columns.Add("exam2date", typeof(date));
    table.Columns.Add("exam2complete", typeof(date));
    table.Columns.Add("exam3date", typeof(date));
    table.Columns.Add("exam3complete", typeof(date));

    table.Rows.Add("joe", "2018-01-30", NULL, NULL, NULL, NULL, NULL);
    table.Rows.Add('james', '2018-02-14', '2018-02-21', '2018-03-02', NULL, NULL, NULL);
    table.Rows.Add('javier', '2018-01-01', '2018-01-14', '2018-03-01', '2018-03-12', '2018-04-01', NULL);
    return table;
}


static void Main()
{
    DataTable dtInfo = GetTable();
}

How can I run this SQL statement against the DataTable to get the data from the datatable in a format I can use?

SELECT fname,  
    CASE 
    WHEN exam1complete IS NULL THEN 'exam1date'
    WHEN exam2complete IS NULL THEN 'exam2date '
    ELSE 'exam3date' END AS columnname,
    CASE
    WHEN exam1complete IS NULL then exam1date 
    WHEN exam2complete IS NULL then exam2date 
    ELSE exam3date END AS examdate
FROM dtInfo;

May be worth checking into `LINQ` instead of `SQL`. They're practically the same though `LINQ` is within C#. It will allow you pretty much query your dataTable just like a `SQL query`. [Linq Query On A DataTable](https://stackoverflow.com/questions/10855/linq-query-on-a-datatable) — Jaskier, Mar 08 '19 at 13:28
you can't run plain SQL against a data table. you'd have to iterate through the rows performing logic, or use LINQ like Symon suggests. — Jeremy, Mar 08 '19 at 14:01

score 0 · Accepted Answer · answered Mar 08 '19 at 14:19

It looks like a bit wierd but if you change the null's to DateTime.MinValue below code seems to work...

class Program
{
    static void Main(string[] args)
    {
        DataTable dtInfo = GetTable();


        var query =
            from row in dtInfo.AsEnumerable() select new
            {
              name =  row.Field<string>("name"),
              columnname = 
              (row.Field<DateTime>("exam1complete")==DateTime.MinValue) 
              ? 
              ("exam1date") 
              :
              (
                (row.Field<DateTime>("exam2complete") == DateTime.MinValue) ? ("exam2date") : ("exam3date")
              ),
              examdate =
              (row.Field<DateTime>("exam1complete") == DateTime.MinValue)
              ?
              (row.Field<DateTime>("exam1date"))
              :
              (
                (row.Field<DateTime>("exam2complete") == DateTime.MinValue) ? (row.Field<DateTime>("exam2date")) : (row.Field<DateTime>("exam3date"))
              )
            }
            ;
    }

    static DataTable GetTable()
    {
        DataTable table = new DataTable();
        table.Columns.Add("name", typeof(string));
        table.Columns.Add("exam1date", typeof(DateTime));
        table.Columns.Add("exam1complete", typeof(DateTime));
        table.Columns.Add("exam2date", typeof(DateTime));
        table.Columns.Add("exam2complete", typeof(DateTime));
        table.Columns.Add("exam3date", typeof(DateTime));
        table.Columns.Add("exam3complete", typeof(DateTime));

        table.Rows.Add("joe", "2018-01-30", DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, DateTime.MinValue);
        table.Rows.Add("james", "2018-02-14", "2018-02-21", "2018-03-02", DateTime.MinValue, DateTime.MinValue, DateTime.MinValue);
        table.Rows.Add("javier", "2018-01-01", "2018-01-14", "2018-03-01", "2018-03-12", "2018-04-01", DateTime.MinValue);
        return table;
    }
}

Run SQL Query Against DataTable

1 Answers1