Sorting a gridview that uses objectdatasource control and business objects - Part 46

Suggested Videos 
Part 43 - Nested gridview in ASP.NET
Part 44 - Sorting a gridview that uses sqldatasource control
Part 45 - Sorting a gridview that uses objectdatasource control and a dataset



In this video we will discuss about sorting a gridview control that uses objectdatasource control. We will discuss about sorting in both ascending and descending order.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.

Please watch Part 45, before proceeding with this video.



Step 1: Drag and drop a gridview and an objectdatasource control on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs". Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
    
    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees()
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Step 3: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetAllEmployees() method.

Step 4: Associate GridView1, with ObjectDataSource1 control.

Step 5: Flip WebForm1.aspx to source mode and set AllowSorting="true" on GridView1 control.

Step 6: Run the application now. Click on any of the column headers to sort data. Notice that you get a runtime error stating - The data source 'ObjectDataSource1' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.

There are 2 ways to solve this issue. We discussed about one way of solving it in Part 45.

In this video, we will discuss about solving it without using a dataset. There are 2 changes that we need to make.
1. Change the implementation of GetAllEmployees() method to accept a parameter that specifies the column by which we want to sort. We will then use this parameter when building the sqlquery.
// sortColumn parameter will be used in order by clause later
public static List<Employee> GetAllEmployees(string sortColumn)
{
    List<Employee> listEmployees = new List<Employee>();

    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string sqlquery = "Select * from tblEmployee";
        // if sortColumn is not null or empty
        // then append order by clause to the sql query
        if (!string.IsNullOrEmpty(sortColumn))
        {
            sqlquery += " order by " + sortColumn;
        }

        SqlCommand cmd = new SqlCommand(sqlquery, con);

        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Employee employee = new Employee();
            employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
            employee.Name = rdr["Name"].ToString();
            employee.Gender = rdr["Gender"].ToString();
            employee.City = rdr["City"].ToString();

            listEmployees.Add(employee);
        }
    }

    return listEmployees;
}

2. Specify ObjectDataSource1 control's, SortParameterName attribute. 
<asp:ObjectDataSource ID="ObjectDataSource1" 
    TypeName="Demo.EmployeeDataAccessLayer"
    SortParameterName="sortColumn"
    SelectMethod="GetAllEmployees"
    runat="server">
</asp:ObjectDataSource>

Notice that we have set SortParameterName="sortColumn". This is the parameter used by GetAllEmployees(string sortColumn) method.

Here is the complete HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    AllowSorting="True" DataSourceID="ObjectDataSource1">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" 
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" 
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" 
            SortExpression="City" />
    </Columns>
</asp:GridView>
<br />
<asp:ObjectDataSource ID="ObjectDataSource1" 
    TypeName="Demo.EmployeeDataAccessLayer"
    SortParameterName="sortColumn" SelectMethod="GetAllEmployees" 
    runat="server">
</asp:ObjectDataSource>