none
HOWTO run a select statement off a DataTable. RRS feed

  • Question

  • HOWTO run a select statement off a DataTable.

    Please help,

    I want to take a given string which is a select statement like "select COL_1, COL_2 from MyDataTable where COL_3 like '%TEST%'" against a given DataTable object.

    I am awar of the method "Select" but this is not the same thing that I am seeking. I want to keep my query as a string, and some how pass it to something like a "OleDbCommand" object and run the "ExecuteReader". If I could this is what I would like:


    Given: DataTable DataTable_MyDataAllSetup;

                OleDbConnection_Temp = new OleDbConnection(~~~ some how point to DataTable_MyDataAllSetup ~~~);
                OleDbConnection_Temp.Open();

                OleDbCommand_Temp = new OleDbCommand
                (
                  "select COL_1, COL_2 from MyDataTable where COL_3 like '%TEST%'",
                  OleDbConnection_Temp
                );

                OleDbDataReader_Temp = null;
                OleDbDataReader_Temp = OleDbCommand_Temp.ExecuteReader();

                while (OleDbDataReader_Temp.Read())
                {
                  // Do stuff...
                }

     

    Monday, September 19, 2011 5:14 PM

Answers

  • This is impossible. The DataTable is an in-memory representation of data. The OleDbCommand and OleDbDataReader work on databases on a database server (or other external things like Excel spreadsheets). The two are totally un-related.

    If you've already got data in a DataTable, use the DataTable.Select() ... plain and simple. If you need to get data from the database, that's when you'd use the OleDb stuff.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Larcolais Gong Monday, September 26, 2011 2:10 AM
    Wednesday, September 21, 2011 4:31 AM
  • Hello,

    Thank you for posting.

    Based on my understanding, it looks that you would like to query DataTable with LIKE keyword. If I'm correct, I suggest you can check the following information.

    1. You should set your DataTable as Enumerable. You can code like the following.

    dataTable.AsEnumerable();
    

    2. You can use Linq to Object to query and filter DataTable. You can check the following code sample.

          public void QryDataTable(DataTable table) {
          var productNames = from products in table.AsEnumerable() select products.Field<string>("ProductName");
          Console.WriteLine("Product Names: ");
          foreach (string productName in productNames) {
             Console.WriteLine(productName);
          }
    
    

    Please check here for more information about Linq to Objects. http://msdn.microsoft.com/en-us/library/bb397919.aspx

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Monday, September 26, 2011 2:10 AM
    Wednesday, September 21, 2011 5:52 AM

All replies

  • This is impossible. The DataTable is an in-memory representation of data. The OleDbCommand and OleDbDataReader work on databases on a database server (or other external things like Excel spreadsheets). The two are totally un-related.

    If you've already got data in a DataTable, use the DataTable.Select() ... plain and simple. If you need to get data from the database, that's when you'd use the OleDb stuff.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Larcolais Gong Monday, September 26, 2011 2:10 AM
    Wednesday, September 21, 2011 4:31 AM
  • Hello,

    Thank you for posting.

    Based on my understanding, it looks that you would like to query DataTable with LIKE keyword. If I'm correct, I suggest you can check the following information.

    1. You should set your DataTable as Enumerable. You can code like the following.

    dataTable.AsEnumerable();
    

    2. You can use Linq to Object to query and filter DataTable. You can check the following code sample.

          public void QryDataTable(DataTable table) {
          var productNames = from products in table.AsEnumerable() select products.Field<string>("ProductName");
          Console.WriteLine("Product Names: ");
          foreach (string productName in productNames) {
             Console.WriteLine(productName);
          }
    
    

    Please check here for more information about Linq to Objects. http://msdn.microsoft.com/en-us/library/bb397919.aspx

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Monday, September 26, 2011 2:10 AM
    Wednesday, September 21, 2011 5:52 AM