Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Sorting a gridview that uses objectdatasource control and a dataset - Part 45

Suggested Videos 
Part 42 - Detailsview insert update delete without using data source controls
Part 43 - Nested gridview in ASP.NET
Part 44 - Sorting a gridview that uses sqldatasource control



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.



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. In this video, we will discuss about solving it using dataset. In our next video, we will discuss about the second way.

Change the implementation of GetAllEmployees() method to return DataSet, as shown below.
public class EmployeeDataAccessLayer
{
    public static DataSet GetAllEmployees()
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlDataAdapter da = new SqlDataAdapter("Select * from tblEmployee", con);

            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }
    }
}

Here is the HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataSourceID="ObjectDataSource1" AllowSorting="true">
    <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>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetAllEmployees" TypeName="Demo.EmployeeDataAccessLayer">
</asp:ObjectDataSource>

No comments:

Post a Comment

It would be great if you can help share these free resources