In contrast, SOAP is another protocol usually defined to support data access and transfer. It's quite powerful and widely used. However, it's anything but simple. The drawback is that SOAP encourages the developer to reinvent a common set of access methods for each application. Furthermore, it disregards the basic features the underlying transport protocol provides, such as authentication and caching. The intention was to have a more independent protocol, but the reality is that all implementations actually use HTTP and ignore the designated features. This is where REST shines. There is, by the way, a major difference. While SOAP is a standardized protocol, REST is merely an architecture using existing protocols. Hence, there is no "REST standard."

The REST architecture defines some constraints. The actual implementation is not part of the standard and another reason for its wide acceptance. These constraints are as follows:

Routing data independently in a layered system means that each routing device operates as if it were alone. It's not looking back (no session tracking) and handling the request as a single operation. This ensures that changing the routing by removing or adding components has no influence on the other components in the chain.

The last point sounds odd, but a RESTful system could indeed respond by sending a piece of code—think JavaScript—and let the code execute on the client. This is where JSON as a data container comes to mind. JSON is more compact than XML—while still human-readable—but it needs a script engine to be processed properly. Once the data block is in the scripting environment, further processing is much easier because the data is converted into business objects again. Clearly, adding JavaScript code to help the client process the data is just the next step.

REST uses Uniform Resource Identifiers (URIs) to address endpoints. An endpoint is a combination of an address (where to get), a binding (protocol to access), and a contract (data schema transferred). The term resource is a central feature. In a hypermedia world, everything is a resource—something out there. A resource is both a piece of information and the physical container storing this information in a transmissible way (read "a document").

In SharePoint, you use REST to address web services. These addresses contain the whole path to a specific resource. This could be a collection of data such as the following:

http://sharepoint/_vti_bin/listdata.svc/calendar

It's even possible to add more specific information and filter the result set:

http://sharepoint/_vti_bin/listdata.svc/calendar('Cal2')/Entries?$filter=Name eq 'Joerg'

(See the section "Querying Data" later in this chapter for details about the URL-based syntax.)

Operations can map to several HTTP verbs, such as GET, POST, PUT, or DELETE. For reading data, GET is obviously the most common. When you navigate to an address in your browser, you are executing a GET query.

By default, WCF Data Services respond with a XML called the Atom Publishing Protocol (AtomPub). JSON is an alternative method. An AtomPub response could look like this:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<entry xml:base="http://myserver/data.svc/"
    xmlns:ads="http://schemas.microsoft.com/ado/2007/08/dataservices"
    xmlns:adsm="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
    adsm:type="NorthwindModel.Customers"
    xmlns="http://www.w3.org/2005/Atom">
  <id>http://myserver/data.svc/Customers('Computacenter')</id>
  <updated />
  <title />
   http://localhost:61243/nw.svc/Customers('Computacenter')
   <author>
    <name />
  </author>
  <link rel="edit" href="//myserver/data.svc/Customers('Computacenter')"
        title="Customers" />
        http://localhost:61243/nw.svc/Customers('Computacenter')
       <content type="application/xml">
    <ads:CustomerID>Computacenter</ads:CustomerID>
    <ads:CompanyName>A. Computacenter AG & Co. oHG</ads:CompanyName>
    <ads:ContactName>Joerg Krause</ads:ContactName>
    <ads:ContactTitle>Senior Consultant</ads:ContactTitle>
    <ads:Address>Mariendorfer Damm 1-3</ads:Address>
    <ads:City>Berlin</ads:City>
    <ads:Region ads:null="true" />
    <ads:PostalCode>12099</ads:PostalCode>
    <ads:Country>Germany</ads:Country>
    <ads:Phone>0172-2302633</ads:Phone>
    <ads:Fax>030-70785505</ads:Fax>
  </content>
  <link rel="related" title="Orders"
        href="Customers('Computacenter')/Orders"
        type="application/xml;type=feed" />
  <link rel="related" title="Demographics"
        href="Customers('Computacenter')/ Demographics"
        type="application/xml;type=feed" />
</entry>

AtomPub is the key to reading such data with an ordinary feed reader. Figure 5-2 shows Internet Explorer accessing a SharePoint list.

Figure 5.2. Accessing a SharePoint list as a feed

Setting the Accept request header to the application/json MIME type forces the WCF Data Services server to respond in JSON format. A sample response follows:

