Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls

I haven't played too much with ASP.NET 3.5's new LinqDataSource control yet - so I figured it was about time I did a little research and checked it out.  Typically, I like to document my research by creating a small prototype application focused on a handful of the new features that I am investigating.  I find building a reference application is helpful because it tends to drive out some of those nasty implementation details that somehow remain hidden if I just browse the documentation or read a reference book. 

So for a recent research project, I decided I would look into the LinqDataSource's control GroupBy and OrderGroupBy attributes to see if they could be used to help me build a grid that supports grouping.  Below is the final screen shot for my reference application.  The grid shows the rows in the Northwind Orders table - grouped by the customer that placed the order.  The group of orders for each customer can be expanded/collapsed by clicking on the plus/minus icon (no postback required).  Read on if you are interested in the details.  If you download the code, make sure to update the connection string in the web.config to point to your Northwind database. 

Live Demo (IE6, IE7 and FF) | Download

image

The LinqDataSource Control

Below is what MSDN has to say about the LinqDataSource control.  In a nutshell, the control is a nice way to leverage the Linq features in your web apps.

image

Creating the Northwind DataContext

The LinqDataSource requires a context object that represents the data source.  For my scenario I want to access the rows in the Orders table of the Northwind database, so my first step was to use Visual Studio's designer tools to create the context object for my Northwind database.  To do this I opened the 'Add New Item' dialog and selected the 'LINQ to SQL Classes' template.  I named my template Northwind.dbml and clicked Add.

image

If you connect Visual Studio's Server Explorer to your database, it becomes really easy to create entities from your the tables in your database.  So once the designer was opened for the Northwind.dbml file, I opened the Server Explorer (CTRL + ALT + S), navigated out to my local SQL Server where I have my Northwind database.  I expanded the Tables node in the Northwind tree and dragged the Orders table onto the design surface.

image image

Configuring the LinqDataSource

After the Northwind DataContext was setup, I next moved on to configuring my LinqDataSource that I am using to fill the grid.  I want to display all of the orders grouped by the customer that placed the order.  To help me accomplish this, I configured the LinqDataSource as follows

image

The ContextTypeName points to the Northwind DataContext object that I created in the previous step and the TableName attribute is set to the table within the DataContext that I want to be fetching records from - Orders for this sample.  The Select and GroupBy attributes is where it gets a little more interesting.  Like I mentioned earlier, I was to group the orders by the customer that placed the order.  So I set the GroupBy attribute to the customerid column.  If you look again at the screen shot for my grid, I am also displaying the customerid and the number of orders in the group using the following format: '{0} ({1} Orders)' where {0} is the customerid  and {1} is the number of orders they placed.  I want to use these values in my databinding expressions so I need to make sure to use them in the select statement.  You can reference the grouped by property in the select statement by using the well known Key property.  So when the orders for ALFKI are bound to the grid, Eval('Key') resolves to 'ALFKI' and Eval('Count') resolves to '6'.

The other interesting item in my Select statement is the use of the It keyword.  The It keyword provides you with access to the individual items of the group - so in this scenario the orders for the individual groups.  So for customer ALFKI, here are the values of the Key, Count and Items properties.

Key Count Items
ALFKI 6 Order { ID='10643', OrderedDate='08/25/1997' ... }
Order { ID='10692', OrderedDate='10/31/1997' ... }
Order { ID='10702', OrderedDate='11/24/1997' ... }
Order { ID='10835', OrderedDate='02/12/1998' ... }
Order { ID='10952', OrderedDate='04/27/1998' ... }
Order { ID='11011', OrderedDate='05/07/1998' ... }

Configuring the ListView

After my LinqDataSource was all setup I started working on creating the ListView and its data binding expressions that are used to fill my grid.  I thought the easiest way to produce the markup for the grouping was to use two ListView's, one nested inside the other.  The outer ListView is responsible for rendering the grouping header rows and the nested ListView renders the orders belonging to each of the groups.

So I first created the outer ListView as follows.  I set the DataSourceID to point to the LinqDataSource we configured in the previous section.  Then I used the LayoutTemplate to define the header columns of the table and the ItemTemplate to render the header rows for each of the groups.  As you can see, I am referencing the Key and Count properties of the LinqDataSource using the standard DataBinding syntax. 

 image

Next, I added a nested ListView to the outer ListView for rendering the individual orders.  For the nested ListView, I bound the DataSource property to the Items property of the LinqDataSource and I used regular DataBinding expressions to reference the individual properties of each of the orders.

image 

Expanding and Collapsing the Groups

