(Lọc dữ liệu từ Dropdownlist trong Gridview) – Chức năng AutoFilter trong Excel giúp người sử dụng lọc và tìm dữ liệu dễ dàng và thuận tiện. Chỉ cần kích vào mũi tên tại mỗi cột dữ liệu, một danh sách lựa chọn xuất hiện và người dùng chỉ cần chọn thì toàn bộ dữ liệu sẽ được lọc theo tiêu chí đã chọn.
Quay lại danh sách dữ liệu trong Gridview, người dùng sẽ rất khó để xem dữ liệu nếu không có các điều kiện để lọc. Bài viết dưới đây sẽ hướng dẫn các bạn cách xây dựng các điều kiện lọc trong chính các HeaderTemplate của Gridview.
- B1: Download CSDL Northwind
- B2: Bổ xung thêm stored procedure trong SQL Server
Quay lại danh sách dữ liệu trong Gridview, người dùng sẽ rất khó để xem dữ liệu nếu không có các điều kiện để lọc. Bài viết dưới đây sẽ hướng dẫn các bạn cách xây dựng các điều kiện lọc trong chính các HeaderTemplate của Gridview.
- B1: Download CSDL Northwind
- B2: Bổ xung thêm stored procedure trong SQL Server
USE [Northwind]
GO
CREATE PROCEDURE [dbo].[Pro_Products_List]
@Keyword nvarchar(250),
@SupplierID int,
@CategoryID int
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Suppliers.CompanyName, Products.CategoryID,
Categories.CategoryName, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Products INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID'
set @strWhere =' Where 1=1 '
if @Keyword<>''
set @strWhere= @strWhere +' And (ProductName like N''%' +@Keyword+'%'')'
if@SupplierID <>-1
set @strWhere=@strWhere + ' and (Products.SupplierID='+ convert(nvarchar,@SupplierID) + ')'
if@CategoryID <>-1
set @strWhere=@strWhere + ' and (Products.CategoryID='+ convert(nvarchar,@CategoryID) + ')'
set @strOrder =' Order by Products.ProductName'
set @strSQL=@strSQL+@strWhere+@strOrder
print @strSQL
exec sp_executesql @strSQL
- B3: Tạo Project trong Microsoft Visual Studio 2010
Trong Visual Studio tạo 1 Class có tên: Utility và nhập đoạn Code phía dưới cho Class này.
Imports System.Data.SqlClient
Imports System.Data
Namespace FilterRecordsUsingDropDownListInHeaderTemplate
Public Class SqlDataProvider
#Region "Membres Prives"
Shared _IsError As Boolean = False
Private _connectionString AsString
#End Region
#Region "Constructeurs"
Public Sub New()
Try
_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
_IsError = False
Catch ex As Exception
_IsError = True
End Try
End Sub
#End Region
#Region "Proprietes"
Public ReadOnly Property ConnectionString() AsString
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public FunctionFillTable(ByVal sql AsString) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew SqlDataAdapter(sql, _connectionString)
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
Public FunctionFillTable(ByVal ProcName As String, ByVal ParamArrayPara() As ObjectPara) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew SqlDataAdapter(ProcName, _connectionString)
adap.SelectCommand.CommandType = CommandType.StoredProcedure
If NotPara Is NothingThen
For Eachp As ObjectParaIn Para
adap.SelectCommand.Parameters.Add(New SqlParameter(p.Name, p.Value))
Next
End If
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
#End Region
End Class
Public Class ObjectPara
Dim _name As String
Dim _Value As Object
Sub New(ByVal Pname As String, ByVal PValue As Object)
_name = Pname
_Value = PValue
End Sub
Public PropertyName() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public PropertyValue() As Object
Get
Return _Value
End Get
Set(ByVal value As Object)
_Value = value
End Set
End Property
End Class
End Namespace
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B4: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Filter Records using DropDownList in GridView HeaderTemplate in ASP.Net" Language="vb"MasterPageFile="~/Site.Master"AutoEventWireup="false"EnableEventValidation="false" CodeBehind="Default.aspx.vb" Inherits="FilterRecordsUsingDropDownListInHeaderTemplate._Default"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"runat="server">
</asp:ScriptManager>
<h3>
Filter Records using DropDownList in GridView HeaderTemplate in ASP.Net
</h3>
<asp:UpdatePanel ID="updatePanel"runat="server"UpdateMode="Conditional">
<ContentTemplate>
<table cellpadding="2"cellspacing="3"width="100%">
<tr>
<td>
<h4>
<asp:Label ID="lblTotal"runat="server"Visible="false"></asp:Label>
</h4>
</td>
<tdalign="right">
<asp:TextBox ID="txtSearch" CssClass="form-control" ToolTip="Enter Keyword" runat="server" width="200px"></asp:TextBox>
<asp:ImageButton ID="cmdQuickSearch" runat="server" causesvalidation="false" imageurl="~/images/icon_search.gif"></asp:ImageButton>
</td>
</tr>
<tr>
<tdcolspan="2">
<asp:GridView ID="grvObject" runat="server" AllowPaging="true" PageSize="10" ShowHeaderWhenEmpty="true"
CssClass="GridStyle"BorderColor="#cbcbcb"BorderStyle="solid"
BorderWidth="1"AutoGenerateColumns="false"DataKeyNames="ProductID"width="100%">
<AlternatingRowStyleCssClass="GridStyle_AltRowStyle"/>
<HeaderStyle CssClass="GridStyle_HeaderStyle"/>
<RowStyle CssClass="GridStyle_RowStyle"/>
<pagerstyle cssclass="GridStyle_pagination"/>
<Columns>
<asp:BoundField ItemStyle-Width="16%"DataField="ProductName"HeaderText="ProductName"/>
<asp:TemplateField HeaderText="Supplier">
<ItemStyle Width="12%"></ItemStyle>
<HeaderTemplate>
Supplier
<asp:DropDownList ID="ddlSupplier"Width="180"CssClass="form-control"runat="server"AppendDataBoundItems="true"OnSelectedIndexChanged="SupplierChanged"AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblSupplier"Text='<%# Eval("CompanyName") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Category">
<ItemStyle Width="12%"></ItemStyle>
<HeaderTemplate>
Category
<asp:DropDownList ID="ddlCategory"CssClass="form-control"runat="server"AppendDataBoundItems="true"OnSelectedIndexChanged="CategoryChanged"AutoPostBack="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblCategory"Text='<%# Eval("CategoryName") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="QuantityPerUnit">
<ItemStyle Width="8%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblQuantityPerUnit"Text='<%# Eval("QuantityPerUnit") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UnitPrice">
<ItemStyle HorizontalAlign="Right"width="8%"/>
<ItemTemplate>
<asp:Label ID="lblUnitPrice"Text='<%# Eval("UnitPrice") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<trid="trMessage"runat="server"visible="false">
<tdcolspan="2"align="center"valign="top">
<table style="BORDER-COLLAPSE: collapse" borderColor="#cccccc" cellspacing="1" cellpadding="2" width="100%" align="center" border="1">
<tr>
<td align="center"style="height:145px;padding-top:15px;">
<asp:label id="lblMessage"runat="server"Text="No Data"></asp:label>
</td>
</tr>
</table>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>- B5: Viết Code cho file Default.aspx
C# Code
//Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
namespace FilterRecordsUsingDropDownListInHeaderTemplate
{
public partial class _Default : System.Web.UI.Page
{
#region"ComboData"
private voidLoadDropDownList(DropDownList ucControl, string DataValueField, stringDataTextField, string sql)
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable(sql);
ucControl.Items.Clear();
if (objBind != null)
{
ucControl.DataTextField = DataTextField;
ucControl.DataValueField = DataValueField;
ucControl.DataSource = objBind;
ucControl.DataBind();
ucControl.Items.Insert(0, (new ListItem("---All---", "-1")));
}
}
#endregion
#region"Bind Data"
private voidBindProducts()
{
DataTable objBind = newDataTable();
int iTotal = 0;
int SupplierID = -1;
int CategoryID = -1;
if (ViewState["Filter_Supplier"] != null)
{
SupplierID = Convert.ToInt32(ViewState["Filter_Supplier"]);
}
if (ViewState["Filter_Category"] != null)
{
CategoryID = Convert.ToInt32(ViewState["Filter_Category"]);
}
objBind = BindData(txtSearch.Text, SupplierID, CategoryID);
if (objBind != null)
{
if (objBind.Rows.Count > 0)
{
iTotal = objBind.Rows.Count;
grvObject.DataSource = objBind;
grvObject.DataBind();
trMessage.Visible = false;
lblTotal.Visible = true;
lblTotal.Text = "Total " + iTotal + " records";
}
else
{
trMessage.Visible = true;
lblTotal.Visible = false;
grvObject.DataSource = new List<string>();
grvObject.DataBind();
}
updatePanel.Update();
}
}
private DataTableBindData(string Keyword, int SupplierID, intCategoryID)
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_Products_List", new ObjectPara("@Keyword", Keyword.Trim()), new ObjectPara("@SupplierID", SupplierID), new ObjectPara("@CategoryID", CategoryID));
return objBind;
}
#endregion
#region"GridView Methods"
protected voidgrvObject_RowDataBound(object sender, System.Web.UI.WebControls.GridViewRowEventArgse)
{
if (e.Row.RowType == DataControlRowType.Header)
{
string sSQL = "Select distinct Suppliers.SupplierID, Suppliers.CompanyName From Products INNER JOIN Suppliers on Products.SupplierID = Suppliers.SupplierID";
DropDownList ddlSupplier = (DropDownList)e.Row.FindControl("ddlSupplier");
if (ddlSupplier != null)
{
LoadDropDownList(ddlSupplier, "SupplierID", "CompanyName", sSQL);
if (ViewState["Filter_Supplier"] != null)
{
ddlSupplier.Items.FindByValue(ViewState["Filter_Supplier"].ToString()).Selected = true;
}
}
DropDownList ddlCategory = (DropDownList)e.Row.FindControl("ddlCategory");
if(ddlCategory != null)
{
sSQL = "Select distinct Categories.CategoryID, Categories.CategoryName From Products INNER JOIN Categories on Products.CategoryID = Categories.CategoryID";
LoadDropDownList(ddlCategory, "CategoryID", "CategoryName", sSQL);
if (ViewState["Filter_Category"] != null)
{
ddlCategory.Items.FindByValue(ViewState["Filter_Category"].ToString()).Selected = true;
}
}
}
}
protected voidgrvObject_PageIndexChanging(object sender, System.Web.UI.WebControls.GridViewPageEventArgse)
{
grvObject.PageIndex = e.NewPageIndex;
BindProducts();
}
#endregion
#region"Event Handles"
protected voidPage_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
//Default Submit Button
Page.Form.DefaultButton = cmdQuickSearch.UniqueID;
BindProducts();
}
}
catch
{
}
}
protected voidcmdQuickSearch_Click(object sender, System.EventArgs e)
{
BindProducts();
}
protected voidSupplierChanged(object sender, EventArgs e)
{
DropDownList ddlSupplier = (DropDownList)sender;
if (ddlSupplier != null)
{
ViewState["Filter_Supplier"] = ddlSupplier.SelectedValue;
}
BindProducts();
}
protected voidCategoryChanged(object sender, EventArgs e)
{
DropDownList ddlCategory = (DropDownList)sender;
if (ddlCategory != null)
{
ViewState["Filter_Category"] = ddlCategory.SelectedValue;
}
BindProducts();
}
#endregion
}
}
VB.NET Code
Namespace FilterRecordsUsingDropDownListInHeaderTemplate
Public Class _Default
Inherits System.Web.UI.Page
#Region "ComboData"
Private SubLoadDropDownList(ByVal ucControl As DropDownList, ByVal DataValueField AsString, ByValDataTextField As String, ByVal sql As String)
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable(sql)
ucControl.Items.Clear()
If Not objBind Is Nothing Then
With ucControl
.DataTextField = DataTextField
.DataValueField = DataValueField
.DataSource = objBind
.DataBind()
End With
ucControl.Items.Insert(0, (New ListItem("---All---", "-1")))
End If
End Sub
#End Region
#Region "Bind Data"
Private SubBindProducts()
Dim objBind As New DataTable
Dim iTotal As Integer = 0
Dim SupplierID As Integer = -1
Dim CategoryID As Integer = -1
If Not ViewState("Filter_Supplier") Is Nothing Then
SupplierID = ViewState("Filter_Supplier")
End If
If Not ViewState("Filter_Category") Is Nothing Then
CategoryID = ViewState("Filter_Category")
End If
objBind = BindData(txtSearch.Text, SupplierID, CategoryID)
If Not objBind Is Nothing Then
If objBind.Rows.Count > 0 Then
iTotal = objBind.Rows.Count
grvObject.DataSource = objBind
grvObject.DataBind()
trMessage.Visible = False
lblTotal.Visible = True
lblTotal.Text = "Total "& iTotal & " records"
Else
trMessage.Visible = True
lblTotal.Visible = False
grvObject.DataSource = New List(Of String)
grvObject.DataBind()
End If
updatePanel.Update()
End If
End Sub
Private FunctionBindData(ByVal Keyword AsString, ByValSupplierID As Integer, ByVal CategoryID AsInteger) As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_Products_List", New ObjectPara("@Keyword", Keyword.Trim), _
New ObjectPara("@SupplierID", SupplierID), _
New ObjectPara("@CategoryID", CategoryID))
Return objBind
End Function
#End Region
#Region "GridView Methods"
Private SubgrvObject_RowDataBound(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewRowEventArgs) Handles grvObject.RowDataBound
If (e.Row.RowType = DataControlRowType.Header) Then
Dim sSQL AsString = "Select distinct Suppliers.SupplierID, Suppliers.CompanyName From Products INNER JOIN Suppliers on Products.SupplierID = Suppliers.SupplierID"
Dim ddlSupplier AsDropDownList = CType(e.Row.FindControl("ddlSupplier"), DropDownList)
If NotddlSupplier Is NothingThen
LoadDropDownList(ddlSupplier, "SupplierID", "CompanyName", sSQL)
IfNot ViewState("Filter_Supplier") Is Nothing Then
ddlSupplier.Items.FindByValue(ViewState("Filter_Supplier").ToString()).Selected = True
End If
End If
Dim ddlCategory AsDropDownList = CType(e.Row.FindControl("ddlCategory"), DropDownList)
If NotddlCategory Is NothingThen
sSQL = "Select distinct Categories.CategoryID, Categories.CategoryName From Products INNER JOIN Categories on Products.CategoryID = Categories.CategoryID"
LoadDropDownList(ddlCategory, "CategoryID", "CategoryName", sSQL)
If NotViewState("Filter_Category") Is Nothing Then
ddlCategory.Items.FindByValue(ViewState("Filter_Category").ToString()).Selected = True
End If
End If
End If
End Sub
Private SubgrvObject_PageIndexChanging(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewPageEventArgs) Handles grvObject.PageIndexChanging
grvObject.PageIndex = e.NewPageIndex
BindProducts()
End Sub
#End Region
#Region "Event Handles"
Protected SubPage_Load(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Me.Load
Try
If Page.IsPostBack = False Then
'Default Submit Button
Page.Form.DefaultButton = cmdQuickSearch.UniqueID
BindProducts()
End If
Catch ex As Exception
End Try
End Sub
Private SubcmdQuickSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) HandlescmdQuickSearch.Click
BindProducts()
End Sub
Protected SubSupplierChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddlSupplier As DropDownList = DirectCast(sender, DropDownList)
If Not ddlSupplier Is Nothing Then
ViewState("Filter_Supplier") = ddlSupplier.SelectedValue
End If
BindProducts()
End Sub
Protected SubCategoryChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddlCategory As DropDownList = DirectCast(sender, DropDownList)
If Not ddlCategory Is Nothing Then
ViewState("Filter_Category") = ddlCategory.SelectedValue
End If
BindProducts()
End Sub
#End Region
End Class
End Namespace
Bây giờ chạy Project bạn sẽ có kết quả như ảnh phía dưới.
Chúc các bạn thành công!
Quang Bình
0 comments Blogger 0 Facebook
Post a Comment