{
   "d":{
    __metadata: {
        uri: "Customers('Computacenter')",
        type: "NorthwindModel.Customers"
    },
    CustomerID: "COMPUTACNT",
    CompanyName: "Computacenter AG & Co. oHG",
       ContactName: "Joerg Krause",
       ContactTitle: "Senior Consultant",
    Address: "Mariendorfer Damm 1-3",
    City: "Berlin",
    Region: null,
    PostalCode: "12099",
    Country: "Germany",
    Phone: "0172-2302633",
    Fax: "030-700855505",
    Orders: {
        __deferred: {
            uri: "Customers('COMPUTACNT')/Orders"
        }
    }
   }
}

When accessing data from a client application with low bandwidth, the JSON format is preferable. It is compact and not as verbose as AtomPub's XML.

WCF Data Services is not part of either current operating system. For Windows Server 2008 R2 and Windows 7, you can find the download here:

http://www.microsoft.com/downloads/details.aspx?
       familyid=79d7f6f8-d6e9-4b8c-8640-17f89452148e&displaylang=en

For Windows Server 2008 and Windows Vista, the download is here:

http://www.microsoft.com/downloads/details.aspx?
       familyid=4B710B89-8576-46CF-A4BF-331A9306D555&displaylang=en

Double-check that you select the 64-bit version to use the services with SharePoint 2010 if you intend to run it within your SharePoint development environment, as suggested in Chapter 1.

The package is a hotfix and doesn't require any further action except for a system restart to get the service up and running.

SharePoint 2010 simply uses WCF Data Services, and hence the access method is not specific to SharePoint. The URI for all such services has the following form:

http://<server>/<site>/_vti_bin/listdata.svc

Replace <server> with your machine's name and replace <site> with the site containing the data to be retrieved. The <site> fragment is not required if you run your code in the root site. Most of the examples in this chapter run in the root site, and thus the <site> fragment is omitted. To create an application to retrieve data from a SharePoint list, follow the steps explained in Chapter 4 "Data Access". Figure 5-3 illustrates a typical response from the service to a request without any additional parameters.

Figure 5.3. listdata.svc without parameters returns all available resources.

To further investigate the source and retrieve data, you can add a list's name after the base URL. You'll see some examples of how this looks later in this chapter. If the client acts as a feed reader (as in Figure 5-2), you will see the reader's view. Otherwise, the feed data is displayed. The source XML would look similar to this:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://sharepointserve/_vti_bin/listdata.svc/"
      xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
      xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
      xmlns="http://www.w3.org/2005/Atom">
  <title type="text">Authors</title>
  <id>http://sharepointserve/_vti_bin/listdata.svc/Authors/</id>
  <updated>2010-03-26T12:58:33Z</updated>
  <link rel="self" title="Authors" href="Authors" />
  <entry m:etag="W/&quot;2&quot;">
    <id>http://sharepointserve/_vti_bin/listdata.svc/Authors(1)</id>
    <title type="text">Krause</title>
    <updated>2010-03-26T13:57:02+01:00</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="AuthorsItem" href="Authors(1)" />
    <link rel="http://schemas.microsoft.com/ado/2007/
               08/dataservices/related/CreatedBy"
          type="application/atom+xml;type=entry"
          title="CreatedBy" href="Authors(1)/CreatedBy" />
    <link rel="http://schemas.microsoft.com/ado/2007/
               08/dataservices/related/ModifiedBy"
          type="application/atom+xml;type=entry"
          title="ModifiedBy" href="Authors(1)/ModifiedBy" />

<link rel="http://schemas.microsoft.com/ado/2007/
               08/dataservices/related/Attachments"
          type="application/atom+xml;type=feed"
          title="Attachments" href="Authors(1)/Attachments" />
    <category term="Microsoft.SharePoint.DataService.AuthorsItem"
              scheme="http://schemas.microsoft.com/ado/2007/
                      08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">1</d:Id>
        <d:ContentTypeID>0x0106001A6C5BC3D1EED04C97BC134601639F39</d:ContentTypeID>
        <d:ContentType>Contact</d:ContentType>
        <d:LastName>Krause</d:LastName>
        <d:Modified m:type="Edm.DateTime">2010-03-26T13:57:02</d:Modified>
        <d:Created m:type="Edm.DateTime">2010-03-26T13:53:35</d:Created>
        <d:CreatedById m:type="Edm.Int32">1</d:CreatedById>
        <d:ModifiedById m:type="Edm.Int32">1</d:ModifiedById>
        <d:Owshiddenversion m:type="Edm.Int32">2</d:Owshiddenversion>
        <d:Version>1.0</d:Version>
        <d:Path>/Lists/Authors</d:Path>
        <d:FirstName>Joerg</d:FirstName>
        <d:FullName>Joerg Krause</d:FullName>
        <d:EMailAddress>[email protected]</d:EMailAddress>
        <d:Company>Computacenter</d:Company>
        <d:JobTitle>Senior Consultant</d:JobTitle>
        <d:BusinessPhone m:null="true" />
        <d:HomePhone m:null="true" />
        <d:MobileNumber m:null="true" />
        <d:FaxNumber m:null="true" />
        <d:Address m:null="true" />
        <d:City>Berlin</d:City>
        <d:StateProvince m:null="true" />
        <d:ZIPPostalCode m:null="true" />
        <d:CountryRegion m:null="true" />
        <d:WebPage>http://www.joergkrause.de, http://www.joergkrause.de</d:WebPage>
        <d:Notes>&lt;div&gt;&lt;/div&gt;</d:Notes>
      </m:properties>
    </content>
  </entry>