Finally, to put the finishing touches on the grid, I need a way to expand/collapse the groups by clicking on the plus/minus icon.  To do this, I added a JavaScript function to the page called toggleGroup.  This function applies and removes a css class called hidden the rows to control their visibility. 

image

When the grid first renders, the hidden class is applied to all of the data rows.  When the user clicks the plus icon, the following bit of JavaScript is run and uses the addCssClass/removeCssClass ASP.NET AJAX functions to add or remove the hidden class from the rows.  Finally, the function takes care of updating the plus/minus icon.

image

That's it.  Enjoy!


TrackBack

TrackBack URL for this entry:
http://mattberseth.com/blog-mt/mt-tb.fcgi/99

Listed below are links to weblogs that reference Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls:

» Jan 24th Links: ASP.NET, ASP.NET AJAX, Visual Studio, .NET, IIS from ScottGu's Blog
I just arrived back from my trip from Asia, and decided to celebrate (since I'm jet-lagged and can't [Read More]

» Jan 24th Links: ASP.NET, ASP.NET AJAX, Visual Studio, .NET, IIS from Community Blogs
I just arrived back from my trip from Asia, and decided to celebrate (since I'm jet-lagged and can't [Read More]

» Jan 24th Links: ASP.NET, ASP.NET AJAX, Visual Studio, .NET, IIS from Mirrored Blogs
I just arrived back from my trip from Asia, and decided to celebrate (since I'm jet-lagged and can't [Read More]

Comments


Posted by: wonderland on January 11, 2008 12:00 AM

Bookmarked!

This is technical ques, can you group it by date? I mean, just like how your blog archives were group by Month then by Year like so,

January 2008
December 2007
...

I cant do it using the OrderDate then by Total Orders :(


More Power to Matt!

Posted by: Deepak Chawla on January 11, 2008 12:00 AM

Should I give you an easier way.

Use the AjaxControlToolkit accordion control and stick the grid into the template.

And what you get is a grid shown at a time. And if you want to see another grid the first one closes. So you use the maximum real estate of the page.

Nicely done! I like this.

Great article as usual!

Posted by: PEN on January 11, 2008 12:00 AM

Deepak, You cannot dynamically populate an accordion controls panes then bind a gridview to that pane.
Let me know if you have tried it.
Nice article Matt.

Hey Now Matt,
Great post, very informative and useful. The end result for the gridview functionality is really nice to have.
Thx 4 the info,
Catto

Posted by: Gregory on January 12, 2008 12:00 AM

Does LinqDataSource allow to limit queries?

Thanks in advance

Looks great, but its running quite slow when paging? or its just me?

Cheers

Posted by: wu jian on January 14, 2008 12:00 AM

thank you for the article

Posted by: Norbert Kornyi on January 15, 2008 12:00 AM

You also cant forget about the GroupTemplate. I cant tell if all of this could be done using it, but its a useful feature anyway..

Great article.I really like your documantion style.Its very clean.Thanks for sharing your knowledge with us..

Posted by: Deepak Chawla on January 16, 2008 12:00 AM

Hi PEN,
I tried to put the code in here but its just not showing correctly here. ie only part of the code is getting pasted. But I will try to point out the important features.

Code Page
1) Add an accordion control to the page. which supports OnItemDataBound="AccordionDisplay_ItemDataBound"
2) In the Content template add a grid with ID="grdDetails"

In the code behind
1) Somewhere you will bind the data to the accordion control
AccordionDisplay.DataSource = _SomeCollection;
AccordionDisplay.DataBind();

This will instigate a call to the following procedure

2) you will implement AccordionDisplay_ItemDataBound(object sender, AjaxControlToolkit.AccordionItemEventArgs e)

This gets called for each header and template

if (e.ItemType == AjaxControlToolkit.AccordionItemType.Content)
{
GridView grdDetails = (GridView) e.AccordionItem.FindControl("grdDetails");

// Once you got the grid bind it to your data source.

grdDetails.DataSource = _SomeData;
grdDetails.DataBind();
)


Hope this helps. I will keep a check on this blog for any more questions you might have.

Posted by: The Dude on January 17, 2008 12:00 AM

It sure would be cool if you could do a tutorial on how to make a grid like the one from the extjs.com framework. They have the ability to click on the column header and then choose which columns you want to show in the grid. You can show/hide columns - I cant find anything like it on the web for asp.net and I dont want to use the extjs framework.

Awesome post. Makes me wish we were using 3.5 for my current project. Thanks a heap for this

