How to sort data with the ADO.NET DataView in C#

If in your project you need to sort data by using ADO.NET DataView you can use the next example as a basic. The example uses a page with three GridView controls. When the page loads, it binds the same DataTable to each of the grids. However, it uses three different views, each of which sorts the results using a different field. You can implement the idea in your project by following the next simple steps:

1. Retrieve the data into a DataSet.

2. Fill the GridView controls through data binding:

a. Simply use the DataTable directly to bind the grid, which uses the default DataView and displays all the data.

b. Create new DataView objects and set its Sort property explicitly, for other grids.

3. Copy the values form the DataTable into control separately or for entire page by calling Page.DataBind().

 

Important notes:

1. You can sort by single or by multiple fields. In second case you should specify comma separated list. For example you can replace in the next code view2.Sort = “LastName” with view2.Sort = “LastName, FirstName”;

2. The sort is according to the data type of the column. Numeric and date columns are ordered from smallest to largest. String columns are sorted alphanumerically without regard to case, assuming the DataTable.CaseSensitive property is false (the default). Columns that contain binary data cannot be sorted. You can also use the ASC and DESC attributes to sort in ascending or descending order.

 

// 1. Retrieve the data into a DataSet.

// Create the Connection, DataAdapter, and DataSet.

string connectionString = WebConfigurationManager.ConnectionStrings[“SomeCompany”].ConnectionString;

SqlConnection con = new SqlConnection(connectionString);

 

string sql = “SELECT TOP 5 EmployeeID, TitleOfCourtesy, LastName, FirstName FROM Employees”;

SqlDataAdapter da = new SqlDataAdapter(sql, con);

DataSet ds = new DataSet();

 

// Fill the DataSet.

da.Fill(ds, “Employees”);

// 2. Fill the GridView controls through data binding:

 

// a. Simply use the DataTable directly to bind the grid, which uses the default DataView and displays all the data.

 

grid1.DataSource = ds.Tables[“Employees”];

 

//b. Create new DataView objects and set its Sort property explicitly, for other grid

 

// Sort by last name and bind it to grid  #2.

 

DataView view2 = new DataView(ds.Tables[“Employees”]);

view2.Sort = “LastName”;

grid2.DataSource = view2;

 

// Sort by first name and bind it to grid  #3.

 

DataView view3 = new DataView(ds.Tables[“Employees”]);

view3.Sort = “FirstName”;

grid3.DataSource = view3;

 

// 3. Copy the values form the DataTable into control separately or for entire page by calling Page.DataBind().

Page.DataBind();