[ Team LiB ]Recipe 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

try
{
topN = Convert.ToInt32(topNTextBox.Text);

if(topN <= 0)
{
MessageBox.Show("Enter an Integer greater than 0.", "",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
}
catch(System.FormatException)
{
MessageBox.Show("Enter an Integer greater than 0.", "",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}

// Clear the filter on the view.
dv.RowFilter = "";
// Sort the view descending on the top n field.
dv.Sort = topNFieldName + " DESC";

// Create a filter for all records with a value greater than the nth.
StringBuilder rowFilter = new StringBuilder(topNFieldName + ">=" +
dv[topN-1][topNFieldName]);
// Apply the filter to the view.
dv.RowFilter = rowFilter.ToString( );

// Handle where there is more than one record with the nth value.

At this point, we are done unless there can be more than one instance of the value in the
nth record, as is the case with Freight. In this case, iterate over the records following the
nth record and add criteria to a copy of the data view filter to exclude them from the
view. Use either the primary key or a unique column or combination of columns to
identify the row to be excluded in each case. Apply the new filter to the view. If the view
is ordered on the primary key or unique columns in addition to the top n columns, this
can be used in the initial data view filter to limit returned records in cases where there
might be duplicate values in the nth record. This would be used instead of the technique
just outlined. However, the technique shown requires no sort other than on the top n
column.
The solution can be extended with little change to handle multiple column top n criteria
as well as ascending sorts.
Finally, the T-SQL TOP clause limits the number of rows returned by an SQL statement
from the data source. This might be a more appropriate solution in some cases, especially
when the disconnected table does not already exist. For more information, look up "TOP
clause" in Microsoft SQL Server Books Online.
[ Team LiB ]