Querying inside a Dataset C#
Solution 1
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.
Solution 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.
JCTLK
I am a Software engineer and mainly into C#. Also interested in SQL server and it's applications.
Updated on June 04, 2022Comments
-
JCTLK almost 2 years
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.