This post is part of a series (download code here)

1. SharePoint list definition using the Template pattern
2. SharePoint list access using the Repository pattern
3. Building and deploying SharePoint solutions with MSBuild (to come)

Maybe it’s that the SharePoint API is hard to use. Maybe it’s that coding against SharePoint is about making smaller additions here and there. Maybe it’s that the Patterns & Practices SharePoint Guidance isn’t widely known. Whatever the reason, developing for SharePoint requires equal attention to the separation of presentation, business, and data access code. Hence, starting with data access, we may want to create a repository and route queries through it (the SharePoint Guidance outlines a more sophisticated implementation than the one below):

    [TestClass]
    public class EmployeesRepositoryTest {
        private SPSite _siteCollection;
        private SPWeb _site;
        private EmployeesRepository _repository;

        private readonly Employee _duffyDuck = new Employee {
            Id = 1000, Name = "Duffy Duck", HireDate = new DateTime(2009, 12, 1),
            Remarks = "Looks like a duck, quacks like a duck, probably is a duck"
        };
        private readonly Employee _porkyPig = new Employee {
            Id = 1001, Name = "Porky Pig", HireDate = new DateTime(2010, 2, 1)
        };
        private readonly Employee _sylvesterTheCat = new Employee {
            Id = 1002, Name = "Sylvester the Cat", HireDate = new DateTime(2010, 3, 1)
        };
        private readonly Employee _bugsBunny = new Employee {
            Id = 1100, Name = "Bugs Bunny", HireDate = new DateTime(2010, 1, 1)
        };

        public void AddEmployees() {
            _repository.AddEmployee(_site, _duffyDuck);
            _repository.AddEmployee(_site, _porkyPig);
            _repository.AddEmployee(_site, _sylvesterTheCat);
        }

        public void ClearEmployees() {
            var definition = new EmployeesDefinition();
            var employees = _site.Lists[definition.ListName];
            while (employees.Items.Count > 0)
                employees.Items.Delete(0);
        }

        [TestInitialize]
        public void Initialize() {
            _siteCollection = new SPSite("http://localhost");
            _site = _siteCollection.OpenWeb("/");
            _repository = new EmployeesRepository();
            ClearEmployees();
            AddEmployees();
        }

        [TestCleanup]
        public void Cleanup() {
            _site.Dispose();
            _siteCollection.Dispose();
        }

        [TestMethod]
        public void AddEmployee_should_add_valid_employee() {
            _repository.AddEmployee(_site, _bugsBunny);
            var e = _repository.GetEmployeeById(_site, _bugsBunny.Id);
            Assert.AreEqual(_bugsBunny.Id, e.Id);
            Assert.AreEqual(_bugsBunny.Name, e.Name);
            Assert.AreEqual(_bugsBunny.HireDate, e.HireDate);
            Assert.AreEqual(_bugsBunny.Remarks, e.Remarks);
        }

        [TestMethod]
        public void GetEmployeesHiredBetween_should_return_2010_hires() {
            var from = new DateTime(2010, 1, 1);
            var to = new DateTime(2010, 12, 31);
            var employees = _repository.GetEmployeesHiredBetween(_site, from, to);
            Assert.AreEqual(2, employees.Count);
            Assert.AreEqual(_porkyPig.Id, employees[0].Id);
            Assert.AreEqual(_sylvesterTheCat.Id, employees[1].Id);
        }
    }

In the words of Martin Fowler, here’s the essence of the Repository pattern:

A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer.

In SharePoint terms, business logic should query a repository which in turn queries a SharePoint list. Within the repository, the weakly typed items returned are then mapped to strongly typed data transfer objects, which are returned to the business layer.

With this approach to data access comes a number of advantages: (1) duplicate data access code is eliminated. Only within the repository do we setup the query and transform the weakly typed SPListItemCollection into strongly typed data transfer objects. (2) The list definition classes introduced in SharePoint list definition using the Template pattern may be used to construct CAML queries from strongly typed field names. Lastly, (3) accessing data through a repository makes it easier to mock the data access part of the application and to integration test that part.

