SharePoint Lists as DataTables

  • [アーティクル]

This blog posting applies to Microsoft Office SharePoint Server 2007.

You may know that SPList can return a DataTable type: SPList.Items.GetDataTable().  And if you've worked with this, you may know that the DataTable you get is not databound to the list.  This means you can't update the List from changes you make to the DataTable.  In addition, if the List is empty, the DataTable you get back won't have any schema information.  I don't know if that's a bug, but it's certainly a pain.

I wrote a code sample to overcome these problems by providing a new type: ListDataTable.  Instantiate an object of this type with the URL of a list, and it behaves like a DataTable connected to the list.  You can perform Insert, Update and Delete queries on the ListDataTable, then just call Update() to save those changes back to the list.

Download: https://code.msdn.microsoft.com/ListDataTable.

Important Guidelines

"Wow, I can use SharePoint like a database? Even making join relationships between lists?"  Hold on, there, Skippy.  Yes, it's possible, but maybe not so desirable.  Consider what you want to accomplish and why.  You can use Excel spreadsheets like a database with joined tables, too, but it's not usually a good idea.  SharePoint and Excel are designed for user interaction, not serving data to applications... so this approach may not scale arbitrarily. 

Recommendation 1: use the ListDataTable type to help you manipulate data in SharePoint lists with the power and flexibility of queries.

Recommendation 2: Don't forget Microsoft Access... it's a fantastic tool for manipulating SharePoint lists.  On a list's DataSheet view, click the long, thin toolbar tab to the right of the datasheet.  This opens options for linking to Access.  Or, from Access, open your SharePoint lists directly.  You can really clean up data in big lists in a hurry with this approach.

And Sahil Malik has another technique for treating lists like tables: creating a join using a DataView web part from SharePoint Designer 2007.

Comments