(Làm nổi bật các kết quả tìm kiếm phù hợp với từ khóa trong Gridview) – Chức năng tìm kiếm giúp người sử dụng nhanh chóng tìm được thông tin một cách nhanh chóng nhất. Tuy nhiên khi tìm kiếm từ khóa trên nhiều tiêu chí thì người dùng rất khó quan sát để biết trường thông tin nào phù hợp với từ khóa. Giải pháp dưới đây sẽ giúp đánh dấu các kết quả tìm kiếm phù hợp với từ khóa trong Gridview.
- B1: Download CSDL Northwind
- B2: 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
Bạn có thể tải về bảng cơ sở dữ liệu SQL bằng cách nhấn vào liên kết tải về dưới đây
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 SearchRecordsGridViewAndHighlightResults
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 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
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B4: Mở file Site.css và bổ xung thêm thuộc tính dưới
.highlight
{
text-decoration:none;
font-weight:bold;
color:black;
background:#ffff33;
}
- B5: Mở file Default.aspxdưới dạng HTML và nhập mã HTML
<%@ PageTitle="Search records in GridView and highlight results in ASP.Net" Language="vb"MasterPageFile="~/Site.Master"AutoEventWireup="false"EnableEventValidation="false" CodeBehind="Default.aspx.vb" Inherits="SearchRecordsGridViewAndHighlightResults._Default"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"runat="server">
</asp:ScriptManager>
<h3>
Search records in GridView and highlight results in ASP.Net
</h3>
<asp:UpdatePanel ID="updatePanel"runat="server"UpdateMode="Conditional">
<ContentTemplate>
<table cellpadding="2"cellspacing="3"width="100%">
<tr>
<td>
</td>
<tdalign="right">
<asp:Label ID="plKeyword" runat="server" Text="Keyword"></asp:Label>
<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">
<asp:Label ID="lblMessage" runat="server" Text="No Data"></asp:Label>
</td>
</tr>
<tr>
<tdcolspan="2">
<asp:GridView ID="grvObject" runat="server" AllowPaging="true" PageSize="12" AllowSorting="true"
CssClass="GridStyle"BorderColor="#cbcbcb"BorderStyle="solid"DataKeyNames="CustomerID"
BorderWidth="1"AutoGenerateColumns="false"width="100%">
<AlternatingRowStyle CssClass="GridStyle_AltRowStyle" />
<HeaderStyle CssClass="GridStyle_HeaderStyle"/>
<RowStyle CssClass="GridStyle_RowStyle"/>
<Pagerstyle cssclass="GridStyle_pagination"/>
<FooterStyle CssClass="GridStyle_FooterStyle"/>
<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:TemplateField HeaderText="CompanyName">
<ItemStyle Width="18%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblCompanyName"runat="server"Text='<%# Highlight(Eval("CompanyName").ToString()) %>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ContactName">
<ItemStyle Width="15%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblContactName"runat="server"Text='<%# Highlight(Eval("ContactName").ToString()) %>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ContactTitle">
<ItemStyle Width="12%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblContactTitle"runat="server"Text='<%#Highlight(Eval("ContactTitle").ToString()) %>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<ItemStyle Width="16%"></ItemStyle>
<ItemTemplate>
<asp:Label ID="lblAddress"runat="server"Text='<%# Highlight(Eval("Address").ToString()) %>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>
- B6: Viết Code cho file Default.aspx
'Visit http://thuthuatlaptrinh.blogspot.com for more ASP.NET Tutorials
Imports System.Data.SqlClient
Namespace SearchRecordsGridViewAndHighlightResults
Public Class _Default
Inherits System.Web.UI.Page
#Region "Private Methods"
Public FunctionHighlight(ByVal InputTxt As String) As String
Dim Search_Str As String = txtSearch.Text.ToString()
Dim RegExp As New Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase)
Return RegExp.Replace(InputTxt, New MatchEvaluator(AddressOf ReplaceKeyWords))
RegExp = Nothing
End Function
Public FunctionReplaceKeyWords(ByVal m As Match) As String
Return "<span class=""highlight"">" & Convert.ToString(m.Value) & "</span>"
End Function
#End Region
#Region "Bind Data"
Private SubBindCustomer()
Dim objBind As New DataTable
objBind = BindData()
If Not objBind Is Nothing Then
If objBind.Rows.Count > 0 Then
grvObject.DataSource = objBind
grvObject.DataBind()
trMessage.Visible = False
grvObject.Visible = True
Else
trMessage.Visible = True
grvObject.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", "All"))
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
BindCustomer()
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
BindCustomer()
End If
Catch ex As Exception
End Try
End Sub
Private SubcmdQuickSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) HandlescmdQuickSearch.Click
BindCustomer()
End Sub
#End Region
End Class
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