Eric Fang's SharePoint Boat: General solution to retrieve data from SharePoint list view

It's not easy to retrieve data from SharePoint List View in InfoPath, especially for business users. A link like below do the job, however, it asks for GUID of the list, and the internal name of filter field, which is not so easy to implement and maintain.

http://contoso/sites/sales/_vti_bin/owssvr.dll?Cmd=Display&List={115BC7B7-0A82-403E-9327-F3C73E6D37F3}&XMLDATA=TRUE&noredirect=true&FilterField1=xd__x007b_52AE1EF8_x002d_28E7_x002d_4CE4_x002d_AE23_x002d_54E23E80DDB5_x007d_&FilterValue1=Approved

Normally we can build a web service for that InfoPath data connection.

Let's say users want to retrieve data from a list view through Data Connection, and want to bind the data to Dropdown control.

The web service could be like below:

public class ListData : System.Web.Services.WebService

{

public class ReferenceData

{

public ReferenceData() { }

public int ID;

public string Title;

}

[WebMethod]

public ReferenceData[] getReferenceDataFromListView(string strListViewUrl)

{ //parameter: http://contoso/sites/sales/Lists/products/AllItems.aspx

StackTrace objStackTrace = new StackTrace();

string strLogMethodPrefix = objStackTrace.GetFrame(1).GetMethod().Name + "() - ";

ReferenceData[] resultList = null;

try

{

SPSecurity.RunWithElevatedPrivileges(delegate()

{

using (SPSite site = new SPSite(strListViewUrl))

{

using (SPWeb web = site.OpenWeb())

{

SPView objSPView = web.GetViewFromUrl(strListViewUrl);

SPList currentList = objSPView.ParentList;

SPListItemCollection oSPListItemCollection = currentList.GetItems(objSPView);

resultList = new ReferenceData[oSPListItemCollection.Count];

int iIndex = 0;

foreach (SPListItem currentListItem in oSPListItemCollection)

{

resultList[iIndex] = new ReferenceData();

resultList[iIndex].ID = currentListItem.ID;

resultList[iIndex].Title = currentListItem.Title;

iIndex++;

}

} // using spweb web = site.openweb()

}

});

}

catch (Exception ex)

{

SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex, strListViewUrl={1}", strLogMethodPrefix, strListViewUrl));

SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.Message={1}", strLogMethodPrefix, ex.Message));

SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.StackTrace={1}", strLogMethodPrefix, ex.StackTrace));

throw;

}

return resultList;

}

Below is the returned data.

As we can see, in the code, we need to define a internal class for the structure of the retrieved data. It's possibly OK for small system, but in real world, we may need to create and maintain hundreds of classes, which is not acceptable.

A better solution is to build a general web service for all list views.

[WebMethod]
public XmlDocument getDataFromListView(string strListViewUrl)
{ //parameter: http://contoso/sites/sales/Lists/products/AllItems.aspx
StackTrace objStackTrace = new StackTrace();
string strLogMethodPrefix = objStackTrace.GetFrame(1).GetMethod().Name + "() - ";

System.Data.DataTable objDataTable = null;
XmlDocument xmlDoc = new XmlDocument();
string strReturn = @"";

if (string.IsNullOrEmpty(strListViewUrl))
{
strReturn += @"";
xmlDoc.LoadXml(strReturn);
return xmlDoc;
}

try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(strListViewUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPView objSPView = web.GetViewFromUrl(strListViewUrl);
if (objSPView != null)
{
SPList currentList = objSPView.ParentList;
SPListItemCollection oSPListItemCollection = currentList.GetItems(objSPView);
//SPWSShared.sysWriteAppEntry(string.Format(@"{0} oSPListItemCollection.Count={1}", strLogMethodPrefix, oSPListItemCollection.Count));

objDataTable = oSPListItemCollection.GetDataTable();

string strItem = string.Empty;

strReturn += @"";
foreach (System.Data.DataRow currentItem in objDataTable.Rows)
{
strItem = string.Empty;
foreach (System.Data.DataColumn currentColumn in objDataTable.Columns)
{
strItem += string.Format("<{0}>{1}", currentColumn.ColumnName, currentItem[currentColumn.ColumnName]);
}
strReturn += string.Format("{0}", strItem);
}

strReturn += @"";
}
else
{
SPWSShared.sysWriteAppEntry(string.Format(@"{0} List view URL ({1}) is invalid!", strLogMethodPrefix, strListViewUrl));
}
}
}
});
}
catch (Exception ex)
{
SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex, List view URL ({1}) is invalid!", strLogMethodPrefix, strListViewUrl));
SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.Message={1}", strLogMethodPrefix, ex.Message));
SPWSShared.sysWriteAppEntry(string.Format(@"{0} ex.StackTrace={1}", strLogMethodPrefix, ex.StackTrace));

throw;
}

if (objDataTable != null)
{
xmlDoc.LoadXml(strReturn);
}

return xmlDoc;
}

Below is the returned data.