3

I am programming an Excel add-in in C# where I process data contained in different DataTable objects. I would like to provide a function to perform SQL queries on the data, with the ability to reference data from other tables in where and sort by clauses (for example, using a join).

An example of such a query would be

SELECT name
FROM Table1
WHERE id = Table2.id AND Table2.age > 18

The problem with this is that a DataTable doesn't know of the existance of the other DataTables, so (for so far I know) there are no such methods in the class. Also, I cannot use something like LINQ, since the query will be written by the users of the add-in in excel.

Would it be a good solution to copy the data to an in-memory database, where each DataTable is mapped to a table? How would this work performance-wise? Is there a simpler solution?

Niels Waleson
  • 31
  • 4
  • A SQL query always returns one flat table so there is never an existence of other tables. Either you need to use a join in the SQL Query to combine tables, or the queries you already perform have a common field(s) that you can use Linq to join. – jdweng Jun 27 '15 at 14:43

1 Answers1

1

In terms of SQL query you are missing a table reference in selecting the tables, corrected query will look like

SELECT name
FROM Table1, Table2
WHERE Table1.id = Table2.id AND Table2.age > 18

Use Table1.name if there is same named attribute in Table2. However using only WHERE condition in Joins without specifying the joining attribute is not recommended read this question. Use JOIN.

SELECT Table1.name
    FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id WHERE Table2.age > 18
Community
  • 1
  • 1
Ritwik
  • 521
  • 7
  • 17