Creating a FrequencyDecoder.com Style Grid with the ListView, DataPager and LinqDataSource Controls
One of the projects I am working on is on the verge of failure. For a while now the writing has been on wall, but this was the week that everyone finally realized it. Its a rotten feeling and I hate it. So to cheer myself up and to keep the creative juices flowing I thought I would play around a little bit with ASP.NET 3.5's new ListView, DataPager and LinqDataSource controls.
A while back, I came across a really nice looking data grid that I wanted to create a ListView skin for. Besides the nice soft blue color scheme, I liked the data grid's sorting icons, all caps headers, alternating row styles as well as the simple text-based data pager. So I used the ListView and DataPager controls to build the visual elements of the data grid and used the LinqDataSource to source the grids data. I have written a few other posts on these controls before, feel free to check them out if you would like to see other examples (all come with live demos as well as code downloads) ...
- Building a Grouping Grid with the ASP.NET 3.5 LinqDataSource and ListView Controls
- A Slider DataPager with Dynamic Tooltips
- Data Navigation with the ListView, DataPager and SliderExtender Controls
- YUI DataTable Styled ListView
Here is a quick screen shot of the sample grid I will outline in this post. Feel free to check out the live demo. If you download the code, make sure to update the connection string in the web.config to point to your SQL Server instance.
Configuring the LinqDataSource
In the screen shot above, I am showing the products found in the Northwind Products table. For each of the products, I want to display a few attributes from the Product, but I also want to show the category name as well as the supplier. If I were to write a custom sql statement for filling this grid, it would look something like this. Notice I am joining across to the categories and suppliers tables to retrieve additional attributes from these entities.
One of the cool LINQ to SQL features is that if you use the designer to create the DataContext object for your database, you can use the dot property notation to navigate these relationships from a LINQ query (i.e. Supplier.CompanyName). So you can specify the same query using the LinqDataSource using by setting the Select attribute. And because the LinqDataSource automatically supports paging and sorting, we get those features without having to do anything extra. If you look at the LINQ query defined in the Select attribute below, you will see the last 2 items in the select statement are using the dot property notation to traverse through the Supplier and Category property (association in the database) to retrieve the suppliername and categoryname values.
Creating the DataContext object that supports the above LINQ to SQL query is really simple.
1. In Visual Studio you add a new LINQ to SQL Classes file (File -> New -> File -> LINQ to SQL Classes)
2. This will create a dbml file in the App_Code directory. Open it by double clicking it. Once the designer surface is displayed, open Server Explorer (CTRL+ALT+S), navigate to the location of your SQL Server and drag the Northwind.dbo.Product, Northiwnd.dbo.Supplier and Northinwd.dbo.Category tables on to the design surface. You will notice because the designer can use the database metadata to discover the relationships between these entities.
And that's it. Your LINQ to SQL query will now run. If you are really brave you can also open the designer.cs file that was created for the dbml file. Notice four partial classes were created: NorthwindDataContext, Product, Supplier and Category.
If we crack open the Product class, we will see the designer code generated the metadata the LINQ to SQL runtime uses to generate the SQL statements required to execute our LINQ queries. Below are the properties that represent the Supplier and Category relationships. The properties are tagged with the Association attribute. I have never used this attribute before, but I think you can kind of take a guess as to how the LINQ runtime uses this information to generate the corresponding SQL statements.
Creating the ListView
Now that our data source is all setup, we can start looking at what style elements we need our grid to support. You can see from the screen shot, we have the following requirements
- The first data cell needs to have a custom style for data rows and alternating data rows
- The numeric data cells need to be right aligned
- The column headers need a blue background image
- We need to toggle the css class that is applied to both the header cell as well as all of the data cells for a column that is currently being sorted on
To tackle the data row style requirements, I created a TR within the ListView's ItemTemplate. Within the TR, I created TD's for each of my columns. To handle applying different styles for alternating rows, I use a databinding expression to conditionally set the class of the data row. If the DataItemIndex the TR is bound to is even I set the class to row otherwise it is an alternating row and it gets the altrow style. I chose this approach over using the AlternatingItemTemplate because this is the only bit that would vary between the templates. I also applied the first css class to the first data cell and the numeric class to the unitprice and unitsinstock cells. Here is the markup for my ItemTemplate.
After the ItemTemplate was created, I created the template for the table structure within the ListView's LayoutTemplate. Within each of the TH columns, I added a LinkButton and set the CommandName to Sort and the CommandArgument to the name of the column I want to sort by. Here is the markup for the ID column. The markup for the other are similar.
To handle applying the custom sorting styles, I handled the ListView's ItemDataBound event and apply the sort css class to both the header and data rows.
Creating the DataPager
After the ListView and LinqDataSource controls were created, I then added the markup for my DataPager. I had not used the NextPreviousPagerField and NumericPagerFields before, so this part was a little new for me. I really liked the interface for the PagerField's - like many of the AjaxControlToolkit controls, it has properties that you can use to apply CSS classes to the pager elements.
To build the control shown above, I used a sequence of three PagerFields, first a NextPreviousPagerField to display the << and < buttons (representing the navigation to the first and previous page), then a NumericPagerField for displaying the 1-5 and ... buttons, and finally another NextPreviousPagerField for the > and >> buttons (the next and last pages). You can use the ButtonCssClass attribute of the NextPreviousPageField to style Next/Previous/First/Last buttons and the NumericPagerField defines CSS classes for the current page label, the Next/Previous pages buttons as well as the NumericButtonCssClass. The markup and CSS below is what I am using the generate the control above. I am using a gradient image for the background of each of the buttons.
One small quirk that I have not yet researched was that it appears the DataPager is rendering two spaces between the NumericPagerField's NextPrevious button and the numeric buttons. It's pretty subtle, but you can see it in the following screen shot. See how the spacing is a little larger between these controls.
I opened the IE Developer Toolbar to see why this spacing was so large and it looks like 2 spaces is being rendered instead of a single one. You can set the RenderNonbreakingSpaceBetweenControls if you want the controls all packed together.
This attribute defaults to false and a single space is supposed to be rendered between the pager controls. For some reason it looks like 2 spaces is being rendered between the last numeric pager control and the next page control. Anyone else notice this?
That's it. Enjoy!