Tuesday, September 30, 2008

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);


}


}

Post a Comment