The Kitchen Sync

Every so often I run into an article like this: http://reddevnews.com/Articles/2009/01/01/Is-LINQ-to-SQL-Dead.aspx?Page=3&p=1

…or I’ll hear a colleague complain or wonder out loud about the future of Linq to Sql.

First. Can we agree that LINQ (not specifically LINQ To SQL, but the Language Integrated Query feature built into .net) will be around a loooooong time (more than a decade)? I, for one, have been using LINQ, and its related features, Extension Methods and Lambda Expressions, all over the place. Not just for querying databases, but any type of collection or object graph or even a page full of web controls (for example, try it next time when you need to get a collection of checked checkboxes). Nothing has had a bigger impact on my code. Loops evaporate and in their place is simple code with clear intent.

Ok, can we agree that SQLServer will be around a loooooong time? Hundreds of Microsoft products may come and go, but SQLServer will remain.

Great. That settles it! Long live LINQ. Long live SQLServer. So long live LINQ To SQL!!!!

If you’re one of those thinking. “our team just invested so much in LINQ To SQL and now we need to stop because we don’t know if it has future.” Please STOP EXPOSING THE FACT THAT YOU HAVE NO IDEA WHAT LINQ TO SQL IS!! (By the way, when you say “Invested”, what do you mean? You guys dragged and dropped a table on to a canvas. Was that really so hard?).

Next point.

What makes a platform or framework or tool compelling?

If you answered features, vendor support, price, buz, marketing, or any of that rot, please reconsider. I think the key factor is Community. As long as there is a passionate base of happy coders, with happy customers, there will be LINQ to SQL.

Now get back to work!!

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.

SharePoint stores ‘user’ data in its “User Information List”. You can check it out here: http://YourSharePointServer/_layouts/people.aspx. It’s a lot like any other SharePoint list. You can add or remove fields to suit the needs of your organization.

When users first visit/authenticate to the site, or someone first adds a user via the people picker control, data from Active Directory is automatically copied into the “User Information List”. In my experience, this data is pretty minimal. Account (login), Name, and E-mail for example. SharePoint apps can really fall short when you need to provide more detailed, accurate, and up-to-date user information.

There’s probably plenty of excellent user data sitting in some other database, usually maintained by Human Resources, but leveraging it from within SharePoint can be a challenge. To help with this I wrote a console app that copies an organization’s “HR” data to SharePoint. Here’s what the main routine looks like:

private static void UpdateSharePointUsers() {

    //get disposable reference to SharePoint site
    using (SPSite site = new SPSite(Settings.Default.SharePointSiteUrl))
    {
        Console.WriteLine("Updating Users for {0}", site.Url);

        Console.WriteLine("Getting HR Data...");

        HrDataContext hrDb = new HrDataContext();
        foreach (Employee hrEmployee in hrDb.Employees)
        {
            Console.Write("Attempting to get or create SPUser for {0}:", hrEmployee.Login);

            SPUser user = site.GetOrCreateSPUser( hrEmployee );

            if (null == user)
            {
                Console.WriteLine(" Failed");
            }
            else
            {
                Console.WriteLine(" Got SPUser #{0}", user.ID);

                try
                {
                    Console.Write("Attempting to update User Information for SPUser #{0}:", user.ID);
                    user.UpdateUserInformation( hrEmployee );
                    Console.WriteLine(" Success");

                }
                catch (Exception ex) {
                    Console.WriteLine(" Failed.\r\nError: {0}",
                        user.ID, ex.Message);
                }
            }
        }
    }
}

So the SPSite class has a GetOrCreateSPUser(SPSite), and the SPUser class has a handy UpdateUserInformation(Employee) method. What? Your SharePoint object model doesn’t have these methods? I’m afraid this article wasn’t too much help then.

Happy Coding! Cheers!!

Just kidding. My SharePoint object model doesn’t have those methods either. The magic of Extension Methods has allowed my console application to ‘velcro’ these methods onto SPSite and SPUser.

Here they are:

public static SPUser GetSPUser(this SPSite site, Employee hrEmployee) {
    try
    {
        return site.RootWeb.SiteUsers[hrEmployee.Login];
    }
    catch (Exception)
    {
        return null;
    }
}

public static SPUser CreateSPUser(this SPSite site, Employee hrEmployee)
{
    SPUser user = null;
    try
    {
        site.RootWeb.SiteUsers.Add(
            hrEmployee.Login,
            hrEmployee.Email,
            hrEmployee.FullnameLast,
            "User added via automated process.");

        user = site.RootWeb.SiteUsers[hrEmployee.Login];

    }
    catch (Exception) { 

    }
    return user;
}

public static SPUser GetOrCreateSPUser(this SPSite site, Employee hrEmployee)
{
    SPUser user = site.GetSPUser(hrEmployee);
    if (null == user)
    {
        user = site.CreateSPUser(hrEmployee);
    }
    return user;
}

public static SPListItem GetUserInformation(this SPUser user) {
    return user.ParentWeb.SiteUserInfoList.GetItemById(user.ID);
}

public static void UpdateUserInformation( this SPUser user, Employee hrEmployee)
{
    var userInfo = user.GetUserInformation();

    userInfo["Title"] = hrEmployee.FullNameLast;
    userInfo["E-Mail"] = hrEmployee.Email;
    userInfo["Employee ID"] = hrEmployee.EmployeeNumber;
    userInfo["Department Number"] = hrEmployee.DepartmentNumber;
    userInfo["Supervisor Name"] = hrEmployee.Supervisor.FullnameLast;
    userInfo["Phone Extension"] = hrEmployee.Extension;
    userInfo["Cell"] = hrEmployee.CellPhone;

    userInfo.Update();    //write changes to SharePoint
}

