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
