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