Chapter 6
Advanced List Management
IN THIS CHAPTER
- Accessing BDC Data in Lists
- Querying List Items with CAML
- Creating Parent/Child Relationships in a Single List
The list item is the core piece of data upon which all of SharePoint rests. You would be hard pressed to find any functionality in all of Microsoft Office SharePoint Server (MOSS) or Windows SharePoint Services (WSS) that doesn’t rely directly or indirectly on list items in some way. Chapter 5, “Managing SharePoint Lists,” provided an introduction to the list and list item and how to manipulate them programmatically.
This chapter provides information on some more advanced list management topics, such as accessing Business Data Catalog data within a list, querying and filtering list items using the Collaborative Application Markup Language (CAML), and, finally, creating a single list that models a parent/child relationship using content types.
Accessing BDC Data in Lists
The Business Data Catalog (BDC), as you will discover throughout this book, is a feature of SharePoint that allows you to access data in remote Line of Business (LOB) applications. By supplying metadata information about the external application, you can tell SharePoint how to properly index data from that application.
Another incredibly powerful thing you can do with BDC applications is to create columns in lists that are actually lookup columns into tables in the external application. This means that you can, for example, create a column in a SharePoint list called Defect that is actually a lookup into a defect tracking system that is integrated with SharePoint through the Business Data Catalog.
When you create a list with these columns, you might think that you need to treat these columns differently. The code in Listing 6.1 shows how accessing data contained within an external system via the BDC is completely transparent to the client code. The sample in Listing 6.1 accesses the Customer table within the AdventureWorks sample database by reading columns in a custom list that are actually lookup values contained in the remote database.
Listing 6.1. Accessing BDC Data Within Lists
When the preceding code is executed, assuming there is a custom list named “List with BDC Lookup” that contains lookup columns into the AdventureWorks sample database, the output will look similar to the following. (For information on how to create custom lists and manipulate the Business Data Catalog, you might want to look at Microsoft SharePoint 2007 Unleashed [ISBN: 0672329476], the SharePoint 2007 administration book from Sams Publishing.)
Not only does the preceding output illustrate that accessing BDC-stored data is completely transparent to the programmer, but it also shows all of the various properties that are common to every list item within SharePoint.
Querying List Items with CAML
The Collaborative Application Markup Language (CAML), pronounced “Camel,” is used within SharePoint for everything from site definitions to defining and invoking complex search queries, list views, and item filters. For more information on the CAML syntax, consult the MSDN documentation online or download the WSS v3.0 Software Development Kit (SDK) from Microsoft Downloads.
Listing 6.2 shows how to use a CAML query, which is basically an Extensible Markup Language (XML) fragment that defines the criteria that each element in the resultset must match.
Listing 6.2. Using a CAML Query to Filter Items
When the preceding code is executed against some sample data from the AdventureWorks database, it displays the following output:
Found 1 items in query.
Found customer: Aaron Chen
CAML queries can get extremely complex very quickly depending on the type of query you are performing. A variety of third-party tools are available on the Internet that you can download to assist you in defining CAML queries. One such tool is the U2U CAML Query Builder, which is shown in Figure 6.1.
Figure 6.1. U2U tool for building CAML queries.
You can download this tool from http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx.
Creating Parent/Child Relationships in a Single List
Content types are an extremely powerful new feature of SharePoint 2007. They allow administrators to define properties and functionality that are common to documents and list items that belong to the same content type. This provides for functionality such as defining a common set of properties and actions for things like whitepapers, expense reports, PDFs, and much more.
One interesting thing you can do with content types is to create a content type that inherits from the master content type of “Folder”. This allows you to create a folder within a list (SharePoint 2007 allows folders in all lists now, not just document libraries!) that can have its own set of properties.
Using the combination of folders, items contained within folders, and content types, you can create a parent/child hierarchy of items contained within a single list that doesn’t even require the use of lookup columns.
If you have ever tried to accomplish parent/child hierarchies with SharePoint 2003 lists, you know that it was difficult and tedious and required creating lookup columns that linked the child items to the parent items, but the hierarchy was still flimsy and not easily discernable from the context of looking at the lists.
Now, you can create a hierarchy in which the parent is a folder and the children are simply items contained within that folder. This is an extremely natural “Container” style approach to related data that a lot of developers find extremely appealing.
To see how this works, the first step is to create some content types. Figure 6.2 shows a screenshot of the Order Parent type, a content type that inherits from the Folder content type that will be used to store orders for customers. Each folder will represent a single order. As you can see, additional properties about the order can be created as columns on the content type.
Figure 6.2. The Order Parent content type.
After you have the parent content type in place, you need to create a content type for the children. In the case of the Order Item content type (see Figure 6.3), it is a custom content type that inherits from the basic List Item content type. Note that there is no column in this content type that links to a parent—all of the parent/child association is done implicitly by items being contained within an order folder.
Figure 6.3. The Order Item content type.
With those content types in place, you can easily create new items that are orders or order items, as shown in Figure 6.4. This type of scenario is ideal for lists that might be used as the backing store for help-desk support applications, issue-tracking applications, and many other applications that use traditional parent/child data relationships.
Figure 6.4. The New Item menu with content types.
With some sample data stored in a list that supports the Order Parent and Order Item content types, you can write some code that looks similar to Listing 6.3 to traverse a hierarchical list of items.
Listing 6.3. Writing Code to Traverse a Hierarchical List
Note that the way that the preceding code obtained a list of all items that belong to a given folder was by using an empty instance of the SPQuery class and specifying the folder. By default, if you do not specify a filtering query, the SPQuery object will return all list items. If you indicate a folder and still leave the query empty, the object will return a list of all items that are contained within that folder.
When the code is executed against some sample data, it produces the following output:
Summary
Lists and list items are not only the core way of storing data within SharePoint, but they are absolutely unavoidable if you are a developer. Very few SharePoint development tasks do not involve lists or list items. This chapter provided an overview of some of the more advanced things that can be done with lists and list items.