The code below is a simple, yet usable, implementation of the Repository pattern. The idea is to have all repositories inherit from a common base class. Its purpose is to wrap the querying of a list and to log what’s going on. Because it’s a base class, it shouldn’t know how to transform the weakly typed result into strongly data transfer objects or which CRUD operations a particular repository supports:

    public abstract class ListRepository {
        protected ListDefinition Definition { get; set; }
        protected SPListItemCollection Result { get; set; }

        protected void Query(SPWeb site, string caml) {
            AssertValidSite(site);
            AssertValidCaml(caml);
            AssertListExistence(site);
            AssertListDefinitionSetBySubclass();

            var watch = new Stopwatch();
            var list = site.Lists[Definition.ListName];
            var query = new SPQuery {Query = caml};
            Debug.WriteLine(
                string.Format("About to run query against list '{0}': {1}", list, caml));
            watch.Start();
            Result = list.GetItems(query);
            watch.Stop();
            Debug.WriteLine(
                string.Format("Query against '{0}' returned {1} rows in {2} ms",
                              list, Result.Count, watch.ElapsedMilliseconds));
        }

        protected void AssertListExistence(SPWeb site) {
            if (!ListDefinition.ListExists(site, Definition.ListName))
                throw new ArgumentException(
                    string.Format("No '{0}' list on site '{1}'", Definition.ListName, site.Url));
        }

        protected void AssertListDefinitionSetBySubclass() {
            if (Definition == null)
                throw new NullReferenceException(
                    string.Format(
                        "Sublcass must set Definition property prior querying '{0}' list",
                        Definition.ListName));
        }

        protected void AssertValidCaml(string query) {
            if (string.IsNullOrEmpty(query))
                throw new NullReferenceException("Query must not be null or empty");
        }

        protected void AssertValidSite(SPWeb site) {
            if (site == null)
                throw new NullReferenceException("Site must not be null");
        }
    }

Each ListRepository connects to a corresponding ListDefinition, holding the name of the list to query and its strongly typed field names. It’s the responsibility of a concrete repository to set the Definition property prior to doing any querying. After running a query, the Result property holds the weakly typed result, which a concrete repository can then transform into data transfer objects to be passed to the business layer.

As an example, add to the concrete EmployeeRepository any CRUD method you see necessary to fulfill the business requirements:

    public class EmployeesRepository : ListRepository {
        public EmployeesRepository() {
            Definition = new EmployeesDefinition();
        }

        public void AddEmployee(SPWeb site, Employee e) {
            var list = site.Lists[Definition.ListName];
            var item = list.Items.Add();
            item[EmployeesDefinition.EmployeeId] = e.Id;
            item[EmployeesDefinition.Name] = e.Name;
            item[EmployeesDefinition.HireDate] = e.HireDate;
            item[EmployeesDefinition.Remarks] = e.Remarks;
            item.Update();
        }

        public Employee GetEmployeeById(SPWeb site, int id) {
            var caml =
                string.Format(@"
                      <Where>
                        <Eq>
                          <FieldRef Name=""{0}"" />
                          <Value Type=""Integer"">{1}</Value>
                        </Eq>
                      </Where>",
                    EmployeesDefinition.EmployeeId, id);
            Query(site, caml);

            IList<Employee> employees = Map(Result);
            if (employees.Count == 0)
                throw new ArgumentException(string.Format("No employee with id = {0} exists", id));
            return employees[0];
        }

        public ReadOnlyCollection<Employee> GetEmployeesHiredBetween(SPWeb site, DateTime from, DateTime to) {
            var caml =
                string.Format(@"
                      <Where>
                        <And>
                            <Geq>
                              <FieldRef Name=""{0}"" />
                              <Value IncludeTimeValue=""TRUE"" Type=""DateTime"">{1}</Value>
                            </Geq>
                            <Leq>
                              <FieldRef Name=""{0}"" />
                              <Value IncludeTimeValue=""TRUE"" Type=""DateTime"">{2}</Value>
                            </Leq>
                        </And>
                      </Where>",
                    EmployeesDefinition.HireDate,
                    SPUtility.CreateISO8601DateTimeFromSystemDateTime(from),
                    SPUtility.CreateISO8601DateTimeFromSystemDateTime(to));
            Query(site, caml);
            return new ReadOnlyCollection<Employee>(Map(Result));
        }

        protected IList<Employee> Map(SPListItemCollection items) {
            var employees = new List<Employee>();
            foreach (SPItem item in items) {
                var e = new Employee {
                                Id = (int)item[EmployeesDefinition.EmployeeId],
                                Name = (string)item[EmployeesDefinition.Name],
                                HireDate = (DateTime)item[EmployeesDefinition.HireDate],
                                Remarks = (string)item[EmployeesDefinition.Remarks]
                            };
                employees.Add(e);
            }
            return employees;
        }
    }

