Create table from existing DataTable/Dataset data with ADO.net ?

Question

I need to create two tables.

The first one I can get via database with a command. No sweat.

The second one is built from the first one with nested SELECT statements, JOINs, and operators like SUM, AVG etc. So it needs more functionality than filtering and sorting. It is done in C# with .NET 2.0, so no advanced features available.

Essentially I can execute a second command, but I wonder if I can circumvent this if I have all my necessary data already available in a DataTable/Dataset ?

I am aware that I can create and fill a new table manually, but that would be tedious and error-prone.

I do need both tables and the database must not be influenced. The solution must use only ADO.NET, there is no SQL Server or other tools installed.

Is there any way to create a new table with SQL-Like statements on the already local existing data and if yes, what are the limitations ?

Answer 1

With out specifics it's hard to answer your question but you should look into Filtering and Sorting. This allows you to create views of your dataset without requerying the datasource.

Answer 2

Perhaps you should look at LINQ. Linq-to-datasets is standard and lets you query a[n in-memory] DataTable object with SQL-like syntax.

It looks like this (simple filtering):

var result = from row in yourDataTable.AsEnumerable()
   where row.Field<string>("FieldName").Contains("Something")
   select row;

Aggregations/joins are also allowed. Here's an official reference from Microsoft.