...

The retrieved data appears more or less unstructured. The fields follow the requested list's fields, but this is not sufficient to navigate through lists automatically. By adding $metadata to the URL, you can retrieve the entity data model description (see Figure 5-4).

Figure 5.4. The service's metadata description

The URL looks like this:

http://<server>/_vti_bin/listdata.svc/$metadata

The entities are described for each list available on the site you're querying. Armed with this, you have everything you need to know about the lists' fields.

WCF Data Services currently supports exchanging entities in JSON and Atom formats. The same format can be used both to receive information from the data service and to send to it. The choice of format depends mostly on the client. In a JavaScript environment, JSON is preferable. A .NET- or Silverlight-based client will probably find Atom's XML much easier to process. If bandwidth is important, choose JSON.

To specify the format in which information is to be sent from WCF Data Services, set the Content-Type HTTP header. The corresponding setting, according to RFC 2616, controlling the format accepted by WCF Data Services is the Accept HTTP header. Table 5-4 lists some valid types.

To understand how you can retrieve data simply by typing a URL into your browser's address bar, let's examine some examples. To execute these examples, add the expression after the /_vti_bin/listdata.svc part. If the service can't execute the expression because of invalid syntax, it will return a Bad Request error (HTTP code 400). When using field names, you must enter the internal names and be aware that the names are case sensitive.

Figure 5.5. Developer-friendly error message

The filter using the eq operator extracts exactly one element if the filtered element is unique:

/Authors?$filter=Id eq 1

It is more common to use filter expressions to search for elements:

Authors?$filter='Joerg,Krause' eq concat(FirstName, concat(',', LastName))

This matches the item from the Authors list with the value of the properties FirstName and LastName equal to Joerg,Krause. The string is built using the concat function twice.

Boolean operators enable even more complex queries:

Authors?$filter=City eq 'Berlin' and FirstName eq 'Joerg'

Some functions enable you to operate with parts of data, such as the year of the Modified property:

Authors?$filter=year(Modified) eq 2010

Using functions is not always as straightforward as you might expect. If you filter using functions, the result set must be limited to one item before you can work within a field. This expression works well:

Authors?$filter=Id eq 3 and substring(City, 0, 1) eq 'M'

It works because the first part extracts exactly one item, and the second expression checks whether it matches a very specific condition (the first letter of City equals M). If you filter just for the second expression, an error occurs. This expression will not work:

Authors?$filter=substring(City, 0, 1) eq 'M'

While it is syntactically correct, the function cannot apply to "many."

Typing the filter expression into the browser's address bar is easy and provides immediate feedback. However, it is a "no code" approach that isn't very useful. To work with this data, you need a more versatile method of accessing the data services layer.

Adding a reference to the service from any kind of Visual Studio 2010 project creates an object-relational mapping to the list. The mapping is generated code in reference.cs, and these classes build the data context. The name is predefined as <sitename>DataContext. It inherits from the DataServiceContext base class. Each list found on the site creates a property of generic type DataServiceQuery<T>. The type parameter T is the list item type for that particular list. The name is autogenerated using the pattern <listname>Item. Figure 5-6 shows this using the namespace MyServiceReference.

Figure 5.6. Adding a reference to the service

To use this service, add a using statement with the project's default namespace and the namespace you entered in the Add Service Reference dialog:

using Apress.SP2010.ListService.MyServiceReference

In this example, the site is called Home. It is the root site, so we don't need another path section. The data context is automatically named HomeDataContext. The data context exposes all the lists, and you can use standard LINQ expressions to retrieve data, change it, and save it to SharePoint.

The next example retrieves the Authors list. If the site does not allow anonymous access, you must provide the credentials to access the service. To execute the code, add the System.Net namespace to your using statements.

In this example (Listing 5-1), the service returns all the Authors data, and LINQ is used to select it. Clearly, you can filter further using the full power of LINQ. (You can find many examples of using LINQ in Chapter 4.)

