Everything from the site level (SPSite
) down to a list item (SPListItem
) can be read, modified, and saved back to the database using the API. However, before using the object model, you need a basic understanding of lists and the content type model. Different data access techniques are necessary when dealing with large sets of data. They include querying with the Collaborative Application Markup Language (CAML) and the API based on its SPQuery
object. SharePoint 2010 provides extensive support for accessing lists and libraries using Language Integrated Query (LINQ). Using spmetal.exe
, you can create a type-safe object layer over any existing list or library. This layer uses LINQ to access and retrieve, write, or delete data. This method has some advantages, as you will see.
Learn more about data access in this chapter:
How lists and libraries work internally and how to access data through the object model
The event model that handles list and list item–related events
CAML to query lists, and the SPQuery
class
LINQ to query data with type-safe objects
Every application needs a flexible data model as a foundation. The user needs somewhere to store, deal with, and retrieve data. The data represents the natural and virtual objects managed in the application. SharePoint organizes data in lists and their derivatives, the libraries. Several objects assist with defining the list's structure, such as field types and content types.
Metadata is a semantically enhanced description of the data to express the meaning of data. For example, adding additional information (such as author, date, and keywords) to a document stored in a document library enhances the ability of a search engine to find it and order the results. SharePoint's list model is amazingly flexible. Moreover, it empowers ordinary users to create and manage their own data store—normally a function "for experts only." After years of computer technology advancement, the administration of a data model is no longer rocket science. However, this does not mean that you—as a developer—cannot programmatically interact with the data model. The whole power of the list model and its associated parts is available through the object model. Whatever the end user may do (and most of what they can't do) through the UI, you can accomplish with the object model.
Early on in your contact with SharePoint lists, you should have encountered the term content type. Though they are ignored by many, they are, as a matter of fact, a core concept. The Microsoft definition on MSDN crystallizes their essential quality:
When designing a smart data model for your application, your first inclination will most likely be toward a similar structure as you would create for a relational database. You probably know that a lookup field can be used to define a many-to-many relationship without a helper table in between. It seems that this database-centric approach will result in a great data model.
But stop, this is not true! SharePoint is not just another UI layer on top of SQL Server. If you look into the SQL model, you will see that all list data is stored in the same table. It doesn't make sense to create a classic relational model within another generic one. Apart from the flawed performance, you will miss out on the advantages.
SharePoint has more subtle and powerful ways to structure data. That's necessary, because human-readable data is not well structured at all. Consider a taxonomy for storing documents in your company. You might keep the offers, invoices, and project documents in different libraries. Alternatively, you could store the documents by customer—collecting all the customer-specific data in one place, regardless of the type of document. In a relational model, things get complicated. You have a customer table, an invoice table, an offer table, and so on. They have relationships, of course, and retrieving data necessitates creating several joins over some or all the tables. However, not everyone needs everything. There are different consumers for the data. A co-worker will retrieve the offers, while another needs a summary of the projects in progress. A report for the board of directors includes a report on the overdue invoices. A developer modeling the data diagram needs access to everything.
With SharePoint, the data model is arranged very differently. The documents are stored in essentially an unstructured way. All documents are kept in one large library. Searching, filtering, reporting, and performing similar tasks are more efficient and easy to construct. On the other hand, to get access to particular data, more information about the documents is required. "More" is often expressed as "meta" in information technology. Instead of creating a complex and error-prone data model—which is still a compromise—each document has a distinct set of semantic data to describe itself. Each search instance can use all or some of the metadata to find the desired documents. If you think that additional columns in a relational data model table would offer equivalent functionality, that's not entirely true. If several different types of document share the same table—because they are "similar enough"—the number of columns would eventually exceed the capabilities of the database. Adding metadata means that you have the right amount of additional data restricted to the actual document type.
Such a predefined set of metadata fields is called a content type. Like a database table, lists in SharePoint have columns. Metadata expressed as content types are sets of columns. The clever feature is that each document in a list can be associated with its own content type. The content type has a private set of columns that describe the document (or data item) further without breaking the model. In other words, content types encapsulate the data schema on a very high level. The physical location of the data is no longer part of the schema. Having two schemas in one data container resolves the oldest conflict of a relational database—the target it's written for can be either one or another, but not both. In contrast to relational tables, it's like you have a superset of columns while each item (row) refers to a subset of columns. Each content type defines such a schema and several content types can be bound to the same list.
A content type includes a collection of information, such as the following:
The columns that define the metadata
Custom forms to edit the data, especially for New, Edit, and Display operations
Associated workflows including the event or condition that invokes the workflow
For documents, the template used to create a new document
Associated custom code that's required to deploy and run custom content types
Creating and using content types is possible using the UI and the object model. In the section "Content Types" later in this chapter we explore the object model and learn how to program against lists and content types.
Content types are a breakthrough in handling loosely structured data. They do, however, reside in lists. Before enhancing a list using content types, you need at least a basic list. It's good practice to check what internal lists are already available before creating your own.
In this section we refer often to lists. If not explicitly stated, this means both lists and libraries. Document libraries are a hybrid. They are lists, and they can store documents. Document libraries and their specific features are further explained in the "Document Libraries and Document Sets" section.
SharePoint comes with several built-in lists (see Table 4-1). For simple projects, you probably don't need to create anything to store your user's data.
Table 4.1. Some Lists Built into SharePoint
List | Type | ID | Base Type | Description |
---|---|---|---|---|
Generic | List | 100 | 0 | Custom list's base type |
Document | Library | 101 | 1 | Default document library supporting versioning, check-in and check-out, and workflows |
Survey | List | 102 | 4 | Stores surveys and results |
Links | List | 103 | 0 | Manages hyperlinks |
Announcements | List | 104 | 0 | Timely news with expiration support |
Contacts | List | 105 | 0 | Tracking people and contact information with Microsoft Outlook support |
Events | List | 106 | 0 | Stores events in a calendar |
Tasks | List | 107 | 0 | Activity-based items with workflow support |
Discussions | List | 108 | 3 | Supports threaded discussions |
Picture | Library | 109 | 1 | Stores slide shows, pictures, and thumbnails, and allows editing using the Microsoft Office Picture Manager |
Data Sources | Library | 110 | 1 | Stores data connection files |
Site templates | Library | 111 | 1 | Site template files |
User Information | List | 112 | 0 | Information about users |
Web part gallery | Library | 113 | 1 | Web parts |
List templates | Library | 114 | 1 | List template definitions |
Form | Library | 115 | 1 | Stores XML forms created with InfoPath |
Master pages | Library | 116 | 1 | Master page gallery |
No-Code Workflows | Library | 117 | 1 | Workflow library |
Library | 118 | 1 | Workflows with code | |
Wiki Page | Library | 119 | 1 | Stores wiki pages |
Custom Grid | Library | 120 | 1 | A customizable grid view |
Data Connection | Library | 130 | 1 | Stores information about external data connections |
Workflow History | List | 140 | 0 | The task history a workflow produces |
Project Tasks | List | 150 | 0 | Tasks with Gantt chart rendering support |
Issue tracking | List | 1100 | 5 | Stores issues with resolution information and prioritization support |
The base type is defined in an SPBaseType
enumeration that is defined like this:
public enum SPBaseType { UnspecifiedBaseType = −1, GenericList = 0, DocumentLibrary = 1, Unused = 2, DiscussionBoard = 3, Survey = 4, Issue = 5, }
Additional lists are available for specific purposes. The meeting workspace defines several lists in the number range from 200, and the blog site defines lists with an ID starting at 300.
An empty list definition called "Generic" is available to create your own set of columns. The base type shown in the table allows SharePoint to internally track the right columns for the specific item.
You can easily create a new list using the Add
method of the SPListCollection
type. Listing 4-1 demonstrates this in a console application.
Example 4.1. Create a List in Code
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList list = null; string listName = "Books";
// Check whether the list already exists try { list = web.Lists[listName]; } catch (ArgumentException) { } if (list == null) { Guid listId = web.Lists.Add(listName, "All our books", SPListTemplateType.GenericList); list = web.Lists[listId]; list.OnQuickLaunch = true; list.Update(); } Console.WriteLine("Created list {0} with id {1}", list.Title, list.ID); } }
This code runs in a console application and creates a new generic (custom) list without any additional columns. Before creating a new list, you should check whether it already exists. You can either loop through all lists or try to access the new list and catch the exception thrown if it doesn't yet exist. An alternative approach is to use a LINQ query:
bool exists = (from l in web.Lists.OfType<SPList>() where l.Title.Equals(listName) select l).Count() > 0;
The list itself is not queryable and must be copied into a List<T>
element using the OfType
operator. That means the list is copied completely and uses a significant amount of memory if you have many lists. However, from the perspective of clean code, the pattern seems smarter than a try...catch
block.
Once the new list is created, you can start changing properties and adding columns. In the example, only one property has been set—OnQuickLaunch
. This enables instant access to the new list from the quick menu, as shown in Figure 4-1.
Once the list exists, you can add fields. Fields represent the columns. Internally, a field is a type derived from SPField
. SharePoint includes a number of predefined fields for specific types. The following example demonstrates adding several fields for scalar types:
list.Fields.Add("ISBN", SPFieldType.Text, true); list.Fields.Add("LeadAuthor", SPFieldType.Text, true); list.Fields.Add("Price", SPFieldType.Currency, false); list.Update();
The SPFieldType
enumeration contains all possible types. You can also define custom types and extend the list of field types. A field type is not only a scalar or complex value; it can also have a relationship to a custom field editor. (For more information, see the "Custom Field Types" section later in this chapter.)
The Add
method has three overloads. These are the five possible parameters:
Field name (string
)
Field type (SPFieldType
)
Flag to specify whether the field is mandatory (boolean
)
Flag indicating that the name is to be compacted to eight characters (optional) (boolean
)
An optional collection of values for choice fields (System.Collection.Specialized.StringCollection
)
After all the fields have been added, you need to call the Update
method, which changes the model in the database. Figure 4-2 shows the edit window of the modified list.
Adding a lookup requires more information. A lookup creates a relationship between two lists. Instead of using the Add
method, call AddLookup
. This method requires three or four parameters:
Adding a lookup field follows a similar process to adding any other field type:
using (SPWeb web = site.OpenWeb()) { SPList authorList = web.Lists["Authors"]; SPList bookList = web.Lists["Books"]; Guid listId = authorList.ID; bookList.Fields.AddLookup("Authors", listId, false); bookList.Update(); }
This code adds a lookup from books to the authors list (see Figure 4-3).
Again, the Update
method commits the changes to the database model.
Certain properties are not available through the UI. Even SharePoint Designer, with its advanced capabilities, eventually reaches its limits. The example in Listing 4-2 shows how to enable an invisible field to appear in the dialogs again and remove the read-only flag.
Example 4.2. Change Field Properties Programmatically
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList questions = web.Lists["Questions"]; SPField field = questions.Fields["Voters"]; field.Hidden = false;
field.ReadOnlyField = false; field.Update(); } }
The change affects only the SPField
object—not the list. The code snippet assumes you have a Questions list with a Voters field that is hidden and read-only. The changes are assigned to the underlying content type if the column is defined there. There is no need to change the particular content type directly.
Once the fields have been added—either programmatically or by using the UI—you can iterate through them and change properties. The foreach
approach very simply iterates through all the fields:
SPList bookList = web.Lists["Books"]; foreach (SPField field in bookList.Fields) { Console.WriteLine("{0}, Hidden:{1}, ReadOnly:{2}", field.Title, field.Hidden, field.ReadOnlyField); }
LINQ is an effective tool to filter the list:
foreach (SPField field in bookList.Fields.OfType<SPField>().Where(f => !f.ReadOnlyField) ) { Console.WriteLine("{0}, Type:{1}", field.Title, field.Type); }
The OfType
operator reads the complete list and converts it into List<SPField>
type. Even if the result shows only a few fields, this operation reads all the fields and filters them in memory.
In the output, as shown in Figure 4-4, all the read-only fields are omitted. You can filter by any property the SPField
type exposes.
Adding items is as straightforward as adding fields. However, the procedure is not yet type-safe because the column's ID must be provided as a string
or Guid
. The field name you need to use here is the internal Name, not the DisplayName.
Using a string that clearly describes the field is recommended:
SPListItem item = list.Items.Add(); item["Title"] = "ASP.NET Extensibility"; item["ISBN"] = "978-1-4305-1983-5"; item["LeadAuthor"] = "Joerg Krause"; item["Price"] = 59.99; item.Update();
An ArgumentException
is thrown if a field name does not exist. Alternatively, you can use the field's zero-based index or its Guid
to identify the field. This is recommended if the index or Guid
is returned from any previous operation, to avoid errors due to mistyping.
Some properties the SPList
class exposes have specific types. Imagine that you have a Voters column, which can contain multiple users. The following code fetches the item with ID 1 from the Questions list. If there is no voter already attached, the current user executing the code is added. If there is already a user present, all users of the current web are added.
SPListItem item0 = questions.GetItemById(1); if (item0.Fields["Voters"].Type == SPFieldType.User) { if (item0["Voters"] == null) { SPFieldUserValue uv = new SPFieldUserValue(web, web.CurrentUser.ID, web.CurrentUser.Name); SPFieldUserValueCollection coll = new SPFieldUserValueCollection(); coll.Add(uv); item0["Voters"] = coll; } else { // add all users var users = from u in web.AllUsers.Cast<SPUser>() where !u.Name.Contains(web.CurrentUser.Name) && !u.Name.StartsWith("NT AUTHORITY") && !u.Name.StartsWith("SHAREPOINT") && !u.Name.EndsWith("SYSTEM") select new SPFieldUserValue(web, u.ID, u.Name); SPFieldUserValueCollection coll = (SPFieldUserValueCollection)item0["Voters"]; foreach (SPFieldUserValue user in users) { coll.Add(user); } item0["Voters"] = coll; } item0.Update(); }
An interesting observation is that access to a field containing a collection is pernickety. If there is no user in the list, the item0["Voters"]
call returns null
. To add a new user, an object of type SPFieldUserValueCollection
must be assigned. Note that when you merely want to add another user to an already existing collection, you must assign the whole collection too. You can call Add
for the SPFieldUserValueCollection
object, but not directly to the property. This code will not work as expected:
((SPFieldUserValueCollection)item0["Voters"]).Add()
Unfortunately, it will not throw an exception but simply does nothing.
Each item in a SharePoint list has an associated SPAttachmentCollection
array. The collection contains strings, which may seem odd, as you would expect SPFile
objects instead. What you see is a direct representation of the underlying storage model. Attachments are part of the containing web. This means that all lists share all their attachments in one library. The names collection must be combined with the web's URL to give the complete address to the file in the repository.
Basic Techniques
Fortunately, the SPAttachmentCollection
has several useful direct methods, such as Add
, Delete
, and Recycle
.
SPList list = web.Lists["MyList"]; foreach (string fileName in item.Attachments) { SPFile file = item.ParentList.ParentWeb.GetFile( item.Attachments.UrlPrefix + fileName); // Work with SPFile }
Listing 4-3 shows access to a specific item of a list.
Example 4.3. Read an Attachment for a Specific Item
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList list = web.Lists["WordDocuments"]; SPListItem item = list.Items[id]; if (item.Attachments.Count > 0) { SPFile attachment = item.ParentList.ParentWeb.GetFile( item.Attachments.UrlPrefix + item.Attachments[0]); Stream content = attachment.OpenBinaryStream(); using (Package p = Package.Open(content)) { // handle as a package (just a suggestion) } } } }
This code assumes that you have a list, called WordDocuments. The current item is selected by using the variable id
. The code checks whether one or more attachments are present, and if so, it reads the first attachment (index zero). The attachment is loaded into an SPFile
object and read as a binary stream. We suggest you use the Package
class to access files in the package format (such as XPS, DOCX, XSLX, and so forth). The Package
class is defined in the namespace System.IO.Packaging
—the containing assembly is WindowsBase.dll
. (You can find more about working with Office packages in the section "Working with Office Documents.")
The example in Listing 4-4 retrieves the very same attachment, using the folder structure instead of the full URL.
Example 4.4. Read an Attachment via the Folder Hierarchy
SPList list = web.Lists["WordDocuments"]; SPListItem item = list.Items[id]; SPFolder folder = web.Folders["Lists"] .SubFolders[list.RootFolder.Name] .SubFolders["Attachments"] .SubFolders[item.ID.ToString()]; if (folder.Files.Count > 0) { SPFile attachment = folder.Files[0]; Stream content = attachment.OpenBinaryStream(); using (Package p = Package.Open(content)) { // handle as a package (just a suggestion) } }
The twist is the direct access to the Attachments
folder of the web, where the files are organized. As before, the variable id
must contain a valid ID for the specified list.
Example: Adding an Attachment to an Item
In the same way that you can read an attachment, you can add an attachment to a list item. This is the preferred way to overcome the limitations of the common upload dialog and implement something more sophisticated (see Listing 4-5).
Example 4.5. Add an Attachment to an Item
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList list = web.Lists["WordDocuments"]; SPListItem newItem = list.GetItemById(id); byte[] contents = null; if (fileUpload.PostedFile != null && fileUpload.HasFile) { using (Stream fileStream = fileUpload.PostedFile.InputStream) { contents = new byte[fileStream.Length]; fileStream.Read(contents, 0, (int) fileStream.Length); fileStream.Close(); } SPAttachmentCollection attachments = newItem.Attachments;
string fileName = Path.GetFileName(fileUpload.PostedFile.FileName); attachments.Add(fileName, contents); newItem ["AttachmentName"] = fileName; newItem.Update(); } } }
This code assumes you have an .aspx
page with a FileUpload
control named fileUpload
. The user can use this to upload a file, where it's attached directly to the item. It is further assumed that your WordDocuments list has an AttachmentName column that takes the attachment's name for further reference. This column is of type text
.
Example: Deleting an Attachment from SPList
As with reading and adding, you can delete an attachment (see Listing 4-6). The item is again selected by using the variable id
.
Example 4.6. Delete an Existing Attachment
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList list = web.Lists["WordDocuments"]; SPListItem delItem = list.GetItemById(id); SPAttachmentCollection atCol = delItem.Attachments; if (delItem["AttachmentName"] != null) { string strFileName = delItem["AttachmentName"].ToString(); delItem["AttachmentName"] = string.Empty; atCol.Delete(strFileName); delItem.Update(); } } }
In this example, the WordDocuments list contains an additional column, AttachmentName, that holds the URL of the attachment. As you can see, this avoids the URL operations to get the real URL of the attachment.
If you work programmatically with attachments, it's helpful to store the complete URL in a hidden field of the containing list to have instant access to the file.
Example: Downloading an Attachment
To download an attachment, you must first find the download link and then redirect to another page. The ending of the response from current page does not affect the functionalities on the second one. The code shown in Listing 4-7 assumes you have a WordDocuments list and the AttachmentName column, as shown in the previous examples.
Example 4.7. Download an Attachment
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { string AttachmentURL = string.Empty; SPList list = web.Lists["WordDocuments"]; SPListItem attItem = myList.GetItemById(NodeID); if (attItem["AttachmentName"] != null) { AttachmentURL = "/Lists/ WordDocuments/Attachments/" + NodeID.ToString() + "/" + attItem["AttachmentName"].ToString(); System.Web.HttpContext.Current.Session["FileName"] = attItem["AttachmentName"].ToString(); System.Web.HttpContext.Current.Session["Attachment"] = AttachmentURL.Trim(); } else { lblReport.Text = "No File name found"; } if (AttachmentURL != string.Empty) { Reponse.Redirect("download.aspx"); } } }
On the download
.aspx
page, you need to the code shown in Listing 4-8 to force the browser to download the file.
Example 4.8. Download a File (Code-Behind of an Otherwise Empty ASPX Page)
if (System.Web.HttpContext.Current.Session["Attachment"] != null) { string strName = System.Web.HttpContext.Current.Session["FileName"].ToString(); string sbURL = System.Web.HttpContext.Current.Session["Attachment"].ToString(); System.Web.HttpResponse response; response = System.Web.HttpContext.Current.Response; System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; response.AppendHeader("Content-disposition", "attachment; filename=" + strName); response.AppendHeader("Pragma", "cache"); response.AppendHeader("Cache-control", "private"); response.Redirect(sbURL); response.End(); }
The download page is standard fare used to set the appropriate headers that cause the browser to display an attractive download dialog with some values preloaded.
Example: Copying an Attachment from One Item to Another Using SPList
Copying an attachment is simply a combination of the previous techniques (see Listing 4-9). There is no direct copy method—only reading an attachment into a stream and writing it back into the new attachment.
Example 4.9. Copy an Attachment
private void CopyAttachment(int fromID, int toID, string attachedFile) { byte[] contents = null; SPList list = w.ParentWeb.Lists["Item List"]; SPListItem toItem = list.GetItemById(toID); SPListItem fromItem = list.GetItemById(fromID); SPAttachmentCollection attColl = fromItem.Attachments; SPFile attFile = fromItem.ParentList.ParentWeb.GetFile( fromItem.Attachments.UrlPrefix + attachedFile); string fileRead = fromItem.Attachments.UrlPrefix.ToString() + attachedFile; StreamReader fsReader = new StreamReader(attFile.OpenBinaryStream()); using (Stream fStream = fsReader.BaseStream) { contents = new byte[fStream.Length]; fStream.Read(contents, 0, (int)fStream.Length); fStream.Close(); } toItem.Attachments.Add(attachedFile, contents); toItem.Update(); }
If you plan to copy very large files, such as 1 MB or more, we recommend you copy it in chunks. This reduces the amount of memory you need, because the byte array (contents
) will hold a copy of the whole file into memory while the opened stream is doing the same already. A loop that reads chunks of 2,048 bytes, for example, is appropriate for most projects.
If a list becomes huge—Microsoft allows theoretically up to 50 million items—the direct handling using SPList
and LINQ to Objects will fail. Instead, a CAML query, as described in the section "Query Data Using CAML and LINQ," can dramatically reduce the result set. However, if you want to modify all items in the list or need to page through a still substantial result set, this will not help. In a query, you can specify the current result set position using the SPListItemCollectionPosition
class. The query is based on the SPQuery
class, but the position does not require adding a specific condition—an empty query will also work. (See the section "Efficient Access to Huge Lists" in Chapter 5.)
The previous examples work well in code. However, when a user tries to access the list using the AllItems.aspx
page, only the title is visible. The actual appearance depends on the base type from which the list inherits. In the example, the custom (generic) list is used. This type has the obligatory Title field only. To display more columns in the standard grid, you must change the default view or add a custom view. As with any other feature, it's also possible to customize the view in code.
Each list has a default view you can reach through the DefaultView
property:
SPList mylist = SPWeb.Lists["MyList"]; SPView view = mylist.DefaultView; // modify View view.Update();
You can access the view object and save updates by calling the Update
method. It is not possible to use the property directly, as in this snippet:
mylist.DefaultView.ViewFields.Add("NewField1");
mylist.DefaultView.ViewFields.Add("NewField2");
mylist.DefaultView.Update(); // saves the "NewField2" only!
The reason is that the property returns a new SPView
instance with every call. To handle a single instance, you need to retrieve the object and modify it directly.
The next example retrieves all the lists and lets the user select any of the views of the chosen list. Within a GridView
control, all the properties are displayed. First, the .aspx
page shows how to deal with the values. The page uses the standard Master page's main placeholder, as shown in Listing 4-10.
Example 4.10. Application Page with a Ajax-Driven GridView
<asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server"> <div style="margin: 20px"> <asp:UpdatePanel ID="up1" runat="server" ChildrenAsTriggers="true" UpdateMode="Conditional"> <ContentTemplate> <h2> Available Lists in this Web</h2> <ul> <li>Web: <asp:Label ID="lblWeb" runat="server" Text="Label"></asp:Label></li> <li>Lists: <asp:Label ID="lblLists" runat="server" Text="Label"></asp:Label> Lists<br /> <asp:DropDownList ID="ddlLists" runat="server" AppendDataBoundItems="true" AutoPostBack="true" OnSelectedIndexChanged="ddlLists_SelectedIndexChanged"> <asp:ListItem Text="Select a List" /> </asp:DropDownList> </li> </ul> <h2> Views in selected list</h2> Views: <asp:DropDownList ID="ddlViews" runat="server"
AppendDataBoundItems="true" AutoPostBack="true" OnSelectedIndexChanged="ddlViews_SelectedIndexChanged"> <asp:ListItem Text="Select a View" /> </asp:DropDownList><br /><br /> <asp:GridView ID="gvProperties" runat="server" AutoGenerateColumns="false"> <Columns> <asp:BoundField DataField="PropertyName" HeaderText="Name" /> <asp:BoundField DataField="PropertyValue" HtmlEncode="true" HeaderText="Value" /> <asp:BoundField DataField="PropertyType" HeaderText="Type" /> </Columns> </asp:GridView> </ContentTemplate> </asp:UpdatePanel> </div> </asp:Content>
This markup defines a DropDownList
control for the lists, another for the views of the selected list, and a GridView
that displays all the public properties of the selected view. All the code to create and assign the data sources is in the code behind (see Listing 4-11).
Example 4.11. Code of the Application Page
using System; using System.Linq; using Microsoft.SharePoint; using Microsoft.SharePoint.WebControls; using System.Reflection; namespace Apress.SP2010.Layouts.CreateViews { public partial class ViewsManager : LayoutsPageBase { private SPWeb web; protected void Page_Load(object sender, EventArgs e) { web = SPContext.Current.Web; if (!IsPostBack) { lblWeb.Text = web.Title; lblLists.Text = web.Lists.Count.ToString(); ddlLists.DataSource = web.Lists; ddlLists.DataValueField = "ID"; ddlLists.DataTextField = "Title"; ddlLists.DataBind(); } } protected void ddlLists_SelectedIndexChanged(object sender, EventArgs e) {
Guid id = new Guid(ddlLists.SelectedValue); SPList list = web.Lists[id]; ddlViews.DataSource = list.Views; ddlViews.DataTextField = "Title"; ddlViews.DataValueField = "ID"; ddlViews.DataBind(); } protected void ddlViews_SelectedIndexChanged(object sender, EventArgs e) { Guid lid = new Guid(ddlLists.SelectedValue); SPList list = web.Lists[lid]; Guid vid = new Guid(ddlViews.SelectedValue); SPView view = list.Views[vid]; var props = from pi in view.GetType().GetProperties( BindingFlags.Public | BindingFlags.Instance) orderby pi.Name ascending select new { PropertyName = pi.Name, PropertyValue = pi.GetValue(view, null), PropertyType = pi.PropertyType.Name }; gvProperties.DataSource = props; gvProperties.DataBind(); } } }
In the final event handler attached to the ddlViews
control that shows the views, the SPView
object is retrieved. A LINQ query gathers all the public properties that are instance members. For each property, the type and value is read. Several properties contain XML. In the markup of the GridView
, the value column has the HtmlEncode
attribute set to make the XML visible (see Figure 4-5).
The property that accepts XML can be filled with different CAML schemas. It depends on the property and usage what subset of CAML is appropriate. The most important is the Query
property, which determines what the view returns. (CAML is described in more detail in the section "Understanding CAML" later in this chapter.)
Defining a view is very straightforward. You can see from the Change View dialog (select a list and then select List Tools
SPList books = web.Lists["Books"]; SPView view = books.Views["All Items"]; view.ViewFields.Add("ISBN"); view.ViewFields.Add("LeadAuthor"); view.ViewFields.Add("Price"); view.Update();
The AllItems.aspx
page now displays these fields, along with Title. As explained earlier, the internal properties sometimes return new objects with each call. Regarding ViewFields
, it still works as shown, because the collections are synchronized internally. However, the recommended coding style is as follows:
SPList books = web.Lists["Books"];
SPView view = books.Views["All Items"];
SPViewFieldCollection svfc = view.ViewFields;
svfc.Add("ISBN");
svfc.Add("LeadAuthor");
svfc.Add("Price");
view.Update();
If a view does not yet exist, you can create one programmatically. This can be part of a feature—usually in the feature receiver—or any tool a user can use to change the current list.
The code in Listing 4-12 assumes you have a Books list with some entries. It has at least two fields: Publisher, which is a selection field, and Author, containing text.
Example 4.12. Creating a New View Programmatically
using System.Collections.Specialized; using System.Xml.Linq; using Microsoft.SharePoint; namespace Apress.SP2010.CreateNewView { class Program { static void Main(string[] args) { using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList books = web.Lists["Books"]; StringCollection fields = new StringCollection(); fields.Add("Title"); fields.Add("Publisher"); fields.Add("Author"); var query = new XElement("Where", new XElement("Eq", new XElement("FieldRef", new XAttribute("Name", "Publisher")), new XElement("Value", new XAttribute("Type", "CHOICE"), "Apress") ) ).ToString(SaveOptions.DisableFormatting); SPView view = books.Views.Add("ApressBooks", fields, query, 100, false, false, Microsoft.SharePoint.SPViewCollection.SPViewType.Html, false ); } } } } }
You can add a new view to the SPViewCollectio
n
exposed by the Views
property. You need to provide the fields as a StringCollection
object. If the view should filter the data, a query can be applied using CAML. The XElement
and XAttribute
types aid in creating valid XML on the first attempt. The query is then assigned as a string. The Add
method has some overloads. Table 4-2 explains the parameters this method supports.
Table 4.2. Parameters of the Add Method Required to Create a View
Parameter | Type | Description |
---|---|---|
viewName |
| The name for the view |
fieldColl |
| The collection of field names |
query |
| CAML query that filters the data |
rowLimit |
| Maximum number of rows per page |
paged |
| Flag to indicate whether to show rows with paging |
makeDefault |
| Flag to indicate whether this view is the default view |
type |
| The type of view—valid values are Html (default view), Grid (datasheet view), Calendar, Recurrence (recurring calendar items), Chart, and Gantt |
isPersonal |
| Flag to indicate whether the view is public (false) or personal to the current user (true) |
There is no Update
method—after adding a view, users can use it immediately.
A custom field type extends a field's behavior. A field is not only a simple storage location for data; it has additional functions:
Constraints that limit the data value to a range or specific conditions
Validation formula that checks data entered by user
A field editor for the common edit forms
If you create a custom field type, you can modify all of these aspects. These features are independent of each other. If you want to display a drop-down to enter a selection of integer values, you will use a standard number field and simply change the editor, while a validation doesn't make sense (because the user can't manipulate the selection.)
A custom field type is usually accompanied with a custom editor, which appears in the default EditForm
for the list. Building the editor is similar to building a user control or Web Part. It's therefore covered in Chapter 11 in more detail.
As explained in the introduction, the content type has a significant role. Instead of modeling a list with a fixed set of fields, the content type can be bound to a list. A list may have different content types and accept different types. Content types are defined outside the scope of any list. This enhances reusability. Having defined content types to describe a specific set of items, you can use these definitions in as many lists or libraries as you want. As a developer, you can treat content types as a form of polymorphism. Content types are supported on several levels. Consequently, you can, for instance, search your site for all items of a specific content type, regardless of which list contains the item.
Content types support the concept of inheritance. This means that you do not usually create a new content type from scratch. Instead, you inherit from a similar one and add the fields and definitions you actually need. SharePoint includes several built-in content types for this purpose—making the process of creating a new one fast and simple.
In this chapter, provided we're not referring to documents explicitly, we don't distinguish between lists and libraries. Content types have two base types, one for lists and one for libraries. As a result, if you want to create a new content type for a document library, it must inherit from the appropriate base type. A content type designed for a library will not work with a standard list.
The previous sections described fields. Content types define not only fields to create columns. They also define events or associated workflows—the item's behavior. For example, you can create a content type that is bound to a list, and when an item is created with that type, an associated event receiver uses the item to perform some calculations or check validity. Because of this power, creating and using content types is complicated. However, it's worth examining closer, for any real life project.
Like the content type itself, the fields it contains are not explicitly defined there. Instead, you create global fields (in the scope of the web or site) and assign a reference to such a field. SharePoint comes with many predefined global field definitions that can be used to create content types with standard field types. These appear as site columns in the SharePoint UI.
While the global fields internally use the term field, the UI uses the term column. In this chapter, we use the term field, and you can regard this as equivalent to column. The authors prefer the term field because it expresses the nature of the data model with lists and items, while a column is closer to the relational model based on tables and records.
Internally, a content type is of type SPContentType
. This type contains two collections, Fields
(of type SPFieldCollection
) and FieldLinks
(of type SPFieldLink
). If you want to add a field to the content type, you need to use an SPFieldLink
object. The Fields
collection is the corresponding list that gives access to the final definition of each column including a column's attributes. The resulting SPField
object is a merged view of the part defined in the references field and the one inherited from.
Even if the object model appears to support addition or deletion of fields using the Fields
collection, an exception is thrown if you try to do so.
Before you start creating your own fields, examine the list of predefined ones. It is likely that you will find one appropriate for your new content type, and it will save you time and effort. Figure 4-6 shows how site columns appear in the UI.
You can find the complete list on MSDN—there are about 410 field definitions available. Hence, instead of creating one, take your time to find the right one. The following example shows both techniques, using an existing field type (site column) and a new one previously added to the field collection:
SPContentTypeCollection cts = web.ContentTypes; SPContentType newctpy = new SPContentType( cts[SPBuiltInContentTypeId.Contact], cts, "Certificates");// Add content type to the site
cts.Add(newctpy);// Create site fields and link to the content type
SPFieldCollection siteFields = web.Fields; siteFields.Add("Certificate", SPFieldType.Text, true); siteFields.Add("Score", SPFieldType.Integer, true); web.Update();
// Add fields to the new content type
newctpy.FieldLinks.Add(new SPFieldLink(siteFields["Certificate"])); newctpy.FieldLinks.Add(new SPFieldLink(siteFields["Score"])); newctpy.FieldLinks.Add(new SPFieldLink(web.Fields["Start Date"])); newctpy.Update();// Add content type to the list
SPList authors = web.Lists["Authors"]; authors.ContentTypesEnabled = true; if (authors.IsContentTypeAllowed(newctpy)) { SPContentType certCT = authors.ContentTypes.Add(newctpy); authors.Update(); }
The content type is used to add certificates an author has earned to the Authors list used in the previous examples. The code starts with a reference to the web's ContentTypes
collection. The new content type Certificates is added. Then two fields are added to the web's field collection: Certificate and Score. These and an existing field, Start Date, are added to the FieldLinks
collection of the new content type. If no errors occurred, the list is enabled to support content types, and the content type is added to the list. The result can immediately be checked by comparing the content types in the site administration with those in the list.
In Figure 4-7 you can see that the custom site column is available.
Also available are the custom content type using these columns and the built-in columns. When you start editing the items of the list, you can select your content type. This is because the existing list already implements the Contact content type. The new Certificates content type inherits from Contact and hence has the same fields plus the new ones. You can delete the existing content type if no existing items are using it:
list.ContentTypes["Name"].Delete();
In code that runs every day in your application, you will probably not use content types. Programmatic access to a site's content type is usually an administrative operation. Typically you deploy a new feature, and the users can create custom lists within your application. To minimize their effort, a custom content type is ideal. Consequently, the feature must be able to create the content type on activation. That's exactly when you invoke code similar to that shown previously.
There is some interaction between the UI and the settings made by custom code. First, when adding a content type to a list, the fields in the content type are copied to the list. That's why the edit form shows the fields immediately. If a user with appropriate rights now changes the field's definition, they change the copied definition. That means such modified fields remain local to that list and do not change the content type. Second, when changing the content type using the web's Site Settings dialog, the user has the choice to push changes to existing copies (see Figure 4-8).
To interact with lists and items, you can attach event receivers. With these your code is called when a specified condition is met in the attached list. The implementation uses the SPListItemEventReceiver
base class—you simply override the methods you want to activate.
SharePoint is rich with the events it exposes. We can categorize SharePoint events in two ways: by the "level" that fires the event (site, list, item) and by the type of the event (synchronous or asynchronous).
Table 4-3 shows the full list of synchronous events you can handle with your receivers.
Table 4.3. Synchronous Item-Level Events
Event | Description |
---|---|
| Occurs when a new item is added to its containing object |
| Occurs when a user adds an attachment to an item |
| Occurs when a user removes an attachment from an item |
| Occurs as a file is being checked in |
| Occurs before an item is checked out |
| Occurs before an existing item is completely deleted |
| Occurs when a file is being moved |
| Occurs before an item is being unchecked out |
| Occurs when an existing item is changed, for example, when the user changes data in one or more fields |
Synchronous events are also called "before" events. They appear before the internal operation occurs. The event handler is also blocking the current flow. For this reason, we recommend you avoid time-consuming operations here.
Asynchronous events (see Table 4-4) are classified as "after" events. They are called asynchronously. Lengthy operations do not block the current thread. Because the internal operation is completed, you can safely manipulate the freshly added or changed data.
Table 4.4. Asynchronous Item-Level Events
Event | Description |
---|---|
Occurs after a new item has been added to its containing object | |
| Occurs after a user adds an attachment to an item |
| Occurs after a user removes an attachment from an item |
| Occurs after an item is checked in |
| Occurs after an item is checked out |
| Occurs after an existing item is completely deleted |
| Occurs after a file has been converted |
| Occurs after a file is moved |
| Occurs after an item is unchecked out |
| Occurs after an existing item is changed, for example, when the user changes data in one or more fields |
List events (see Tables 4-5 and 4-6) occur for list operations that affect the whole list, such as schema changes. As for the list item events, you can choose synchronous and asynchronous events. The implementation uses the SPListEventReceiver
base class—you simply override the appropriate methods.
Table 4.5. Synchronous List-Level Events
Event | Description |
---|---|
| Occurs when a field link is being added to a content type |
| Occurs when a field is being removed from the list |
| Occurs when a field link is being updated |
Apart from list and list item events, several other objects in SharePoint support events, too. For site- and web-level events, refer to Chapter 3.
This section contains a step-by-step guide to developing event receivers and attaching them to the appropriate objects. In this example, we will create a simple receiver, which will handle two asynchronous events (ItemAdded
and ItemUpdated
) in all Document and Picture libraries at a SharePoint web site, and deploy it as a feature at the site level. The templates Visual Studio 2010 provides for this (see Figure 4-9) add the required configuration files. The template comes with a wizard that asks for the particular event you want to create. Therefore, there is just one template for all possible events.
The wizard asks for the SharePoint site and whether the solution is a sandboxed or farm solution. Next (see Figure 4-10), you can select the type of event (List, List Item, and so on) and what specific event handler the wizard should create.
The wizard creates the package (solution), the feature that activates and deactivates the event handlers, and the event handler definition. An event handler definition consists of an Element.xml
file that defines events, where they attach and the entry point of the assembly that contains the code (see Listing 4-13).
Example 4.13. Elements.xml of an Event Receiver
<?xml version="1.0" encoding="utf-8"?> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <Receivers ListTemplateId="104"> <Receiver> <Name>EventReceiver1ItemAdded</Name> <Type>ItemAdded</Type> <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly> <Class>ItemEventReceiverProject.EventReceiver1.EventReceiver1</Class> <SequenceNumber>10000</SequenceNumber> </Receiver> <Receiver> <Name>EventReceiver1ItemUpdated</Name> <Type>ItemUpdated</Type> <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly> <Class>ItemEventReceiverProject.EventReceiver1.EventReceiver1</Class> <SequenceNumber>10000</SequenceNumber> </Receiver>
</Receivers> </Elements>
This file defines two events, ItemAdded
and ItemUpdated
. Both are defined in the same class. The ListItemTemplateId
attribute of 104 defines that the receiver is attached to one of the default lists—in this case, the Announcement list. See Table 4-1 earlier in this chapter for a list of built-in lists and their respective internal IDs.
Each Receivers
node has following child nodes:
Name
: A unique name for the receiver—you can set it as you want for future reference.
Type
: This is actually the event name from the events table.
SequenceNumber
: An integer that determines in which order the event receivers will be fired.
Assembly
: The assembly that contains your event handler. The assembly will be deployed in the GAC.
Class
: The class in the assembly containing the handler methods.
The crucial parts here are the Assembly
and Class
tags. They must exactly match the class definition when they are deployed.
The code snippet created by the wizard (see Listing 4-14) attaches the two events to their event handlers. This is shown "as is." No changes were made to fix namespaces or anything else.
Example 4.14. The Event Receivers Handler Methods
using System; using System.Security.Permissions; using Microsoft.SharePoint; using Microsoft.SharePoint.Security; using Microsoft.SharePoint.Utilities; using Microsoft.SharePoint.Workflow; namespace Apress.SP2010.EventReceiver1 { /// <summary> /// List Item Events /// </summary> public class EventReceiver1 : SPItemEventReceiver { /// <summary> /// An item is being added. /// </summary> public override void ItemAdded(SPItemEventProperties properties) {
base.ItemAdded(properties); } /// <summary> /// An item is being updated. /// </summary> public override void ItemUpdated(SPItemEventProperties properties) { base.ItemUpdated(properties); } } }
The receiver class must inherit from SPItemEventReceiver
. To attach other events, you can override the corresponding methods in this class and extend the Elements.xml
to connect the handler with the event source. A closer look into the SPItemEventProperties
reveals some options. Listing 4-15 shows an example.
Example 4.15. Using an Event Receiver to Change Permissions
using System; using System.Security.Permissions; using Microsoft.SharePoint; using Microsoft.SharePoint.Security; using Microsoft.SharePoint.Utilities; using Microsoft.SharePoint.Workflow; namespace Apress.SP2010.EventReceiver2 { public class EventReceiver2 : SPItemEventReceiver { public override void ItemAdded(SPItemEventProperties properties) { updateItemPermissions(properties); } public override void ItemUpdated(SPItemEventProperties properties) { updateItemPermissions(properties); } private void updateItemPermissions(SPItemEventProperties properties) { try { this.EventFiringEnabled = false; SPListItem item = properties.ListItem; SPSecurity.RunWithElevatedPrivileges(delegate() { SPList parentList = item.ParentList; SPSite elevatedSite = new SPSite(parentList.ParentWeb.Site.ID); SPWeb elevatedWeb =
elevatedSite.OpenWeb(parentList.ParentWeb.ID); SPList elevatedList = elevatedWeb.Lists[parentList.ID]; SPListItem elevatedItem = elevatedList.Items.GetItemById(properties.ListItem.ID); if (!item.HasUniqueRoleAssignments) item.BreakRoleInheritance(false); SPUser editor = elevatedWeb.EnsureUser( (new SPFieldLookupValue(item["Editor"].ToString())) .LookupValue); SPUser author = elevatedWeb.EnsureUser( (new SPFieldLookupValue(item["Author"].ToString())) .LookupValue); SPRoleDefinition RoleDefReader = elevatedWeb.RoleDefinitions.GetByType( SPRoleType.Reader); SPRoleDefinition RoleDefWriter = elevatedWeb.RoleDefinitions.GetByType( SPRoleType.Contributor); SPRoleAssignment RoleAssReader = new SPRoleAssignment((SPPrincipal)editor); SPRoleAssignment RoleAssWriter = new SPRoleAssignment((SPPrincipal)author); RoleAssReader.RoleDefinitionBindings.Add(RoleDefReader); RoleAssWriter.RoleDefinitionBindings.Add(RoleDefWriter); item.RoleAssignments.Add(RoleAssReader); item.RoleAssignments.Add(RoleAssWriter); item.Update(); }); this.EventFiringEnabled = true; } catch (Exception ex) { } } } }
The receiver is attached to a list that has at least two fields—called Author and Editor—both of User
type containing a user available in the current site. Usually the creator of the item becomes the owner. The event receiver overwrites the inherited permissions by setting the users added in the Author field with write permissions and in the Editor field with read permissions.
In addition, the code shows some basic principles. To change the permissions, you need sufficient rights. You can achieve this by elevating the privileges or by assigning a specific user's token to the OpenWeb
method. In this example, the RunWithElevatedPrivileges
method is used. The code checks whether the item already has specific permissions using the HasUniqueRoleAssignments
property. This avoids rewriting the values again. If the permissions are at the default level, the inheritance hierarchy is broken by calling BreakRoleInheritance
. Now you can assign user's roles. EnsureUser
checks that the site's user is available in the list's web. The specific permissions are retrieved from the web's RoleDefinitions
property and set to standard permissions (SPRoleType.Reader
and SPRoleType.Contributor
).
Because the changes will update the item and an event receiver connects to ItemUpdating
event, you need to suppress events temporarily. This is done by setting the EventFiringEnabled
property.
Document libraries are lists that handle documents. Other than the attachments, which are stored in the containing web's central attachment library, a document library stores its files directly. The programmatic access to libraries is similar to lists. In addition to list-related functions, you use streams, such as a MemoryStream
, when saving or loading files.
Several classes are dedicated to working with libraries, folders, and the files in them. In essence, a library is a list with a special content type. As a result, you can access any document library as a list and cast it to the SPList
type. Each library contains at least one folder—the root folder—which provides access to any files in it. Because files are stored using an address relative to the site, even the SPSite
object is a possible source. Precisely how to determine the correct storage location depends on what kind of library you use. The following examples in this section should give you ideas.
Each site contains a library called Shared Documents. Listing 4-16 shows how to retrieve a file as an SPFile
object.
Example 4.16. Read a File from a Folder Using SPFolder
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { if (web.GetFolder("Shared Documents").Exists) { SPFolder folder = web.GetFolder("Shared Documents"); SPFile file = folder.Files["WordTemplate.docx"]; } } }
There is a more direct way to access a specific file using the SPDocumentLibrary
(Listing 4-17).
Example 4.17. Read a File from a Folder Using SPDocumentLibrary
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { string url = SPUrlUtility.CombineUrl(web.Url, "/Shared Documents"); SPDocumentLibrary lib = (SPDocumentLibrary) web.GetList(url); SPFile file = lib.RootFolder.Files["WordTemplate.docx"]; } }
Both examples fetch the same file. However, both will fail with an ArgumentException
if the file does not exist. To check this, you cannot test the return value for null
. Even an if
statement such as the one shown next will not help:
if (lib.RootFolder.Files[["WordTemplate.docx"] != null)
Instead, use the GetFile
method provided by SPWeb
:
if (web.GetFile(fileName).Exists)
The interesting thing is that this method always returns an object. If the file does not exist, the method returns an SPFile
object whose Exists
property is set to false
. There are still circumstances in which GetFile
could return null
. Hence, a helper method as in Listing 4-18 is desirable if you have many such file operations.
Example 4.18. Helper Method to Check for File (Retrieved from Internal Code)
static SPFile GetExistingFile(SPWeb web, string serverRelativeUrl) { SPFile file = null; bool exists = false; try { file = web.GetFile(serverRelativeUrl); if (file != null) { exists = file.Exists; } } catch (ArgumentException) { exists = false; } if (!exists) { return null; } return file; }
Once you have the SPFile
, you can do many things with it. The SPFile
object gives access to some powerful properties and methods.
You can find the complete list of members for SPFile
at http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfile_members(office.14).aspx
.
The main functions are the OpenBinaryStream
method, which opens the file as a stream, and OpenBinary
, which returns a byte array. Apart from these, you can do everything programmatically that users with appropriate rights can do from the UI.
If you work with folders, your users might not be satisfied with the UI support. For greater capability, you can create a function (see Listing 4-19) that adds folders programmatically.
Example 4.19. Adding a Folder Using SPFolder
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPList lib = web.Lists["Shared Documents"]; SPFolder parent = lib.RootFolder; SPFolder child = parent.SubFolders.Add("SubFolderName"); parent.Update(); } }
This code adds a SubFolderName folder to the library's root. Exactly the same functionality is possible (see Listing 4-20) if you prefer to work with SPList
.
Example 4.20. Add a Folder Using SPList
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb spweb = site.OpenWeb()) { spweb.AllowUnsafeUpdates = true; SPList lib = spweb.Lists["Shared Documents"]; SPListItem child =lib.Items.Add(lib.RootFolder.ServerRelativeUrl, SPFileSystemObjectType.Folder, "SubFolderName"); child.Update(); } }
The previous example reveals that a folder is simply a special kind of item. That's indeed true and is the reason why some collections return all elements, regardless of the folder to which they are currently assigned.
To upload a file to a library, you use a similar approach (see Listing 4-21). The file must be available as a Stream
or a byte array.
Example 4.21. Read a File from Disk and Save It to a Document Library
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { spweb.AllowUnsafeUpdates = true;
SPFolder folder = web.Folders[site.URL + "/Shared Documents/"];
byte[] content = null;
using (FileStream filestream = new FileStream(@"C:Sample.docx",
System.IO.FileMode.Open))
{
content = new byte[(int) filestream.Length];
filestream.Read(content, 0, (int) filestream.Length);
filestream.Close();
}
SPFile file = folder.Files.Add("Sample.docx", content, true);
// do something usefil with file
}
}
You can add the file to a specific folder:
SPFile file = folder.SubFolders["SubFolder"].Files.Add("Sample.docx", content, true);
The content
variable is the same as shown previously in Listing 4-21.
Deleting a file is elementary. Listing 4-22 shows how to delete a file directly.
Example 4.22. Deleting a File
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { web.AllowUnsafeUpdates = true; SPFolder folder = web.Folders[web.Url + "/Shared Documents/"]; folder.Files["Sample.docx"].Delete(); folder.Update(); } }
Recycling a file (Listing 4-23) uses a variation on the same approach. Recycling recycles the item into the Recycle Bin folder SharePoint provides.
Example 4.23. Recycle a File from Parent Folder
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { web.AllowUnsafeUpdates = true; SPFolder folder = web.Folders[web.Url + "/Shared Documents/"]; folder.Files["Sample.docx"].Recycle(); folder.Update();
} }
Deleting a file from a subfolder follows the same rules (see Listing 4-24).
Example 4.24. Deleting a File from a Subfolder
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { web.AllowUnsafeUpdates = true; SPFolder folder = web.Folders[site.Url + "/Shared Documents/"]; folder.SubFolders["SubFolder"].Files["Sample.docx"].Delete(); folder.Update(); } }
Storing Microsoft Office documents in SharePoint is a common usage scenario. Creating such documents and filling them with data from SharePoint lists is more challenging. All Office products have an API that is available to .NET developers. However, the hierarchy of sections and paragraphs in Word, the structure of cells in Excel, or the slides in PowerPoint require several steps through the object model to write a single line of text. In addition, the products must be installed on the server. For complex document creation tasks, it's an impasse. Microsoft introduced a new document format with Office 2007—the .docx
, .xslx
, and .pptx
file formats, respectively. Internally these formats are zipped packages that contain the main data file as XML (see Figure 4-11), all related resources, and a manifest.
The package itself is based on the packaging standard Open Packaging Convention (OPC), an ECMA International standards organization–approved format. To start working with it, you can take a .docx
file, rename the file extension to .zip
, and open the package. For the example in this section, we focus on Word and use .docx
. Other Office formats follow a similar convention. However, the key to the solution isn't the package itself. The XML-based format is the clue. To generate a document, you need a template first. The template must be unpacked, modified, packed, and stored elsewhere as the final document.
The best way to add placeholders is via controls. To add placeholders, you first need to activate the developer toolbar (see Figure 4-12).
Before you can fetch a document from a library, you must have one. As shown in Figure 4-11, through the XML you have access to any part of the document. To build a template, you need to create a regular Word document and add placeholders at the appropriate positions. To construct a report, insert a table. Add one data row as a template and repeat this row for each line of data. Building the document can be accomplished with a few clicks, and an end user with some Word experience should be able to master this. They will, however, add an unpredictable amount of formatting styles to such a document. Therefore, you must be able to recognize the placeholders reliably.
On the activated Developer tab, click Design Mode, and you can add controls to your layout. For this exercise, use Text controls. In the Properties options, as shown in Figure 4-12, you can set the Title of the control (as it appears in design view) and the Tag name (as it appears in the XML).
Once the placeholders are inserted, you can save the document and upload it to the appropriate document library.
The techniques demonstrated here work with any combination of Office 2007 or Office 2010 product with SharePoint Foundation or SharePoint Server.
The complex system based on SharePoint, Word, and package formats is now as easy as reading and writing XML. Using LINQ to XML makes this even more straightforward. However, you need the XML first. Begin by fetching the document into an SPDocumentLibrary
object:
SPDocumentLibrary templateLib = web.Lists.Cast<SPList>().FirstOrDefault( list => list.RootFolder.Name == "WordTemplates") as SPDocumentLibrary;
The lambda expression is used to reference the list using the RootFolder.Name
property. If you can use the Title
, the Lists
property's indexer will work, too. Call the OpenBinaryStream
method to retrieve the file as a stream:
string documentUrl = SPUrlUtility.CombineUrl( web.Site.MakeFullUrl(templateLib.RootFolder.Url), templateName); SPFile template = templateLib.RootFolder.Files[documentUrl]; Stream templateStream = template.OpenBinaryStream();
To work with OPC packages, you need classes from the System.IO.Packaging
namespace. The assembly containing the classes is called WindowBase.dll
.
You have to first unpack the document. SharePoint libraries return files as a stream, as shown previously. To open a file as a package, you can use code like this:
Package package = Package.Open(templateStream, FileMode.Open, FileAccess.ReadWrite);
The variable templateStream
is of type Stream
. From the Package
object, you can now read the actual file:
Uri uri = new Uri("/word/document.xml", UriKind.Relative); PackagePart part = package.GetPart(uri);
The PackagePart
provides access to the document, as a stream that can be loaded into an XDocument
:
Stream partStream = part.GetStream(FileMode.OpenOrCreate, FileAccess.ReadWrite); XmlReader xmlReader = XmlReader.Create(partStream); XDocument doc = XDocument.Load(xmlReader);
Once the XML is in the right type, any further manipulation is simple using LINQ to XML. With minimal instructions you can locate the <w:sdt>
element that creates a table:
XElement tablePlaceHolder = doc.Descendants(W.sdt).FirstOrDefault( x => x.Element(W.sdtContent).Descendants(W.sdt).FirstOrDefault() != null);
The content is found within <w:stdContent>
, which this statement returns. From here you extract the table's row to use as a prototype for the rows being generated:
XElement prototype = tablePlaceHolder.Element(W.sdtContent).Descendants(W.tr) .Where(x => x.Descendants(W.sdt).FirstOrDefault() != null).FirstOrDefault();
Before proceeding, you need to read the data from a SharePoint list. For the phone list, read from a list called Contacts:
SPList dataList = web.Lists.Cast<SPList>().FirstOrDefault( list => list.RootFolder.Name == "Contacts"); IEnumerable<SPListItem> allItems = dataList.GetItems( new SPQuery()).Cast<SPListItem>();
The items in allItems
contain the data to display in the report rows. Listing 4-25 shows the complete code. The inner foreach
statement reads the placeholders and replaces their contents with the data read from SharePoint. ContainsField
is used to avoid exceptions if a user named the placeholder field accidentally with a name the list does not provide. li[celltag]
fetches the data as input for a format instruction. Imagine additional operations here following either fixed rules or instructions added to the document. The prototype row is replaced with a number of rows—one for each line in the data list. The file is written back into the package, and the package is sent to another document library in SharePoint as the final report. You can find the complete solution in Listing 4-25.
Example 4.25. The Complete Solution: Generate Template-Based Reports in WordML
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SharePoint; using Microsoft.SharePoint.Utilities; using System.IO; using System.IO.Packaging; using System.Xml; using System.Xml.Linq; namespace Apress.SP2010.DocxDemo { class Program
{ static void Main(string[] args) { using (SPSite site = new SPSite("http://sharepointserve")) using (SPWeb web = site.OpenWeb()) { SPDocumentLibrary templateLib = web.Lists.Cast<SPList>().FirstOrDefault( list => list.RootFolder.Name == "WordTemplates") as SPDocumentLibrary; SPDocumentLibrary reportLib = web.Lists.Cast<SPList>().FirstOrDefault( list => list.RootFolder.Name == "WordReports") as SPDocumentLibrary; SPList dataList = web.Lists.Cast<SPList>().FirstOrDefault( list => list.RootFolder.Name == "Finances"); string templateName = "ReportTemplate.docx"; string reportName = String.Format("DailyReport{0}.docx", DateTime.Now.ToShortDateString()); string documentUrl = SPUrlUtility.CombineUrl( web.Site.MakeFullUrl(templateLib.RootFolder.Url), templateName); SPFile template = templateLib.RootFolder.Files[documentUrl]; Stream templateStream = template.OpenBinaryStream(); Stream documentStream = new MemoryStream(); BinaryReader templateReader = new BinaryReader(templateStream); BinaryWriter documentWriter = new BinaryWriter(documentStream); documentWriter.Write( templateReader.ReadBytes((int)templateStream.Length)); documentWriter.Flush(); templateReader.Close(); templateStream.Dispose(); Package package = Package.Open(documentStream, FileMode.Open, FileAccess.ReadWrite); Uri uri = new Uri("/word/document.xml", UriKind.Relative); PackagePart part = package.GetPart(uri); Stream partStream = part.GetStream( FileMode.OpenOrCreate, FileAccess.ReadWrite); XmlReader xmlReader = XmlReader.Create(partStream); XDocument doc = XDocument.Load(xmlReader); xmlReader.Close(); XElement tablePlaceHolder = doc.Descendants(W.sdt) .FirstOrDefault(x => x.Element(W.sdtContent) .Descendants(W.sdt) .FirstOrDefault() != null);
XElement prototype = tablePlaceHolder.Element(W.sdtContent) .Descendants(W.tr) .Where(x => x.Descendants(W.sdt) .FirstOrDefault() != null) .FirstOrDefault(); IEnumerable<SPListItem> allItems = dataList.GetItems( new SPQuery()).Cast<SPListItem>(); prototype.Parent.Add(allItems.Select( li => { var result = new XElement(prototype); foreach (var placeholder in result.Descendants(W.sdt)) { string celltag = placeholder.Element(W.sdtPr) .Element(W.tag) .Attribute(W.val) .Value; if (li.Fields.ContainsField(celltag)) { placeholder.Element(W.sdtContent) .Descendants(W.t) .Single() .Value = String.Format("{0}", li[celltag]); } else { placeholder.Element(W.sdtContent) .Descendants(W.t) .Single() .Value = String.Empty; } } return result; })); prototype.Remove(); partStream.SetLength(0); XmlWriter writer = XmlWriter.Create(partStream); doc.WriteTo(writer); writer.Close(); package.Flush(); string reportUrl = SPUrlUtility.CombineUrl( web.Site.MakeFullUrl(reportLib.RootFolder.Url), templateName); SPFile report = reportLib.RootFolder.Files.Add( reportUrl, documentStream, true); SPListItem reportItem = report.Item; reportItem["Title"] = reportName; // Set Metadata
reportItem.Update(); } } } }
Because you need access to WordML elements, it makes sense to create a helper class with predefined XName
objects (in Listing 4-26).
Example 4.26. Helper Class with WordML Elements
public static class W { public static XNamespace w = "http://schemas.openxmlformats.org/ wordprocessingml/2006/main"; public static XName body = w + "body"; public static XName sdt = w + "sdt"; public static XName sdtPr = w + "sdtPr"; public static XName tag = w + "tag"; public static XName val = w + "val"; public static XName sdtContent = w + "sdtContent"; public static XName tbl = w + "tbl"; public static XName tr = w + "tr"; public static XName tc = w + "tc"; public static XName p = w + "p"; public static XName r = w + "r"; public static XName t = w + "t"; public static XName rPr = w + "rPr"; public static XName highlight = w + "highlight"; public static XName pPr = w + "pPr"; public static XName color = w + "color"; public static XName sz = w + "sz"; public static XName szCs = w + "szCs"; }
This is the small subset of WordML tags used in the previous example. For other projects, it may be necessary to extend this list.
Documents sets are new in SharePoint Server 2010. Technically, these sets are another content type, with particular functionality. A document set is related to a document library and is similar to a folder. Like a folder, you can add multiple documents to one document set. While a folder can be opened, a document set is much more. Each document set can have a different set of content types. Creating a new document set means creating a set of different documents. Imagine you want to produce offers for customers. The offer can contain various Office documents, such as the quote, terms and conditions, a calculation sheet, a presentation with some company information, and more. All these can be assembled by a user with a single click.
Document sets are a document management feature of SharePoint Server 2010. They are not available in SharePoint Foundation. The assemblies necessary to access the features programmatically are not part of a SharePoint Foundation installation.
The types used to work with document sets are defined in the Microsoft.Office.DocumentManagement.dll
assembly.
Document sets have several advantages:
Each set has its own version history. It's not necessary to track changes at the document level.
You can assign access rights on a per-set level.
You can assign and start workflows related to a set.
Users can download the whole document set as a ZIP file.
The home page related to a document set is a wiki page that can be edited easily.
Documents that are part of a document set can share their metadata.
Before you can access and use document sets programmatically, some preparatory steps are required.
The document sets feature is available at the site collection level. To activate it, open the site settings and click "Site collection features." On the next page, click the Activate button near the Document Sets feature entry (see Figure 4-13).
A document set can now be created by adding a new content type, which inherits from the built-in content type Document Set. As with any other content type, you can add any number of metadata to modify the behavior appropriately. Additionally, you can configure multiple templates to support the documents the set can contain. The Document Set Settings function is available to modify the templates (see Figure 4-14).
The various settings of the new content type are as follows:
Assigned content types: These are the content types of the documents within the set.
Default content: You can upload documents which automatically become part of a new set. These are usually static documents, such as "Terms and Conditions."
Shared columns: The columns defined here are shared between all documents of the document set.
Home page columns: These columns are displayed on the document set's home page—the standard wiki page.
After setting the appropriate properties, you can assign the new content type to any document library. Henceforth, this library supports document sets. The document library must generally support content types.
All types required to access document set functions are defined in the namespace Microsoft.Office.DocumentManagement.DocumentSets
. The namespace contains the classes shown in Table 4-7.
Table 4.7. Classes and Structures Related to Document Sets
Type | Description |
---|---|
| A class that stores a list of the content types that can be included in an associated |
| A class that represents a document that will be provisioned automatically for every |
| A collection of |
| An exception thrown if an object exceeds 50 MB |
| A document set item |
| The template on which |
| Metadata that is associated with a major or minor version of the |
| A collection of |
| The list of metadata fields for the |
| The list of metadata fields for the content type that is associated with this |
| A |
| A |
The most valuable class is DocumentSet
. This class has three static methods you need frequently:
Create
: Creates a new instance
GetDocumentSet
: Creates an existing instance from an SPFolder
object
Import
: Imports a document from a stream or byte array into a folder
Some methods are provided by a class instance:
Export
: Exports the document set to a stream or a byte array
Provision
: Places the default documents in the document set
SendToOfficialFile
: Submits a document set to the records management
Moreover, all the current settings are exposed through properties:
ContentType
: Returns the content type for the document set
ContentTypeTemplate
: Returns the content type template associated with the DocumentSet
object
Folder
: Returns the SPFolder
object that contains this DocumentSet
object
Item
: Returns the SPListItem
object that is associated with this DocumentSets
object
ParentFolder
: Returns the parent folder of the DocumentSet
object
ParentList
: Returns the parent list of the DocumentSet
object
VersionCollection
: Returns the version collection of the DocumentSet
object
WelcomePageUrl
: Returns the URL of the Welcome page for the DocumentSet
object
The relationship between a folder and a document set reveals the underlying approach. A document set is—from a developer's perspective—a folder that is associated with a specific content type. The examples shown in the next few sections express this in various ways.
A document set is specific folder based on a custom content type that inherits from a built-in content type. The new API necessary to deal with document sets is added to the existing model without changing basic classes such as SPList
or SPFolder
. To achieve this several new classes have been added, which provide some static methods (see Listing 4-27).
Example 4.27. Accessing a Document Set Programmatically
SPListItem item = SPContext.Current.ListItem; DocumentSet set = DocumentSet.GetDocumentSet(item.Folder); Console.WriteLine("ContentType: {0}", item.ContentType.Name); Console.WriteLine("Title: {0}", item.Title); Console.WriteLine("WelcomePageUrl: {0}", set.WelcomePageUrl); Console.WriteLine("ItemCount: {0}", set.Folder.ItemCount); Console.WriteLine("Welcomepage Fields:"); DocumentSetTemplate template = set.ContentTypeTemplate; WelcomePageFieldCollection fields = template.WelcomePageFields; foreach (SPField field in fields) { Console.WriteLine("{0}", field.Title); }
The DocumentSet
class encapsulates a document set. The GetDocumentSet
method is used to convert the technical base (SPFolder
) into a more appropriate object type. The home page for each document set is defined using the WelcomePageFields
property. The ContentTypeTemplate
property returns the template information as a DocumentSetTemplate
type.
You often need to know whether an item is already in a set. The method in Listing 4-28 checks this.
Example 4.28. A Method That Checks Whether an Item Is in a DocumentSet
public bool IsDocumentSetItem(SPListItem itemToCheck) { bool documentSetItem = false; if (itemToCheck.File != null) { DocumentSet documentSet; documentSet = DocumentSet.GetDocumentSet(itemToCheck.File.ParentFolder); if (null != documentSet) { documentSetItem = true; } } return documentSetItem; }
The code asks the current item to return the containing set. If there is no set, then the item is not part of any document set.
Storing data in lists and handling events are only part of the equation. More often you need to retrieve specific data and query a list for a particular result set. In previous versions of SharePoint, querying data has been tightly coupled with but not the sole domain Collaborative Application Markup Language (CAML). With SharePoint 2010, a new LINQ layer offers alternative data access options.
This section explains both API data access methods: CAML and LINQ. It's a common misconception to regard LINQ as the successor to CAML. Although CAML was the only data access method for SharePoint 2007 and LINQ is new to SharePoint 2010, both methods are fully supported in SharePoint 2010.
The truth is that CAML is still the one and only data access method. There is a small performance penalty when using LINQ to SharePoint, because it translates queries into CAML before executing. However, the layer is thin and fast. Hence, it is worth knowing the advantages of LINQ and benefiting from them. First, LINQ uses built-in keywords, enabling the compilers (both C# and VB.NET) to check the syntax, types, and structure of a query. This eliminates invalid query statements, reduces unexpected responses, and improves the quality of your software. Second, strongly typed objects allow you to program against a real object model, rather than against generic types, such as SPListItem
. This makes your code more readable, makes it less generic, and further improves code quality. Bear in mind that hardware is relatively cheap, and by adding another module of RAM, the LINQ to SharePoint method will win.
In the light of all this, we provide an insight into both methods. You have to decide for each project which to use. If overall performance is the uppermost criteria, CAML is superior. In all other cases, we encourage you to use LINQ.
While CAML is still the tool of choice for querying the data store, two other technologies are officially deprecated. SharePoint 2010 still supports them—though for backward compatibility only.
SharePoint provides a set of web services to enable client interaction with the data model. Some of these are specifically designed for data retrieval or to support at least a subset of related functions. These services are still supported for backward compatibility and interoperability with web service clients. For reasons of both performance and ease, Microsoft recommends that you use either the object model API or the ADO.NET Data Services Framework. (Both methods are described in full in Chapter 5.)
Access to SharePoint deployments using RPC calls into the owssvr.dll
is still supported. However, Microsoft says that this is only to provide troubleshooting assistance to existing client applications. For maximum application compatibility, Microsoft does not recommend using this method of client access. We do not recommend it either and exclude it from further consideration.
CAML plays a significant role in SharePoint, not limited to querying data.
You can use CAML to do the following:
Modify parameters to transport complex data
Define the body of SOAP messages to transport data using web services
Configure SharePoint for usage or deployment
Add specific behavior to features
SharePoint utilizes many XML files to define behavior and content. Most of these files use CAML as the dialect to express SharePoint-specific settings. In this section, we examine more closely the usage of CAML to query the data store.
CAML is defined in several schemas. When you first attempt to understand CAML, it looks like an amalgam of many different dialects. However, it's simply a collection of schemas that make CAML powerful enough for both data schema definition and data retrieval. These schemas are as follows:
For data access, the query schema provides a set of elements to build queries.
The Query schema is well documented, and numerous examples are available. However, the first time you try it, you'll discover it's not self-explanatory. All queries must be executed against a list. You need a reference to either an SPList
or SPView
object, which accepts a CAML query and can run it. The following examples demonstrate the bare bones of the technique.
The basic way to query data from SharePoint lists is to use queries constructed with CAML. Listing 4-29 shows a simple CAML query to fetch all items from a SharePoint list called Books whose Publisher is Apress.
Example 4.29. A Simple CAML Query
public IEnumerable<SPListItem> GetBooksFromAPress() { SPList list = SPContext.Current.Web.Lists["Books"]; SPQuery query = new SPQuery(); query.Query = @"<Where> <Eq> <FieldRef Name='Publisher' /> <Value Type='Text'>Apress</Value> </Eq> </Where>"; IEnumerable<SPListItem> books = list.GetItems(query).OfType<SPListItem>(); return books; }
In this example, the list is obtained from the current SPWeb
instance. A new SPQuery
object is instantiated to contain the query. The query is written as an XML string fragment.
With CAML, you can easily build your own custom queries, including conditions (Where
) and logical operators (AND, OR) in XML format. Internally, SharePoint translates the CAML query into a SQL query to retrieve the data from the SharePoint content database. You can consider CAML as a necessary abstraction layer to the database—necessary because it is responsible for security trimming and caching. A SQL query to the content database returns all queried items, even though the user who made the request may not be permitted to view the items. "Security trimming" means filtering out the items the user lacks permission to see. This is done internally by SharePoint's data access layer. There are also some caching mechanisms implemented that ensure that if the same CAML query is executed in quick succession, no direct database access has to be made after the first call.
CAML allows single quotes (') for attributes. Using this style, you can write complete queries without escaping double quotes within the data, resulting in highly readable XML snippets within the C# code.
One big disadvantage of manually constructed CAML queries, such as the one shown in Listing 4-30, is a high error rate. If you make one small mistake, such as encapsulating your CAML query within <Query></Query>
tags, the query will execute without errors, but it will return all items instead of only those items whose Publisher is APress. This result can be very dangerous and subtle, and it can be time-consuming to find the error. There are alternative ways to construct a CAML query.
You can use LINQ to XML and the XElement
type to create a query:
SPQuery queryl = new SPQuery(); queryl.Query = new XElement("Where", new XElement("Eq", new XElement("FieldRef", new XAttribute("Name", "Company")), new XElement("Value", new XAttribute("Type", "Text")))) .ToString();
While this approach takes care of closing tags correctly and will always create valid XML, it does not seem easier. The CAML keywords are strings, and hence they can suffer from typing errors. You might consider using constants to predefine such keywords:
const string WHERE = "Where"; const string EQ = "Eq"; const string FIELDREF = "FieldRef"; const string VALUE = "Value"; const string NAME = "Name"; const string TYPE = "Type"; SPQuery queryc = new SPQuery(); queryc.Query = new XElement(WHERE, new XElement(EQ, new XElement(FIELDREF, new XAttribute(NAME, "Company")), new XElement(VALUE, new XAttribute(TYPE, "Text")))) .ToString();
That's indeed safe and compact, but it's still a manually built query. To overcome the manual construction of CAML queries, there are some useful implementations of CAML query builders that support creating a CAML query in a safe manner. To demonstrate this, check out an example using CAML.NET from John Holliday, which is a free project hosted on CodePlex (www.codeplex.com/camldotnet
). Listing 4-30 shows how to use it.
Example 4.30. Simple CAML Query Using CAML.NET Query Builder
public IEnumerable<SPListItem> GetBooksFromAPress2() { SPList list = SPContext.Current.Web.Lists["Books"]; SPQuery query = new SPQuery(); query.Query = CAML.Where( CAML.Eq( CAML.FieldRef("Publisher"), CAML.Value("APress") ) ); IEnumerable<SPListItem> books = list.GetItems(query).OfType<SPListItem>(); return books; }
If you need to use many manually constructed CAML queries, we strongly recommend using a CAML query builder such as CAML.NET. This is the least error-prone and most efficient method.
The primary schema starts with a <Query>
element that resembles the structure of a SQL query:
<Query> <Where> ... </Where> <GroupBy> ... </GroupBy> <OrderBy> ... </OrderBy> </Query>
The <GroupBy>
and <OrderBy>
elements are optional. Within the elements a field reference is required. For the <Where>
part, a more complex clause can be constructed that contains static values.
Referencing Fields and Values
To reference a field, use the <FieldRef>
element. The Name
attribute is compulsory. The element is also used in other CAML dialects, but several of its attributes are not supported in data queries. Table 4-8 shows the subset that are used for data retrieval. Boolean types are expressed by either TRUE
or FALSE
strings.
Table 4.8. FieldRef Attributes for Data Queries
Attribute | Type | Description |
---|---|---|
| Text | The field's name |
| Guid | The field's internal ID (optional) |
| Boolean | Determines that the value is a reference to a lookup list by its ID, if set to |
| Boolean | Determines that the value is a reference to a lookup list by its value, if set to |
The <Value>
element is used to reference static values against which the field's content is compared. In a query, this would look like the following for a simple field:
<Query> <Where> <Or> <IsNull> <FieldRef Name="Expires" /> </IsNull> <Geq>
<FieldRef Name="Expires" /> <Value Type="DateTime"> <Today /> </Value> </Geq> </Or> </Where> <OrderBy> <FieldRef Name="Modified" Ascending="FALSE" /> </OrderBy> </Query>
Using lookups is more complex—the value's type must be set to Lookup
. If the lookup uses the lookup table's ID field, set the LookupId="TRUE"
attribute of the <FieldRef>
element:
<Query> <Where> <Eq> <FieldRef Name="CatalogItem" LookupId="TRUE" /> <Value Type="Lookup">1</Value> </Eq> </Where> </Query>
To use comparison operators, you first need to join them. Logical joins are accomplished by the <Or>
and <And>
elements. Both elements accept two child elements. If you need to create more branches, you can use another logical join. For a three-part <Or>
clause, this would look like the following:
<Or> <Or> <Eq> <FieldRef /> <Value /> </Eq> <Eq> <FieldRef /> <Value /> </Eq> </Or> <Eq> <FieldRef /> <Value /> </Eq> </Or>
The equality operator <Eq>
is used merely to express the syntax. Table 4-9 lists all such operators.
Table 4.9. Operators for CAML Queries
Description | |
---|---|
| Checks whether a string (Text) begins with another string |
| Checks whether a string (Text) contains another string |
| Checks that a date overlaps another date range |
| Equal to |
| Greater than or equal to |
| Greater than |
| Is true if the field's value is not null |
| Is true if the field's value is null |
| Less than or equal to |
| Less than |
| Checks the membership of a user |
| Not equal to |
Joins between tables are well known in relational databases. The complexity and power of SharePoint lists overcome the need for frequent usage of joins. However, when working with views, you will find that it's occasionally desirable to add the odd field from another list. Lookups are a good way to do this, but they require navigating from the parent list to the related list in order to view fields. If you want to create a view that spans several lists and show the results in just one list, you need to use joins. Direct joins are a new feature in SharePoint 2010. Projections define the fields that become part of the result set's field list. This is comparable with the mapping of fields in the SELECT clause in SQL.
Though this may look like a replacement for lookups, it does not eliminate the need for them. A join uses a field reference previously defined by a lookup. Thus, you have to create a lookup field first.
Regular lookups can be multilookups—meaning that you can form a many-to-many relationship. Using joins, this is not possible; you must use a single-value lookup.
Joins and projections have a direct representation in views and queries. This includes the corresponding properties—Joins
and ProjectedField
—of the SPQuery
and SPView
objects. While both objects use CAML, the XML snippets do not become part of a regular query, and they are not child elements of the <Query>
element. Instead, the root elements are <Joins>
and <ProjectedField>
, respectively. The XML is assigned to the properties of the SPQuery
and SPView
objects without their respective root elements.
If you use joins and projections in list definitions, the root elements <Joins>
, <ProjectedFields>
, and <ViewFields>
are required. If you assign the same XML to the respective properties of an SPView
or SPQuery
object, you must omit the root elements.
Joins
From SQL you may know that there are different kinds of joins. In CAML you can define two kinds of joins, INNER
and LEFT
, using the Type
attribute:
<Joins> <Join Type="INNER" ListAlias="Authors"> <Eq> <FieldRef Name="FullName" RefType="Id" /> <FieldRef List="Authors" RefType="Id" /> </Eq> </Join> </Joins>
An inner join links two lists using an existing lookup. The result is a combination of both result sets. A left join (known in SQL as a LEFT OUTER JOIN
) will return the same combination but includes the result of the parent list even if the related list does not have a matching entry. The common right join available in SQL is not supported by CAML. However, you can create an opposite lookup and reverse the parent and child lists before applying a left join.
As with SQL, in CAML you can combine several joins together. A chain of joins works like a JOIN
statement in SQL but without the additional parentheses. A join B join C
means that B acts as a parent for C, and A acts as a parent for the result from B and C. The default limitation for a chain of joins is eight. You can override this by setting the MaxQueryLookupFields
property of the SPWebApplication
object. However, keep in mind that the result set can become very large when using joined lists, and the more joins you have, the more data must be retrieved. Having more than a few (three or four) joins probably indicates an architectural mistake in your data model.
The meaning and usage of the ListAlias
attribute (see the Join
element in the previous sample) is worthy of a closer look. As described on MSDN, the second FieldRef
, which references the actual list, must have the same name as the ListAlias
attribute. That's irritating, as the term alias suggests that you can replace the internal name with something more descriptive. This is not so for the first join of chained joins or for the only join when there is just one. The name of the list must be defined using this List
attribute, and the alias must correctly echo this name. If you have a second (subsequent) join to the same list that joins through to another, a conflict in the reference attribute occurs. The alias is used to resolve the conflict. However, the second join still needs a reference to an actual list. Therefore, the first FieldRef
element gets yet another element List
that tells the parser to which list it refers.
The ProjectedFields
element enables fields from the foreign lists to be viewed in the list view. The fields must also be identified in the ViewFields
child element of the View
element. The foreign lists are identified by their aliases, as defined in the Joins
element. Again, if there is only one join, then there is no alias that is different from the list's internal name. In this case, the reference in ViewFields
uses the actual list name.
The ShowField
attribute identifies which field from the foreign list is used in the view. This must be the internal name. The Type
attribute always has the value Lookup
—it does not indicate the data type of the field. The source type of a projected field is limited to a number of simple types:
Calculated
(converted into plain text)
ContentTypeId
Counter
Currency
DateTime
Guid
Integer
Note
(first line of multiline text only)
Number
Text
The ViewFields
elements refer to the name of a field as defined in the ProjectedFields
' Field
element.
Example: Using Joins and Projected Fields
The following example shows how to use joins and projections. It is a console application that uses two lists: Books and Authors. The Books list relates to Authors through a field called LeadAuthor. The Authors list derives from Contacts and has an additional field called FullName.
The application (Listing 4-31) retrieves data from these two lists.
Example 4.31. A Console Application That Joins Two Lists
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SharePoint; using System.Xml; namespace Apress.SP2010.CAMLJoins { class Program { static void Main(string[] args) { using (SPSite site = new SPSite("http://sharepointserve")) {
using (SPWeb web = site.OpenWeb()) { SPList bookList = web.Lists["Books"]; SPList authList = web.Lists["Authors"]; SPField la = bookList.Fields["LeadAuthor"]; SPField fa = authList.Fields["Full Name"]; string join = @"<Join Type='LEFT' ListAlias='Authors'> <Eq> <FieldRef Name='" + la.InternalName + @"' RefType='ID' /> <FieldRef List='Authors' Name='ID' /> </Eq> </Join>"; string projField = @"<Field Name='Fullname' Type='Lookup' List='Authors' ShowField='" + fa.InternalName + "' />"; SPQuery query = new SPQuery(); query.Query = ""; query.Joins = join; query.ProjectedFields = projField; query.ViewFields = @"<FieldRef Name='Fullname' /> <FieldRef Name='Title' />"; SPListItemCollection items = bookList.GetItems(query); foreach (SPListItem item in items) { Console.WriteLine("{0} has these lead authors: ", item.Title); if (item["Fullname"] == null) { Console.WriteLine(" no authors assigned"); continue; } SPFieldLookupValue sc = new SPFieldLookupValue(item["Fullname"].ToString()); Console.WriteLine(" - {0}", sc.LookupValue); } } } Console.ReadLine(); } } }
The join is from the parent list Books to the child list Authors. This is defined by List='Authors'
in the join's second FieldRef
element. The ListAlias
attribute must echo this, as it is the only join. The first FieldRef
refers to the parent list's lookup field, using the internal name. It's read from the SPField
object that has a property InternalName
.
The variable pfld
stores the projected fields. The declaration contains one field, FullName, from the Authors list. ShowField
is also defined using the internal name. The name defined in the Name
attribute is used in the ViewField
definition. The Title
field is also added to the result set. The XML snippets are assigned to the appropriate properties of SPQuery
.
The GetItems
method executes the query. Because item["Fullname"]
returns a lookup, the value it returns is in the form of "#1;Joerg Krause." To process it, we create an SPFieldLookupValue
object, passing the data as a string into the constructor to re-create the lookup value. From this object the LookupValue
returns the full name. Figure 4-15 shows the output.
If you create joins and projections in a site template or in the schema.xml
of a list definition, you can use the same syntax, as shown earlier. Because the XML is now placed somewhere in between all the other schema elements, the root elements <Joins>
, <ProjectedFields>
, and <ViewFields>
must be added appropriately.
You can find a thorough explanation of templates in Chapter 7.
LINQ is a technology that integrates data queries directly into the programming language. Consequently, queries are type-safe and checked by the compiler. Before LINQ, you had to use string queries, either T-SQL against a SQL Server or CAML, to query SharePoint. Using strings, the compiler has no way to validate the queries or the returned objects.
In this section we'll give a brief overview about LINQ and how it works. You can skip this section if you're already familiar with it. The following "Queryable SharePoint Objects" section examines in detail the specific ways to query SharePoint data in a type-safe manner.
LINQ fills a gap between the worlds of data and code. Database queries written in the programming language improves both the programmer's productivity and the compiler's ability to help. LINQ itself is largely independent of the underlying data source. A data source specific API transforms LINQ queries into the format required by the data source. LINQ consists of an extension framework within the compiler. This means that there are no additions to the Common Language Runtime (CLR). The .NET 2.0 CLR is sufficient to run LINQ-based programs, and thus SharePoint is able to use it, too. However, the C# compiler must be at least version 3.0 to support the various extensions.
A few base technologies are used, and at points in the book we'll refer to them:
Type inference
Anonymous types
Object initializer
Extension methods
Lambda expressions
Expression trees
Relaxed delegates
Nullables
It is essential that you know at least the fundamental architecture of LINQ. Each data source has its own library containing methods to connect, retrieve, and update data. Figure 4-16 shows this.
LINQ includes a well-defined set of keywords that is known to the API and the language compiler. This provides an abstraction level that allows you to write LINQ queries without needing to know the internals of SharePoint data object retrieval. However, once the objects have been fetched successfully, you need to work with them. From this point, LINQ drops out of the picture, and you're back into the world of CAML and SharePoint objects.
LINQ uses keywords that are independent of the data source. The C# compiler supports this directly with new language keywords. The next example shows a simple array definition and a LINQ query to retrieve a filtered set from the array elements:
var data = new [] {0,1,2,3,4,5,6,7,8,9,10}; var res = from e in data where e < 5 orderby e select e;
This query fetches all the numbers less than 5 from the array and stores them in the variable res. The keyword var
is a variable type. Do not confuse this with object
. A var
type is set to a specific type at compile time by the compiler implicitly and is strongly typed then. That makes it somewhat type-safe, but you need not worry about the type before you get the data back. That's indeed crucial for working with SharePoint. Imagine you read data from a list. The list's columns might change depending on the query. The receiving variable must be robust enough to handle this. On the other hand, it's obviously a good idea to get the data from named properties instead of just string names.
The particular meaning of this small example doesn't matter here—rather, the internal structure is significant:
from e
defines the element's name (or in database terms, the name of a row).
in data
chooses the query data.
where e<5
defines the condition.
orderby e
adds sorting as a optional operation.
select e
selects the element to be returned.
This syntax is geared toward human readability. from
and in
are mandatory and must appear in that order. The compiler converts this into the corresponding lambda expressions. These are anonymous functions that represent queries internally. If you prefer, you could write such expressions directly:
IEnumerable<int> res = data.where(e => e < 5).orderby(e => e).select(e => e);
From this format with anonymous methods, the compiler can then compile the expression into IL code.
Each extension method returns at least an object that implements the IEnumerable<T>
interface. Some queries also return the IQueryable<T>
interface that provides additional features. This means that every query returns enumerable objects—like a collection. Even if the collection has only one element, it's still a collection. To access a single element, you can either use an iterator keyword, such as for
or foreach
, or use extension methods to get the first, last, or any other element that matches particular conditions.
The operators are similar to SQL operators. Table 4-10 summarizes them.
Table 4.10. LINQ Operators and Their Corresponding C# Syntax
C# LINQ Expression | ||
---|---|---|
| group ... by ... | group ... by ... into ... |
| join ... in ... on ... equals ... into ... | |
| join ... in ... on ... equals ... | |
| orderby ... | |
| orderby ... descending | |
| select | |
| from ... in ... from ... in ... | |
| orderby ... , ... | |
| orderby ... , ... descending | |
| where ... |
Before you start querying SharePoint, you should have a basic understanding of LINQ. Let's examine some examples. The use of where
to add a condition to a query is the most common scenario:
int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };
var res =
from n in numbers
where n > 5
select n;
To sort the results, add the orderby
keyword:
var res =
from n in numbers
where n > 5
orderby n
select n;
Because each keyword is translated into its extension method counterpart, you could write the previous example as follows:
var res = (from n in numbers where n > 5 select n).OrderBy(x => x);
Grouping follows a similar format, using the GroupBy
operator.
var res =
from n in numbers
orderby n
group n by n > 5 into g
select new { GreaterFive = g.Key, Numbers = g};
In this example, the numbers are grouped into two groups. One group matches the condition n > 5
, and the other group contains the leftovers. There are numerous ways to return the data. Here the select
clause creates an anonymous type with two properties: GreaterFive
and Numbers
. GreaterFive
contains the group's key to identify it later, while the property Numbers
contains the retrieved data itself. Remember that the whole statement returns an enumerable object. In this case, the grouping would return two elements. Let's examine a more sophisticated example that deals with remainders:
var res = from n in numbers group n by n % 3 into g select new { Class = g.Key, Numbers = g};
Here the numbers are grouped together in classes dependent on the remainder after they are divided by three.
Anonymous types do not imply they are "untyped." The type is not explicitly defined in code, unlike a class. As soon as the data is assigned in the statement, the type is "safe." You cannot change it later. Hence, creating new enumerable constant data is easy:
var people = new [] { new {Name = "Langhirt", GivenName = "Christian", Author=true}, new {Name = "Krause", GivenName = "Jörg", Author=true}, new {Name = "Meurisch", GivenName = "Jörg", Author=false} }; var authors = from person in people where person.Author select new { Name=person.GivenName, person.Name };
The anonymous type is created with the new
statement. The variable people
is an array containing three objects. The object has three public properties. The types are inferred by the compiler and set to string
, string
, and bool
, respectively.
If you want a specific person from the list, a suitable function is used. The first element could be retrieved like this:
string FirstAuthor = people.First().GivenName;
The more complex your questions, the more such operators are needed. Fortunately, LINQ comes with many of them. To get a selection of combined data, the SelectMany
operator is helpful:
var books = new[] { new {Title = "ASP.NET Professional", Authors = Ã new[] {new {Name = "Fischer"}, new {Name = "Krause"}}}, new {Title = "Windows Communication Foundation (WCF)", Authors = Ã new[] {new {Name = "Fischer"}, new {Name = "Krause"}}}, new {Title = ".NET 3.5", Authors = Ã new[] {new {Name = "Fischer"}, new {Name = "Krause"}}}, };
var publications = from book in books where book.Authors.Count() > 0 from author in book.Authors select new { book.Title, author.Name };
If you have two lists and desire a cross-joined table, LINQ is even able to handle this:
int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; int[] k = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; var cross = from x in k where x > 2 from y in i where y > 3 select new {x, y, product = x*y};
Remember that cross joins return the multiplied number of elements from both tables. In the example, you have 8 times 7 numbers, causing 56 elements being returned. More likely, you will deal with regular joins that follow a defined condition.
var names = new[] { new {Name = "Fischer", id = 1}, new {Name = "Krause", id = 2} }; var givennames = new[] { new {GivenName = "Jörg", id = 2}, new {GivenName = "Matthias", id = 1} }; var persons = from name in names join givenname in givennames on name.id equals givenname.id select new {givenname.GivenName, name.Name};
Aggregators are functions that reduce enumerable types. The most common is Count
.
int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; int count = i.Count();
The LINQ library provides some other aggregate operators, such as Sum<T>()
, Min<T>()
, Max<T>()
, and Average<T>()
. The basic methods can handle scalar types only. To deal with complex types, you can use overloads that accept lambda expressions. The expressions resolve the data to be transformed in a way the aggregator can handle it, for example:
var objects = new [] { new {number = 0}, new {number = 1}, new {number = 2}, new {number = 3},
new {number = 4} }; int sum = objects.Sum(x => x.number);
You will often find that a query returns only one element. Even so, the query is built using either IQuerable
or IEnumable
. Both are enumerable and appear as collections. To extract the one and only element from the collection, the First
selector is the best approach. This can fail, though, if the collection is empty. Using FirstOrDefault
calls the default(T)
method if there are no elements. You will usually get a new, empty element back.
int[] a = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 }.First();
This example returns the number 5.
int[] a = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 }; int res = (from n in a where (n > 3 && (n & 1) == 1) orderby n select n).FirstOrDefault();
To use a lambda expression, the same selectors are used:
int res = (a.OrderBy(n => n)).FirstOrDefault(n => (n > 3 && (n & 1) == 1));
You can retrieve the last element with Last<T>()
and LastOrDefault<T>()
, respectively. However, when processing large amounts of data, more functions are required. The extension methods Take<T>(int n)
and Skip<T>(int n)
either aid in selecting the first specified element or skip a number of elements and begin looping through the rest after this position. Using lambda expressions, this is all possible in a conditional manner using SkipWhile<T>(Func<T,T,bool>)
and TakeWhile<T>(Func<T,T,bool>)
.
int[] i = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; var r1 = i.Skip(5); // Skip 5 elements var r2 = i.Take(6); // Take 6 elements var r3 = i.SkipWhile(x => x < 6); // Skip all with x less than 6 var r4 = i.TakeWhile(x => x > 6); // Take all x greater than 6
The SharePoint API gives you direct and powerful access to the data stored in lists and libraries. While this conventional access looks good, the flexibility and elegance of LINQ is particularly appealing.
LINQ is, as previously explained, a complete data abstraction layer. It's a provider between the data source (SharePoint) and the API. Its ability to extend the programming language and the tight integration is a big advantage while programming against SharePoint. You can regard LINQ as an object-relational mapper (OR mapper), defining entities to express your business object model. The LINQ to SharePoint provider uses entity classes to make the relational list model available in the object-oriented world of C#. This includes such features as object change tracking and deferred (or "lazy") loading. The primary feature is the abstraction between the SharePoint API and the object model. First, the object model can be closer to your business logic and a better expression of what you want to see as a developer. Second, the object model is robust against future changes of the API.
The LINQ to SharePoint provider is defined in the Microsoft.SharePoint.Linq
namespace. It translates LINQ queries into CAML queries, which means that you do not need to know how to write CAML queries or learn a different query language for each type of data source. In addition, LINQ queries can be executed against both the server and client operating models.
The gateway class for the LINQ to SharePoint provider is Microsoft.SharePoint.Linq.DataContext
. It is equivalent to the System.Data.Linq.DataContext
class in the LINQ to SQL provider. Just as the latter class has a GetTable
method, which returns a Table<T>
object that implements the IQueryable<T>
interface, DataContext
class has a GetList
method. It returns an EntityList<T>
type, which in turn implements the IQueryable<T>
interface.
The following is an example of how to use LINQ to query a SharePoint list:
// Get DataContext from page context
DataContext data = new DataContext(SPContext.GetContext(this.Context).Web.Url);// Get the SharePoint list
EntityList<Customer> Customers = data.GetList<Customer>("Lists/Customers");// Query for customers from London
var londonCustomers = from customer in Customers where customer.City == "London" select customer;
You can now bind these data to some sort of control.
Query Joined Lists
One important aspect of the LINQ to SharePoint provider is that queries can be inefficient even if they appear to be well written and they produce valid results. An inefficient query is defined as one that requires more queries to the server than there are lists referenced in the query. For example, if there is a subquery to list B for every row of list A, the query is inefficient. The LINQ to SharePoint provider comes with a restriction. Such queries are not supported. If you require such a style of execution, use nested loops (and accept that it is suboptimal).
Queries that assume an implicit join between two SharePoint lists will be supported if and only if the joining field is configured as a lookup field in the SharePoint list.
Restrictions of LINQ to SharePoint Provider
The provider does not supported two LINQ operators:
ElementAt
ElementAtOrDefault
Some queries cannot be completely translated into CAML. However, it is possible to execute such a query by downloading the entire SharePoint list to the client and enumerating through it to carry out the non-CAML parts of the query. You can specify whether to allow such queries by setting the DataContext.AllowInefficientQueries
property to true
. It is obvious that this is not an efficient way to retrieve data.
Assume you have a LINQ select clause like the following:
select new { c.Name, c.ZipCode }
This query would translate into CAML as a <ViewFields>
tag with two <FieldRef>
child elements. But the following select
clause contains a mathematical function that is not supported in CAML:
select new { c.Price*2, c.Orders, c.Customer }
A query that contains a mathematical function or another unsupported aggregator function clause is considered inefficient. If inefficient queries have been enabled, the query will be executed, but only up to the select
clause, by LINQ to SharePoint on the server. Then the results that are received from the CAML query are sent to the client as an IEnumerable
object. A new LINQ query on the client will then execute the select
clause projection on the object by using the System.Linq.Enumerable.Select
method. From here on, the LINQ to Object provider takes over and finishes the querying. It's obvious that this handover is less efficient.
The following LINQ operators are semi-efficient:
Aggregate
All
Any
Average
Distinct
Except
Intersect
Join
Max
Min
Reverse
SequenceEqual
Skip
SkipWhile
Sum
Semi-efficient means that the provider will probably additionally to the CAML query one or more LINQ to Object operations to fulfill the task. That means additional memory consumption and CPU resources. Such queries are also called two-stage queries.
Entity Classes and the SPMetal Tool
As the LINQ to SharePoint provider gives typed access to list data, there must be a way to retrieve the column information. Reading it at runtime would decrease performance appreciably. As with other LINQ providers, the solution is a proxy class. To create such a class, a command-line tool is used. This tool, called SPMetal
, reads the SharePoint list's definition and generates a proxy class. This autogenerated class could look like the following:
[List(Name="Customers")] partial public class Customer { [Column(Id=true)] public int CustomerId; [Column] public string City; }
You can of course write such classes by hand, but using the tool is very helpful. It's part of the SDK for SharePoint 2010. (Refer to the section "Creating the Layer with SPMetal.exe" later in this chapter.)
To ensure that the proxy classes are up-to-date, you should plan to call SPMetal
in a prebuild event and read the most current list definitions.
Creating list proxies is probably not the most efficient way. It is of course a way that will work just fine out of the box.
You can use the gateway class, DataContext
, to write changes to the SharePoint content database. Simply call the DataContext.SubmitChanges
method. The following is an example demonstrating how to add an item to a list and save the changes to the database:
Customer customer = new Customer(); DataContext data = new DataContext(SPContext.GetContext(this.Context).Web.Url); EntityList<Customer> Customers = data.GetList<Customer>("Lists/Customers"); // write data into "customer" object Customers.Add(customer); data.SubmitChanges();
The LINQ to SharePoint provider keeps track of all entities that are returned by queries and all changes to those entities. When a specified entity is returned more than once, the provider will always return the same instance of the entity that it returned the first time. This behavior ensures that an application is always working with the same instance of a specified entity and that it never works with an entity that has been changed by another application.
When SubmitChanges
executes, it compares the current state of the entity in the database with the state of the entity when it was first returned. If there is a discrepancy, then some other application has changed the entity after the first return. You can configure the behavior of SubmitChanges
to stop writing more changes when it finds the first discrepancy, or you can set it to continue writing, regardless of discrepancies.
SPMetal
is a command-line tool that generates entity classes, which provide an object-oriented interface to the content databases. These classes are primarily used in LINQ to SharePoint queries; but they are also be used to add, delete, and change list items with protection against concurrency conflicts. They can be used as an alternative to the regular SharePoint object model for accessing data.
The tool is included with SharePoint Foundation and is usually located here:
%ProgramFiles%Common FilesMicrosoft Sharedweb server extensions14in
You can use this tool in any kind of batch file, typically as a prebuild command in Visual Studio. The generated code should be placed somewhere in the project to make it available for subsequent building steps.
SPMetal
reads the custom lists found in the specified site and creates a class. The class is marked partial
so that you can add members without worrying that your code could be overwritten by subsequent building steps. SPMetal has several options to modify the code generation:
/web:<site>
. The URL of the <site>
from which you want to retrieve the lists.
/code:<file.cs>
. The name of the output file for the generated code. If omitted, the code is emitted to the console.
/language:<lang>
. Either csharp
or vb
—if omitted, the language is used that fits the output file extension.
/namespace:<ns>
. The namespace that wraps around the code.
/user:<user>
and /password:<pass>
. User and password credentials for the site.
/serialization:<none|unidirectional>
. If the value unidirectional
is used, the generated code is serializable. The default is none
.
A typical command with parameters looks like this:
SPMetal /web:http://sharepointserve /code:Authors.cs /namespace:Apress.Sp2010.Linq
This call will generate the code in C# within the current directory.
If a parameter for an SPMetal
option contain spaces, you must enclose it in quotes.
SPMetal
has a few parameters, as described earlier. Several defaults apply for the process of code generation. To override those values, parameters can be supplied via an XML file. A typical file looks like this:
<?xml version="1.0" encoding="utf-8"?> <Web AccessModifier="Internal" xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal"> <ContentType Name="Contact" Class="Contact"> <Column Name="ContId" Member="ContactId" /> <Column Name="ContactName" Member="ContactName1" /> <Column Name="Category" Member="Cat" Type="String"/> <ExcludeColumn Name="HomeTelephone" /> </ContentType> <ExcludeContentType Name="Order"/> <List Name="Team Members" Type="TeamMember"> <ContentType Name="Item" Class="TeamMember" /> </List> </Web>
The <Web>
element is always the root. It supports two attributes:
AccessModifier
: Modifies the access modifier that defaults to public
. The only allowed values are Internal
and Public
.
Class
: Name of the generated class (overwrites the "xxxDataContext" name).
Within the <Web>
element, several other elements can be used to modify the behavior. Use the <List>
element to manage lists. It supports two operations. First, you can include lists that SPMetal
would not model by default, such as hidden lists. Second, you can change the name SPMetal
uses—by default, the list's name. Supported attributes are as follows:
Name
: The name of the list—this attribute is mandatory.
Member
: Specifies an alternate name.
Type
: Alternate type used as the list's base type. You can define the type in your custom code, and this type is used as the parent to the list's type. This is to allow custom base classes that expose additional functionality.
While <List>
can include lists, <ExcludeList>
excludes lists SPMetal
would normally model by default. The only attribute is Name
. If you want to exclude all lists and include only a few specific ones, add an empty <ExcludeOtherLists />
element and use the appropriate <List>
elements. If you want to include all hidden lists, add the <IncludeHiddenLists />
element. This element cannot be used together with <ExludeOtherLists />
.
Lists are made of columns, and several columns are defined in content types. The <ContentType>
element can be used globally (under <Web>
) or within the scope of a list (under <List>
). Two attributes are supported:
Name
: Name of the content type. Use Item
as the basic type the Foundation provides.
Class
: The alternate class name.
To modify the <ContentType>
element, columns can be added to or removed from the model using the <Column>
and <ExcludeColumn>
elements, respectively. An empty <ExcludeOtherColumns />
element can exclude all columns. If you want to include all hidden columns the <IncludeHiddenColumns />
element is used. To have the same function for the whole content type, the <ExcludeOtherContentType />
element can exclude all, and <IncludeHiddenContentType />
includes those with the Hidden
attribute.
Once the XML file is created with the appropriate values, you can use the following syntax to assign the file:
spmetal /parameters:<path>params.xml
The basic parameters are still required and must be provided as described earlier.
SPMetal
has some basic rules that apply (except where the particular default rule has been overridden). First, any class is marked public
by default. You can override this with the AccessModifier
attribute of the <Web>
element. Second, the class is named FileNameDataContext
, where FileName
is the name of the file without the file extension that is specified by the /code
option on the SPMetal
command. For example, if /code:Personnel.cs
is on the command line, the class is called PersonnelDataContext
. You can override this with the Class
attribute of the <Web>
element. A property is generated in the DataContext
-derived class for every nonhidden list in the web site. This can be overridden with the <ExcludeList>
or <ExcludeOtherLists>
elements. The type of the property is EntityList<(Of <(TEntity>)>)
. These properties are marked public
. No property is generated for hidden lists. The <IncludeHiddenLists>
element or a <List>
element whose Name
attribute is assigned to a hidden list can be used to override this behavior. A property that represents a list has the same name as the list with three exceptions. First, if there are spaces in the list name, they are removed from the property name. Note that this is different from the behavior SharePoint uses internally, such as replacing spaces with the _x0020_
string. Second, if the first letter of the list name is lowercase, it is capitalized in the property name. Third, if SPMetal
judges the list name to be an English singular term, it attempts to give the property a plural version of the name. In several common cases, this is doomed, because the complex and arbitrary English pluralization rules such as child/children are not recognized correctly. We suggest overriding any tricky names with suitable values in the XML parameter file, as described earlier.
The type parameter, T
, of the EntityList<(Of <(TEntity>)>)<T>
property is the content type class generated for the list. If there is more than one content type associated with the list, T
is the class that represents the basic Item
content type of SharePoint. Use the Type
attribute of the <List>
element to override this.
A content type class named Item
is generated to represent the basic SharePoint content type. You can override this with the <ExcludeContentType>
element for that content type. For any list that has not been excluded, a content type class is generated for every defined content type assigned to the list. Again, overriding this behavior is possible using the <ExcludeContentType>
or <ExcludeOtherContentTypes>
element. For any defined content type, including the basic Item
content type, the generated class has the same name as the content type. You can override with the Class
attribute of a <ContentType>
element. For any list that does not use a defined content type but to which one or more columns have been added, a class is generated to represent the implied content type. This means that the generator assumes a generic content type to use the same rules. The class that represents an implied content type will be named listpropertynameItem
, where listpropertyname
is the name of the property that represents the list.
If a content type inherits from another, the generator creates the same hierarchy for the classes. The classes are marked both partial
and public
. Use the AccessModifier
attribute of a <ContentType>
element to override this behavior. There is no way to suppress the partial
modifier.
The class that represents the basic Item
content type implements ITrackEntityState
, ITrackOriginalValues
, INotifyPropertyChanged
, and INotifyPropertyChanging
interfaces. A property is generated in a content type class for every nonhidden field. You can override this with the <ExcludeColumn>
or <ExcludeOtherColumns>
element. The property is marked public
. It has the same name as the column it represents, but spaces in a column name are removed.
If the first letter in the column name is lowercase, it is made uppercase in the property name. The type of a column property is determined from the property of the field in accordance with the mapping presented in SharePoint to .NET Type Mapping. You can override this with the Type
attribute of a <Column>
element. No property is generated for columns that are on the list, but not in the content type. The same applies to hidden columns, except the hidden Id
and Version
columns, which are always present.
For every Choice field that does not allow Fill-in choices, an enumerated class is generated. For every MultiChoice field that does not allow Fill-in choices and has fewer than 31 choices, an enumerated class decorated with the [Flags]
attribute is generated. The values Invalid and None are added as possible values to the enumerations. Additionally, a sealed class is generated with a String
constant for each defined value.
To represent lookup list relationship fields' names, EntityRef
, EntitySet
, and LookupList
are generated. For every column that is a lookup to a field on another list and that does not allow multiple values, the property that represents the column wraps a private field of type EntityRef<(Of <(TEntity>)>)<T>
, where T
is the content type of the lookup list. If the lookup to a field allows multiple values, the property that represents the column wraps a private field of type EntitySet<(Of <(TEntity>)>)<T>
, where T
is the content type of the lookup list.
When talking about the content type and its representation in .NET classes, the type mapping is worth a closer look. Table 4-11 shows the complete mapping list.
Table 4.11. Type Mapping Between Field Types and Common Type System (CTS)
Field Type | CTS Type |
---|---|
AllDayEvent |
|
Attachments |
|
Boolean |
|
Calculated | Type of the returned value, |
Choice |
|
Computed |
|
ContentTypeId |
|
Counter | Int32 |
CrossProjectLink |
|
Currency |
|
DateTime |
|
File |
|
GridChoice |
|
Guid |
|
Integer |
|
Lookup |
|
MaxItems |
|
ModStat |
|
MultiChoice | Flagged |
Note |
|
| |
PageSeparator |
|
Recurrence |
|
Text |
|
ThreadIndex |
|
Threading |
|
URL |
|
User |
|
WorkflowEventType |
|
WorkflowStatus |
|
With this information in mind, using LINQ with SharePoint seems to be easy and smart.
All LINQ to SharePoint code begins with the data context. In this section all examples use the same context, stored in the variable ctx
. The context is created on a per-site basis, so it doesn't make sense to refer to specific lists.
DatacontextDataContext ctx = new DatacontextDataContext("http://sharepointserve");
The examples are in one console application and use simple console output to show data.
Assuming that a proxy class created by SPMetal
exists, the following example (see Listing 4-32) shows the entries of items in the list named Authors.
Example 4.32. Complete Example That Retrieves Data Using LINQ
using System; using System.Linq; using Microsoft.SharePoint; namespace Apress.SP2010.Linq { class Program {
static void Main(string[] args) { using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { DatacontextDataContext ctx = new DatacontextDataContext("http://sharepointserve/"); var authors = from a in ctx.Authors select a; foreach (var ac in authors) { Console.WriteLine("{0}, {1}, {2}", ac.Company, ac.FirstName, ac.EMail); } Console.ReadLine(); } } } } }
The DatacontextDataContext
class is generated with SPMetal
using the default settings. The LINQ query does not have any options and fetches the complete list. The object is retrieved directly using the syntax select
a
, where a
is the loop variable defined in the LINQ query. In the foreach
statement, the current author is held in the loop variable ac
. The type defined by the context is AuthorsContact
, the name constructed from the current list (Author) and the base content type (Contact). The loop could also look like this:
foreach (AuthorsContact ac in authors)
However, the var
type is both short and type-safe and is the preferred way to express variables and to improve readability. Instead of accepting the type defined by the list, you can easily create a new anonymous type that fits your needs, as shown in Listing 4-33.
Example 4.33. Different Version of the Query Shown in Listing 4-32
var authors2 = from a in ctx.Authors select new { Firm = a.Company, Name = a.FullName, Mail = a.EMail }; foreach (var ac in authors2) { Console.WriteLine("{0}, {1}, {2}", ac.Firm, ac.Name, ac.Mail); }
The type created by select new
is anonymous and contains three public properties (Firm
, Name
, Mail
). The properties types are determined from the source type (a.Company
and so on). This is still type-safe, but you don't have to care about the types.
To read a joined list, you can use the join
operator. The order of the join is vital. Creating an output type is a common technique to get exactly what you want (see Listing 4-34).
Example 4.34. Retrieve Joined Data
EntityList<AuthorsContact> authorsj = ctx.GetList<AuthorsContact>("Authors");
EntityList<BooksItem> booksj = ctx.GetList<BooksItem>("Books");
var result5 = from book in booksj
join author in authorsj on book.LeadAuthor.Id equals author.Id
select new
{
Book = book.Title,
Author = author.FullName
};
result5.ToList().ForEach(ab => Console.WriteLine("{0} was written by {1}",
ab.Book,
ab.Author));
The code produces the output shown in Figure 4-17. The conversion with ToList
was used only to get access to the ForEach
method for easy output.
Another strategy is to use EntityList
to get a list representation, to which you can add new elements (see Listing 4-35).
Example 4.35. Using the EntityList Class
EntityList<AuthorsContact> list = ctx.GetList<AuthorsContact>("Authors"); foreach (var ac in list) { Console.WriteLine("{0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail); } EntityList<AuthorsContact> list2 = ctx.GetList<AuthorsContact>("Authors"); AuthorsContact newAuthor = new AuthorsContact(); newAuthor.FirstName = "Bernd"; newAuthor.Title = "Pehlke"; newAuthor.EMail = "[email protected]";
newAuthor.Company = "Computacenter";list2.InsertOnSubmit(newAuthor);
ctx.SubmitChanges();
foreach (var ac in list2) { Console.WriteLine("{0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail); }
The foreach
loop is merely to show that the element has been successfully added. The new item is created using the type AuthorsContact
and its default constructor. Once all required properties are filled in with appropriate values, the element can be added using the InsertOnSubmit
method. As the name implies, the SubmitChanges
method invoked on the data context writes the data into the SharePoint database.
Notice carefully the internal field names that are used. For instance, the Contact
content type does not have a field Last Name. The mandatory Title
field is used instead, and expressed with the DisplayName
Last Name. Consequently, in code you must use item.Title
instead of item.LastName
. To make your code safer, use the SPField
object and retrieve the InternalName
property for a particular field.
Deleting an item is similar to the insertion procedure. The item is part of a list. Hence, the DeleteOnSubmit
method can be found in the list's class, Authors
, as in Listing 4-36.
Example 4.36. Delete an Item
var authors3 = from a in ctx.Authors
where a.Title.Equals("Pehlke")
select a;
foreach (var ac in authors3)
{
ctx.Authors.DeleteOnSubmit(ac);
Console.WriteLine("Delete: {0}, {1}, {2}", ac.Company, ac.FullName, ac.EMail);
}
ctx.SubmitChanges();
The context class sends the delete task to the list with the SubmitChanges
call. Instead of deleting an item, you can use RecycleOnSubmit
to remove the item and put it into the Recycle Bin list (see Listing 4-37).
Updating an item is similar and simply requires access to a single item to change its properties and call the SubmitChanges
method.
When changing or accessing all items, a foreach
loop is the only way. All previous examples use this pattern. However, the extensibility model allows a custom definition of such a ForEach
pattern, in the same style as ForEach method supported by the List<T>
and Array<T>
types.
ctx.Authors.ForEach<AuthorsContact>(ac => ac.Company = "Microsoft"); ctx.SubmitChanges(); ctx.Authors.ForEach<AuthorsContact>(ac => Console.WriteLine("{0} at {1}", ac.FirstName, ac.Company));
In this example, a generic ForEach
method is used to invoke an Action<T>
. Each element of the collection is used to call the action defined in the lambda expression. System.Action
is a predefined delegate that has no return value. The definition encapsulates the foreach
statement:
public static class Extensions { public static void ForEach<T>(this IQueryable<T> source, Action<T> func) { foreach (var item in source) func(item); } }
This defines the ForEach
extension method for the commonly used type IQueryable
. The Action parameter takes any lambda expression. A return value—if any—is thrown away. In the previous example, the method is used to change a property and to write the content to the console. Assuming the extension method is defined once somewhere in your project, the LINQ lines are obviously shorter and nonetheless perfectly readable.
Sometimes you deal with detached data. That happens if you serialize an object and send it over the wire to some other application. If you work with Windows Communication Foundation (WCF) connections, Workflow Foundation (WF), or web services at all, this can happen. You receive back, according to the previous examples, an object of type AuthorsContact
, but it is not generated in the context of the previous class. Inserting such an object using the InsertOnSubmit
method is risky. If the same object already exists, the tracking does not matter. If all properties are not unique, this will work, but the object is now in the list twice. If the list requires unique properties, such as an ID, an exception is thrown. To avoid such behavior, you can use the Attach
method. Attaching means that the item is inserted with tracking. (See the section "Track Changes" to read more about how the conflict resolver works internally to deal with such situations.)
The basic techniques to retrieve data from lists are not sufficient in all cases in real-life projects. Sooner or later you will encounter the limitations of LINQ to SharePoint.
As shown at the beginning of the chapter, the LINQ to SharePoint provider translates the LINQ query into CAML. (The CAML query is converted again into SQL clauses.) As a result, you need to be aware of what CAML is able to express efficiently. Simple select constructs, whether they use anonymous or typed objects, are always efficient. However, if the statement contains a formula you can't express with CAML but is allowed in LINQ, the query is rejected. You can, however, take advantage of another LINQ provider—LINQ to Objects. LINQ to Objects is not recommended if the amount of data retrieved from a list is high. If you access a list's item collection directly, the whole list is held in memory. If this is on a per-session basis, it could have a detrimental effect on performance. If you put a result set into the cache, this might work, but you lose the ability to issue per-user queries.
To overcome such limitations, you can retrieve the data first using either CAML directly or LINQ to SharePoint. This should reduce the number of items drastically. The result is then copied to an IEnumerable
type that you can easily filter using LINQ to Objects. Copy your results using a method such as ToList<T>
(see Listing 4-38), ToDictonary<T>
, ToArray<T>
, or Cast<T>
.
Example 4.38. Combining LINQ to SharePoint with LINQ to Objects
DataContext data = new DataContext("http://sharepointserve"); DataContext subData = new DataContext("http://sharepointserve/SubTeamSite"); EntityList<Announcement> announcement = data.GetList<Announcement>("Announcements"); EntityList<Announcement> subannouncements = subData.GetList<Announcement>("Announcements"); List<Announcement> annsList = (from ann in announcements select ann).ToList(); List<Announcement> annsSubList = (from ann in subannouncements select ann).ToList(); IEnumerable<Announcement> allAnnotations = annsSubList.Union(annsAnns); foreach (Announcement ann in allAnnotations) { Console.WriteLine(ann.Title); }
This example uses the LINQ to SharePoint provider to get two result sets from lists. Both results are copied to List<T>
objects. They are combined using the Union
method, which SharePoint does not support directly.
Splitting queries into two parts can be cumbersome work. Fortunately, some of such operations are done internally. Such operations are called semi-efficient queries.
The generation of the LINQ layer is a onetime process. If you run your layer in an environment where users can add fields or where custom field types are used, the layer can't handle this. The reason for the good performance of the LINQ provider is at the same time its biggest weakness—the statically generated classes.
Apart from custom fields, the generator oversees public properties too. Internally SPMetal
reads the content type and its fields. Properties that the list provides by default, such as Attachments, are not recognized.
To overcome this limitation, you can use a custom mapping that provides new public properties and handle the calls to the provider's internal data handling. To attach new properties, the generated class is marked as partial
. The first step is to create a new class with the same name and is also marked as partial
. This new class implements the ICustomMapping
interface. The methods the interface requires and the properties you add create the mapping.
public partial class Book : ICustomMapping { [CustomMapping(Columns = new String[] { "ISBN", "UPCA" })] public void MapFrom(object listItem) { SPListItem item = (SPListItem)listItem; this.ISBN = item["ISBN"]; this.UPCA = item["UPCA"]; } public void MapTo(object listItem) { SPListItem item = (SPListItem)listItem; item["ISBN"] = this.ISBN; item["UPCA"] = this.UPCA; } public void Resolve(RefreshMode mode, object originalListItem, object databaseObject) { } // New property declarations go here. }
The custom mapping is managed by the CustomMapping
attribute. The Columns
array contains the mapped columns.
The public methods MapTo
and MapFrom
are not intended to be called directly from your code.
This does not address how to handle columns added by users after your code has been deployed. To handle "any" column, you can use a placeholder such as the following:
[CustomMapping(Columns = new String[] { "*" })]
The next example shows how to handle such columns properly:
[CustomMapping(Columns = new String[] { "*" })] public void MapFrom(object listItem) { SPListItem item = (SPListItem)listItem; foreach (var field in item.Fields) {
this.Properties[field.InternalName] = item[field.InternalName]; } } public void MapTo(object listItem) { SPListItem item = (SPListItem)listItem; foreach (var kvp in this.Properties) { item[kvp.Key] = this.Properties[kvp.Key]; } }
The ICustomMapping
methods can also be used to map properties to hash table entries of the Properties
property the generated class provides:
[CustomMapping(Columns = new String[] { "*" })] public void MapFrom(object listItem) { this.PreviousManager = ((SPListItem)listItem).Properties["PreviousManager"]; } public void MapTo(object listItem) { ((SPListItem)listItem).Properties["PreviousManager"] = this.PreviousManager; }
Managing Concurrency Conflicts for the New Columns
To ensure that your properties are participating in the object change tracking system, check that the set
accessor of the properties is calling the content type class's OnPropertyChanging
and OnPropertyChanged
methods, as shown in the following example. These methods are part of the code generated by SPMetal
. They handle the PropertyChanging
and PropertyChanged
events, respectively. The following is an example for one of the columns discussed earlier in this topic that uses a custom field type. Note the custom field type is ISBNField in this example.
public ISBNField ISBN { get { return iSBN; } set { if ((value != iSBN)) { this.OnPropertyChanging("ISBN", iSBN); iSBN = value; this.OnPropertyChanged("ISBN"); } } }
The LINQ to SharePoint provider checks changes made in the database against its current state. This is the default behavior. If you access the lists in a read-only manner, the tracking can be suppressed to optimize performance:
ctx.ObjectTrackingEnabled = false;
As a developer, you must always be aware of changes made by other applications, including the standard SharePoint UI. LINQ to SharePoint uses so-called optimistic concurrency to resolve conflicts. If a discrepancy has been found, the provider stops writing data back to the database. The SubmitChanges
method has a parameter ConflictMode
to control the behavior:
ctx.SubmitChanges(ConflictMode.ContinueOnConflict); ctx.SubmitChanges(ConflictMode.FailOnFirstConflict);
In either case, data is written, but if the option opts to continue any further, the same data record is tried again. Optimistic means that the operation does not fail definitely. The user can decide how to proceed and any possible actions are allowed—cancel the change or overwrite existing data. That means that your code must provide a way to inform the user and let them decide what to do or assume a default action. To help you to do the right thing, the EntityState
property is used. In a proxy class created by SPMetal
, it looks like this:
[Microsoft.SharePoint.Linq.ContentTypeAttribute(Name="Item", Id="0x01")] public partial class Item : ITrackEntityState, ITrackOriginalValues, INotifyPropertyChanged, INotifyPropertyChanging { private EntityState _entityState; private IDictionary<string, object> _originalValues; EntityState EntityState { get { return this._entityState; } set { this._entityState = value; } } IDictionary<string, object> OriginalValues { get { return this._originalValues; } set { this._originalValues = value; } } public Item()
{ this._entityState = EntityState.Unchanged; this.OnCreated(); } }
As shown in the exhibit from SPMetal
-generated code, the EntityState
property is private
. You may wonder how to get the information directly from the code. The state is relevant only during a conflict. Such a conflict throws a ChangeConflictException
. The exception details expose some information that's internally retrieved from EntityState
and OriginalValues
properties. The following example triggers the exception by writing values to the same item using two different data contexts:
AuthorDataContext ctx2 = new AuthorDataContext(ctx.Web); var a1 = (from a in ctx2.Authors where a.Title.Equals("Krause") select a).First(); a1.Title = "Krause (LastName)"; // Change same with another value in default context var a2 = (from a in ctx.Authors where a.Title.Equals("Krause") select a).First(); a2.Title = "Krause (Title)"; try { ctx2.SubmitChanges(); ctx.SubmitChanges(); } catch (Microsoft.SharePoint.Linq.ChangeConflictException ce) { foreach (var cc in ctx.ChangeConflicts) { Console.WriteLine("Conflict for {0}", cc.Object); if (cc.MemberConflicts.Count() > 0) { foreach (MemberChangeConflict mcc in cc.MemberConflicts) { Console.WriteLine(" Current: {0}, Database: {1}, Original: {2}", mcc.CurrentValue, mcc.DatabaseValue, mcc.OriginalValue); } } } }
Using a second data context, the same LINQ query ensures that the same item is fetched from the database twice. The Title
property is changed to two different values within the two separate contexts. The conflict occurs when the second SubmitChanges
method is called. This throws the ChangeConflictException
, which is caught. The ChangeConflict
property of the second context (ctx
) exposes all the information you need in order to decide how to proceed (see Figure 4-18).
As shown in the previous example, you can easily use multiple data context objects against the same site. As long as all contexts but one are read-only, there should be no conflicts. The read-only contexts could also be optimized by switching off the tracking:
ctx.ObjectTrackingEnabled = false;
If writing from multiple contexts is necessary for your business layer or if changes from other parties are expected, the ChangeConflict
property (of MemberChangeConflict
type) provides everything you need. Within the object, the property MemberConflicts
resolves the conflict separately for each field (see Table 4-12).
Table 4.12. Properties of MemberChangeConflict Type to Investigate a Conflict
Property | Description |
---|---|
| What it was before any change |
| The value currently found in the database, written by another user |
| The value set by the current operation |
| The value was modified |
| The conflict has been resolved by calling the |
|
|
To resolve a conflict, call the Resolve
method for each item in MemberConflicts
collection. The parameter expects a value from RefreshMode
enumeration:
KeepChanges
: The current value is kept, but all others changed values are updated.
KeepCurrentValues
: No value is modified— keeps what is already in the database.
OverwriteCurrentValues
: This overwrites all current values with those found in the database.
It is worth noting that all changed properties are exposed in this collection, even if only one property throws the exception. That's necessary to resolve conflicts, because returning to the original value of the conflicting item may require doing so for all or some other properties. But it's up to you to resolve multiple conflicts.
Using SPMetal
is powerful and provides a great set of features to access SharePoint lists with type-safe objects. However, under rare circumstances, it may be necessary to define your own classes. This is, typically, the case if the lists are themselves based on an external definition. Such a definition might use XML Schema (XSD) to define lists and their data types. The schema can be distributed with the feature, and it should support both the list creation as well as the data access layer using LINQ to SharePoint.
At least two attributes are required to decorate the classes and properties appropriately:
[ContentType(Name="Announcement", Id="0x0104")] public partial class Announcement { [Column(Name = "Title", FieldType = "Text", IsId=true)] public String Title { get; set; } }
In this example, the only field you can retrieve is Title
. Consequently, you are not limited to the fields defined by the underlying content type in both directions. You can add more fields, and you can leave columns you don't need. However, if a column is not defined as a property, it is not available from the LINQ provider.
When you create a lookup, you can choose to enforce referential integrity between elements in the related lists, as shown in Figure 4-19.
There are two options available here: Restrict Delete and Cascade Delete. The Restrict Delete option enables you to enforce that you cannot delete from the list any item that has related data. For example, if the item you are trying to delete has one or more child items, you cannot delete the item. Cascade Delete, on the other hand, means that when you're trying to delete an item with related data, it will delete the item and the related items as well. If there is no such option, the related data becomes orphaned.
When working with such lists, it's sometimes helpful to know about the current settings before starting a particular action (see Listing 4-39).
Example 4.39. Investigate List Settings
using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPRelatedFieldCollection RelatedFields = web.Lists["Authors"].GetRelatedFields(); foreach (SPRelatedField RelatedField in RelatedFields) { Console.WriteLine("Field <{0}>{5} bound to <{1}>{5} lookup on <{2}>{5}
SPRelationshipDeleteBehavior.{3}{5}Web <{4}>", web.Lists[RelatedField.ListId]. Fields[RelatedField.FieldId].InternalName, web.Lists[RelatedField.ListId].Title, RelatedField.LookupList, RelatedField.RelationshipDeleteBehavior, site.AllWebs[RelatedField.WebId].Title, Environment.NewLine); } } }
In this code, the GetRelatedFields
method is used to get access to the current related data. The SPRelatedField
type returns mostly GUIDs of the particular objects. These are used to resolve the names. The code produces the output shown in Figure 4-20.
You can use the same approach to establish a relationship programmatically. This is typically part of a complete list creation process (see Listing 4-40). Usually, you do this as part of a feature to configure an existing SharePoint site properly.
Example 4.40. Create Two Lists and Add a Relationship with Referential Integrity
string lookupFieldName = "RelatedField"; using (SPSite site = new SPSite("http://sharepointserve")) { using (SPWeb web = site.OpenWeb()) { SPListCollection lists = web.Lists; Guid SourceListId = lists.Add("Parent List", "", SPListTemplateType.GenericList); Console.WriteLine("Parent List Done..."); Guid TargetListId = lists.Add("Child List", "", SPListTemplateType.GenericList); Console.WriteLine("Child List Done..."); SPList SourceList = lists[SourceListId]; SPList TargetList = lists[TargetListId];
SPFieldCollection Fields = TargetList.Fields; Fields.AddLookup(lookupFieldName, SourceList.ID, true); Console.WriteLine("Lookup Field Created"); SPFieldLookup NewLookupField = Fields[lookupFieldName] as SPFieldLookup; NewLookupField.Indexed = true; NewLookupField.LookupField = "Title";NewLookupField.RelationshipDeleteBehavior =
SPRelationshipDeleteBehavior.Restrict;
NewLookupField.Update(); Console.WriteLine("Lookup field integrity enforced"); SPListItem NewSourceItem = SourceList.Items.Add(); NewSourceItem["Title"] = "Parent Data"; NewSourceItem.Update(); Console.WriteLine("Source listitem created"); SPListItem NewTargetItem = TargetList.Items.Add(); NewTargetItem["Title"] = "Child Data"; NewTargetItem[lookupFieldName] = new SPFieldLookupValue(1, "Source Data"); NewTargetItem.Update(); Console.WriteLine("Parent listitem created"); TargetList.Update(); SourceList.Update(); } }
You can test the relationship by deleting an item in the parent list either from code or from the UI. In code, use a try...catch
clause to catch the exception and get an error description, as shown in Figure 4-21.
Furthermore, some properties exist to control the remaining aspects of a list. These definitions are set in the SPField
object. Assume you have an SPField
object called myField
. To activate indexing on this column, you can write the following:
myField.Indexed = true;
To enforce unique values, set this property:
myField.AllowDuplicateValues = false;
In both cases you must call myField.Update()
to effect the change.
The world of LINQ is much bigger than we can convey here. You are sure to find several ways to work with LINQ to SharePoint in real-life projects. To deal with errors or unexpected behavior, you need to know what CAML code is produced from your LINQ code. This is straightforward using the data context's Log
property, as shown in Listing 4-41.
Example 4.41. Retrieve the CAML Created Internally
StringBuilder sb = new StringBuilder();
TextWriter tw = new StringWriter(sb);
ctx.Log = tw;
// Any LINQ activity goes here EntityList<AuthorsContact> authorsj2 = ctx.GetList<AuthorsContact>("Authors"); EntityList<BooksItem> booksj2 = ctx.GetList<BooksItem>("Books"); var result6 = from book in booksj2 join author in authorsj2 on book.LeadAuthor.Id equals author.Id select new { Book = book.Title, Author = author.FullName }; result6.ToList().ForEach(ab => Console.WriteLine("{0} was written by {1}", ab.Book, ab.Author)); // End of LINQ activityConsole.WriteLine(sb.ToString());
tw.Dispose();
In this example, a joined list is queried, and this will produce the following CAML:
<View> <Query> <Where> <And> <BeginsWith> <FieldRef Name="ContentTypeId" /> <Value Type="ContentTypeId">0x0100</Value> </BeginsWith> <BeginsWith> <FieldRef Name="LeadAuthorContentTypeId" /> <Value Type="Lookup">0x010600</Value> </BeginsWith> </And> </Where> <OrderBy Override="TRUE" /> </Query> <ViewFields> <FieldRef Name="Title" /> <FieldRef Name="LeadAuthorFullName" /> </ViewFields>
<ProjectedFields> <Field Name="LeadAuthorFullName" Type="Lookup" List="LeadAuthor" ShowField="FullName" /> <Field Name="LeadAuthorContentTypeId" Type="Lookup" List="LeadAuthor" ShowField="ContentTypeId" /> </ProjectedFields> <Joins> <Join Type="INNER" ListAlias="LeadAuthor"> <!--List Name: Authors--> <Eq> <FieldRef Name="LeadAuthor" RefType="ID" /> <FieldRef List="LeadAuthor" Name="ID" /> </Eq> </Join> </Joins> <RowLimit Paged="TRUE">2147483647</RowLimit> </View>
If you're speaking LINQ more fluently than CAML, it's a good way to learn CAML. However, there are some verbose parts in this query, such as the restriction to a specific content type.
In this chapter, you learned about the basic object model and API regarding lists, libraries, folders, and document sets. Lists contain data in a structured form. Views are used to retrieve filtered and sorted data. Libraries contain files, and several methods are available to store and download them. Document sets are highly customized content types. The Document Sets API provides a way to group items into packages.
While storing data is relatively simple, retrieving data can be more complex. CAML allows queries against lists and libraries to fetch a subset of data. LINQ to SharePoint adds another layer on top of the CAML query layer to give type-safe access. Using SPMetal
, you can create a data context class and embed it into your application to have instant access from a rich type model.