SharePoint List Data Access

SharePoint List Data Access

This tutorial walks through three approaches for accessing SharePoint list data: the SOAP API, direct SQL queries, and the SharePoint object model via FrontPage RPC.

Approach 1: SOAP Web Services

The recommended approach for remote access. Use the Lists.asmx service and GetListItems method with CAML queries. See the SharePoint SOAP API Reference for details.

Approach 2: Direct SQL (Not Recommended)

SharePoint stores list data in SQL Server content databases. While you can query these tables directly, Microsoft strongly discourages it because:

• The schema is undocumented and changes between versions
• Direct queries bypass security trimming
• Write operations can corrupt the content database

If you must read data directly (e.g., for reporting), use read-only queries against a database backup or replica:

-- SharePoint stores list items in the AllUserData table
-- Column names are tp_* for built-in fields, nvarchar1-16 for custom columns
SELECT
    t.tp_ID,
    t.nvarchar1 AS Title,
    t.nvarchar2 AS Status,
    t.tp_Modified,
    t.tp_Author
FROM AllUserData t
INNER JOIN AllLists l ON t.tp_ListId = l.tp_ID
WHERE l.tp_Title = 'Tasks'
  AND t.tp_DeleteTransactionId = 0x
ORDER BY t.tp_Modified DESC

Approach 3: FrontPage RPC Protocol

SharePoint supports the FrontPage Server Extensions RPC protocol for programmatic access. This is a POST-based protocol at /_vti_bin/_vti_aut/author.dll.

REM Example: List files in a document library using RPC
POST http://sharepoint.corp.flamenet.io/_vti_bin/_vti_aut/author.dll
Content-Type: application/x-www-form-urlencoded

method=list+documents:6.0.0.0&service_name=/&listHiddenDocs=false&listExplorerDocs=false

Best Practices

• Use SOAP web services for all read/write operations
• Cache list data client-side to reduce SOAP call overhead
• Use the rowLimit parameter to paginate large result sets
• Always handle SOAP faults in your error handling code
• Consider the SharePoint object model (server-side) for complex operations on the same server