Uri uri = new Uri("http://sharepointserve/_vti_bin/listdata.svc", UriKind.Absolute);
HomeDataContext ctx = new HomeDataContext(uri);
ctx.Credentials = new NetworkCredential("username", "password");

var authors = from a in ctx.Authors select a;

foreach (var ac in authors)
{
   Console.WriteLine("{0} works at {1}",
                       ac.FullName,
                       ac.Company);
}

The SharePoint Designer can create ECTs for these data sources:

For a first walk-through, let's assume that the Northwind database is available and that you want to retrieve some data from it.

To create an ECT, open a site using SharePoint Designer 2010, and click External Content Types (see Figure 5-8).

Figure 5.8. Add an ECT using SharePoint Designer

The front page of the newly created type allows you to assign a name and a description. In this example, the name is NorthwindProducts, and the description is Northwind Products. Next, click the link "Click here to discover external data sources and ..." (see Figure 5-9 at the bottom of the center area External Content Type Information).

Figure 5.9. Create a new ECT.

The wizard jumps to the next page where you can add the connection. First, select the connection type. As shown in Figure 5-10, for this example you should choose SQL Server.

Figure 5.10. Select the appropriate connection type.

For the SQL Server, enter the database server, database name, and the identity that connects to this server (see Figure 5-11).

Figure 5.11. Connection details for a SQL Server connection

The Data Source Explorer should now show all tables in the chosen database (see Figure 5-12). Right-click the table you want to access in order to display the context menu with available operations. For read access, you need at least New Read Item Operation and New Read List Operation. After selecting both—one after another—they appear in the right column named External Content Type Operations.

Figure 5.12. Create the operations used to access the data source.

Each operation uses its own configuration wizard (see Figure 5-13) that allows you to limit the data by choosing specific columns and filter the result sets with specific conditions. For this example, click Finish to get all the data available in this table.

Figure 5.13. Configure the selected operation.

Finally, click the Create Lists & Form icon in the ribbon to create the forms associated with the ECT. That makes the external data appear like a regular list. With the Enterprise license, you can choose to create InfoPath forms, too.

By default the external data is not yet available because there are no access permissions set in the metadata catalog. To correct this, you will need access to the Central Administration (see Figure 5-14).

Figure 5.14. Select "Manage service applications."

On the next screen, click the Business Data Connectivity Service link. The freshly created ECT should appear there as shown in Figure 5-15. Next, open the context menu from the link in the Name column, and select Set Permissions.

Figure 5.15. The ECT

You can now select the user and assign the permissions for this user. Click OK and close the dialog (see Figure 5-16).

Figure 5.16. Set a user and assign particular permissions.

When the External Content Type is created and forms and permissions are assigned, the data appears as if it were internal data. If you refresh the site's front page, the list should appear in the navigation, and you can simply click and view the data (see Figure 5-17).

Figure 5.17. External data appears just like any other internal list.

If you have the appropriate rights, you can add an external list using the UI. Select More Options from the site menu and External List from the Create dialog, as shown in Figure 5-18.

Figure 5.18. Create an external list using the Browser UI (composition).

After this, you can enter a name and select the ECT that must already exist in the Business Metadata Catalog.

Whichever way you used to access the external data, it's now time to get programmatic access using the SharePoint object model.

The SharePoint content database is where SharePoint stores nearly all its data. All content that is uploaded or produced through the web UI, over web services, or via the SharePoint API is stored in the content database. A detailed description of the whole content database is beyond the scope of this section. Instead, let's focus on the SQL table AllUserData as the place where all data that has to do with list items is stored. This important table has 192 (!) columns, and all list items (remember that documents in document libraries are also list items) including their history are stored in this single table. Table 5-5 describes the most important columns.

