Querying inside a Dataset C#

Question

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.

`DataTable.Compute` is best when you need `count(*)` or `max(COUNT)` like operations — Waqas Raja, Mar 31 '11 at 06:20

bernhof · Accepted Answer · 2014-07-10T09:43:25.903

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.

score 2 · Answer 2 · edited Mar 31 '11 at 06:06

2

Yes, you can use DataTable.Select method.

DataTable table = DataSet1.Tables["Orders"];
    // Presuming the DataTable has a column named Date.
    string expression;
    expression = "Date > #1/1/00#";
    DataRow[] foundRows;

    // Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression);

    // Print column 0 of each returned row.
    for(int i = 0; i < foundRows.Length; i ++)
    {
        Console.WriteLine(foundRows[i][0]);
    }

Also see this link.

edited Mar 31 '11 at 06:06

Vijay Mathew

26,737
4
62
93

answered Mar 31 '11 at 05:10

Anuraj

18,859
7
53
79

How about getting the Max count... I mean cant I have much advanced query like "SELECT COUNT(*) FROM .... " – JCTLK Mar 31 '11 at 05:24
1

You can get count using .Length. For more advanced one use Linq - http://msdn.microsoft.com/en-us/library/bb387004.aspx – Anuraj Mar 31 '11 at 06:01
1

@JCTLK: Here is how you count: `.Expression = "Count(OrderID)"`. Explore [`DataColumn.Expression`](http://msdn.microsoft.com/en-us/library/cabd21yw%28vs.71%29.aspx) property. – KMån Mar 31 '11 at 06:17
1

@KMan : If you use DataColumn.Expression, you need a column to display count right? – Anuraj Mar 31 '11 at 06:23

score 0 · Answer 3 · answered Oct 12 '12 at 17:30

0

You can do cross-table queries of a Dataset object using LINQ to DataSet:

msdn.microsoft.com/en-us/library/bb386969.aspx

answered Oct 12 '12 at 17:30

Buggieboy

4,636
4
55
79

Querying inside a Dataset C#

3 Answers3