Filtering Data in ADO.NET

There are two ways for displaying the filtered data to the user. The ways are as mentioned below:

1. The Data Table Select Method: It is overloaded for accepting the argument to filter and sort data rows returned from the array object.
2. The DataView object methods: The lists of argument which are supported are similar to that of the select method.

1. Data Table Select method

The Data Table Select method returns the DataRow array for a table. The record can be for a particular sort order. The changes to the data are reflected to the user. The select method accepts a filter and sort argument and returns an array of DataRow objects.

The list of methods provided by the DataRow objects are as mentioned below:

1. public DataRow[ ] Select (); It is used to return the array of all the DataRow objects.
2. public DataRow[ ] Select ( string ) : It is used to return the array of the DataRow objects that satisfy the filter criteria
3. public DataRow[ ] Select ( string, string ): It returns the array of objects that satisfies the criteria in a particular sort order.
4. public DataRow[ ] Select ( string, string, DataViewRowState): It gets an array of DataRow objects that matches the filter in order of the sorting and matches the criteria.

The limitation with the DataTable Select is that it does not return the filtered table. User cannot directly bind the array to the data binding controls. The Data View control is useful for managing the filter condition.

The following code snippet demonstrates the use of DataRow Select statement in ADO.NET.

Code:

using System;
using System.Data;
using System.Data.SqlClient;

public class FilterData: System.Windows.Forms.Form
{
    public FilterData()
    {
        InitializeComponent();
        SqlConnection con = new SqlConnection ( " Data Source=SQLSERVER02; userid=sa; password=abcd1234; Database=master");
        DataSet ds = new DataSet ("Student");
        SqlDataAdapter da= new SqlDataAdapter ( "Select * from Student", con );
        da.TableMappings.Add( "Table", "Student" );
        da.Fill( ds );
        dg1.DataSource = ds.Tables [ "Student" ];
        dg1.DataBind();
    }
    public static void Main ( )
    {
        string strData = null;
        string strExpr = "StudID=101 OR StudID =102";
        string strSort = "StudID DESC";
        DataRow[ ] resultRows = table1.Select ( strExpr, strSort );
        for ( int i = 0; i<resultRows.GetUpperBound(0);i++)
        {
            for ( int j=0; j<resultRows[i].ItemArray.GetUpperBound(0);j++)
            {
                strData = strData +"\t";
            }
            strData = strData + "\n";
            txt1.Text = strData;
        }
    }
}

2. The DataView object methods

A Dataview is an object associated in a disconnected environment. It is defined in the System.Data namespace. The DataView class enables the user to create several views of the data in a datatable. User can sort and filter data using the dataview. User can add, delete, and modify the rows in the data table.

A dataview has a view of data stored in a datatable. Whenever user modifies the data in a datatable, the dataview associated with the datatable will display the modified data. There are a few restrictions associated with the dataview. They are as mentioned below:

1. The dataview cannot be used as a datatable
2. The dataview cannot provide a view of joined datatable objects
3. The dataview cannot exclude columns present in the datatable.

Creating a DataView

The dataview contains an object model associated with it. The following figure shows the dataview.



In the above figure, the DataTable object contains a default DataView object associated with it. The DefaultView property is used to refer the view. The filtering and sorting properties are enabled later.

A DataView object creates a fixed customized view of a DataTable object. Each dataset has a data view attached with it. The DataView object can be used to display the data based on the criteria.

The following figure is used to display the customized data through the dataviews.



In the above figure, a dataview is used to display the customized data form the data table to the Windows application. The data is filtered on the specified condition. A DataTable can contain multiple DataView objects. It helps user to view the data in different ways.

User can define multiple views on the same datatable. For defining a new view, the DataView object is created. The sorting and filtering of the DataTable object is possible.

The syntax for DataView is as shown below:

Code:

DataView dw = new DataView();

Here, dw is the DataView object.

Filtering and Sorting through the DataViews

The dataview helps user to sort and filter the data in a datatable. There are some points useful while specifying the sorting and filtering of DataView object. They are as mentioned below:

1. The Sort property is useful for sorting the single or multiple columns of the object. The columns are sorted in ascending or descending order.
2. The ApplyDefaultSort property is useful for creating a sorting order of the object. The columns are always sorted in ascending order using this property.
3. The Find() or FindRows() property is useful for returning the subset of rows on the result of the query
4. The RowFilter property is useful for returning the subset of rows on the column values. User can sort the DataView object using the Sort property. The ascending order is the default order for sorting. The following code snippet demonstrates the sorting of two columns in different sorting order.

Code:

DataView dv = new DataView();
dv.Sort = "StudID ASC, Percentage DESC";

The RowFilter property is useful for filtering the DataView object. The functionality is similar to the WHERE clause. The following code snippet demonstrates the property.

Code:

DataView dv = new DataView();
dv.RowFilter = " DOB = 1990";

In the above code, the RowFilter property filters the data where the DOB is 1990.

The RowStateFilter property is used for filters rows and controls the version of the row. The DataView object constructor is used to set the RowStateFilter property. The enumeration is used to retrieve the particular version of data from the datarow.

The list of enumeration values for the DataViewRowState enumeration is mentioned below:

1. CurrentRows: It retrieves the current row version of the added, modified and unchanged rows. It is the default value.
2. Added: It retrieves the current row version of the rows added.
3. ModifiedCurrent: It retrieves the current row version of the modified rows.
4. ModifiedOriginal: It retrieves the original row versions of the modified rows.
5. Deleted: It retrieves the version of the deleted rows.
6. None: No rows are retrieved for the user.
7. Unchanged: It retrieves the current row version of the unchanged rows.

The following code snippet demonstrates the enumeration of the RowStateFilter property.

Code:

DataView dv = new DataView();
dv.RowFilter = " DOB >1980";
dv.RowStateFilter = DataViewRowState.Added;

Consider an example where user wants to view the Students whose major subject is Maths.

Code:

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataView
{
    class Program
    {
        static void Main( string[] args )
        {
            string connectionString = " Data Source=SQLSERVER01; Initial Catalog=master; User id=sa; Password=abcd1234";
            string data = " Select * from Student";
            SqlConnection con = new SqlConnection ( connectionString );
            try
            {
                SqlDataAdapter da= new SqlDataAdapter();
                da.SelectCommand = new SqlCommand ( data, conn );
                DataSet ds = new DataSet();
                da.Fill ( ds, "Student");
                DataTable dt = ds.Tables [ "Student" ];
                DataView dv = new Dataiew ( dt, "MajorSubject="Maths",
                DataViewRowState.CurrentRows);
                foreach ( DataRowsView drv in dv )
                {
                    for ( int i=0; i<dv.Tables.Columns.Count; i++)
                    {
                        Console,WriteLine(drv[i]+"\t");
                    }
                }
            }
            catch ( Exception e )
            {
                Console.WriteLine(" No value found "+e);
            }
            finally
            {
                conn.Close();
            }
            Console.ReadLine();
        }
    }
}