ADO.NET 3.5 Cookbook, 2nd Edition

4.8. Selecting the Top N Rows in a DataTable

Problem

You want to select the top 10 rows in a DataTable, based on the values in one or more of the columns, without requerying the data source.

Solution

Use an appropriate sort order with a DataView filter.

The solution creates a DataTable containing data from the HumanResources. EmployeePayHistory table in AdventureWorks and uses the Filter and Sort properties of the default DataView to select exactly four rows with the top Rate values.

The C# code in Program.cs in the project SelectTopNRowsDataTable is shown in Example 4-9.

Example 4-9. File: Program.cs for SelectTopNRowsDataTable solution

using System; using System.Data; using System.Data.SqlClient; using System.Text; namespace SelectTopNRowsDataTable { class Program { private static int topN = 4; static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Sales.SalesTaxRate"; // Fill the DataTable with Sales.SalesOrderHeader schema and data DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); // Sort the default view on the TaxRate field DataView dv = dt.DefaultView; dv.Sort = "TaxRate DESC"; // Output the top 10 records from the DataView Console.WriteLine("---First 10 records from DataView---"); for (int i = 0; i < 10; i++) Console.WriteLine("Record = {0}\t{1}\t{2}\tTaxRate ...