(Sort Column in Gridview Asp.net) – Đối với các danh sách dữ liệu nhiều, chức năng tìm kiếm, sắp xếp thông tin sẽ giúp người sử dụng nhanh chóng dễ dàng hơn trong việc xem thông tin. Nếu như bạn đã sử dụng Excel thì chức năng này rất quen thuộc khi làm việc. Theo mặc định dữ liệu hiển thị trên Gridview sẽ chưa có chức năng Sort, bài viết dưới đây sẽ hướng dẫn cách bạn cách đưa chức năng Sort vào Gridview. Mỗi khi kích vào tiêu đề một cột dữ liệu nào đó trên Gridview, dữ liệu sẽ được tự động sắp xếp và tại cột sắp xếp sẽ xuất hiện các mũi tên để người sử dụng có thể nhận biết được hướng sắp xếp.
- B1: Tạo CSDL Customers trong SQL Server
- B2: Tạo Bảng Accounts có cấu trúc phía dưới
| STT | Tên trường | Kiểu trường | Ghi chú |
| 1 | AccountID | Int | Trường tự tăng |
| 2 | AccountCode | nvarchar(25) | |
| 3 | AccName | nvarchar(250) | |
| 4 | AccAddress | nvarchar(250) | |
| 5 | AccPhone | nvarchar(50) | |
| 6 | AccFAX | nvarchar(50) | |
| 7 | AccEmail | nvarchar(50) | |
| 8 | AccWebsite | nvarchar(150) | |
| 9 | AccDesc | nvarchar(1500) | |
| 10 | CreatedDate | datetime | |
| 11 | ModifiedDate | datetime |
- B3: Nhập dữ liệu cho bảng Accounts
- B4: Tạo stored procedure trong SQL Server
USE[Customers]
GO
CREATE PROCEDURE [dbo].[Pro_Accounts_List]
@Keyword nvarchar(250),
@SortField nvarchar(50),
@SortType nvarchar(10)
AS
declare@strSQL nvarchar(1000)
declare @strWhere nvarchar(500)
declare @strOrder nvarchar (50)
set @strSQL= 'Select * from Accounts'
set @strWhere =' Where 1=1 '
if @Keyword<>''
set @strWhere= @strWhere +' And (AccountCode like N''%' +@Keyword+'%''
Or AccName like N''%' +@Keyword+'%'' Or AccAddress like N''%' +@Keyword+'%''
Or AccPhone like N''%' +@Keyword+'%'' Or AccFAX like N''%' +@Keyword+'%''
Or AccEmail like N''%' +@Keyword+'%'' Or AccWebsite like N''%' +@Keyword+'%'')'
if @SortField=''
Begin
set@strOrder =' Order by AccName'
End
Else
Begin
set@strOrder =' Order by '+ @SortField + ' '+ @SortType
End
set @strSQL=@strSQL+@strWhere+@strOrder
print @strSQL
exec sp_executesql @strSQL
Go
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 PerformSortingInGriview
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
End Namespace
Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config
- B6: Download các file ảnh tại đây, copy các file ảnh vào thư mục Images của Project
- B7: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Sorting and Paging with Gridview in ASP.NET" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" EnableEventValidation= "false" CodeBehind="Default.aspx.vb"Inherits="PerformSortingInGriview._Default"%>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"runat="server">
</asp:ScriptManager>
<h3>
Sorting and Paging with Gridview 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"
BorderWidth="1"AutoGenerateColumns="false"DataKeyNames="AccountID"width="100%">
<AlternatingRowStyleCssClass="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"/>
<asp:Label ID="lblItemID"Text='<%# Eval("AccountID") %>' Visible="false"runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width="10%" DataField="AccountCode" HeaderText="AccountCode" SortExpression="AccountCode" />
<asp:BoundField ItemStyle-Width="15%"DataField="AccName"HeaderText="AccountName"SortExpression="AccName"/>
<asp:BoundField ItemStyle-Width="10%"DataField="AccPhone"HeaderText="Phone"SortExpression="AccPhone"/>
<asp:BoundField ItemStyle-Width="10%"DataField="AccFAX"HeaderText="FAX"SortExpression="AccFAX"/>
<asp:BoundField ItemStyle-Width="15%"DataField="AccEmail"HeaderText="Email"SortExpression="AccEmail"/>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Content>- B8: 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.Diagnostics;
namespace SortInGridview
{
public partial class _Default : System.Web.UI.Page
{
#region"Const"
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
#endregion
#region"Properties"
private SortDirectionGridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
{
ViewState["sortDirection"] = SortDirection.Ascending;
}
return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}
public stringSortField
{
get
{
object o = ViewState["SortExpression"];
if (o == null)
{
return "AccName";
}
return Convert.ToString(o);
}
set { ViewState["SortExpression"] = value; }
}
#endregion
#region"Bind Data"
private voidBindAccount(string SortExpression)
{
DataTable objBind = newDataTable();
if (GridViewSortDirection == SortDirection.Ascending)
{
objBind = BindData(SortExpression, DESCENDING);
}
else
{
objBind = BindData(SortExpression, ASCENDING);
}
if (objBind != null)
{
if (objBind.Rows.Count > 0)
{
grvObject.DataSource = objBind;
grvObject.DataBind();
trMessage.Visible = false;
grvObject.Visible = true;
}
else
{
trMessage.Visible = true;
grvObject.Visible = false;
}
updatePanel.Update();
}
}
private DataTableBindData(string SortExpression, string SortType)
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = objSQL.FillTable("Pro_Accounts_List", new ObjectPara("@Keyword", txtSearch.Text), new ObjectPara("@SortField", SortExpression), new ObjectPara("@SortType", SortType));
return objBind;
}
#endregion
#region"GridSort"
private voidAddSortDirectionImage(int columnIndex, GridViewRow headerRow)
{
Image sortImage = newImage();
if (GridViewSortDirection == SortDirection.Ascending)
{
sortImage.ImageUrl = "~/Images/sortascending.gif";
sortImage.AlternateText = "Ascending Order";
}
else
{
sortImage.ImageUrl = "~/Images/sortdescending.gif";
sortImage.AlternateText = "Descending Order";
}
headerRow.Cells[columnIndex].Controls.Add(sortImage);
}
private intGetSortColumnIndex()
{
string SortExpression = "";
foreach (DataControlFieldfield in grvObject.Columns)
{
if (ViewState["SortExpression"] != null)
{
if (field.SortExpression == (string)ViewState["SortExpression"])
{
return grvObject.Columns.IndexOf(field);
}
}
else
{
SortExpression = "AccName";
if (field.SortExpression == SortExpression)
{
return grvObject.Columns.IndexOf(field);
}
}
}
return -1;
}
#endregion
#region"GridView Methods"
protected void grvObject_Sorting(object sender, System.Web.UI.WebControls.GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;
ViewState["SortExpression"] = sortExpression;
if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
BindAccount(sortExpression);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
BindAccount(sortExpression);
}
}
protected voidgrvObject_RowCreated(object sender, System.Web.UI.WebControls.GridViewRowEventArgse)
{
if (e.Row.RowType == DataControlRowType.Header)
{
int sortColumnIndex = GetSortColumnIndex();
if (sortColumnIndex != -1)
{
AddSortDirectionImage(sortColumnIndex, e.Row);
}
//Add Css for Header
foreach (Controlctl in e.Row.Controls)
{
foreach (Controlctl2 in ctl.Controls)
{
if (object.ReferenceEquals(ctl2.GetType().BaseType, typeof(LinkButton)))
{
((LinkButton)ctl2).CssClass = "OrderBy";
}
}
}
}
}
protected voidgrvObject_PageIndexChanging(object sender, System.Web.UI.WebControls.GridViewPageEventArgse)
{
grvObject.PageIndex = e.NewPageIndex;
BindAccount(SortField);
}
#endregion
#region"Event Handles"
protected voidPage_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
//Default Submit Button
Page.Form.DefaultButton = cmdQuickSearch.UniqueID;
BindAccount(SortField);
}
}
catch
{
}
}
protected voidcmdQuickSearch_Click(object sender, System.EventArgs e)
{
BindAccount(SortField);
}
#endregion
}
}
VB.NET Code
'Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
Imports System.Data.SqlClient
Namespace PerformSortingInGriview
Public Class _Default
Inherits System.Web.UI.Page
#Region "Const"
Private ConstASCENDING As String= " ASC"
Private ConstDESCENDING As String= " DESC"
#End Region
#Region "Properties"
Private PropertyGridViewSortDirection() As SortDirection
Get
If ViewState("sortDirection") Is Nothing Then
ViewState("sortDirection") = SortDirection.Ascending
End If
Return DirectCast(ViewState("sortDirection"), SortDirection)
End Get
Set(ByVal value As SortDirection)
ViewState("sortDirection") = value
End Set
End Property
Property SortField() AsString
Get
Dim o AsObject = ViewState("SortExpression")
If o Is Nothing Then
Return "AccName"
End If
Return CStr(o)
End Get
Set(ByVal Value As String)
ViewState("SortExpression") = Value
End Set
End Property
#End Region
#Region "Bind Data"
Private SubBindAccount(ByVal SortExpression As String)
Dim objBind As New DataTable
If GridViewSortDirection = SortDirection.Ascending Then
objBind = BindData(SortExpression, DESCENDING)
Else
objBind = BindData(SortExpression, ASCENDING)
End If
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(ByVal SortExpression As String, ByVal SortType As String) As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable = objSQL.FillTable("Pro_Accounts_List", New ObjectPara("@Keyword", txtSearch.Text.Trim), _
New ObjectPara("@SortField", SortExpression), _
New ObjectPara("@SortType", SortType))
Return objBind
End Function
#End Region
#Region "GridSort"
Private SubAddSortDirectionImage(ByVal columnIndex As Integer, ByVal headerRow As GridViewRow)
Dim sortImage As New Image()
If GridViewSortDirection = SortDirection.Ascending Then
sortImage.ImageUrl = "~/Images/sortascending.gif"
sortImage.AlternateText = "Ascending Order"
Else
sortImage.ImageUrl = "~/Images/sortdescending.gif"
sortImage.AlternateText = "Descending Order"
End If
headerRow.Cells(columnIndex).Controls.Add(sortImage)
End Sub
Private FunctionGetSortColumnIndex() As Integer
Dim SortExpression AsString = ""
For Each field As DataControlFieldIn grvObject.Columns
If NotViewState("SortExpression") Is Nothing Then
If field.SortExpression = DirectCast(ViewState("SortExpression"), String) Then
Return grvObject.Columns.IndexOf(field)
End If
Else
SortExpression = "AccName"
If field.SortExpression = SortExpression Then
Return grvObject.Columns.IndexOf(field)
End If
End If
Next
Return -1
End Function
#End Region
#Region "GridView Methods"
Private SubgrvObject_Sorting(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewSortEventArgs) Handles grvObject.Sorting
Dim sortExpression AsString = e.SortExpression
ViewState("SortExpression") = sortExpression
If GridViewSortDirection = SortDirection.Ascending Then
GridViewSortDirection = SortDirection.Descending
BindAccount(sortExpression)
Else
GridViewSortDirection = SortDirection.Ascending
BindAccount(sortExpression)
End If
End Sub
Private SubgrvObject_RowCreated(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewRowEventArgs) Handles grvObject.RowCreated
If (e.Row.RowType = DataControlRowType.Header) Then
DimsortColumnIndex As Integer= GetSortColumnIndex()
If sortColumnIndex <> -1 Then
AddSortDirectionImage(sortColumnIndex, e.Row)
End If
'Add Css for Header
For Eachctl As ControlIn e.Row.Controls
For Eachctl2 As ControlIn ctl.Controls
If ctl2.GetType().BaseType Is GetType(LinkButton) Then
DirectCast(ctl2, LinkButton).CssClass = "OrderBy"
EndIf
Next
Next
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
BindAccount(SortField)
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
BindAccount(SortField)
End If
Catch ex As Exception
End Try
End Sub
Private SubcmdQuickSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) HandlescmdQuickSearch.Click
BindAccount(SortField)
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