You should by default set the row to visible and on load add the css class hidden with javascript. In this way your page shows up nicely in browsers which dont support javascript or have it disabled. And youll add some extra functionality for browsers that do support javascript.

Cheers,
Wes

Posted by: John Mcfetridge on January 29, 2008 12:00 AM

wonderfully written article as it is so well explained, however unless I am missing a key point the LinqdataSource is a great RAD tool but useless for the n-Tier apps that most of use write.

Posted by: Victor on February 5, 2008 12:00 AM

Matt RULEZ!!!

Posted by: rgomez on February 7, 2008 12:00 AM

Great article!

I was wondering how can I display the CustomerName instead of the CustomerId?

What would be the value of my Select= ?

Thanks!

Posted by: tom on February 22, 2008 12:00 AM

I have changed the row content in the second listview "lvItems" with "linkbuton" controls, and now I would like to get the values of each cellule in the row when I click on this row
Ive tried that by using ;


void lvv_SelectedIndexChanging(Object sender, ListViewSelectEventArgs e)
{
using (ListViewDataItem listViewDataItem = (ListViewDataItem)lv.Items[e.NewSelectedIndex])
{
if (listViewDataItem != null)
{
ListView lvv = (ListView)listViewDataItem.FindControl("lvItems");
ListViewItem item1 = (ListViewItem)lvv.Items[e.NewSelectedIndex];
LinkButton l1 = (LinkButton)item1.FindControl("LinkButton1");
Message.Text = l1.Text;
}
}
}


and;


asp:ListView ID="lvItems" runat="server"
DataSource=
OnSelectedIndexChanging="lvv_SelectedIndexChanging" >"

thanks for your help

Posted by: Clay Angelly on February 27, 2008 12:00 AM

Awesome site and contributions as usual.

Im trying to implement a nested ListView. Im having a problem determing which ListView method in which to databind the nested ListView. Ive been going through the MSDN ListView Members documentation and seem close but not quite there.

Here is my relevant ASPX code:

And here is my relevant C# codebehind:

Any direction would be greatly appreciated, meanwhile its back to MSDN. Thanks again for the contributions here!

Clay

Posted by: Clay Angelly on February 28, 2008 12:00 AM

Should mention that I realize ItemDataBound isnt the place to do this, just used to test syntax.

Posted by: Damian Small on March 14, 2008 12:00 AM

Excellent Post Thank you

However paging causes the following error
Sys.WebForms.PageRequestManagerServerErrorException

This occurs with SQL 2000
SQL Express works fine. I have searched for a workaround.

Posted by: Pedro Carvalho on March 16, 2008 12:00 AM

Very nice code, thanks.
Is there a way to nest 3 ListView controls in order to group by yet another property, for instance the month of the order?

I can see where the results are pointed to the itemplaceholder of the listview; however, I am not sure how the nested list view places its results right under the its corresponding parent result.

In other words since there is only one how are the second batch of results placed right under its parent ?

Where is the second placeholder?

Thanks
Erik

Posted by: Raja on April 21, 2008 12:00 AM

Hi. I am playing with your code and using two tables to get data. In one table I have CategoryId and in second CategoryName. Normally with linq I would use
Eval("Table2.CategoryName") to show CategoryName in a listview
but in nested ListView it does not work, probably because the nested ListViews datasource is DataSource=Eval("Items").
I can use a function to make it work but in Linq there should be a direct way for this.
Any idea how to get CategoryName?
Thanks
Raja

Posted by: Nicola Cassolato on May 1, 2008 12:00 AM

Hi Matt, i really liked this article.

Currently Im trying to use an ObjectDataSource instead of a LinqDataSource but it doesnt have a "items" proprty that I can use in the nested listview datasource.

Do you have any suggestion or idea that i can further investigate?

Thank you?

What needs to be done in order to change the Group by order to Count instead of the customer?

Since Count is not part of the data context it does not allow me select as a field.

Any thoughts!

Thanks,

Posted by: Brahim Al-Hawwas on May 26, 2008 12:00 AM

Awesome article

Posted by: apan on June 2, 2008 12:00 AM

Hi, matt! How can I sort the nested table . For example ,When I click the "ID", it will sort by "ID". However, when I use the ,I got a warning message.
Eager for your reply, thank u.

Posted by: prahlad on June 10, 2008 12:00 AM

Is it possible to do same this you did in the article using asp.net 2.0
Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls

Thank You
Prahlad

Posted by: NMyVision on June 11, 2008 12:00 AM

@prahlad : Yes, you could use a listview or repeater and use the itemdatabound event to inject javascript onclick events.

