Filtering and Sorting in ADO.NET

Filtering and Sorting with the DataView Object

using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace Akadia.DataView
{
  public class FilterOrder : System.Windows.Forms.Form
  {
    ....
    private SqlConnection cn;
    private SqlCommand cmd;
    private SqlDataAdapter da;
    private DataSet ds;
   
    public FilterOrder()
    {
      try
      {
        InitializeComponent();
        // Initializing
        cn = new SqlConnection("
        server=xeon;database=northwind;uid=sa;pwd=manager");
        cmd = new SqlCommand("SELECT * FROM orders",cn);
        da = new SqlDataAdapter(cmd);
        ds = new DataSet();
        // Load initial Data
        RetrieveData();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Retrieve Orders from the DB into the DataGrid
    private void RetrieveData()
    {
      try
      {
        da.Fill(ds,"Orders");
        DataGrid.DataSource = ds.Tables[0];
        // Fill Combobx with Column Names
        FillSortCriteria();
      }
      catch (Exception ex)
      {
        Debug.WriteLine(ex.ToString());
        MessageBox.Show(ex.ToString());
      }
    }
    // Fill Combobx with Column Names
    private void FillSortCriteria()
    {
      try
      {
        // IF Combobox are already filled return ...
        if (cmbSortArg.Items.Count > 0)
        {
          return;
        }
        // ... else fill Comobox with Column Names
        foreach (DataColumn dc in ds.Tables[0].Columns)
        {
          cmbSortArg.Items.Add(dc.Caption);  // Sort Combobox
          cmbFields.Items.Add(dc.Caption);  // Filter on Column Combobox
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Setup the expression used to filter the rows which
    // are viewed by the DefaultView
    private void SetFilter(string strFilterExpression)
    {
      try
      {
        // Apply Filter Expression
        ds.Tables[0].DefaultView.RowFilter = strFilterExpression;
        // Gets the number of records in the DataView after
        // RowFilter and RowStateFilter have been applied.
        if (ds.Tables[0].DefaultView.Count > 0)
        {
          DataGrid.DataSource = ds.Tables[0].DefaultView;
        }
        else
        {
          MessageBox.Show("Filter criteria does not meet criteria");
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Remove any existing Filter and ResultSet from a previous query
    private void btnQuery_Click(object sender, System.EventArgs e)
    {
      try
      {
        // Clear DataSet
        ds.Clear();       
        // Clear Filter
        ds.Tables[0].DefaultView.RowFilter = "";
        // Re-Retrieve Data
        RetrieveData();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Sets the sort column or columns, and sort order for the DataView
    private void btnSort_Click(object sender, System.EventArgs e)
    {
      try
      {
        string strSort;
        // IF Radiobox "Ascending" is checked, then
        // sort ascending ...
        if (rbAsc.Checked)
        {
          strSort = cmbSortArg.Text + " ASC";    // Note space after "
        }
        // ... else descending
        else
        {
          strSort = cmbSortArg.Text + " DESC";  // Note space after "
        }
        // Apply Sort Criteria to the DataView
        ds.Tables[0].DefaultView.Sort = strSort;
        DataGrid.DataSource = ds.Tables[0].DefaultView;
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
         
    }
    // Setup specific Filter: "CutomerID LIKE 'xxx'"
    private void btnFilterTitle_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter("CustomerID like '" + txtFilter.Text + "'");
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Setup the general Filter entered by the User
    private void btnGeneralFilter_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter(txtGeneralFilter.Text);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    // Setup the Column Filter entered by the User
    private void btnFilteronColumn_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter(cmbFields.Text + " " + txtFilterColumn.Text);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }
    ...
    [STAThread]
    static void Main()
    {
      Application.Run(new FilterOrder());
    }
  }
}

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski