Database Design
Manage SharePoint Lists
Use the Lists Web service and CAML to update Windows SharePoint Services' MSDE database.
Technology Toolbox: VB.NET, SQL Server 2000, XML, Windows Server 2003, Windows SharePoint Services
Windows SharePoint Services (WSS) is Microsoft's recent reincarnation of SharePoint Team Services (STS) as an industrial-strength, scalable portal for sharing documents, contacts, calendars, Web links, and other information. WSS lets teams and workgroups bypass IT bureaucracy and budget constraints by designing and managing their own collaboration sites. WSS is a no-charge add-on to Windows Server 2003, and it doesn't require WSS-specific client access licenses, so you can expect a substantial increase in SharePoint installations as the new Windows Server System products gain traction. Microsoft Office's Word, Outlook, Excel, Access, and InfoPath 2003 have built-in WSS hooks to simplify the document- and data-sharing process.
One of the significant changes from STS to WSS is the move to SQL Server Desktop Engine (MSDE) 2000 SP3 as the default data store for all site-configuration and content-management metadata. Installing WSS under Windows Server 2003 sets up a SERVERNAME\SHAREPOINT MSDE named instance with two databases—STS_Config and STS_servername_1 for the primary site. You can scale up to SQL Server 2000 clusters and scale out with load-balanced Web server farms as your WSS workload increases. Microsoft claims that WSS can scale to "hundreds of thousands of users."
A quick overview of the STS_servername_1 database's tables in SQL Server Enterprise Manager is enough to discourage even the most intrepid database developer from working directly with the tables. Fortunately, WSS delivers a full complement of ASP.NET XML Web services for remote site administration, content generation, and other create, retrieve, update, and delete operations on the SQL Server tables. WSS also offers a set of .NET Framework 1.1 Microsoft.SharePoint namespaces for extending WSS, but manipulating SharePoint objects in VS.NET involves a steep learning curve. The new XML Web services offer the most accessible and flexible path to integrating WSS with applications and processes that don't have built-in SharePoint connectivity.
Lists form the backbone of WSS sites, so I'll show you how to read and update SharePoint lists with a general-purpose VB.NET WinForms client that consumes WSS' Lists Web service at http://servername/[sitename/]_vti_bin/Lists.asmx (download the ListsWSUpdate project). You'll find it's easy to extend the Web service approach I'll show you to other basic SharePoint administrative chores, such as managing site users, groups, and permissions with the UserGroup and Permissions WSS Web services. You must be a member of the WSS site's Administrator, Web Designer, or Contributor group to update list information; the administrative account you use to install WSS becomes a member of the Administrator group.
Consuming the Lists Web service requires you to add a Web reference to ..._vti_bin/Lists.asmx?wsdl to generate a client proxy. Most WSS sites require Windows authentication, and WSS needs your credentials to permit site access. Gain access to lists with your Windows logon account by adding this line before you invoke a Web method on an intranet:
proxyName.Credentials = _
System.Net.CredentialCache. _
DefaultCredentials
Provide digest authentication to connect through a firewall:
Dim uriLists As New Uri( _
"http://any-uri.com")
Dim crdLists As New CredentialCache()
Dim nwcAdmin As New
NetworkCredential("AdminUser", _
"password", "domain")
crdLists.Add(uriLists, "Digest", _
nwcAdmin)
proxyName.Credentials = _
crdLists.GetCredential(uriLists, _
"Digest")
Fill a DataGrid
Populating a WinForms DataGrid from a WSS list is a multistep process (see Figure 1). You invoke the WSS Lists Web service's GetListCollection method to populate a listbox with the site's List items, select the list you want to view, select the list's fields to include in the DataGrid, transform the Simple Object Access Protocol (SOAP) response message's attribute-centric XML payload to a DataTable, and load the DataTable into the DataGrid (see Listing 1 and Figure 2). All Lists Web service methods return System.Xml.XmlNode objects, so you should be familiar with XmlNode's properties and methods. Most ListsWSUpdate.vb code is devoted to navigating and extracting InnerText values from XmlNodes to populate listboxes.
You can use Extensible Stylesheet Language Transformations (XSLT) 1.0 to transform WSS' old-time, ADO-style rowset nodes to an element-centric DataTable object, but procedural code that manipulates XmlNode objects is easier to debug and handles special-casing of SharePoint decimal values. XSLT 2.0's support for XML Schema data types and the new castable keyword, which lets you check whether an expression can be cast to a particular data type, might make a transform practical. You can learn more about populating DataGrids from SharePoint lists by running the live ASP.NET demonstration project at www.oakleaf.ws/wsslistsasp/. Click on the "About the Project" link, then scroll to the bottom of the About page to peruse the VB.NET code for the GetListItems procedure.
Adding, updating, and deleting WSS list items require you to send SOAP request messages with payloads that you write in an obscure XML dialect called Collaborative Application Markup Language (CAML). Use the CAML Batch element to add, update, or delete list items by invoking New, Update, and Delete methods; the Batch element can execute multiple methods in a single operation. This simple CAML Batch statement updates an existing record and inserts a new record in a Customers list:
<Batch OnError='Continue'
ListVersion='1'>
<Method ID='Items1' Cmd='Update'>
<Field Name='ID'>1</Field>
<Field Name='ContactName'>Maria
Anders-Benson</Field>
</Method>
<Method ID='Items2' Cmd='New'>
<Field Name=
'CustomerID'>BOGUS</Field>
<Field Name='CompanyName'>Bogus
Company</Field>
</Method>
</Batch>
CAML Batch statements for lists resemble ADO.NET DiffGrams, so it's not difficult to transform into CAML the DiffGram generated when you change the DataGrid's column values (see Listing 2). CAML originated with STS, before Microsoft's programmers adopted camelCase element and attribute names, which also explains the use of ADO-style rowsets instead of DataSets for data retrieval. (InfoPath 2003 also uses rowsets to serve as primary and secondary data sources from Jet and SQL Server tables.) You need to send only changed or added field values with CAML, but the ListsWSUpdate app sends all values. A row deletion requires only the ID field value, which SharePoint assigns as the primary key for the list (see Listing 3).
Handle Batch Errors
The significant operational difference between CAML Batch statements and DiffGrams is lack of support for transactions and data-consistency testing in Batch elements. You can set the OnError attribute's value to Return, but the Batch element persists all changes that occur prior to an error. List additions appear at the end of the list, if you don't specify a view with a sort order on a field other than ID (see Figure 3). You apply a descending order to the list by clicking on the DataGrid's column heading.
The Batch element doesn't throw SOAP exceptions for list update errors. Instead, the element returns a SOAP response message with a <Results> element whose child <Result> elements include an error code and, for updates and inserts, a rowset that confirms the changes and additional (uninteresting) information (see Listing 4). You test for an <ErrorCode> whose value isn't 0x00000000 and open a message box that displays an accompanying <ErrorText> value:
<Results xmlns=
"http://schemas.microsoft.com/
sharepoint/soap/">
<Result ID="Items2,Delete">
<ErrorCode>0x81020016</ErrorCode>
<ErrorText>The page you selected
contains an item that does
not exist. It may have
been deleted by another
user. Click "Home" at the
top of the page to return
to your Web
site.</ErrorText>
</Result>
</Results>
You can download the release version of WSS from the Microsoft Windows Server 2003 site (see Additional Resources.) Check the readme file and then install WSS on a Windows Server 2003 test box that's running SQL Server or MSDE 2000 SP3 and Internet Information Services (IIS) 6.0 in the default process isolation mode. Be forewarned: Installing WSS takes over your default Web site, and you must exclude any existing or added virtual directories from WSS management. You must also remove some Office 2003 beta elements, such as the Microsoft Office Web Parts and Components, before you can install WSS. If you still have the WSS beta 2 or Technical Refresh (TR) version on your server, the setup program removes it, reinstalls WSS, and upgrades your site to the release version. Installation takes about 15 minutes to complete for a relatively small site on an 833-MHz Pentium III server. Setup messages often disappear momentarily, and the installer doesn't tell you when it's done.
Use Access 2003's Export to SharePoint feature for tables and queries, or use WSS to create a new list that imports items from an Outlook 2003 Contacts folder for testing . Then, give the ListsWSUpdate VB.NET project a test drive and verify its list-management capabilities. You'll find that adding, deleting, or editing WSS list items is faster with a DataGrid than the Office Web Component's Datasheet control, which requires installation of Office System 2003 on SharePoint client machines. A DataGrid makes adding new items much easier than the alternative Standard view, which requires you to fill in textboxes on an editing page.