Search code examples
c#.netdatasetrowfilterdefaultview

Querying inside a Dataset C#

I have an ADO.NET dataset which is set by a certain query, say

SELECT ID,USER,PRODUCT,COUNT FROM PRODUCTION

Without using a where clause I need to derive some results from the dataset. Say I want to get the User and Product count of the user who has the maximum product count. (And I want to do it by using the existing dataset. I can't derive this from dataset.)

Any idea of a way to query inside the dataset? Since there are Datatables my thought was there is some way to query it.

Solution

  • Traditional SQL queries cannot be applied to the DataSet. The following is possible, however:

    • Filter rows using DataTable.Select. See here for detailed information about expressions in DataTables.
    • Calculate totals etc. using DataTable.Compute.
    • If these two don't do the trick, there's always LINQ.

    Quick-and-dirty LINQ example: (which doesn't return a DataTable, but a list containing an anonymous type):

    var joinedResult = dataTable1
  // filtering:
  .Select("MyColumn = 'value'")
  // joining tables:
  .Join(
    dataTable2.AsEnumerable(), 
    row => row.Field<long>("PrimaryKeyField"), 
    row => row.Field<long?>("ForeignKeyField"),
    // selecting a custom result:
    (row1, row2) => new { AnotherColumn = row1.Field<string>("AnotherColumn") });

    AsEnumerable converts a DataTable into an IEnumerable on which LINQ queries can be performed. If you are new to LINQ, check out this introduction.