As you can see in Table 5-5, there are groups of columns in which the values of SharePoint lists are stored (including nvarchar#, ntext#, bit#, datetime#, float#, int#, and sql_variant#). Every time you create a new column in a SharePoint list, either by calling the API or over the UI, the new SharePoint list column is automatically mapped to a "free" column of the desired type in the table AllUserData. For example, if you add a column Publisher of type Single Line to your SharePoint list, an unused database column in the range from nvarchar1 to nvarchar64 will automatically be assigned (see Figure 5-19).

Figure 5.19. Query result for table AllUserData (showing Books list items and LeadAuthors list items)

Figure 5-19 makes the column assignment clear. Here, you can see three items of the list Books and three items of the list LeadAuthors. The SharePoint column Title is mapped to the database column nvarchar1 for both lists. The Publisher column from Books is mapped to nvarchar4, and also the Street column of LeadAuthors is mapped to nvarchar4.

The next thing to understand regarding the table AllUserData is how to identify an item correctly. You need the ID of your current site collection (SPSite.ID = tp_SiteId) and the ID of the list you want to query (SPList.ID = tp_ListId). To identify a particular list item, it is not sufficient to have the ID of this item. There could be more than one row with the same ID (SPListItem.ID = tp_ID) because every version is stored in the table. So, you also need to query the flag tp_IsCurrentVersion to be sure it is the latest version. Furthermore, you must ensure that the item has not been deleted. Therefore, you need to check if the tp_DeleteTransactionId is null.

There is one final pitfall. Check that the column tp_RowOrdinal is 0. This column was designed for the special case that the provided 156 fields are insufficient, for at least one type (for example, if the datetime type is required more than eight times in one list). In this case, an additional row with tp_RowOrdinal=1 is added to the table and contains the "overflowing" fields. Although the probability of running into this issue is very low, you need to remember that this case can occur. Especially with our custom view implementation, we have to keep that in mind.

The simplest approach for satisfying all the query restrictions mentioned earlier is to use the built-in view, called UserData (see Listing 5-3).

SELECT * FROM AllUserData
WHERE
     tp_IsCurrentVersion = CONVERT(bit,1)
     AND tp_CalculatedVersion = 0
     AND tp_DeleteTransactionId = 0x

This view serves as the foundation for our own views. It ensures that always the current version is returned and that this list item has not been deleted. Let's build a SQL SELECT query for the list Books that returns all the items and only the fields we need (see Figure 5-20).

Figure 5.20. SQL SELECT query that returns all books

The only two things needed for this query are the list ID and the column assignments. To find the ID of a list, you can simply get it programmatically:

SPContext.Current.Web.Lists["Books"].ID

The next challenge is to get the name of the mapped database columns from a SharePoint list. Unfortunately, the SPField class offers no suitable public property containing that data. To overcome this, we use reflection to interrogate a private property called ColName containing the mapped column's name (see Listing 5-4).

private static String GetColName(SPField field)
{
    PropertyInfo pi = field.GetType().GetProperty("ColName",
                      BindingFlags.Instance | BindingFlags.NonPublic);
    return (string) pi.GetValue(field, null);
}

The next section shows how to dynamically create SQL views for your SharePoint lists.

In the previous section, the fundamental approach for querying the content database has been established. But before you start with your own custom SQL views, there is one further consideration: all datetime values in the database are stored in Universal Time Coordinated (UTC). Consequently, if you want to include a datetime value in your queries, the database values have to be converted from UTC to local time.

To achieve this, let's write a SQL function to automatically convert datetime values on the database server (see Listing 5-5).

CREATE FUNCTION [fn_FromUtcToLocalDateTime] (@DateTimeToConvert datetime)
RETURNS datetime
AS BEGIN
     -- Convert a UTC Time to a Local Time
     DECLARE @UTCDate datetime
     DECLARE @LocalDate datetime
     DECLARE @TimeDiff int
     -- Figure out the time difference between UTC and Local time
     SET @UTCDate = GETUTCDATE()
     SET @LocalDate = GETDATE()
     SET @TimeDiff = DATEDIFF(hh, @UTCDate, @LocalDate)
     -- Convert UTC to local time
     DECLARE @DateYouWantToConvert datetime
     DECLARE @ConvertedLocalTime datetime
     SET @ConvertedLocalTime = DATEADD(hh, @TimeDiff, @DateTimeToConvert)
     -- Check Results
     RETURN @ConvertedLocalTime
END

Now you have everything you need for creating SQL views. The SQL function fn_FromUtcToLocalDateTime that converts UTC datetime values into the local time of the SQL Server can be integrated directly into the SELECT statement of the CREATE VIEW, and as a result, the results of querying your SQL views will always contains the correct datetime. Listing 5-6 shows the complete CREATE VIEW script for the SharePoint list Books.

CREATE VIEW [BooksView_d832061a-57e9-473a-b9a5-623d78c0950e]
AS SELECT
     ud.tp_ID AS [ID],
     ud.nvarchar1 AS [Title],
     ud.ntext2 AS [Description],
     ud.nvarchar3 AS [Authors],
     ud.float1 AS [Price],
     ud.nvarchar4 AS [Publisher],
     ud.int1 AS [LeadAuthorID],
     dbo.fn_FromUtcToLocalDateTime(ud.tp_Modified) AS [LastModified] ,
     ui.tp_Title AS [LastModifiedBy]
FROM UserData ud, UserInfo ui
WHERE
     ud.tp_Editor=ui.tp_ID
     AND ud.tp_SiteId=ui.tp_SiteID
     AND ud.tp_ListId='05eeda2d-9c19-4618-be4f-bdd29c926b69'
     AND ud.tp_RowOrdinal=0

This view contains one extra feature. As you can see, a SQL JOIN on the table UserInfo is integrated that resolves the user ID of the Editor (ud.tp_Editor=ui.tp_ID) and adds a column containing the login name of the user who last modified the list item (see Figure 5-21).

Figure 5.21. Query result for SQL view BooksView

Similar to the Books list, the view for the list LeadAuthors can be created (see Listing 5-7).

CREATE VIEW [LeadAuthorsView_d832061a-57e9-473a-b9a5-623d78c0950e]
AS SELECT
     ud.tp_ID AS [ID],
     ud.nvarchar1 AS [Title],
     ud.nvarchar3 AS [City],
     ud.nvarchar4 AS [Street],
     dbo.fn_FromUtcToLocalDateTime(ud.tp_Modified) AS [LastModified] ,
     ui.tp_Title AS [LastModifiedBy]
FROM UserData ud, UserInfo ui
WHERE
     ud.tp_Editor=ui.tp_ID

AND ud.tp_SiteId=ui.tp_SiteID
     AND ud.tp_ListId='12286c78-ef39-425e-a5da-27b86ff87455'
     AND ud.tp_RowOrdinal=0

Figure 5-22 shows the result.

Figure 5.22. Query result for SQL view LeadAuthorsView

You may wonder why both SQL views do not have normal names but contain a GUID string in their names. This is because the views are created automatically by a script. In the examples, the ID of the SPWeb containing the lists is added to the SQL view name. Listing 5-8 presents the generic method CreateViewSql that generates an SQL CREATE VIEW script for the SPList instance passed in as a parameter.

public string CreateViewSql(SPList list)
{
  string listName = list.Title.Replace(" ", "");
  string viewName = "[" + listName + "View_" + list.ParentWeb.ID + "]";
  StringBuilder sb = new StringBuilder();
  sb.Append("CREATE VIEW " + viewName + " AS ");
  sb.Append(" SELECT ud." + GetColName(list.Fields["ID"]) + " AS [ID], ");
  sb.Append("ud." + GetColName(list.Fields.GetFieldByInternalName("Title"))
                        + " AS [Title], ");

  String del = "";
  foreach (SPField f in list.Fields)
  {
      if (IsAllowedField(f))
      {
          if (f.Type == SPFieldType.DateTime)
          {
             sb.Append(del + "dbo.fn_FromUtcToLocalDateTime(ud."
                           + GetColName(f) + ") AS [" + f.InternalName + "]");
           }
           else if (f.Type == SPFieldType.Lookup)
           {
              sb.Append(del + "ud." + GetColName(f)
                            + " AS [" + f.InternalName + "ID]");

}
           else
           {
              sb.Append(del + "ud." + GetColName(f)
                            + " AS [" + f.InternalName + "]");
           }
           del = ", ";
       }
   }

   //added LastModified / LastModifiedBy
   sb.Append(del +
             "dbo.fn_FromUtcToLocalDateTime(ud.tp_Modified) AS [LastModified] ");
   sb.Append(del + "ui.tp_Title AS [LastModifiedBy] ");
   sb.Append(" FROM UserData ud, UserInfo ui ");
   sb.Append(" WHERE ud.tp_Editor=ui.tp_ID AND ud.tp_SiteId=ui.tp_SiteID
               AND ud.tp_ListId='" + list.ID.ToString() + "'
               AND ud.tp_RowOrdinal=0");

    return sb.ToString();
}

To differentiate between important fields and fields that are intended only for internal use, the private method IsAllowedField decides this based on the internal name of the SPField instance. In the actual implementation (see Listing 5-9), 52 fields are disallowed from the SQL view.

private bool IsAllowedField(SPField field)
{
    if (field.Type == SPFieldType.Computed) return false;

    List<String> permittedNames = new List<string>();
    permittedNames.AddRange(new String[]
    {
        "ID",
        "Title",
        "ContentTypeId",
        "ContentType",
        "Modified",
        "Created",
        "Author",
        "Editor",
        "_HasCopyDestinations",
        "_CopySource",
        "owshiddenversion" ,
        "WorkflowVersion",
        "_UIVersion",
        "_UIVersionString",
        "Attachments",
        "_ModerationStatus",
        "_ModerationComments",
        "Edit",
        "LinkTitleNoMenu",

"LinkTitle",
        "SelectTitle",
        "InstanceID",
        "Order",
        "GUID",
        "WorkflowInstanceID",
        "FileRef",
        "FileDirRef",
        "Last_x0020_Modified",
        "Created_x0020_Date",
        "FSObjType",
        "PermMask",
        "FileLeafRef",
        "UniqueId",
        "ProgId",
        "ScopeId",
        "File_x0020_Type",
        "HTML_x0020_File_x0020_Type",
        "_EditMenuTableStart",
        "_EditMenuTableEnd",
        "LinkFilenameNoMenu",
        "LinkFilename",
        "DocIcon",
        "ServerUrl",
        "EncodedAbsUrl",
        "BaseName",
        "MetaInfo",
        "_Level",
        "_IsCurrentVersion",
        "SortBehavior",
        "SyncClientId",
        "ItemChildCount",
        "FolderChildCount"
    });
    return (!permittedNames.Contains(field.InternalName));
}

This concludes the foundation work. While there are many things to take into account when building these queries, as you will see in the next section, the performance gains should more than compensate for the effort spent on SQL view generation.

The next step after creating the SQL views for the SharePoint lists is to implement model classes. These classes are intended to ensure type-safe access to the SQL views. You need a model class for each list. Listing 5-10 shows the classes BooksDbItem and LeadAuthorsDbItem for the lists Books and LeadAuthors, respectively.

namespace Apress.SP2010.DbModel
{
    public class BooksDbItem

{
        public int ID { get; set; }
        public String Title { get; set; }
        public String Description { get; set; }
        public String Authors { get; set; }
        public decimal Price { get; set; }
        public String Publisher { get; set; }
        public int LeadAuthorID { get; set; }
        public DateTime LastModified { get; set; }
        public String LastModifiedBy { get; set; }
    }

    public class LeadAuthorsDbItem
    {
        public int ID { get; set; }
        public String Title { get; set; }
        public String City { get; set; }
        public String Street { get; set; }
        public DateTime LastModified { get; set; }
        public String LastModifiedBy { get; set; }
    }
}

Both classes contain exactly the same properties as the SQL views of the SharePoint lists. You can now use these model classes with LINQ, as shown in Listing 5-11. This method needs a using reference to System.Linq namespace.

public DataTable QueryBooksOfLeadAuthorsByLinqToSql()
{
    //create result DataTable
    DataTable dataTable = new DataTable("BooksOfLeadAuthors");
    dataTable.Columns.Add(new DataColumn("FullName", typeof(String)));
    dataTable.Columns.Add(new DataColumn("NumberOfBooks", typeof(int)));

    String conStr = SPContext.Current.Web.Site.ContentDatabase.DatabaseConnectionString;
    String xml = GetXmlMapping(new String[] {"Books", "LeadAuthors"});

    DataContext ctx = new DataContext(conStr, XmlMappingSource.FromXml(xml));
    var results = from book in ctx.GetTable<BooksDbItem>()
                  join leadAuthor in ctx.GetTable<LeadAuthorsDbItem>()
                  on book.LeadAuthorID equals leadAuthor.ID
                  group leadAuthor by leadAuthor.Title into grp
                  select new
                  {
                     FullName = grp.Key,
                     NumOfBooks = grp.Count()
                  };

     //add new row to DataTable
     foreach (var obj in results)

dataTable.Rows.Add(obj.FullName, obj.NumOfBooks);

     return dataTable;
}

The implementation of this method uses the DataContext class of LINQ to SQL. But instead of using the default attribute mapping that requires code attributes in the model classes describing the mapping to the database columns, a custom XML mapping is used. In Listing 5-12 is an example of a dynamically generated XML file, defining the mapping from the SQL view columns to the properties of the model class. The code for the method that generates the XML is shown in Listing 5-13.

<Database Name="SharePoint" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">

  <Table Name="[BooksView_d832061a-57e9-473a-b9a5-623d78c0950e]"
         Member="Apress.SP2010.DbModel.BooksDbItem">

    <Type Name="Apress.SP2010.DbModel.BooksDbItem">
      <Column Name="ID" Member="ID" />
      <Column Name="Title" Member="Title" />
      <Column Name="Description" Member="Description" />
      <Column Name="Authors" Member="Authors" />
      <Column Name="Price" Member="Price" />
      <Column Name="Publisher" Member="Publisher" />
      <Column Name="LeadAuthorID" Member="LeadAuthorID" />
      <Column Name="LastModified" Member="LastModified" />
      <Column Name="LastModifiedBy" Member="LastModifiedBy" />
    </Type>

  </Table>

  <Table Name="[LeadAuthorsView_d832061a-57e9-473a-b9a5-623d78c0950e]"
         Member="Apress.SP2010.DbModel.LeadAuthorsDbItem">

    <Type Name="Apress.SP2010.DbModel.LeadAuthorsDbItem">
      <Column Name="ID" Member="ID" />
      <Column Name="Title" Member="Title" />
      <Column Name="City" Member="City" />
      <Column Name="Street" Member="Street" />
      <Column Name="LastModified" Member="LastModified" />
      <Column Name="LastModifiedBy" Member="LastModifiedBy" />
    </Type>

  </Table>

</Database>

private String GetXmlMapping(String[] listNames)
{
  string assembly = @"Apress.SP2010, Version=1.0.0.0, Culture=neutral,
                      PublicKeyToken=4113b8ec9b28df52";

StringBuilder sb = new StringBuilder();

  //add xml header
  sb.Append("<Database Name="SharePoint""
          + " xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
");
  using (SPWeb web = SPContext.Current.Web)
  {
     foreach (String listName in listNames)
     {
         String viewName = "[" + listName + "View_" + web.ID + "]";
         String modelClass = "Apress.SP2010.DbModel." + listName + "DbItem";

         sb.Append("<Table Name="" + viewName + "" Member=""
                                    + modelClass + "">
");
         sb.Append("<Type Name="" + modelClass + "">");
         Type t = Type.GetType(modelClass + "," + assembly);

         foreach (PropertyInfo pi in t.GetProperties())
         {
            sb.Append("<Column Name="" + pi.Name + "" Member=""
                                        + pi.Name + "" />");
         }
         sb.Append("</Type>
</Table>
");
     }
   }
   sb.Append("</Database>");
   return sb.ToString();
}

The GetXmlMapping method dynamically generates a mapping XML that is used by the LINQ to SQL DataContext to resolve the mapping of SQL view columns to SharePoint list columns (see Listing 5-13). The method takes a String array of list names (such as Books, LeadAuthors) as a parameter. Each individual name, with the suffix DbItem appended and the namespace APress.SP2010.DbModel as a prefix (such as Apress.SP2010.DbModel.BooksDbItem), is assumed to exist as a model class implementation. The Type.GetType() call returns the type of the model class and is used for iterating through all the existing properties. These properties are written as XML Column elements that assign the property name to both mapping attributes: Name (the name of the SQL view column) and Member (the property name).

Accessing the content database directly is not supported by Microsoft. It is possible that the structure of the content database could be changed in one of the next updates, breaking the custom SQL views. Although the probability of a major change in the structure is not high (SharePoint 2007 and SharePoint 2010 have nearly the same database structure for storing list items), it is a possibility you should always remember.

Another limitation concerns the Business Connectivity Services introduced with SharePoint 2010. BCS supports retrieving, editing, updating, and deleting data from external systems. External content types integrate external data sources, such as databases, web, or WCF services and other custom data sources. These external data sources can be used like normal SharePoint lists. Using SQL views for accessing SharePoint lists is valid only for normal SharePoint lists and not for SharePoint lists that are connected through BCS. Hence, the data to be queried by SQL views has to be fully stored within the content database—BCS-based lists or fields are not supported.

With SQL views, the performance problems arising from inefficient LINQ or CAML queries are mainly resolved. However, before you can use SQL views, you have to expend some effort to make it work. It's not sufficient to manually create SQL views once only. Instead, you need to implement functionality to re-create your SQL views automatically, such as if columns are added or removed.

But if, for your requirements, the performance of your queries is of topmost importance then you should use direct SQL access. It offers the very best performance for complex queries over large SharePoint lists.

A great advantage of using LINQ in general is the ability to change your data access method at any time with very low effort. For example, you can start with LINQ to SharePoint, and if you notice that your performance degrades, then you can quickly switch to direct access with LINQ to SQL. The changes required in your code are quite minimal because the LINQ queries are largely unchanged. You simply have to substitute the DataContext instance and the model classes (for example, change BooksItem to BooksDbItem).

We treat as advanced any scenario that has a list that is ordered by anything other than ID, that has incomplete IDs, or whose items are not part of the regular paging process, such as folders. The complete paging management is done by parameters (see Table 5-6) in the PagingInfo string.

Some paging examples taken from actual sites look like these:

The information what sort parameter is currently used looks a bit weird, because the field's name is the parameter (p_Created, p_Modified, p_Title, and so on) while the value is the current element's—the first element on the page—field value. In the example, the list is sorted by the Created field, and the date is part of the query. This way you can browse through a sorted list page by page.

The code snippet in Listing 5-12 can be extended by adding sorting:

q.Query = "<OrderBy><FieldRef Name='Created' Ascending='FALSE' /></OrderBy>";

If you retrieve the internally created PageInfo property, the value now has an additional parameter:

Paged=TRUE&p_Created=20100306%2012%3a37%3a58&p_ID=2938

Since the intention of the PageInfo property is to produce the pagination portion of the URL, the values must be encoded. If you construct the content and insert values, we recommend you use the SPEncode.UrlEncode method to encode values.