The Kitchen Sync

So much of the data we work with fits nicely in a typical grid. Of course, most data itself is stored in tables and it’s natural to want to display it as such. Many of the underlying data structures in our toolbox up til now (ADO.Net DataReader, DataTable, the old classic Recordset…) have also consisted of simple columns and rows. But now that I’m using LINQ, I’m finding that the shape of the data I’m working with breaks with the usual cell-ular symmetry. For example, let’s say I’ve got a table that stores Orders and another table that stores products, and a third that relates Orders to Product. In the grid world you’d run a join like…

SELECT o.Name OrderName, p.Name ProductName
FROM Order o
JOIN OrderProduct op on o.OrderId = op.OrderId
JOIN Product p on op.ProductId = p.ProductId

…and get back a result that looks something like this:

You may decide to get fancy with your presentation code and suppress the repetition of the Order items. You may decide to leave the repeated cells blank like this:

Or you could merge the multiple product items rows into a single cell, perhaps make them a bulleted list (or even another sub-table, perhaps) like this:

But in either case if you use the standard asp.net tools such as an ADO.Net DataTable and the GridView control, you’ll probably be coding against the grain.

I believe the more fitting approach is to use LINQ with the ListView control.

To begin with, getting the data via LINQ to SQL will likely give you data that’s not ‘grid’ shaped any more, but something more object oriented. Here’s an example LINQ query:

using (var db = new ProductOrdersDataContext())
{
    var orders = db.Orders
}

So where are the products in each Order? Well, assuming you setup your LINQ to SQL model based on your DB schema with relationships established, you’ll automatically get entity classes with built in relationships to other entity classes. So to get the products in a each Order you’d code something like:

using (var db = new ProductOrdersDataContext())
{
    var orders = db.Orders
    foreach(var order in orders){
        var products = from po in order.OrderProducts
                              select po.product;
        //... do something with products ....
    }
}

So what about binding to a data control? GridView and ListView and BulletedList and any of the others can all be bound to the typical collection types that LINQ statements might return (IEnumerable, List, Array, etc.), so we’re in business there. But what about the problem of the odd, object-oriented shape of the data? LINQ projections can help with that.

So here’s an example of a LINQ statement that produces results shaped something like this:

using (var db = new ProductOrdersDataContext())
{
    var results = from o in db.Orders
                       orderby o.Name
                       select new
                                    {
                                        OrderName = o.Name,
                                        Products = from op in o.OrderProducts
                                        select new
                                        {
                                            ProductName = op.Product.Name
                                        }
                                    };
}

You can bind this result to a ListView. First let’s look at the ListView declaration along with a Label and BulletedList control that will help out with presentation:

<asp:ListView ID="ListView1" runat="server">
    <LayoutTemplate>
        <table id="CardAccessLevelList" class="iTunes">
            <thead>
                <th>Order Name</th>
                <th>Products</th>
            </thead>
            <tbody runat="server" id="itemPlaceHolder" />
        </table>
    </LayoutTemplate>
    <ItemTemplate>
        <tr class='<%# (Container.DataItemIndex % 2 == 0) ? "even" : "odd" %>'>
            <td><asp:Label ID="Label1" runat="Server" Text='<%# Eval( "OrderName" ) %>' /></td>
            <td>
                    <asp:BulletedList ID="BulletedList1" runat="server"
                         DataSource='<%# Eval("Products") %>'
                         DataTextField="ProductName" />
            </td>
        </tr>
    </ItemTemplate>
</asp:ListView>

Here’s how you could bind the ListView in the code behind:

using (var db = new ProductOrdersDataContext())
{
    ListView1.DataSource = from o in db.Orders
                       orderby o.Name
                       select new
                                    {
                                        OrderName = o.Name,
                                        Products = from op in o.OrderProducts
                                        select new
                                        {
                                            ProductName = op.Product.Name
                                        }
                                    };
    ListView1.DataBind();

}