NOTE: I used “Title” in place of the “Name” field in SharePoint’s User Information List, because even though SharePoint displays this field as “Name”, the “internal field name” is actually “Title”. Stuff like this tends to bite you a lot in SharePoint. There are ways to get around ‘internal’ vs. ‘display’ field names by taking extra steps. You can also refer to fields via GUID or index. Field name is easiest to read though, I think.

So, another thing that would be great… Doesn’t a SharePoint list support User fields? So rather than have a “Supervisor Name” Text field, why not instead have a “Supervisor” Person field? Here’s an example of the UpdateUserInformation() method with that bit added.

public static void UpdateUserInformation( this SPUser user, Employee hrEmployee)
{
    var userInfo = user.GetUserInformation();

    userInfo["Title"] = hrEmployee.FullNameLast;
    userInfo["E-Mail"] = hrEmployee.Email;
    userInfo["Employee ID"] = hrEmployee.EmployeeNumber;
    userInfo["Department Number"] = hrEmployee.DepartmentNumber;
    userInfo["Phone Extension"] = hrEmployee.Extension;
    userInfo["Cell"] = hrEmployee.CellPhone;

    //Instead of just a name,
    // add a 'user' as this person's supervisor
    //userInfo["Supervisor Name"] = hrEmployee.Supervisor.FullnameLast;
    var supervisorSPUser = user.ParentWeb.Site.GetSPUser(hrEmployee.Supervisor);
    userInfo["Supervisor"] = supervisorSPUser.ID;

    userInfo.Update();    //write changes to SharePoint
}
...

The UpdateUserInformation() method in the example above has a lot of hard-coded field mappings in it. Wouldn’t it be nice to add mappings or make other changes without having to re-compile and re-deploy the app?

Here’s an example that uses a collection of mapping objects that define how Employee properties map to SharePoint User Information fields. SharePoint field type is also indicated so that “User” fields are handled differently than “Text” fields.

public static void UpdateUserInformation(
    this SPUser user,
    Employee hrEmployee,
    List<HrSharePointMapping> mappings)
{
    var userInfo = user.GetUserInformation();

    foreach (var mapping in mappings)
    {
        //handle user fields differently from text fields
        if (mapping.SPFieldType == SPFieldType.User)
        {
            try
            {
                //since the target is a SPFieldUser type,
                //we'll assume that the HrField is an Employee object
                var anotherHrEmployee = hrEmployee.GetPropertyValue(mapping.HrField) as Employee;
                var anotherUser = user.ParentWeb.Site.GetSPUser(anotherHrEmployee);
                //assign user id to this field
                userInfo[mapping.SPField] = anotherUser.ID;
            }
            catch (Exception)
            {
                //couldn't assign user field. Not a show stopper though
            }
        }
        else   //probably just a text field. TODO: add other type conditions if needed
        {
            userInfo[mapping.SPField] = hrEmployee.GetPropertyValue(mapping.HrField);
        }
    }
    userInfo.Update();    //write changes to SharePoint
}

Here’s the HrSharePointMapping class:

public class HrSharePointMapping
{
    public string HrField { get; set; }
    public string SPField { get; set; }
    public SPFieldType SPFieldType { get; set; }
}

And this code builds the List from an XML file:

public List<HrSharePointMapping> GetHrSharePointMappings() {
    XDocument mapXml = XDocument.Load(Settings.Default.HrSharePointMappingXmlFile);
    var mappings = from m in mapXml.Descendants("Mapping")
                   select new HrSharePointMapping
                   {
                       HrField = m.Element("HrField").Value,
                       SPField = m.Element("SPField").Value,
                       SPFieldType = (SPFieldType)Enum.Parse(
                           typeof(SPFieldType),
                           m.Element("SPFieldType").Value,
                           true )
                   };
    return mappings.ToList();
}

Example mappings XML file, which allows mappings to be configured without recompiling and re-deploying the app:

<?xml version="1.0" encoding="utf-8" ?>
<Mappings>
	<Mapping>
		<HrField>Email</HrField>
		<SPField>E-Mail</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>EmployeeNumber</HrField>
		<SPField>Employee Number</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>FullnameLast</HrField>
		<SPField>Title</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>Department</HrField>
		<SPField>Department Number</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>Extension</HrField>
		<SPField>Phone Extension</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>CellPhone</HrField>
		<SPField>Cell</SPField>
		<SPFieldType>Text</SPFieldType>
	</Mapping>
	<Mapping>
		<HrField>Supervisor</HrField>
		<SPField>Supervisor</SPField>
		<SPFieldType>User</SPFieldType>
	</Mapping>
</Mappings>

Perhaps ADO would have lent itself to this configurable mapping approach better than our Employee object. The DataRow object, for example, allows you to access its field values by column name string. But since we’re using an Employee object, I had to employ a bit of reflection to enable configuration. Here’s the GetPropertyValue() Extension Method that makes the Employee object a bit more ‘dynamic’. This could come in handy for any object, by the way.

public static object GetPropertyValue(this object obj, string propertyName)
{
    Type classType = obj.GetType();
    PropertyInfo info = classType.GetProperty(propertyName);
    return info.GetValue(obj, null);
}

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.