ADO.NET Cookbook

3.10. Selecting the Top n Rows in a DataTable

Problem

You want to create a grid that shows the t op five rows in a DataTable, based on the values in one of the columns.

Solution

Use an appropriate sort order with a DataView filter.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataTable containing the Orders table from the Northwind sample database. The default view of the table is bound to the data grid on the form.

Select Button.Click

Builds a filter on the DataView to limit the number of rows to the user-specified count with the largest Freight values.

The C# code is shown in Example 3-10.

Example 3-10. File: DataViewTopNSelectForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Text; using System.Data; using System.Data.SqlClient; private DataView dv; // Table name constants private const String ORDERS_TABLE = "Orders"; // Field name constants private const String ORDERID_FIELD = "OrderID"; private const String FREIGHT_FIELD = "Freight"; // . . . private void DataViewTopNSelectForm_Load(object sender, System.EventArgs e) { // Fill the Orders table. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable dt = new DataTable(ORDERS_TABLE); da.Fill(dt); da.FillSchema(dt, SchemaType.Source); // Get the default view for the table and bind it to the grid. dv = dt.DefaultView; dataGrid.DataSource = dv; ...