public DataTable ExecuteQuery(string list, QueryInfo query)
{
CheckDisposed();
//
// Construct the query ready for consumption by the SharePoint web services, including a <Query> root element.
//
XmlDocument doc = new XmlDocument();
XmlNode q = doc.CreateElement("Query");
if (query.Where != null && query.Where.ChildNodes.Count != 0)
{
q.AppendChild(doc.ImportNode(query.Where, true));
}
if (query.Order != null)
{
q.AppendChild(doc.ImportNode(query.Order, true));
}
if (query.Grouping != null)
{
q.AppendChild(doc.ImportNode(query.Grouping, true));
}
//
// Retrieve the results of the query via a web service call, using the projection and a row limit (if set).
//
uint top = 0;
if (query.Top != null)
{
top = (uint)query.Top.Value;
if (top == 0) //FIX: don't roundtrip to server if no results are requested.
{
return(new DataTable());
}
}
//
// Store results in a DataSet for easy iteration.
//
DataSet results = new DataSet();
//
// Get data; server-side result paging could occur.
//
XmlNode res;
string nextPage = null;
bool page = false;
do
{
//
// Set query options.
//
XmlNode queryOptions = doc.CreateElement("QueryOptions");
XmlElement inclMand = doc.CreateElement("IncludeMandatoryColumns");
inclMand.InnerText = "FALSE";
queryOptions.AppendChild(inclMand);
XmlElement utcDate = doc.CreateElement("DateInUtc");
utcDate.InnerText = "TRUE";
queryOptions.AppendChild(utcDate);
if (nextPage != null)
{
XmlElement paging = Paging(doc, nextPage);
queryOptions.AppendChild(paging);
}
//
// Make web service call.
//
try
{
res = _wsProxy.GetListItems(list, null, q, query.Projection, query.Top == null ? null : top.ToString(CultureInfo.InvariantCulture.NumberFormat), queryOptions, null);
}
catch (SoapException ex)
{
throw RuntimeErrors.ConnectionExceptionWs(_wsProxy.Url, ex);
}
//
// Merge results.
//
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
ds.ReadXml(new StringReader(res.OuterXml));
results.Merge(ds);
//
// If no results found, break.
//
if (ds.Tables["row"] == null)
{
break;
}
//
// Avoid paging when a row limit has been set (Take query operator).
//
if (query.Top != null && results.Tables["row"].Rows.Count >= query.Top)
{
break;
}
//
// Check for paging.
//
nextPage = res["rs:data"].GetAttribute("ListItemCollectionPositionNext");
page = !string.IsNullOrEmpty(nextPage);
} while (page);
//
// Make sure results are available. If not, return nothing.
//
DataTable src = results.Tables["row"];
if (src == null)
{
return(new DataTable());
}
//
// Determine columns. Trim ows_ prefix.
//
DataTable tbl = new DataTable();
tbl.Locale = CultureInfo.InvariantCulture;
foreach (DataColumn col in src.Columns)
{
tbl.Columns.Add(col.ColumnName.Substring("ows_".Length));
}
//
// Store data in DataTable.
//
foreach (DataRow srcRow in src.Rows)
{
DataRow row = tbl.NewRow();
foreach (DataColumn col in src.Columns)
{
row[col.ColumnName.Substring("ows_".Length)] = srcRow[col];
}
tbl.Rows.Add(row);
}
return(tbl);
}
public DataTable ExecuteQuery(string list, QueryInfo query)
{
CheckDisposed();
//
// Construct the query ready for consumption by the SharePoint Object Model, without <Query> root element.
//
StringBuilder queryBuilder = new StringBuilder();
XmlWriterSettings settings = new XmlWriterSettings();
settings.OmitXmlDeclaration = true;
settings.ConformanceLevel = ConformanceLevel.Auto;
using (XmlWriter xw = XmlWriter.Create(queryBuilder, settings))
{
if (query.Where != null && query.Where.ChildNodes.Count != 0)
{
query.Where.WriteTo(xw);
}
if (query.Order != null)
{
query.Order.WriteTo(xw);
}
if (query.Grouping != null)
{
query.Grouping.WriteTo(xw);
}
xw.Flush();
}
//
// Make the SharePoint SPQuery object.
//
SPQuery q = new SPQuery();
q.Query = queryBuilder.ToString();
q.IncludeMandatoryColumns = false;
q.DatesInUtc = true;
//
// Include projection fields if a projection clause has been parsed.
//
if (query.Projection != null)
{
q.ViewFields = query.Projection.InnerXml;
}
//
// In case a row limit (Take) was found, set the limit on the query.
//
if (query.Top != null)
{
uint top = (uint)query.Top.Value;
if (top == 0)
{
return(new DataTable());
}
else
{
q.RowLimit = top;
}
}
//
// Execute the query via the SPList object.
//
SPListItemCollection items;
SPList lst = _site.RootWeb.Lists[list];
try
{
items = lst.GetItems(q);
}
catch (Exception ex)
{
throw RuntimeErrors.ConnectionExceptionSp(_site.Url, ex);
}
DataTable tbl = new DataTable();
tbl.Locale = CultureInfo.InvariantCulture;
Debug.Assert(query.Projection != null);
//
// Set projection columns.
//
foreach (XmlNode node in query.Projection.ChildNodes)
{
tbl.Columns.Add(XmlConvert.DecodeName(node.Attributes["Name"].Value));
}
//
// Retrieve data rows.
//
foreach (SPListItem item in items)
{
DataRow row = tbl.NewRow();
foreach (DataColumn col in tbl.Columns)
{
object o = item[col.ColumnName];
if (o is DateTime)
{
o = SPUtility.CreateISO8601DateTimeFromSystemDateTime((DateTime)o);
}
row[col] = o;
}
tbl.Rows.Add(row);
}
return(tbl);
}
