Asp.net gridview paging and sorting with linq to sql custom linq statement

Question

Well, I know this may not be so hard, but it’s hard for them to find any information. I have a gridview on a page that I populate with data based on a user-selected date range (drop-down list). When the user clicks the button, I fill in the grid and show it. All this is done using Linq to Sql. I also need to enter paging and sorting. Please help me!!! Below is my button click event ... I am open to any suggestions to get this working

protected void btnGenerate_Click(object sender, EventArgs e) { int dateRange =0; if (rbDateList.Checked) { switch (ddlDateRange.SelectedIndex) { case 0: dateRange = 30; break; case 1: dateRange = 60; break; case 2: dateRange = 90; break; default: dateRange = 30; break; } } GYTDataContext gt = new GYTDataContext(); var productList = from o in gt.PurchaseOrderDetails join p in gt.Products on o.ProductId equals p.ProductId join h in gt.PurchaseOrderHeaders on o.PurchaseOrderId equals h.PurchaseOrderId where h.OrderDate>DateTime.Now.AddDays(-dateRange) group o by o.ProductId into orderedItems select new { orderedItems.Key, QuantityOrdered = orderedItems.Sum(s => s.OrderQuantity) }; var totalOrderInfo = from p in productList join prod in gt.Products on p.Key equals prod.ProductId select new { prod.Reference, UnitPrice = prod.Price, prod.ManufacturerProductId, p.QuantityOrdered, TotalCost = prod.Price * Convert.ToInt32(p.QuantityOrdered) }; gvOrderReport.DataSource = totalOrderInfo; gvOrderReport.DataBind(); gvOrderReport.Visible = true; 

Answer 1

I had the same problem as you. Most examples of linqdatasource, ilustare WhereParameters> controlparameter functions are cool, but not so powerful.

The answer is simple: Use LinqDataSource and simply implement the "onselecting" event, passing any data you want.

Here is a short example with a full set of features for filtering and swapping (also note that the filled sql is optimal and only requests the top 10 records each time)

Aspx:

 <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox> <asp:Button ID="btnFilter" runat="server" Text="Filter" onclick="btnFilter_Click"/> <asp:LinqDataSource ID="LinqDataSource1" runat="server" onselecting="LinqDataSource1_Selecting"> </asp:LinqDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="LinqDataSource1"> <Columns> <asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True" SortExpression="FirstName" /> <asp:BoundField DataField="MiddleName" HeaderText="MiddleName" ReadOnly="True" SortExpression="MiddleName" /> <asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True" SortExpression="LastName" /> </Columns> </asp:GridView> 

CodeBehind:

  protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e) { var ctx = new LinqDataSource.DBDataContext(); IQueryable<Customer> customers = ctx.Customers; if (!String.IsNullOrEmpty(txtLastName.Text)) customers = customers.Where ( c => c.LastName.Contains(txtLastName.Text)); e.Result = customers; } protected void btnFilter_Click(object sender, EventArgs e) { GridView1.DataBind(); } 

Answer 2

Since you are using LINQ-to-SQL with GYTDataContext , why not use LinqDataSource to populate your Gridview?

LinqDataSource can handle paging and sorting automatically.

http://msdn.microsoft.com/en-us/library/bb547113.aspx