We Are Microsoft - Great Event

It's been a week now since our two teams from Pariveda volunteered at the We Are Microsoft event.   Toi Wright and Chris Koenig did a great job organizing the event.

My team which had Jeff Townes, Justin Finch and Kyle Stock from Pariveda and Amy Stewart from Stewart Design was assigned to Texas CARES, a cat rescue group here in North Texas.  Beyond some initial phone discussions where we worked through logistics and timing for the weekend, we started out with a clean slate on Friday evening. 

Over the course of two and half days, we developed a revamped website with a searchable and maintainable database for managing rescued cats and helping pair them up with adoptive owners. 

To see the improvement, here's a link to their current site. Here's a link to the new site (in beta as we work through transferring domain registration).  Here's a link to the interview that Geeks with Blogs did with Carol Benassi, the president of Texas CARES and me, and here's another link to the interview they did with the Texas Response Unit Search and Rescue (the second Pariveda team).

One of the things that makes Pariveda unique is that we have an expectation that all of our folks participate in community service.  Depending upon the person's level (it increases the higher up you go), there are a certain number of hours that are expected you commit to community service.  We Are Microsoft was a great way to be able to help our community by doing the things that we do best- develop great software.

We each spent around 40 hours between Friday night until late Sunday afternoon.  It took us a while to recover, but I was extremely proud of both teams.  By the way, our two teams from Pariveda came in First and Third in the competition-- Woo Hoo!

ObjectDataSource / LINQ / Paging / Sorting Example

[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.