[Edit: Updated link to download code]
Click here to download the source for these examples. This is out on GoogleCode. I recommend TortoiseSVN to download the code.
One of the first issues you encounter when doing .NET development the “Right Way”/”Pariveda Way” is finding an example of how to support paging and sorting in a GridView. Many of the examples out on the web assume you are using a SqlDataSource to get at your data. If you are developing against an N-Tier architecture, you know you will need to use the ObjectDataSource, but there are few examples that show how to support paging and sorting when using this model.
This example will hopefully point you in the right direction for how to support paging and sorting using an ObjectDataSource. In addition, it will show you a method for supporting multiple search criteria where a user may or may not choose to filter on all the criteria available—LINQ has a great story around this topic.
Here’s the scenario, we need a Grid to display a list of products (Product Name, Category Name, and Supplier Name) from the Northwind database. The grid can be filtered on the Category, the Supplier or both. In addition, the grid can be sorted by any of the three columns by clicking on the columns. Sorting on the Category or Supplier should also use a secondary sort by Product since there will be multiple records for each Category and/or Supplier when sorted.
The grid should look like this:
One of the primary reasons we advocate using a business tier and the ObjectDataSource is to support writing tests against your code (it’s difficult and not all that useful to write web testing code, so we choose to have as much code as possible isolated to our business and data tiers). In true Test Driven Development fashion, let’s start by creating the tests that we will use to test out our Business layer code before we write anything else. We are using NUnit, but you can use whatever testing framework you want).
Let’s create a project and solution called Pariveda.ObjectDataSource.Test inside a solution called Pariveda.ObjectDataSource.
After creating our first project in the new solution, let’s also add two new projects to the solution: Pariveda.ObjectDataSource.Business (Windows Class Library) and Pariveda.ObjectDataSource.Web (ASP.NET Web Application).
Next, we need to add our references. Both Test and Web need to point to the Business Project as a reference so they can use the business layer code.
Also, let’s add a reference to the nunit.framework.dll in our Test project.
So let’s work on adding at least the stub for our Business library method for retrieving a list of Products.
First let’s add a “LINQ to SQL Classes” file:
Next we’ll drag the Category, Product and Supplier tables from the Server Explorer (the Northwind database is included with the zipped up solution I have a link to).
First, let’s create an entity class in our Business Library that will store our grid results. We will call it ProductGetListResult. This will enable us to have a more optimized call that only returns the key information we need to display for our grid.
public class ProductGetListResult
{
public string ProductName { get; set; }
public string CategoryName { get; set; }
public string SupplierName { get; set; }
}
Next, we’ll create a class called ProductController in the Business Library. Our main method will be called GetList.
We know the parameters will need to be the following:
· An optional categoryId parameter to filter by category
· An optional supplierId parameter to filter by supplier
· A sortType indicating the sort order
· A startRowIndex parameter for use with paging to support starting at a specified row
· A maximumRows parameter for indicating how many total rows to retrieve
public List<ProductGetListResult> GetList(int? categoryId, int? supplierId, string sortType, int startRowIndex, int maximumRows)
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
//First we start with the base query
var productQuery = from p in db.Products
select p;
//Next we filter if category or supplier has been specified
productQuery = GetListQuery(productQuery, categoryId, supplierId);
//Sort the query
productQuery = GetListSort(productQuery, sortType);
//Use the Skip and Take methods to limit the results to the page requested
productQuery = productQuery.Skip(startRowIndex).Take(maximumRows);
//Project results into a ligher weight class and actually execute the query by
//calling the ToList method
return productQuery.Select(p => new ProductGetListResult
{
ProductName = p.ProductName,
SupplierName = p.Supplier.CompanyName,
CategoryName = p.Category.CategoryName
}).ToList();
}
}
We are creating an instance of our DataContext (being sure to use a disposing “use” statement on the object to ensure the data context and subsequent data connections are closed).
We do four things in this method:
· We dynamically filter based on our criteria (category and supplier ids)
· We dynamically sort based on our sortType parameter
· We return a subset of the information based on the startRowIndex and maximumRows parameters
· We then project the results into our lightweight list of ProductGetListResults
Let’s look at the GetListQuery method.
private IQueryable<Product> GetListQuery(IQueryable<Product> productQuery, int? categoryId, int? supplierId)
{
//Only filter by category id if specified (query not executed here either)
if (categoryId.HasValue && categoryId.Value > 0)
{
productQuery = productQuery.Where(p => p.CategoryID == categoryId.Value);
}
//Only filter by supplier id if specified (query not executed here either)
if (supplierId.HasValue && supplierId.Value > 0)
{
productQuery = productQuery.Where(p => p.SupplierID == supplierId.Value);
}
//Return the query (Query isn't even executed when we return it)
return productQuery;
}
What we are doing in this method is very similar to things we have done in the past prior to LINQ where we would build dynamic SQL except we get strong-typing and intellisense. In addition, we get all the benefits for prevention of SQL injection attacks etc.
Now let’s look at the sorting.
private IQueryable<Product> GetListSort(IQueryable<Product> productQuery, string sortType)
{
//Determining whether to sort ascending or descending
//(GridView appends DESC if the column is clicked on twice to indicate a descending sort)
bool sortDescending = false;
if (!String.IsNullOrEmpty(sortType))
{
string[] values = sortType.Split(' ');
sortType = values[0];
if (values.Length > 1)
{
sortDescending = values[1] == "DESC";
}
}
switch (sortType)
{
case "CategoryName":
if (sortDescending)
{
productQuery = productQuery.OrderByDescending(p => p.Category.CategoryName)
.ThenBy(p => p.ProductName); ;
}
else
{
productQuery = productQuery.OrderBy(p => p.Category.CategoryName)
.ThenBy(p => p.ProductName); ;
}
break;
case "SupplierName":
if (sortDescending)
{
productQuery = productQuery.OrderByDescending(p => p.Supplier.CompanyName)
.ThenBy(p => p.ProductName);
}
else
{
productQuery = productQuery.OrderBy(p => p.Supplier.CompanyName)
.ThenBy(p => p.ProductName);
}
break;
default:
if (sortDescending)
{
productQuery = productQuery.OrderByDescending(p => p.ProductName);
}
else
{
productQuery = productQuery.OrderBy(p => p.ProductName);
}
break;
}
//The query has not executed during this method, it is only setting up the query for execution
return productQuery;
}
We are basically doing two things, first we are seeing if a direction specifier is attached to our sortType parameter. The GridView automatically appends DESC to the end of the sortType parameter when a user clicks on a column twice (once for ascending, twice for descending). Next we are switching on the SortType which is a string and we are then using strong typing to sort on the column. Notice that for CategoryName and SupplierName we also do a secondary sort on ProductName since only sorting on Category or Supplier isn’t specific enough.
Big point about the benefits of LINQ: Please note that throughout all of this code, nothing is being executed on the database. That is one of the main benefits of LINQ: delayed execution. The only time SQL Server is contacted and the query is executed is when we first access the collection neither by enumerating through it or in this case, when we call the ToList method.
Now here’s where many folks get hung up. In order for the ObjectDataSource to correctly support paging, you need one more method, GetListCount. If you think about it, it becomes obvious why this is required. We are only returning a subset of records back to the ObjectDataSource (a page’s worth). The GridView has to know how many total rows are available so that it can correctly show the right number of links to the different page numbers. Since we separated the call for filtering from the rest of the code, we can easily use the same method in our GetListCount method.
public int GetListCount(int? categoryId, int? supplierId)
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
var productQuery = from p in db.Products
select p;
productQuery = GetListQuery(productQuery, categoryId, supplierId);
return productQuery.Count();
}
}
Here we simply call the Count() method on our query. You may be thinking to yourself, “Hey, isn’t that two calls to the database?” and you would be correct. While this is true, this is the most optimized query you can do if you need both a SUBSET of the data for paging and yet you also need the total row count.
In addition to our ProductController, I created two additional controller classes: CategoryController and SupplierController.
public int GetListCount(int? categoryId, int? supplierId)
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
var productQuery = from p in db.Products
select p;
productQuery = GetListQuery(productQuery, categoryId, supplierId);
return productQuery.Count();
}
}
Now to use it!
In order to create our web page, we don’t need to use any code-behind logic. It can all be accomplished declaratively using the ObjectDataSource. In order to clean up the aspx code and to follow best practices, we are using a MasterPage for css styling and AJAX support. In addition, we are using App_Themes for our GridView so we don’t see any messy formatting within the aspx page.
First, let’s add our two drop down lists to the page. We’ll add a CategoryDropDownList and a SupplierDropDownList.
Notice we select “Value” as the data field to display and “Key” as the data field for the value. These two properties map to the Id and the Name that we return from the business controller classes.
First thing, we’ll add a GridView to the page, and click on the chevron arrow to set our Data Source. We’ll select “New data source.”
We will select “Object” as our Data Source Type and call it ProductObjectDataSource.
We’ll pick our ProductController in the next screen…
We’ll select our GetList method from the drop down.
Now we hook up our two parameters in the GetList method to the SelectedValue properties of the DropDownList controls
Next, we will modify the DropDownList controls to support an “-All-“ property and ensure the GridView refreshes when we change the filters. I find it easier to change these properties in the “Source” view of the aspx editor. Note the AppendDataBoundItems ensures that even though we are binding to an ObjectDataSource, the existing ListItem (the “-All-“ option) still remains.
<asp:DropDownList ID="SupplierDropDownList" runat="server" AutoPostBack="true"
DataSourceID="SupplierObjectDataSource"
DataTextField="Value" AppendDataBoundItems="true" DataValueField="Key">
<asp:ListItem Value="-1" Text="- All Suppliers -" />
</< span>asp:DropDownList>
Now, we need to tell the GridView that we will be using Paging (check the Enable Paging checkbox):
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" EnableSortingAndPagingCallbacks="True"
AllowSorting="true" Width="600px" AutoGenerateColumns="False" DataSourceID="ProductObjectSource">
<Columns>
<asp:BoundField DataField="ProductName" HeaderText="<%$ Resources:ObjectDataSource, ProductLabel %>" SortExpression="ProductName"
ItemStyle-Width="250px" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="CategoryName" HeaderText="<%$ Resources:ObjectDataSource, CategoryLabel %>" SortExpression="CategoryName"
ItemStyle-Width="100px" HeaderStyle-HorizontalAlign="Left" />
<asp:BoundField DataField="SupplierName" HeaderText="<%$ Resources:ObjectDataSource, SupplierLabel %>" SortExpression="SupplierName"
ItemStyle-Width="250px" HeaderStyle-HorizontalAlign="Left" />
</< span>Columns>
<EmptyDataTemplate>
<asp:Localize runat="server" meta:resourcekey="EmptyData" />
</< span>EmptyDataTemplate>
</< span>asp:GridView>
We’ll use the “Edit Columns” option on the GridView to set the headers appropriately.
Here’s where the “wizards” breakdown and it gets “hokey.” You must add the SelectCountMethod and set it to the name of our GetListCount method, you must add the EnablePaging and the SortParameterName properties as well. And then you have to delete the sort and paging parameters that were automatically added to the ObjectDataSource when we set it up (sortType, startRowIndex, maximumRows).
<asp:ObjectDataSource ID="ProductObjectSource" runat="server" EnablePaging="True"
OldValuesParameterFormatString="original_{0}" SelectCountMethod="GetListCount"
SelectMethod="GetList" SortParameterName="sortType" TypeName="Pariveda.ObjectDataSource.Business.ProductController">
<SelectParameters>
<asp:ControlParameter ControlID="CategoryDropDownList" DefaultValue="null" Name="categoryId"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="SupplierDropDownList" DefaultValue="null" Name="supplierId"
PropertyName="SelectedValue" Type="Int32" />
</< span>SelectParameters>
</< span>asp:ObjectDataSource>
Whew! Now run it and confirm that it works as expected.
To add to the look and feel, use ASP.NET theming to set the styles of both the GridView and the general layout of the page.
Next we can easily add AJAX functionality so we don’t get those pesky postbacks. First we can use the built-in GridView functionality to switch pages and sorts without postbacks. Then we put the GridView inside and AJAX UpdatePanel and set the triggers to be based on the DropDownLists.
<asp:ObjectDataSource ID="ProductObjectSource" runat="server" EnablePaging="True"
OldValuesParameterFormatString="original_{0}" SelectCountMethod="GetListCount"
SelectMethod="GetList" SortParameterName="sortType" TypeName="Pariveda.ObjectDataSource.Business.ProductController">
<SelectParameters>
<asp:ControlParameter ControlID="CategoryDropDownList" DefaultValue="null" Name="categoryId"
PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="SupplierDropDownList" DefaultValue="null" Name="supplierId"
PropertyName="SelectedValue" Type="Int32" />
</< span>SelectParameters>
</< span>asp:ObjectDataSource>
Hopefully, this was easy enough to follow. Once you get the hang of it and get over some of the initial hurdles, it is easy to maintain and apply to different applications.
Click here to download the source for these examples.
Save This Page
22 comments:
Thanks, this was exactly what I was looking for. Had loads of use of your pattern with Nullables to not have to write a great amount of methods, and of the solution to the problem with the redundant parameters (I was very confused at first by that one)
Cheers
This is fabulous. The only thing that's annoying to me here is that you have to explicitly call out each column you want to sort and handle in a switch statement instead of doing it dynamically.
I found a way to do this by using the free MS library System.Linq.Dynamic that can be downloaded here:
http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx
Here's the updated code in the GetListSort() method I refactored:
bool bIsSortDescending = false;
if (!String.IsNullOrEmpty(sSortType))
{
string[] sValues = sSortType.Split(' ');
if (sValues.Length > 1)
{
if (sValues[1].ToUpper() == "DESC")
{
bIsSortDescending = true;
}
}
}
if (!String.IsNullOrEmpty(sSortType))
{
query = DbContext.Projects.OrderBy(sSortType);
}
else
{
// use a default sort here
if (bIsSortDescending)
{
query = query.OrderByDescending(q => q.NAME);
}
else
{
query = query.OrderBy(q => q.NAME);
}
}
return query;
Agreed, that it can feel a bit "ookie" whenever you see a switch statement for the sort. What is lost in your dynamic solution that I think is pretty cool useful is the need for a single column sort request to have a SECONDARY sort factored in as well (using the ThenBy method).
Of course, to implement that using the dynamic you would need to set the additional columns as your sort expression in your grid and split it on the backend the way we did for the desc/asc but that ties the grid too much to the implementation of the sort I think.
Good point. You could do more string parsing for the sort expression to set that up without too much difficulty. Typically, GridView's on the web haven't needed more than 1 sort (although it would be nice).
One question, have you gone further in implementing an Update() and Insert() method in the DAL object and set up a DetailsView with data-binding?
I've been messing around with that and have encountered some issues with this scenario which are related to the fact that each method create's it's own data context so the updates don't save when you called DbContext.SubmitChanges().
Have you already solved this problem?
I've done a post http://borrell.parivedasolutions.com/2008/02/linq-to-sql-updating-in-aspnet-right.html (and I'm giving a presentation at Microsoft here in Dallas) on doing LINQ Updates the "Right Way"...
Long story short (straight from the Redmond mouth), there isn't a good story on a DAL-based approach for updating in LINQ to SQL or the Entity Framework for that matter.
The way you need to look at the current use for LINQ to SQL from a DAL perspective is that your DAL *IS* your model. You shouldn't create new "update methods" that take an entity and then trigger the update. It might make more sense when you view my blog posting.
Of course, this isn't a workable answer if you actually split your logical tiers using WCF or something-- and they're working on that. For a web app, don't do the traditional DAL as an additional layer of abstraction... use the datacontext (as you would your own business controller) within your web page.
For WCF, you really have to replay the changes to the entity... which is painful and feels pointless, but is no different than what you would do if you called a stored proc in your business layer before.
Hope that makes sense.
Wow, no wonder. I read your post but it looks like I am going to have to "hand code" each property of the DAL object to manually update it in LINQ. Makes the code much harder to maintain but I am really hesitant to use the Data Context directly in my web code.
It just feels wrong to me and makes it so that my LINQ code is strung out across my entire project instead of directly in one class for each object where all team members can edit it and build custom data access methods.
I will keep looking for a solution. Thanks!
Hey, since you seem to be "in the know", what's your thoughts on using a static instance of your data context that you put in something like a factory singleton pattern to share for all DAL calls?
Is this a problem for multi-threaded applications/web apps?
Positives? Negatives?
The benefit of doing this is that you could share the data context and it would make it a breeze to do updates / selects in the same context.
Just say no. I have seen multiple examples of people attempting to do this and it is a BAD idea.
Remember, DataContext objects are not connections to the database. They are observers in what is being done to the database. If two users share the same datacontext, then a call to submit changes by one user would submit changes not only for the user making the call, but any other changes that the datacontext has observed by others. Bad, bad, bad.
Microsoft advocates putting a "using" clause around all uses of DataContext objects for that very reason.
And on it making easier to do updates, the problem is not with the datacontext but rather the entity object that is being updated. The Attach method of the datacontext works very well in this regard. If you did have a way of holding on to the datacontext, you would also need a way of holding on to the entity object as well.
Hope this helps,
Brian
Click here to download the source for these examples. - link is not OK.
No source code.
Great article, but I'm also having trouble accessing the source code. It looks like it was taken down.
I updated the link to point to a GoogleCode repository I posted it to. Thanks for letting me know about the problem.
Brian, this was very informative, I learned a lot (I also have been in the business for about 20 years so that is saying something :-) ). I did find one small glitch in your code.
Open the page and make sure each dropdown has All Categories and All Suppliers.
If you page to the last page, click the dropdown and choose anything you will get the empty data template. This would as you know cause someone to whine about it.
Do you have a suggestion on how to fix it without writing code?
Saw your article. What sets 'maximumRows' as we deleted them when the objectdatasource was created?
maximumRows is passed by default whether you delete it from the list of parameters or not. It's just a quirk within the objectdatasource (not very clear).
so, if i wanted to only show 5 rows then i would have to limit that in the gridview property? as maximumRows always seems to have a value of 10
Hey Brain, I'm a developer on the asp.net team. I just came across your blog looking for ObjectDataSource using Linq as the DAL. Very cool stuff. You can improve on the Sorting Logic, I wrote a blog post on this:
An excellent example of how to put theory in to practice to solve a real world problem. The MS documentation and a lot of books drive me crazy for documenting functionality without putting it in context and showing actual implementations.
Sorry, rant over. Good article.
Wow, this is exactly what I was looking for. I used for another sample that has a customers table and it work perfectly. Of course I removed the categoryid and supplierid.
I was wondering I could I get this work where I want to use a textbox to filter on customer lastname if the lastname is null then return all records.
Brian, nice article. It's what I was looking for.
It worked the first time I downloaded it and installed it. Then I tried to implement it into my application and I received the error:
ObjectDataSource 'sourceOrders' could not find a non-generic method 'GetListCount' that has parameters: sortType, startRowIndex, maximumRows.
Then it actually happened on your application, but that was after I noticed something on yours and I wanted to see what would happen.
When I first looked at your ProductObjectSource object and went in to check the configuration, it showed that on the Select tab, the drop down list was not selected. There were methods available, but none selected.
So I decided to select one per your instructions and clicked Finish and rebuilt the site, and the same error appeared on your application as did with mine.
Do you have any ideas what this error means and why it appears?
Also, what can I do to fix this?
Thanks, your article, when it works, will have saved me much heart ache.
King Wilder
Thanks a bunch for this great post Brian. Really helped me improve my data access classes to be able to support paging and sorting. I ran into one small faux pas while testing out my own GridView, which I've blogged about (and linked to your post) hereThanks again!
Great post. I've just finished up a CodeSmith template to generate this for me.
Great post!
Could you prevent two trips to the database by keeping a local var with the count, something like this? (the ... means I omitted code for brevity)
private int m_iCount = -1;
public List<...> GetList(...)
{
var query = GetListQuery(...);
m_iCount = query.Count();
...
}
...
public int GetListCount(...)
{
using (DataClasses1DataContext db = new DataClasses1DataContext())
{
if (m_iCount > 0)
return m_iCount;
var productQuery = GetListQuery(...);
return productQuery.Count();
}
}
Post a Comment