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
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;
}

private void selectButton_Click(object sender, System.EventArgs e)
{
    // This example will select the top n freight values.
    // Set the field name variable.
    String topNFieldName = FREIGHT_FIELD;

    int topN = 0;
    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.
    // Eliminate enough rows from the bottom of the dv using a filter on
    // the primary key to return the correct number (top n) of values.
    bool refilter = false;
    // Iterate over all records in the view after the nth.
    for(int i = dv.Count; i > topN; i--)
    {
        // Exclude the record using a filter on the primary key.
        rowFilter.Append(" AND " + ORDERID_FIELD + "<>" +
            dv[i-1][ORDERID_FIELD]);
        refilter = true;
    }

    // Reapply the view filter if necessary.
    if (refilter)
        dv.RowFilter = rowFilter.ToString( );

    // Bind the view to the grid.
    dataGrid.DataSource = dv;
    dataGrid.CaptionText = ORDERS_TABLE + " table: Top " + topN +
        " records for " + FREIGHT_FIELD + " value.";
}

Discussion

While it is possible to locate, sort, and filter records in a DataTable or DataView, there is no method in either class to select the top n rows.

The procedure to get the user-specified top n rows with the largest Freight value involves several steps. First, sort the DataView on the Freight field in descending order; this places the top n records at the top of the view. Next, get the Freight value for the nth record and set the DataView filter to contain only rows with a Freight value greater than or equal to that value. Add the appropriate delimiters when making non-numeric comparisons in the filter expression.

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.