(Lựa chọn tiêu chí tìm kiếm từ Dropdownlist trong Asp.net) – Chức năng tìm kiếm nhanh giúp người sử dụng nhanh chóng và dễ dàng tìm kiếm thông tin mình cần. Tuy nhiên do chỉ có 1 Textbox để nhập từ khóa nên đôi khi kết quả tìm kiếm ra quá nhiều và có những thông tin không cần thiết. Vậy làm sao để người dùng có thể lựa chọn được những tiêu chí tìm kiếm mong muốn mà vẫn có thể thực hiện nhanh. Bài viết dưới đây sẽ hướng dẫn các bạn cách làm công việc này. Khi chạy ứng dụng nếu người dùng chọn tiêu chí All, ứng dụng sẽ tìm tất cả dữ liệu phù hợp với từ khóa nhập vào và nếu chọn 1 tiêu chí nào đó phần mềm sẽ chỉ tìm dữ liệu trên tiêu chí được chọn.
- B1: Download CSDL Northwind
- B2: Bổ xung thêm stored procedure trong SQL Server
- B1: Download CSDL Northwind
- B2: Bổ xung thêm stored procedure trong SQL Server
USE [Northwind]
GO
CREATE PROCEDURE [dbo].[Pro_Customers_List]
@Keyword nvarchar(250),
@Category varchar(15)
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'Select * from Customers'
set @strWhere =' Where 1=1 '
if @Keyword<>'' And @Category='All'
set @strWhere= @strWhere +' And (CompanyName like N''%' +@Keyword+'%''
Or ContactName like N''%' +@Keyword+'%'' Or ContactTitle like N''%' +@Keyword+'%''
Or Address like N''%' +@Keyword+'%'' Or City like N''%' +@Keyword+'%''
Or Region like N''%' +@Keyword+'%'' Or PostalCode like N''%' +@Keyword+'%'')'
if @Keyword<>'' And @Category='CompanyName'
set @strWhere= @strWhere +' And (CompanyName like N''%' +@Keyword+'%'')'
if @Keyword<>'' And @Category='ContactName'
set @strWhere= @strWhere +' And (ContactName like N''%' +@Keyword+'%'')'
if @Keyword<>'' And @Category='ContactTitle'
set @strWhere= @strWhere +' And (ContactTitle like N''%' +@Keyword+'%'')'
if @Keyword<>'' And @Category='Phone'
set @strWhere= @strWhere +' And (Phone like N''%'+@Keyword+'%'')'
if @Keyword<>'' And @Category='Fax'
set @strWhere= @strWhere +' And (Fax like N''%'+@Keyword+'%'')'
set @strOrder =' Order by CompanyName'
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 SearchGridViewbyDropDownListTextBox
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="Search GridView by DropDownList and TextBox Value in ASP.Net" Language="vb"MasterPageFile="~/Site.Master"AutoEventWireup="false"EnableEventValidation="false" CodeBehind="Default.aspx.vb" Inherits="SearchGridViewbyDropDownListTextBox._Default"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"runat="server">
</asp:ScriptManager>
<h3>
Search GridView by DropDownList and TextBox Value in ASP.Net
</h3>
<br />
<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:Label ID="plSearch" runat="server" Text="Search By"></asp:Label>
<asp:DropDownList ID="drpCategory" CssClass="form-control" runat="server" Width="120px">
</asp:DropDownList>
<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>
<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>
<tr>
<tdcolspan="2">
<asp:GridView ID="grvObject" runat="server" AllowPaging="true" PageSize="8"
CssClass="GridStyle"BorderColor="#cbcbcb"BorderStyle="solid"
BorderWidth="1"AutoGenerateColumns="false"DataKeyNames="CustomerID"width="100%">
<AlternatingRowStyle CssClass="GridStyle_AltRowStyle" />
<HeaderStyle CssClass="GridStyle_HeaderStyle"/>
<RowStyle CssClass="GridStyle_RowStyle"/>
<pagerstyle cssclass="GridStyle_pagination"/>
<Columns>
<asp:TemplateField HeaderText = "Number">
<ItemStyle HorizontalAlign="Center"Width="2%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblRowNumber"Text='<%# Container.DataItemIndex + 1 %>' runat="server"/>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width="18%"DataField="CompanyName"HeaderText="CompanyName"/>
<asp:BoundField ItemStyle-Width="15%"DataField="ContactName"HeaderText="ContactName"/>
<asp:BoundField ItemStyle-Width="12%"DataField="ContactTitle"HeaderText="ContactTitle"/>
<asp:BoundField ItemStyle-Width="12%"DataField="Phone"HeaderText="Phone"/>
<asp:BoundField ItemStyle-Width="12%"DataField="Fax"HeaderText="Fax"/>
</Columns>
</asp:GridView>
</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 SearchGridViewbyDropDownListTextBox
{
public partial class _Default : System.Web.UI.Page
{
#region"ComboData"
private voidLoadSearchBy()
{
drpCategory.Items.Clear();
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("---All---", "All"));
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("CompanyName", "CompanyName"));
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("ContactName", "ContactName"));
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("ContactTitle", "ContactTitle"));
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("Phone", "Phone"));
drpCategory.Items.Add(newSystem.Web.UI.WebControls.ListItem("Fax", "Fax"));
}
#endregion
#region"Bind Data"
private voidBindCustomers()
{
DataTable objBind = newDataTable();
int iTotal = 0;
objBind = BindData();
if (objBind != null)
{
if (objBind.Rows.Count > 0)
{
iTotal = objBind.Rows.Count;
grvObject.DataSource = objBind;
grvObject.DataBind();
trMessage.Visible = false;
grvObject.Visible = true;
lblTotal.Visible = true;
lblTotal.Text = "Total " + iTotal + " records";
}
else
{
trMessage.Visible = true;
grvObject.Visible = false;
lblTotal.Visible = false;
}
updatePanel.Update();
}
}
private DataTableBindData()
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_Customers_List", new ObjectPara("@Keyword", txtSearch.Text), new ObjectPara("@Category", drpCategory.SelectedValue));
return objBind;
}
#endregion
#region"GridView Methods"
protected voidgrvObject_PageIndexChanging(object sender, System.Web.UI.WebControls.GridViewPageEventArgse)
{
grvObject.PageIndex = e.NewPageIndex;
BindCustomers();
}
#endregion
#region"Event Handles"
protected voidPage_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
LoadSearchBy();
//Default Submit Button
Page.Form.DefaultButton = cmdQuickSearch.UniqueID;
BindCustomers();
}
}
catch
{
}
}
protected voidcmdQuickSearch_Click(object sender, System.EventArgs e)
{
BindCustomers();
}
#endregion
}
}
VB.NET Code
Namespace SearchGridViewbyDropDownListTextBox
Public Class _Default
Inherits System.Web.UI.Page
#Region "ComboData"
Private SubLoadSearchBy()
drpCategory.Items.Clear()
With drpCategory
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("---All---", "All"))
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("CompanyName", "CompanyName"))
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("ContactName", "ContactName"))
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("ContactTitle", "ContactTitle"))
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("Phone", "Phone"))
.Items.Add(NewSystem.Web.UI.WebControls.ListItem("Fax", "Fax"))
End With
End Sub
#End Region
#Region "Bind Data"
Private SubBindCustomers()
Dim objBind As New DataTable
Dim iTotal As Integer = 0
objBind = BindData()
If Not objBind Is Nothing Then
If objBind.Rows.Count > 0 Then
iTotal = objBind.Rows.Count
grvObject.DataSource = objBind
grvObject.DataBind()
trMessage.Visible = False
grvObject.Visible = True
lblTotal.Visible = True
lblTotal.Text = "Total "& iTotal & " records"
Else
trMessage.Visible = True
grvObject.Visible = False
lblTotal.Visible = False
End If
updatePanel.Update()
End If
End Sub
Private FunctionBindData() As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_Customers_List", New ObjectPara("@Keyword", txtSearch.Text.Trim), _
New ObjectPara("@Category", drpCategory.SelectedValue))
Return objBind
End Function
#End Region
#Region "GridView Methods"
Private SubgrvObject_PageIndexChanging(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewPageEventArgs) Handles grvObject.PageIndexChanging
grvObject.PageIndex = e.NewPageIndex
BindCustomers()
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
LoadSearchBy()
'Default Submit Button
Page.Form.DefaultButton = cmdQuickSearch.UniqueID
BindCustomers()
End If
Catch ex As Exception
End Try
End Sub
Private SubcmdQuickSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) HandlescmdQuickSearch.Click
BindCustomers()
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