locked
Avoid Hitting Server Every Time While Filtering Using Asp.Net GridView RRS feed

  • Question

  • User1152553138 posted

    I the below is just an example of how I am binding gridview and doing filter with dropdown list

    I have some 50,000 row in 8 tables. I am joining the entire table to get my desired output. Minimum 1500 rows will be the output.

    While testing the query in sql, the result gets generated within 3 seconds. But in the page it is taking long time.

    I would like to avoid hitting server every time when I do filter using dropdown list. Only doing first time the gridview gets data from database. From there on filter should be done with in the fetch data. But in my case every time it is hitting the database. How to avoid this?

    See the code below

     

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestData.aspx.cs" Inherits="TestData" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
       
    
       <asp:DropDownList ID="DrpYear"    runat="server" AutoPostBack="true" onselectedindexchanged="DrpYear_SelectedIndexChanged">  </asp:DropDownList>
       <asp:DropDownList ID="DrpMonth"   runat="server" AutoPostBack="true" onselectedindexchanged="DrpMonth_SelectedIndexChanged">  </asp:DropDownList>
       <asp:DropDownList ID="DrpTraffic" runat="server" AutoPostBack="true" onselectedindexchanged="DrpTraffic_SelectedIndexChanged">  </asp:DropDownList>
    
       <br /> <br />
    
    <asp:GridView ID="Grid" runat="server" AutoGenerateColumns="false"    > 
    <Columns>
    
       <asp:TemplateField HeaderText="Year"    >
       <ItemTemplate>
       <asp:Label ID="LblYear"  runat="server"  Text='<%# Bind("Year") %>'></asp:Label>
       </ItemTemplate>
       </asp:TemplateField>
    
       <asp:TemplateField HeaderText="Month"    >
       <ItemTemplate>
       <asp:Label ID="LblMonth"  runat="server"  Text='<%# Bind("Month") %>' ></asp:Label>
       </ItemTemplate>
       </asp:TemplateField>
    
       <asp:TemplateField HeaderText="Count"    >
       <ItemTemplate>
       <asp:Label ID="LblCount"  runat="server"   Text='<%# Bind("visit_count") %>'></asp:Label>
       </ItemTemplate>
       </asp:TemplateField>
    
       <asp:TemplateField HeaderText="Traffic"   >
       <ItemTemplate>
       <asp:Label ID="LblTraffic"  runat="server"   Text='<%# Bind("traffic_source") %>' ></asp:Label>
       </ItemTemplate>
       </asp:TemplateField>  
    
    </Columns>        
    </asp:GridView>
    
    
        </form>
    </body>
    </html>
    
    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;
    using System.Data.SqlClient;
    
    public partial class TestData : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GridPageLoadBind();
    
                DrpYearBind();
                DrpYearBind();
                DrpMonthBind();
                DrpTrafficBind();
            }
        }
    
    
        private void GridPageLoadBind()
        {       
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            SqlCommand cmd12 = new SqlCommand("GridPageLoad", con);
            cmd12.CommandType = CommandType.StoredProcedure;          
    
            SqlDataAdapter da = new SqlDataAdapter(cmd12);
            DataSet ds = new DataSet();
    
            da.Fill(ds);        
    
            Grid.DataSource = ds;
            Grid.DataBind();
                    
            con.Dispose();
           
        }
    
        private void GridYearFilterBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            SqlCommand cmd12 = new SqlCommand("GridYearFilter", con);
            cmd12.CommandType = CommandType.StoredProcedure;
            cmd12.Parameters.AddWithValue("@Year", DrpYear.SelectedValue);       
    
            SqlDataAdapter da = new SqlDataAdapter(cmd12);
            DataSet ds = new DataSet();
    
            da.Fill(ds);
    
            Grid.DataSource = ds;
            Grid.DataBind();
    
            con.Dispose();
    
        }
    
        private void GridMonthFilterBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            SqlCommand cmd12 = new SqlCommand("GridMonthFilter", con);
            cmd12.CommandType = CommandType.StoredProcedure;
            cmd12.Parameters.AddWithValue("@Month", DrpMonth.SelectedValue);       
    
            SqlDataAdapter da = new SqlDataAdapter(cmd12);
            DataSet ds = new DataSet();
    
            da.Fill(ds);
    
            Grid.DataSource = ds;
            Grid.DataBind();
    
            con.Dispose();
    
        }
    
        private void GridTrafficFilterBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            SqlCommand cmd12 = new SqlCommand("GridTrafficFilter", con);
            cmd12.CommandType = CommandType.StoredProcedure;
            cmd12.Parameters.AddWithValue("@Traffic", DrpTraffic.SelectedValue);       
    
            SqlDataAdapter da = new SqlDataAdapter(cmd12);
            DataSet ds = new DataSet();
    
            da.Fill(ds);
    
            Grid.DataSource = ds;
            Grid.DataBind();
    
            con.Dispose();
    
        }
    
        
        private void DrpYearBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            DrpYear.AppendDataBoundItems = true;
            DrpYear.Items.Clear();
            DrpYear.Items.Add(new ListItem("Select Year", ""));
    
            SqlCommand cmd1 = new SqlCommand("SELECT Year FROM [Northwind].[dbo].[traffic_data]", con);
             
            con.Open();
            DrpYear.DataSource = cmd1.ExecuteReader();
            DrpYear.DataTextField = "Year";
            DrpYear.DataValueField = "Year";
            DrpYear.DataBind();
            con.Close();
    
            con.Dispose();
        }
    
        private void DrpMonthBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            DrpMonth.AppendDataBoundItems = true;
            DrpMonth.Items.Clear();
            DrpMonth.Items.Add(new ListItem("Select Month", ""));
    
            SqlCommand cmd1 = new SqlCommand("SELECT Month FROM [Northwind].[dbo].[traffic_data]", con);
    
            con.Open();
            DrpMonth.DataSource = cmd1.ExecuteReader();
            DrpMonth.DataTextField = "Month";
            DrpMonth.DataValueField = "Month";
            DrpMonth.DataBind();
            con.Close();
    
            con.Dispose();
        }
    
        private void DrpTrafficBind()
        {
            String strConnString = ConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
    
            DrpTraffic.AppendDataBoundItems = true;
            DrpTraffic.Items.Clear();
            DrpTraffic.Items.Add(new ListItem("Select Traffic", ""));
    
            SqlCommand cmd1 = new SqlCommand("SELECT traffic_source as Traffic FROM [Northwind].[dbo].[traffic_data]", con);
    
            con.Open();
            DrpTraffic.DataSource = cmd1.ExecuteReader();
            DrpTraffic.DataTextField = "Traffic";
            DrpTraffic.DataValueField = "Traffic";
            DrpTraffic.DataBind();
            con.Close();
    
            con.Dispose();
        }
    
    
        protected void DrpYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridYearFilterBind();
        }
    
        protected void DrpMonth_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridMonthFilterBind();
        }
    
        protected void DrpTraffic_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridTrafficFilterBind();
        }
    
    
    
    }

    DB

    USE [Northwind]
    GO
    
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[traffic_data](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Year] [int] NULL,
    	[Month] [varchar](50) NULL,
    	[visit_count] [int] NULL,
    	[traffic_source] [varchar](50) NULL,
     CONSTRAINT [PK_traffic_data] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    
    
    Create Procedure GridPageLoad as
    
    SELECT [ID],[Year],[Month],[visit_count],[traffic_source] FROM [Northwind].[dbo].[traffic_data]
    
    Create Procedure GridYearFilter @Year int as
    
    SELECT [ID],[Year],[Month],[visit_count],[traffic_source] FROM [Northwind].[dbo].[traffic_data] where Year=@Year
    
    
    Create Procedure GridMonthFilter @Month int as
    
    SELECT [ID],[Year],[Month],[visit_count],[traffic_source] FROM [Northwind].[dbo].[traffic_data] where Month=@Month
    
    
    Create Procedure GridTrafficFilter @Traffic varchar(50) as
    
    SELECT [ID],[Year],[Month],[visit_count],[traffic_source] FROM [Northwind].[dbo].[traffic_data] where traffic_source=@Traffic



     

    Tuesday, August 28, 2018 5:53 AM

All replies