GridView custom paging with ObjectDataSource : GridView « ADO.net Database « ASP.Net






GridView custom paging with ObjectDataSource

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ObjectDataSource ID="sourceEmployees" 
                              runat="server" 
                              SelectMethod="GetEmployees"
                              TypeName="EmployeeDB" 
                              EnablePaging="True" 
                              SelectCountMethod="CountEmployees">
        </asp:ObjectDataSource>
        <asp:GridView ID="GridView1" 
                      runat="server" 
                      CellPadding="4" 
                      DataSourceID="sourceEmployees"
                      Font-Names="Verdana" 
                      Font-Size="Small" 
                      ForeColor="Black" 
                      GridLines="None" 
                      AutoGenerateColumns="False" 
                      AllowPaging="True" 
                      PageSize="5">
            <FooterStyle BackColor="White" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="Pink" ForeColor="Black" />
            <PagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="White" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy" SortExpression="TitleOfCourtesy" />
            </Columns>
        </asp:GridView>
         </div>
    </form>
</body>
</html>

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

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
      <connectionStrings>
        <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
      </connectionStrings>
</configuration>

 








Related examples in the same category

1.Displaying data in database table with the GridView control.
2.The GridView control supports programmatic databinding
3.Selecting Data
4.Using Data Keys
5.Paging Through Data
6.Sortable GridView
7.Change row style for higher value
8.Highlight Rows
9.Numeric Format Strings
10.Time and Date Format Strings
11.GridView format event
12.Turn off the GridLine
13.GridView selection style
14.GridView Summaries
15.Using the RowDeleted event to catch SQL errors (C#)
16.Using the RowDeleted event to catch SQL errors (VB)
17.Adding a delete link to the GridView
18.Checking for Update errors using the RowUpdated event (C#)
19.Checking for Update errors using the RowUpdated event (VB)
20.Turning off AutoGenerateColumns in the GridView control
21.Using the RowCreated Event to programmatically change the style
22.Adding Indicators to display Sorting Direction