Home > ASP.Net > Letter Paging Reprter with DataGrid

Letter Paging Reprter with DataGrid


Hi 

try this example for Letter Paging  Reprter control 

Step1 :- Open MS SQL Server2005 and attach Northwind Database 

Step2 :- Create the following Stored Procedures 

 

CREATE PROCEDURE GetCustomerLNameInitials
AS 

SELECT UPPER(SUBSTRING(CompanyName, 1, 1)) lni, COUNT(*) Num
FROM Customers
GROUP BY UPPER(SUBSTRING(CompanyName, 1, 1))
ORDER BY lni
Go

 

 

CREATE PROCEDURE GetCustomersByLNameInitial
(
   @Lni nchar(1)
)
AS 

SELECT CompanyName, Address, City, Country
FROM Customers
WHERE CompanyName LIKE @Lni + '%'
ORDER BY CompanyName
Go

 

 

CREATE PROCEDURE GetAllCutomers
AS 

SELECT CompanyName, Address, City, Country
FROM Customers
ORDER BY CompanyName
Go

Step3 :- Open VS2005 and create new website and add new class called “DataAccess.cs” 

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; 

/// <summary>
/// Summary description for DataAcess
/// </summary>
public class DataAcess
{
 public DataAcess()
 {
  //
  // TODO: Add constructor logic here
  //
 } 

    public SqlDataReader GetCustomerLNameInitials()
    {
        SqlDataReader dr = null;
        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
        SqlCommand myCommand = new SqlCommand("GetCustomerLNameInitials", myConnection); 

        try
        {
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;
            // Open the database connection and execute the command
            myConnection.Open();
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            // clear Paramters
            myCommand.Parameters.Clear();
            // Return the datareader
        }
        catch
        {
            myConnection.Close();
            //throw;
        }
        return dr;
    } 

    public SqlDataReader GetCustomersByLNameInitial(string Lni)
    {
        SqlDataReader dr = null;
        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
        SqlCommand myCommand = new SqlCommand("GetCustomersByLNameInitial", myConnection);
        try
        {
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;
            // Add Parameters to SPROC
            SqlParameter parameterLni = new SqlParameter("@Lni", SqlDbType.NChar, 1);
            parameterLni.Value = Lni;
            myCommand.Parameters.Add(parameterLni);
            // Open the database connection and execute the command
            myConnection.Open();
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            // clear Paramters
            myCommand.Parameters.Clear();
            // Return the datareader
        }
        catch
        {
            myConnection.Close();
            //throw;
        }
        return dr;
    } 

    public SqlDataReader GetAllCutomers()
    {
        SqlDataReader dr = null;
        // Create Instance of Connection and Command Object
        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
        SqlCommand myCommand = new SqlCommand("GetAllCutomers", myConnection);
        try
        {
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;
            // Open the database connection and execute the command
            myConnection.Open();
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            // clear Paramters
            myCommand.Parameters.Clear();
            // Return the datareader
        }
        catch
        {
            myConnection.Close();
            //throw;
        }
        return dr;
    }
}

Step4 :- Add new User control called “CustomerUserControl.ascx” and in design mode add Repeter and DataGrid Control 

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="CustomersUserControl.ascx.cs" Inherits="CustomersUserControl" %>
<table cellpadding="0" cellspacing="0" border="1">
 <tr>
  <td bgColor="#ccccff">
   <table>
    <tr>
     <td>
      <asp:Repeater ID="LetterList" runat="server" Visible="true" OnItemCommand="LetterList_ItemCommand">
       <itemtemplate>
        <asp:linkbutton id="lniLetter" runat="server" commandargument='<%# DataBinder.Eval(Container.DataItem, "Lni")%>' ToolTip='<%# "Subtotal: " + DataBinder.Eval(Container.DataItem, "Num") %>'>
         <%# DataBinder.Eval(Container.DataItem, "Lni")%>
        </asp:linkbutton>
       </itemtemplate>
      </asp:Repeater></td>
     <td><asp:LinkButton id="ShowAllUsers" runat="server" OnClick="ShowAllUsers_Click">(All)</asp:LinkButton></td>
    </tr>
   </table>
  </td>
 </tr>
 <tr>
  <td bgColor="#ccccff">
   <asp:DataGrid id="DataGrid1" HeaderStyle-backcolor="#94d6e7" Alternatingitemstyle-backcolor="#fff5ee" bordercolor="#deba84" cellspacing="2" borderstyle="None" width="100%" cellpadding="3" runat="server" BackColor="LemonChiffon">
    <AlternatingItemStyle BackColor="SeaShell"></AlternatingItemStyle>
    <HeaderStyle Font-Bold="True" BackColor="#94D6E7"></HeaderStyle>
   </asp:DataGrid>
  </td>
 </tr>
</table>

Step5 :- In code behind of user control add the following code 


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class CustomersUserControl : System.Web.UI.UserControl
{
   
    void IniBindData()
    {
        DataAcess da = new DataAcess();
        LetterList.DataSource = da.GetCustomerLNameInitials();
        LetterList.DataBind();
        int n = LetterList.Items.Count;
        if (n > 0)
        {
            // try to find the first letter on the list
            string letter = ((LinkButton)(LetterList.Items[0].Controls[1])).CommandArgument;
            // show the Names with the first letter
            DataGrid1.DataSource = da.GetCustomersByLNameInitial(letter);
            DataGrid1.DataBind();
        }

    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            IniBindData();
        }
    }

    protected void ShowAllUsers_Click(object sender, EventArgs e)
    {
        DataAcess da = new DataAcess();
        DataGrid1.DataSource = da.GetAllCutomers();
        DataGrid1.DataBind();
    }

    protected void LetterList_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        DataAcess da = new DataAcess();
        // find the selected letter
        string letter = (string)e.CommandArgument;
        // show the name list with this initial
        DataGrid1.DataSource = da.GetCustomersByLNameInitial(letter);
        DataGrid1.DataBind();
    }
}

Step6 :- Add new web page and Drag User Control inside this page

Hope this helps

Good Luck.

Advertisements
Categories: ASP.Net
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: