ASP.NET GridView Tutorial

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

Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists

Link for text version of this video
http://csharp-video-tutorials.blogspot.com/2013/04/sorting-gridview-that-uses_4.html

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.
http://csharp-video-tutorials.blogspot.com/2013/02/deleting-data-from-gridview-using.html

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
{
//Replace square brackets with angular brackets
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;
}
}
}