How to apply a SQL statement on one or more DataTable's?

Question

Please help me writing a C# or vb.net routine that will accept

• a DataTable

• a SQL query as a string

and create

• a DataTable with the result of the query applied to the in put DataTable

To my knowledge, the tool to run SQL in .net is linq, but this does not lead me to a sollution.

In VB.net terms: How do I implement a function like this

Public Function SelectFromDataTable(Sql As String, T1 As DataTable) As DataTable // Apply Sql to T1 End Function

(or even better, like this)

Public Function SelectFromDataTable(Sql As String, T1 As DataTable, Optional T2 As DataTable) As DataTable // Apply Sql to T1 and T2 End Function

What I tried so far

For some reason, I thought linq could be the solution, but that is no requirement.

Trial 1

if I look for the combination of linq and DataTable's I get that typicle syntax in which you write sql-like code inline in your .net code, as on. Queries in LINQ to DataSet

I want the query to be defined outside my routine,so can you also create such queries from a SQL string?

Trial 2

Looking for the combination of linq and SQL, I get examples using a SqlDataAdapter, but they need a SqlConnection, which apparently must point to a database, as in How to receive a SQL-Statement as a DataTable

However, for me, not only the destination, but also the source should be a DataTable, so can you also create a SqlConnection to DataTables?

Context

If you are curious where my question comes from:

BluePrism is a graphic Robotic Process Automation (RPA) tool. It has one container object, called a collection, which is under the hood a .net DataTable and gives verry little support to manipulate these.

Fortunately, one can create so called "business objects" in .net and implements "Action" that receive and return variables. (This is meant to manipulate other applications, but can also be used to manipulate data.)

We already have such an object, which we called Collection Manipulation. One of the actions, Filter Collection, is implemented as

Dim NewRow As DataRow Collection_Out = Collection_In.Clone For Each parentRow As DataRow In Collection_In.Select(Select_Condition) NewRow = Collection_Out.NewRow For Each c As DataColumn In NewRow.Table.Columns NewRow(c.ColumnName) = parentRow(c.ColumnName) Next Collection_Out.Rows.Add(NewRow) Next NewRow = Nothing Collection_In = Nothing

I would like to implement a general purpose Action, to runs queries against my collection like

select category, sum(unit_price * units) as total_price from invoice group by category; select article, order.units - delivery.units as units_missing from order, delivery where order.article = delivery.article;

Answer 1

I think what you are asking is you want a SQL syntax like multi-purpose select for data tables

using System.Data; using System.Linq; using System.Linq.Dynamic; namespace Foo { public class Bar { /// <summary> /// /// </summary> /// <param name="from"></param> /// <param name="where"></param> /// <param name="skipRows"></param> /// <param name="takeRows"></param> /// <param name="orderBy">Needed for range selections (skipRows, takeRows) </param> /// <returns></returns> public DataTable GeneralPurposeSelect(DataTable from, string where = null, int? skipRows = null, int? takeRows = null, string orderBy = "Id") { var fromQryAble = from.AsEnumerable().AsQueryable(); IQueryable<DataRow> toQryAble = null; if (!string.IsNullOrEmpty(where)) { toQryAble = fromQryAble.Where(where); } if (takeRows != null) { if (skipRows == null) { skipRows = 0; } } if (skipRows != 0) { if (takeRows == null) { takeRows = int.MaxValue; } } if (takeRows != null) { if (skipRows == null) { skipRows = 0; } toQryAble = toQryAble == null ? fromQryAble.OrderBy(orderBy).Skip(skipRows.Value).Take(takeRows.Value) : toQryAble.OrderBy(orderBy).Skip(skipRows.Value).Take(takeRows.Value); } return toQryAble == null ? from : toQryAble.CopyToDataTable(); } } }