CODEWITHSUNDEEP
c#sqlexceldatatablein-memory-database
Niels Waleson
Niels Waleson

Reputation: 31

How to perform a complex SQL query on DataTable objects?

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?

Upvotes: 3

Views: 591

Answers (1)

Ritwik
Ritwik

Reputation: 611

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

Upvotes: 1

Related Questions