On a side note, I want to give credit to Andrew L. Van Slaars for the bit of code in the <tr> tag. The purpose of that DataItemIndex inspection in setting the css class of the row is to allow the style of the row to alternate back and forth, saving us the trouble of coding the ListView’s “AlternatingItemTemplate” which would be a much larger chunk of code in a real world case.

A value in a typical User based field in a SharePoint list is kind of useless on its own. Usually looks like “123;#MyDomain\SomeLogin”. I’d rather be able to get a person’s full name or email address.

These extension methods help with getting an SPUser object and User Information List item directly from a List Item that references a user.

public static SPUser GetUser(this SPListItem item, string displayName)
{
    //get field and cast to User Field type
    var field = item.Fields.GetField(displayName) as SPFieldUser;

    if (null == field)
    {
        throw new ApplicationException("This field is not a User!");
    }

    var fieldValue = field.GetFieldValue(item[field.Id].ToString()) as SPFieldUserValue;

    if (null == fieldValue) {
        return null;
    }

    return fieldValue.User;
}

public static SPListItem GetUserInfo(this SPUser user) {
    //guard
    if (null == user) {
        return null;
    }
    return SPContext.Current.Site.RootWeb.SiteUserInfoList.GetItemById(user.ID);
}

Here’s what a call looks like.

...
var managers
    = from m in SPContext.Current.Web.Lists["Some List that has a Manager field"].Items.Cast<SPListItem>();
        select new
        {
                Text = m.GetUser("Manager").GetUserInfo()["FullName"],
                Value = m.GetUser("Manager").Id
        };
ManagerDropDownList.DataSource = managers;
ManagerDropDownList.DataTextField = "Text";
ManagerDropDownList.DataValueField = "Value";
ManagerDropDownList.DataBind();
...

This example binds a DropDownList to some User related data.

NOTE: SharePoint’s “User Information List” is built-in, but a field such as “FullName” in this example is custom to my environment. Check http://YourSharePointServer/_layouts/people.aspx to see what fields are available in your site. In an upcoming post I’ll demonstrate how the User Information List can be customized and kept in sync with another source of data (such as an organization’s HR database).

LINQ is great. I like to use it with SharePoint lists. This extension method tightens up the code a bit.

public static IEnumerable<SPListItem> AsEnumerable(this SPList list) {
    return list.Items.Cast<SPListItem>();
}

Example Call: Bind SharePoint List to an asp.net WebControl such as a radio button list.

...
var choices  = from c in SPContext.Current.Web.Lists["Some SharePoint List With Choices"].AsEnumerable()
        select new
        {
            Text = c["Title"],
            Value = c.ID
        };
ChoiceRadioButtonList.DataSource = choices;
ChoiceRadioButtonList.DataTextField = "Text";
ChoiceRadioButtonList.DataValueField = "Value";
ChoiceRadioButtonList.DataBind();
...

Ok, but what if the SharePoint list field wasn’t plain text such as “Title”, but a Person such as “Assigned To” or “Manager” instead?
See next entry on Extension Methods that get user info from a SharePoint List User Field.

This extension method is handy for pages that have multiple panels you want to switch between. Call any panel’s Switch() method to make it visible and at the same time, make all ‘peer’ panels in-visible. This example also demonstrates use of an anonymous delegate within the List object’s ForEach method, which in this case makes all the panels in the list disappear.

public static void Switch(this Panel panel)
{
    panel.Parent.Controls.OfType<Panel>().ToList().ForEach(delegate(Panel p) { p.Visible = false; });
    panel.Visible = true;
}

Example Calls:

protected void Page_Load(object sender, EventArgs e)
{
    FirstPanel.Switch();
}

protected void ButtonOnFirstPanel_Click(object sender, EventArgs e)
{
    if (Page.IsValid) {
        SecondPanel.Switch();
    }
}

protected void ButtonOnSecondPanel_Click(object sender, EventArgs e)
{
    if (Page.IsValid) {
        ThirdPanel.Switch();
    }
}