How do I execute a complex SQL query on DataTable objects?
I am programming an Excel add-in in C # where I process the data contained in different objects DataTable
. I would like to provide a function for executing SQL queries on the data, with the ability to refer to data from other tables where and sort by sentences (for example, using a join).
An example of such a request would be
SELECT name
FROM Table1
WHERE id = Table2.id AND Table2.age > 18
The problem is that a DataTable
doesn't know about the existence of another DataTable
s, so (as far as 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 add-in users in excel.
Would it be a good solution to copy the data into an in-memory database where each is DataTable
mapped to a table? How will this work in terms of performance? Is there an easier solution?
source to share
In terms of SQL query, you are missing the table reference when selecting tables, the revised query will look like
SELECT name
FROM Table1, Table2
WHERE Table1.id = Table2.id AND Table2.age > 18
Use Table1.name
if Table2
there is the same attribute in. However, using only a condition WHERE
in Joins without specifying a join attribute is not recommended to read this question . Use JOIN
.
SELECT Table1.name
FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id WHERE Table2.age > 18
source to share