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

Home
ASP.Net
1.ADO.net Database
2.Ajax
3.Asp Control
4.Collections
5.Components
6.Data Binding
7.Development
8.File Directory
9.HTML Control
10.Language Basics
11.Login Security
12.Mobile Control
13.Network
14.Page
15.Request
16.Response
17.Server
18.Session Cookie
19.Sitemap
20.Theme Style
21.User Control and Master Page
22.Validation by Control
23.Validation by Function
24.WebPart
25.WPF
26.XML
ASP.Net » ADO.net Database » GridView 
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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.