Tech in the 603, The Granite State Hacker

Reading SharePoint Lists into an ADO.Net DataTable

[Feb 18, 2009: I’ve posted an update to show the newer technique suggested below by Kirk Evans, also compensating for some column naming issues.]

The other day, I needed to write some code that processed data from a SharePoint list. The list was hosted on a remote MOSS 2007 server.

Given more time, I’d have gone digging for an ADO.NET adapter, but I found some code that helped. Unfortunately, the code I found didn’t quite seem to work for my needs. Out of the box, the code missed several columns for no apparent reason.

Here’s my tweak to the solution:

(The ListWebService points to a web service like http://SiteHost/SiteParent/Site/_vti_bin/lists.asmx?WSDL )

private data.DataTable GetDataTableFromWSS(string listName)

{

ListWebService.Lists lists = new ListWebService.Lists();

lists.UseDefaultCredentials = true;

lists.Proxy = null;

//you have to pass the List Name here

XmlNode ListCollectionNode = lists.GetListCollection();

XmlElement List = (XmlElement)ListCollectionNode.SelectSingleNode(String.Format(“wss:List[@Title='{0}’]”, listName), NameSpaceMgr);

if (List == null)

{

throw new ArgumentException(String.Format(“The list ‘{0}’ could not be found in the site ‘{1}'”, listName, lists.Url));

}

string TechListName = List.GetAttribute(“Name”);

data.DataTable result = new data.DataTable(“list”);

XmlNode ListInfoNode = lists.GetList(TechListName);

System.Text.StringBuilder fieldRefs = new System.Text.StringBuilder();

System.Collections.Hashtable DisplayNames = new System.Collections.Hashtable();

foreach (XmlElement Field in ListInfoNode.SelectNodes(“wss:Fields/wss:Field”, NameSpaceMgr))

{

string FieldName = Field.GetAttribute(“Name”);

string FieldDisplayName = Field.GetAttribute(“DisplayName”);

if (result.Columns.Contains(FieldDisplayName))

{

FieldDisplayName = FieldDisplayName + ” (“ + FieldName + “)”;

}

result.Columns.Add(FieldDisplayName, TypeFromField(Field));

fieldRefs.AppendFormat(“”, FieldName);

DisplayNames.Add(FieldDisplayName, FieldName);

}

result.Columns.Add(“XmlElement”, typeof(XmlElement));

XmlElement fields = ListInfoNode.OwnerDocument.CreateElement(“ViewFields”);

fields.InnerXml = fieldRefs.ToString();

XmlNode ItemsNode = lists.GetListItems(TechListName, null, null, fields, “10000”, null, null);

// Lookup fields always start with the numeric ID, then ;# and then the string representation.

// We are normally only interested in the name, so we strip the ID.

System.Text.RegularExpressions.Regex CheckLookup = new System.Text.RegularExpressions.Regex(“^\\d+;#”);

foreach (XmlElement Item in ItemsNode.SelectNodes(“rs:data/z:row”, NameSpaceMgr))

{

data.DataRow newRow = result.NewRow();

foreach (data.DataColumn col in result.Columns)

{

if (Item.HasAttribute(“ows_” + (string)DisplayNames[col.ColumnName]))

{

string val = Item.GetAttribute(“ows_” + (string)DisplayNames[col.ColumnName]);

if (CheckLookup.IsMatch((string)val))

{

string valString = val as String;

val = valString.Substring(valString.IndexOf(“#”) + 1);

}

// Assigning a string to a field that expects numbers or

// datetime values will implicitly convert them

newRow[col] = val;

}

}

newRow[“XmlElement”] = Item;

result.Rows.Add(newRow);

}

return result;

}

// The following Function is used to Get Namespaces

private static XmlNamespaceManager _nsmgr;

private static XmlNamespaceManager NameSpaceMgr

{

get

{

if (_nsmgr == null)

{

_nsmgr = new XmlNamespaceManager(new NameTable());

_nsmgr.AddNamespace(“wss”, “http://schemas.microsoft.com/sharepoint/soap/”);

_nsmgr.AddNamespace(“s”, “uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882”);

_nsmgr.AddNamespace(“dt”, “uuid:C2F41010-65B3-11d1-A29F-00AA00C14882”);

_nsmgr.AddNamespace(“rs”, “urn:schemas-microsoft-com:rowset”);

_nsmgr.AddNamespace(“z”, “#RowsetSchema”);

}

return _nsmgr;

}

}

private Type TypeFromField(XmlElement field)

{

switch (field.GetAttribute(“Type”))

{

case “DateTime”:

return typeof(DateTime);

case “Integer”:

return typeof(int);

case “Number”:

return typeof(float);

default:

return typeof(string);

}

}

6 thoughts on “Reading SharePoint Lists into an ADO.Net DataTable”

  1. You should look at the SharePointUtility class included in the Sharepoint/SSIS Adapters at this location: http://www.codeplex.com/SQLSrvIntegrationSrvIt lets you get at the data in a SharePoint list using LinQ like expressinons, and also return only the columns you want, and update and more. Not exactly a dataset, but removes all of the ugly webservice plumbing code.

  2. Thanks, Kevin… I’ll keep it in mind once the client bumps up to 2008 tools. I’m still curious about an ADO.NET adapter… and I should think a true LinQ to SharePoint sites would be in order, somewhere, as well.

  3. The following works like a champ.ListsSoapClient proxy = BuildProxy();DataTable ret = null; XmlElement e = proxy.GetListItems(listName, default(string), null, null, default(string), null, default(string));DataSet ds = new DataSet(); XmlNodeReader reader = new XmlNodeReader(e);ds.ReadXml(reader);ret = ds.Tables[1];

  4. FYI, now there is a true ADO.NET connector available from bendsoft called Camelot .NET Connector (www.bendsoft.com). It allows you to query sharepoint lists/views using standard sql syntax.

  5. For GetSiteData you must successfully pass SPSiteDataQuery item. Fix it. Moreover, with SPSiteDataQuery it is possible to get record information from several details.

Leave a Reply

Your email address will not be published. Required fields are marked *