Hi,
We cannot get good performance if we query all the data and use the paging
feature that provided by GridView. To get a better performance we can use
custom paging. Please refer to following code. It uses pure Linq to Sql.
For the simplicity the pager has no next/previous/fist/last support. If you
need them you can try to add it yourself.
I wrote code in Page.aspx.cs directly, you may create a UserControl for
reuse.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Linq;
using System.Collections.Generic;
namespace WebApplication8
{
public partial class _Default : System.Web.UI.Page
{
public int PageSize
{
get
{
int i = (int)this.ViewState["PageSize"];
return i;
}
set
{
this.ViewState["PageSize"] = value;
}
}
public int PageIndex
{
get
{
int i = (int)this.ViewState["PageIndex"];
return i;
}
set
{
this.ViewState["PageIndex"] = value;
}
}
public SortDirection Sortdirection
{
get
{
if (this.ViewState["Sortdirection"] != null)
return (SortDirection)this.ViewState["Sortdirection"];
else
return SortDirection.Ascending;
}
set
{
this.ViewState["Sortdirection"] = value;
}
}
DataClasses1DataContext context = new DataClasses1DataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
this.PageSize = 4;
this.PageIndex = 0;
BindGrid();
BindPager();
}
}
protected void GridView1_Sorting(object sender,
GridViewSortEventArgs e)
{
//sample for sorting. theName is a field name in my table.
if (e.SortExpression == "theName")
{
if ((SortDirection)this.Sortdirection ==
SortDirection.Ascending)
{
this.Sortdirection = SortDirection.Descending;
}
else
{
this.Sortdirection = SortDirection.Ascending;
}
BindGrid();
}
}
void BindGrid()
{
if ((SortDirection)this.Sortdirection ==
SortDirection.Ascending)
{
GridView1.DataSource =context.SelectQuery1().OrderBy((key)
=> key.theName).Skip(this.PageIndex * this.PageSize).Take(this.PageSize);
}
else
{
GridView1.DataSource
=context.SelectQuery1().OrderByDescending((key) =>
key.theName).Skip(this.PageIndex * this.PageSize).Take(this.PageSize);
}
GridView1.DataBind();
}
void BindPager()
{
ArrayList array = new ArrayList();
int total = context.SelectQuery1().Count();
for (int i = 0; i < total / this.PageSize +
((total%this.PageSize==0)?0:1); i++)
{
array.Add(new HelperClass() { Index = i,
Iscurrent=i==this.PageIndex?true:false });
}
this.Repeater1.DataSource = array;
this.Repeater1.DataBind();
}
protected void Repeater1_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "Page")
{
this.PageIndex = Convert.ToInt32(e.CommandArgument);
BindGrid();
BindPager();
}
}
}
//HelperClass help to show/hide linkbutton and label in Repeater and
provide the index.
public class HelperClass {
public int Index { get; set; }
public bool Iscurrent { get; set; }
}
}
<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="True"
onsorting="GridView1_Sorting">
</asp:GridView>
<asp:Repeater ID="Repeater1" runat="server"
onitemcommand="Repeater1_ItemCommand">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server"
CommandName="Page" CommandArgument='<%#Eval("Index")%>'
Visible='<%#(bool)Eval("Iscurrent")?false:true
%>'><%#Eval("Index")%></asp:LinkButton>
<asp:Label ID="Label1" runat="server" Text='<%#Eval("Index")
%>' Visible='<%#(bool)Eval("Iscurrent")?true:false %>'></asp:Label>
</ItemTemplate>
</asp:Repeater>
Please test it and let me know if it works. If you have further questions
please feel free to ask.
Regards,
Allen Chen
Microsoft Online Support
--------------------
| Thread-Topic: Sorting a Gridview Control / Linq Result Set from SPROC
| thread-index: AckIaW9+a6/+o+m0RgG1FQXzhdj/ng==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?Y2hlY2tyYWlzZXJAY29tbXVuaXR5Lm5vc3BhbQ==?=
<
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| Subject: RE: Sorting a Gridview Control / Linq Result Set from SPROC
| Date: Wed, 27 Aug 2008 10:22:09 -0700
| Lines: 163
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet:74755
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| Ok, thanks. The solution seems fine, but I have to ask: performance
wise, is
| this a good thing to do?
|
| It seems like we're doing a query and then burning time looping through
the
| data again after retrieving the result set and then binding. Would it be
| better to use something other than LINQ, e.g. just do it the old way:
query,
| fill the data table, sort the datatable, and bind?
|
| "Allen Chen [MSFT]" wrote:
|
| > Hi,
| >
| > Based on my understanding you're using Linq to Sql and retrieve data
via
| > the stored procedure. You want to sort/paging the GridView. If my
| > understanding is wrong please correct me.
| >
| > The simplest solution I can provide is to use ObjectDataSource and
return a
| > DataTable. Thus we can use the built-in sorting/paging that
| > ObjectDataSource provides. Please try following code:
| >
| > using System.Collections;
| > using WebApplication8;
| > using System.Data.Linq;
| > using System.Collections.Generic;
| > using System.Data;
| > using System.Reflection;
| > public class MyClass {
| > public DataTable Select()
| > {
| >
| > DataClasses1DataContext context = new DataClasses1DataContext();
| >
| > ISingleResult<SelectQuery1Result> result =
| > context.SelectQuery1();//SelectQuery1 is the auto-generated code to
call a
| > stored procedure.It's context.selectCategories() in your case.
| >
| > DataTable dt = new DataTable();
| > PropertyInfo[] propertys =
| > typeof(SelectQuery1Result).GetProperties();//The reason to use
reflection
| > is that we don't have to change code in this BLL if the ORM changes.
| > foreach (PropertyInfo p in propertys)
| > {
| > dt.Columns.Add(p.Name);
| > }
| > foreach (SelectQuery1Result r in result)
| > {
| > DataRow dr = dt.NewRow();
| > foreach (PropertyInfo p in propertys)
| > {
| > dr[p.Name] = p.GetValue(r, null);
| >
| > }
| > dt.Rows.Add(dr);
| > }
| > return dt;
| > }
| > }
| > <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
| > SelectMethod="Select"
TypeName="MyClass"></asp:ObjectDataSource>
| >
| >
| >
| >
| > <asp:GridView ID="GridView1" runat="server" AllowSorting="True"
| > AutoGenerateColumns="True" AllowPaging="true" PageSize="2"
| > DataSourceID="ObjectDataSource1" >
| >
| > </asp:GridView>
| >
| > Please have a try and feel free to let me know if you have any
questions.
| > Regards,
| > Allen Chen
| > Microsoft Online Support
| >
| > Delighting our customers is our #1 priority. We welcome your comments
and
| > suggestions about how we can improve the support we provide to you.
Please
| > feel free to let my manager know what you think of the level of service
| > provided. You can send feedback directly to my manager at:
| > (e-mail address removed).
| >
| > ==================================================
| > Get notification to my posts through email? Please refer to
| >
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
| > ications.
| >
| > Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
| > where an initial response from the community or a Microsoft Support
| > Engineer within 1 business day is acceptable. Please note that each
follow
| > up response may take approximately 2 business days as the support
| > professional working with you may need further investigation to reach
the
| > most efficient resolution. The offering is not appropriate for
situations
| > that require urgent, real-time or phone-based interactions or complex
| > project analysis and dump analysis issues. Issues of this nature are
best
| > handled working with a dedicated Microsoft Support Engineer by
contacting
| > Microsoft Customer Support Services (CSS) at
| >
http://msdn.microsoft.com/subscriptions/support/default.aspx.
| > ==================================================
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --------------------
| > | Thread-Topic: Sorting a Gridview Control / Linq Result Set from SPROC
| > | thread-index: Acj83DFXc8mly4CMSiuiZOqp7LBz6g==
| > | X-WBNR-Posting-Host: 207.46.19.168
| > | From: =?Utf-8?B?Y2hlY2tyYWlzZXJAY29tbXVuaXR5Lm5vc3BhbQ==?=
| > <
[email protected]>
| > | Subject: Sorting a Gridview Control / Linq Result Set from SPROC
| > | Date: Tue, 12 Aug 2008 17:33:23 -0700
| > | Lines: 32
| > | Message-ID: <
[email protected]>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| > | Newsgroups: microsoft.public.dotnet.framework.aspnet
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl
| > microsoft.public.dotnet.framework.aspnet:73793
| > | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| > | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
| > |
| > | Hello,
| > |
| > | Trying to sort a PAGED gridview control by one template column. The
| > result
| > | set I get back is from a LINQ stored procedure query. I have the
| > following
| > | set on my Gridview Tag: allowpaging="true", AllowSorting="true",
| > | onpageindexchanging="myGrid_PageIndexChanged", and the
| > | OnSorting="myGrid_Sorting"
| > |
| > | In the myGrid_Sorting event, I have a call the Linq method that runs
a
| > | "select categories" stored proc:
| > |
| > | myGrid.DataSource = context.selectCategories();
| > | myGrid.DataBind();
| > |
| > | The query returns three fields in the result set that are used in the
| > Grid:
| > | ItemCategoryId, ItemCatgoryDescription, ItemCategoryRecordStatus.
| > |
| > | I can't figure out how to order the LINQ results to sort the Gridview
by
| > | ItemCategoryDescription and support the paging in the Gridview.
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| >
| >
|
