Styjun

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;

If I understood your question correct, you want a SQL syntax like multi-purpose select for data tables.

Based on the info found here: https://www.hanselman.com/blog/TheWeeklySourceCode48DynamicQueryableMakesCustomLINQExpressionsEasier.aspx, I wrote the following example further down. You can expand it as you see fit.

TL;DR Add the System.Linq.Dynamic NuGet package so you can use strings for where clauses amongst others.

BTW: Writing a query string parser, to parse for instance "select category, sum(unit_price * units) as total_price from invoice group by category;" is entirely possible, but IMHO you will spend a lot of time to gain little.

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();