How to: Sort and Filter ADO.NET Data with the Windows Forms BindingSource Component

How to: Sort and Filter ADO.NET Dat...

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Windows Forms Programming

How to: Sort and Filter ADO.NET Data with the Windows Forms BindingSource Component

You can expose the sorting and filtering capability of BindingSource control through the Sort and Filter properties. You can apply simple sorting when the underlying data source is an IBindingList, and you can apply filtering and advanced sorting when the data source is an IBindingListView. The Sort property requires standard ADO.NET syntax: a string representing the name of a column of data in the data source followed by ASC or DESC to indicate whether the list should be sorted in ascending or descending order. You can set advanced sorting or multiple-column sorting by separating each column with a comma separator. The Filter property takes a string expression.

Note:
Storing sensitive information, such as a password, within the connection string can affect the security of your application. Using Windows Authentication (also known as integrated security) is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

To filter data with the BindingSource

Set the Filter property to expression that you want.
In the following code example, the expression is a column name followed by value that you want for the column.

Visual Basic

BindingSource1.Filter = "ContactTitle='Owner'"

            BindingSource1.Filter = "ContactTitle='Owner'";

To sort data with the BindingSource

Set the Sort property to the column name that you want followed by ASC or DESC to indicate the ascending or descending order.
Separate multiple columns with a comma.

Visual Basic

BindingSource1.Sort = "Country DESC, Address ASC"

            BindingSource1.Sort = "Country DESC, Address ASC";

Example

The following code example loads data from the Customers table of the Northwind sample database into a DataGridView control, and filters and sorts the displayed data.

Visual Basic

Private Sub InitializeSortedFilteredBindingSource()

    ' Create the connection string, data adapter and data table.
    Dim connectionString As New SqlConnection("Initial Catalog=Northwind;" & _
        "Data Source=localhost;Integrated Security=SSPI;")
    Dim customersTableAdapter As New SqlDataAdapter("Select * from Customers", _
        connectionString)
    Dim customerTable As New DataTable()

    ' Fill the the adapter with the contents of the customer table.
    customersTableAdapter.Fill(customerTable)

    ' Set data source for BindingSource1.
    BindingSource1.DataSource = customerTable

    ' Filter the items to show contacts who are owners.
    BindingSource1.Filter = "ContactTitle='Owner'"
    ' Sort the items on the company name in descending order.
    BindingSource1.Sort = "Country DESC, Address ASC"

    ' Set the data source for dataGridView1 to BindingSource1.
    dataGridView1.DataSource = BindingSource1


End Sub

        private void InitializeSortedFilteredBindingSource()
        {
            // Create the connection string, data adapter and data table.
            SqlConnection connectionString =
                 new SqlConnection("Initial Catalog=Northwind;" +
                 "Data Source=localhost;Integrated Security=SSPI;");
            SqlDataAdapter customersTableAdapter =
                new SqlDataAdapter("Select * from Customers", connectionString);
            DataTable customerTable = new DataTable();

            // Fill the the adapter with the contents of the customer table.
            customersTableAdapter.Fill(customerTable);

            // Set data source for BindingSource1.
            BindingSource1.DataSource = customerTable;

            // Filter the items to show contacts who are owners.
            BindingSource1.Filter = "ContactTitle='Owner'";

            // Sort the items on the company name in descending order.
            BindingSource1.Sort = "Country DESC, Address ASC";

            // Set the data source for dataGridView1 to BindingSource1.
            dataGridView1.DataSource = BindingSource1;

        }

Compiling the Code

To run this example, paste the code into a form that contains a BindingSource named BindingSource1 and a DataGridView named dataGridView1. Handle the Load event for the form and call InitializeSortedFilteredBindingSource in the load event handler method.

To filter data with the BindingSource

To sort data with the BindingSource

Tasks

Reference

Other Resources