Solved

Datagrid slow for paging.

Posted on 2009-04-06
3
228 Views
Last Modified: 2012-05-06
I have a million records. I need to fix the below
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace SIDWebClient
{
    public partial class GridCustomPaging : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid MyDataGrid;
        protected System.Web.UI.WebControls.Label CurrentPage;
        protected System.Web.UI.WebControls.Label TotalPages;
        protected System.Web.UI.WebControls.LinkButton NextPage;
        protected System.Web.UI.WebControls.LinkButton PreviousPage;
        protected System.Web.UI.WebControls.LinkButton FirstPage;
        protected System.Web.UI.WebControls.LinkButton LastPage;

        protected Int32 _currentPageNumber = 1;

        public void Page_Load(Object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindData();
            }
        }

        public void BindData()
        {
            SqlConnection myConnection = new SqlConnection("Data Source=SM3-93-BILL2;Initial Catalog=KP-FE-KW-PROD-SID-PROV1;User ID=sa;Password=devserver;");

            SqlCommand myCommand = new SqlCommand("Get_SIMS_By_Page", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int)).Value = _currentPageNumber;
            myCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)).Value = MyDataGrid.PageSize;
            myCommand.Parameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int)).Direction = ParameterDirection.Output;

            try
            {
                myConnection.Open();
                MyDataGrid.DataSource = myCommand.ExecuteReader();
                MyDataGrid.DataBind();
            }
            finally
            {
                myConnection.Close();
            }

            CurrentPage.Text = _currentPageNumber.ToString();

            Double _totalPages = 1;
            if (!Page.IsPostBack)
            {
                Int32 _totalRecords = (Int32)myCommand.Parameters["@TotalRecords"].Value;
                _totalPages = _totalRecords / MyDataGrid.PageSize;
                TotalPages.Text = (System.Math.Ceiling(_totalPages)).ToString();
            }
            else
            {
                _totalPages = Double.Parse(TotalPages.Text);
            }

            if (_currentPageNumber == 1)
            {
                PreviousPage.Enabled = false;

                if (_totalPages > 1)
                {
                    NextPage.Enabled = true;
                }
                else
                {
                    NextPage.Enabled = false;
                }
            }
            else
            {
                PreviousPage.Enabled = true;

                if (_currentPageNumber == _totalPages)
                {
                    NextPage.Enabled = false;
                }
                else
                {
                    NextPage.Enabled = true;
                }
            }
        }

        protected void NavigationLink_Click(Object sender, CommandEventArgs e)
        {
            switch (e.CommandName)
            {
                case "First":
                    _currentPageNumber = 1;
                    break;
                case "Last":
                    _currentPageNumber = Int32.Parse(TotalPages.Text);
                    break;
                case "Next":
                    _currentPageNumber = Int32.Parse(CurrentPage.Text) + 1;
                    break;
                case "Prev":
                    _currentPageNumber = Int32.Parse(CurrentPage.Text) - 1;
                    break;
            }

            BindData();
        }

    }
}
<%@ Page Title="" Language="C#" MasterPageFile="~/App_MasterPages/SIDWebClientMasterPage.Master" AutoEventWireup="true" CodeBehind="GridCustomPaging.aspx.cs" Inherits="SIDWebClient.GridCustomPaging" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="pageTitle" runat="server">

</asp:Content>

<asp:Content ID="Content3" ContentPlaceHolderID="cphLeft" runat="server">

</asp:Content>

<asp:Content ID="Content4" ContentPlaceHolderID="cphRight" runat="server">

 <asp:DataGrid runat="server" id="MyDataGrid"

  Width="740" CellPadding="4" CellSpacing="0"

  Gridlines="Horizontal" HorizontalAlign="Center"
 

  HeaderStyle-CssClass="tableHeader"

  ItemStyle-CssClass="tableItem"

  AlternatingItemStyle-BackColor="#FFFFCC"
 

  AllowPaging="True"

  AllowCustomPaging="True"

  PageSize="10"

  PagerStyle-Visible="False"

 />

 <center>

 <p class="pageLinks">

  <b>Page

  <asp:Label id="CurrentPage" CssClass="pageLinks" runat="server" />

  of

  <asp:Label id="TotalPages" CssClass="pageLinks" runat="server" />

 </p>

 <asp:LinkButton runat="server" CssClass="pageLinks"

  id="FirstPage" Text="[First Page]"

  OnCommand="NavigationLink_Click" CommandName="First" />

 <asp:LinkButton runat="server" CssClass="pageLinks"

  id="PreviousPage" Text="[Previous Page]"

  OnCommand="NavigationLink_Click" CommandName="Prev" />

 <asp:LinkButton runat="server" CssClass="pageLinks"

  id="NextPage" Text="[Next Page]"

  OnCommand="NavigationLink_Click" CommandName="Next" />

 <asp:LinkButton runat="server" CssClass="pageLinks"

  id="LastPage" Text="[Last Page]"

  OnCommand="NavigationLink_Click" CommandName="Last" />

 </center>

</asp:Content>
 

======================================================================

CREATE PROCEDURE [Get_SIMS_By_Page]

 @CurrentPage int,

 @PageSize int,

 @TotalRecords int output

AS

 --Create a temp table to hold the current page of data

 --Add and ID column to count the records

 CREATE TABLE #TempTable

 (

    ID int IDENTITY PRIMARY KEY,

    SIMNumber nvarchar(50), 

    MSISDN nvarchar(50), 

    IMSI nvarchar(50), 

    SIMStatus nvarchar(50)

 )

 --Fill the temp table with the Customers data

 INSERT INTO #TempTable 

 (

    SIMNumber, 

    MSISDN, 

    IMSI, 

    SIMStatus

  )

 SELECT 

     SIMNumber, 

    MSISDN, 

    IMSI, 

    SIMStatus

 FROM 

    tbl_sims

 --Create variable to identify the first and last record that should be selected

 DECLARE @FirstRec int, @LastRec int

 SELECT @FirstRec = (@CurrentPage - 1) * @PageSize

 SELECT @LastRec = (@CurrentPage * @PageSize + 1)

 --Select one page of data based on the record numbers above

 SELECT 

      SIMNumber, 

    MSISDN, 

    IMSI, 

    SIMStatus

 FROM 

    #TempTable

 WHERE 

    ID > @FirstRec 

 AND 

    ID < @LastRec

 --Return the total number of records available as an output parameter

 SELECT @TotalRecords = COUNT(*) FROM tbl_tickets

Open in new window

0
Comment
Question by:mathieu_cupryk
  • 2
3 Comments
 
LVL 27

Expert Comment

by:nmarun
Comment Utility
I'm not sure what the actual issue is. If you're saying you need to display a million records, this WILL be slow. Please let us know what the actual issue is or where the error is occurring.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
no error. just slow.
0
 
LVL 27

Accepted Solution

by:
nmarun earned 500 total points
Comment Utility
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now