none
SQL query over DataTableCollection RRS feed

  • Question

  • Hello all.

    Is it possible to run a SQL command over multiple tables in a DataTableCollection? I currently have a problem where my query using Microsoft dBASE Driver .dbf works on several computers, but the same query does not work on other computers. Much work has been done to determine driver versions and whatnot. I am at the point where I can run SELECT * FROM TABLE_X (where x is 1, 2, and 3) successfully. I would now like to run a query using an INNER JOIN between the 3 tables of my DataTableCollection. Is this possible?

    So we have:
    DataSet.Table[0] = "TABLE_1"
    DataSet.Table[1] = "TABLE_2"
    DataSet.Table[2] = "TABLE_3"

    I would like to run a query such as:
    SELECT
    DataSet.Table[0] .NAME, DataSet.Table[1] .DOB
    FROM
    DataSet.Table[0] , DataSet.Table[1], DataSet.Table[2]
    WHERE
    DataSet.Table[0].NAME = DataSet.Table[1].NAME AND DataSet.Table[1].DOB = DataSet.Table[1].DOB

    (I know this is a stupid query, but it shows my point)
    Wednesday, June 20, 2007 3:38 AM

All replies

  • You can not run SQL commands against a data set or its table.  Commands only run against data providers.  Normally you run the command to retrieve the data set from the DB.  However the DataTable class does have a Select method where you can retrieve the rows that match a certain criteria.  It can only use basic expressions that target the columns.  Your particular query is too complex for Select however.  You should really do this type of query in your DB to avoid bringing in too much data.  Pretty much any type of JOIN or UNION is going to require DB support although you can implement it in code if you wanted.

     

    Michael Taylor - 6/20/07

    http://p3net.mvps.org

    Wednesday, June 20, 2007 11:22 AM
  • I see. I will try to get as much data as possible into the DataTableCollection and then run a generic query against it. Thanks for the help.
    Thursday, June 21, 2007 2:53 AM