GridView-custom-paging-with-ObjectDataSource


            <%@ Page Language="C#" AutoEventWireup="true"%>



    Untitled Page


    
                                      runat="server" 
                              SelectMethod="GetEmployees"
                              TypeName="EmployeeDB" 
                              EnablePaging="True" 
                              SelectCountMethod="CountEmployees">
        
                              runat="server" 
                      CellPadding="4" 
                      DataSourceID="sourceEmployees"
                      Font-Names="Verdana" 
                      Font-Size="Small" 
                      ForeColor="Black" 
                      GridLines="None" 
                      AutoGenerateColumns="False" 
                      AllowPaging="True" 
                      PageSize="5">


    


File: EmployeeDB
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;
public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.CommandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees(string sortExpression)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    try
    {
      con.Open();
      adapter.Fill(ds, "Employees");
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
    DataView view = ds.Tables[0].DefaultView;
    view.Sort = sortExpression;
    ArrayList employees = new ArrayList();
    foreach (DataRowView row in view)
    {
      EmployeeDetails emp = new EmployeeDetails(
        (int)row["EmployeeID"], (string)row["FirstName"],
        (string)row["LastName"], (string)row["TitleOfCourtesy"]);
      employees.Add(emp);
    }
    return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
  }
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.CommandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees(int startRowIndex, int maximumRows)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployeePage", con);
    cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Int, 4));
    cmd.Parameters["@Start"].Value = startRowIndex + 1;
    cmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int, 4));
    cmd.Parameters["@Count"].Value = maximumRows;
    ArrayList employees = new ArrayList();
    try
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
}
File: Web.config

ADO Database ASP.Net