Calling the GetEmployeeById or GetEmployeesHiredBetween methods, the caller is required to pass in an SPWeb instance pointing to the the site holding the list to query. Outside the SharePoint context, you have to manually create this instance, like with the integration tests above. But within the SharePoint context, callers are likely to just pass in SPContext.Current.Web.

The above repository implementation deliberately ignores any issue of caching. If you find the need for it, however, you can replace SPList.GetItem with PortalSiteMapProvider.GetCachedListItemsByQuery. The advantage of using the PortalSiteMapProvider over Asp.Net caching of the result is that the provider takes care of invalidating cache entries when items are modified. The disadvantage is that the provider is part of the SharePoint publishing API, which isn’t part of WSS 3.0. In addition, it’s only available from code running within SharePoint, and likely requires cache settings to be tweaked.

Another, non-trivial, improvement would include the use of the Unit of Work pattern. A commonly used pattern in ORMs because it offers a way to “keep track of everything you do during a business transaction that can affect the database. When you’re done, it figures out everything that needs to be done to alter the database as a result of your work". Like with the DataContext class in LINQ to SQL or the DataSet class in ADO.NET, it could add transaction support to SharePoint lists.

This post is part of a series (download code here)

1. SharePoint list definition using the Template pattern
2. SharePoint list access using the Repository pattern
3. Building and deploying SharePoint solutions with MSBuild (to come)

Books on SharePoint often show how to create lists from code by calling the SharePoint API from directly within a feature receiver. This receiver would then contain in-place string literals for column names, create the columns, update the default view, and so on. While such an approach provides for an nice demonstration of the SharePoint API, it tends to carry over into production code.

My take on feature receivers, however, is that they should contain the least possible amount of code. Staying true to object-orientation and separation of concerns, list definition and creation should instead consist of a common set of classes and methods to be called from the receiver:

    public override void FeatureActivated(SPFeatureReceiverProperties p) {
        using (var site = p.Feature.Parent as SPWeb) {
            var employees = new EmployeesDefinition();
            if (!ListDefinition.ListExists(site, employees.ListName))
                employees.CreateOnSite(site);
        }
    }

The first area to improve on is that of string literal field names getting duplicated across the code base. String literals make it impossible for the compiler to enforce a consistent naming of columns. As column names are used in defining the list, in forming CAML queries against it, and in accessing the result, misspelled column names is a common source of runtime errors. The second area to improve on is that of duplication of code for doing common list operations, like checking for the existence of the list or removing its title column. Lastly, we want to get rid of duplicate control logic, i.e., verifying preconditions and calling the same series of methods with every list definition.

One approach to improving on these areas is to start by defining a base class for list definitions. The base class combines helper methods, working on lists, with the Template pattern for defining the common steps of list creation:

    public abstract class ListDefinition {
        public string ListName;
        public string ListDescription;
        protected SPList List;

        public void CreateOnSite(SPWeb site) {
            if (string.IsNullOrEmpty(ListName))
                throw new ArgumentException("Expected not null or not empty list name member");
            if (site == null) throw new NullReferenceException("Expected valid site");
            CreateList(site);
            if (List == null) throw new NullReferenceException("Expected list member not null");
            CreateFields(List);
            UpdateDefaultView(List);
            SetAdditionalProperties(List);
        }

        protected abstract void CreateList(SPWeb site);
        protected virtual void CreateFields(SPList l) {}
        protected virtual void UpdateDefaultView(SPList l) {}
        protected virtual void SetAdditionalProperties(SPList l) {}

        public static void HideAndMakeTitleFieldNotRequiredOnItemNewAndEditPage(SPList l) {
            var title = l.Fields.GetField("Title");
            title.Required = false;
            title.Hidden = true;
            title.Update();
        }

        public static bool ListExists(SPWeb site, string listName) {
            if (string.IsNullOrEmpty(listName))
                throw new ArgumentException("ListNameMustNotBeNullOrEmpty", listName);
            SPList list = null;
            try {
                list = site.Lists[listName];
            }
            catch (ArgumentException) {
                // list not found
            }
            return list != null ? true : false;
        }
    }

At the core of the Template pattern is the template method. It defines the steps of an algorithm through a series of method calls. Each either abstract or virtual depending on if the step is mandatory or voluntary in the algorithm. Subclasses then specify the behavior of individual steps by implementing or overriding methods. In case of the ListDefinition class, CreateOnSite is our template method. It ensures that context, in the form of an SPList instance, is passed along to each step in the algorithm.

Each subclass defines the specific characteristics of a list. It defines constants for column names and their data types, the views and which column to index, and so on. This makes for a concise, easy to read, and consistent list definition:

    public class EmployeesDefinition : ListDefinition {
        public const string EmployeeId = "EmployeeId";
        public const string Name = "Name";
        public const string HireDate = "HireDate";
        public const string Remarks = "Remarks";

        public EmployeesDefinition() {
            ListName = "AcmeEmployees";
            ListDescription = "Employees at Acme Corp.";
        }

        protected override void CreateList(SPWeb site) {
            var guid = site.Lists.Add(ListName, ListDescription, SPListTemplateType.GenericList);
            List = site.Lists[guid];
        }

        protected override void CreateFields(SPList l) {
            l.Fields.Add(EmployeeId, SPFieldType.Integer, true);
            l.Fields.Add(Name, SPFieldType.Text, true);
            l.Fields.Add(HireDate, SPFieldType.DateTime, true);
            var remarks = (SPFieldMultiLineText) l.Fields[l.Fields.Add(Remarks, SPFieldType.Note, false)];
            remarks.RichText = true;
            remarks.RichTextMode = SPRichTextMode.FullHtml;
            remarks.Update();
            l.Update();
        }

        protected override void UpdateDefaultView(SPList l) {
            var v = l.DefaultView;
            v.ViewFields.Delete("LinkTitle");
            v.ViewFields.Add(EmployeeId);
            v.ViewFields.Add(Name);
            v.ViewFields.Add(HireDate);
            v.ViewFields.Add(Remarks);
            v.Update();
        }

        protected override void SetAdditionalProperties(SPList l) {
            HideAndMakeTitleFieldNotRequiredOnItemNewAndEditPage(l);
            l.Fields[HireDate].Indexed = true;
            l.Update();
        }
    }

Since the string literals have now become public constants, we get IntelliSense and compile-time checking referencing them. In addition, by defining common operations on the base class and using the Template pattern, we reduce the amount of code that would otherwise have to go into each definition. Finally, the base class asserts, on behalf of all subclasses, that the list name is set before attempting to create the list and that the SPList field is set after calling the CreateList method.

In my previous post I looked at 2009 in hindsight. Consequently, in this one I look ahead at 2010 and what I want to accomplish professionally this coming year. I don’t plan on anything too esoteric. Instead, I want to consolidate my skills around building web applications with C#.

My goals for the coming year are:

• Focus more on architecture and design. Not in the architecture astronaut connotation. But as in becoming more aware of how to build better software. I want to apply the SOLID principles more consciously and re-read classics such as Design Patterns: Elements of Reusable Object-Oriented Software and Patterns of Enterprise Application Architecture.
• Continue exploring LINQ. It’s a fascinating technology that’s going to get only more popular in years to come. Truly understanding LINQ is a way to getting into the habit of applying the more functional and succinct style to my own code. To get started on this endeavor I’m going to read and apply LINQ in Action.
• Learn Entity Framework. Using an object-relational mapper should be second-nature to most developers. In the past the light-weight LINQ2SQL has served me well. Still, I want to step up and learn how Entity Framework deals with the impendence mismatch. I want to write an application that uses Entity Framework.
• Become better at browser technologies. JavaScript and jQuery are high on the list, but I want to improve on HTML and CSS as well. My goal isn’t to become an HTML or CSS Jedi. I simply want to have enough knowledge to avoid making rookie mistakes. The technologies all come together in jQuery, making Learning jQuery a good starting point. Besides, I want to use a tool like Firebug to learn from others.
• Be on top of SharePoint 2010. SharePoint 2007 and 2010 is what I want to work with daily. That doesn’t imply me not keeping up to speed with the rest of .Net. Just that SharePoint will act as my gateway to other technologies. The great thing about SharePoint is that work is frequently divided between working inside and outside SharePoint, integrating with external systems.
• Become more involved in higher-level development decisions. By the end of the day, developers don’t deliver software, they deliver value to stakeholders. Writing code is but a means to that end. I want to be involved from idea to completion.

To me these points strike a good balance between technical and non-technical goals and between short and long term goals. However, by the end of the day, building great software is about being attentive to the entire stack and its surroundings.