@matt : you could alter the html to use tbody tags and hide/show the tbody element which will hide the rows with it.

Posted by: Nikolas on June 18, 2008 12:00 AM

Hi

Your projects are great!

I Have a question how can I make this grid to load with expanded nodes?

Thanks in advance!

Hi Matt,

As usual awesome article. I was wondering if we could add a accordion like behavior with grouping. If one group is opened the other should close. I have so many categories so I do not want to use separate listview for all of them. A point to the right direction will be much appreciated.

Thanks

Posted by: Elmer on August 12, 2008 03:54 AM

Hi Matt,

This tutorial is very useful to me....However, i encounter a compile error (The ListView 'lvItems' raised event ItemEditing which wasn't handled.)

after clicking the "edit" button in

Pls give me some suggestion?

Thanks a lot!

Posted by: trifu on August 20, 2008 09:10 AM

protected void PagerCommand(object sender, DataPagerCommandEventArgs e)
{
switch (e.CommandName)
{
case "Next":
// guard against going off the end of the list
e.NewStartRowIndex = Math.Min(e.Item.Pager.StartRowIndex + e.Item.Pager.MaximumRows, e.Item.Pager.TotalRowCount - e.Item.Pager.MaximumRows);
e.NewMaximumRows = e.Item.Pager.MaximumRows;
break;
.....

You didn't handle paging correctly. What happens if I choose a PageSize of 8 and the total number of records is less than 8, lets say 2? It will break because
e.Item.Pager.TotalRowCount - e.Item.Pager.MaximumRows = 2 - 8 = -6.
You've got the same error in the "Last" command.

Posted by: KJosh on August 20, 2008 10:34 PM

Hi Matt,
Very good article. Can we display the column headers above the child rows when the Customer Id is clicked? Instead of displaying at the top for all customers. This is to save some space in my web page.
Thanks

Posted by: ari on August 26, 2008 03:26 AM

Hi Matt,your project is great! Thanks.
Is there any way to make for each cosetumer details a last row of summarizing (total price..)?

Matt, thanks a bunch for this tip. Powerful, with little code, and the added CSS makes for a beautiful UI.

I'm trying to assemble the data source from the back end and struggling a bit with the LINQ-based grouping logic. I'm sure I'll figure it out. Thanks again!

Erik

Posted by: Jose on September 11, 2008 02:04 PM

Does the It keyword only works in C#? I wa snot able to find more information on the It keyword.

Could you post how does the LINQ query looks? I'd like to be able to replicate the LINQ query in VB.NET without the need of using LinqDataSource

Posted by: Daniel Smith on September 21, 2008 01:09 PM

This is a fantastic example of how to group data in ASP .net - very useful indeed. I wondered if it might be possible for you to extend this example to include sorting. I'd love for users to be able to sort the columns by clicking the Headers, like in the GridView. I haven't been able to do this myself yet.

Posted by: Jay Gabilo on November 4, 2008 11:30 PM

Hi,

I'm using your technique. However I'm having an error from the java script function when i try to click on the "+" image and it is pointing to the following line.

"Sys.UI.DomElement.removeCssClass(table.rows[i], 'hidden');"

and the message says "object required".

Any idea?


Jay

Posted by: Bill Fu on February 16, 2009 12:12 PM

Now what happen if there are multiple tables and the GroupBy is actually a foreign key to another table, how would you refer to that key, I tried "tablename.fieldname" which I thought was going to work but it didn't?

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Consulting Services

Yep - I also offer consulting services. And heck, I'll do just about anything. If you enjoy my blog just drop me an email describing the work you need done.

Recent Comments

  • Bill Fu wrote: Now what happen if there are multiple tables and the GroupBy is actually a foreign key to another ta...
  • Jay Gabilo wrote: Hi, I'm using your technique. However I'm having an error from the java script function when i try ...
  • Daniel Smith wrote: This is a fantastic example of how to group data in ASP .net - very useful indeed. I wondered if it ...
  • Jose wrote: Does the It keyword only works in C#? I wa snot able to find more information on the It keyword. Co...
  • Erik wrote: Matt, thanks a bunch for this tip. Powerful, with little code, and the added CSS makes for a beautif...
  • ari wrote: Hi Matt,your project is great! Thanks. Is there any way to make for each cosetumer details a last r...
  • KJosh wrote: Hi Matt, Very good article. Can we display the column headers above the child rows when the Customer...
  • trifu wrote: protected void PagerCommand(object sender, DataPagerCommandEventArgs e) { ...