public ActionResult Index(string category = "") { var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext); using (var clientContext = HttpContext.GetUserClientContextForSPHost()) { ViewBag.SPHostUrl = spContext.SPHostUrl; ViewBag.Category = category; var list = clientContext.Web.GetListByTitle(ListDetails.EventsListName); var categoryQuery = string.Empty; if (!string.IsNullOrEmpty(category)) { categoryQuery = CAML.Where(CAML.Eq(CAML.FieldValue(Event.FIELD_CATEGORY, "Text", category))); } var orderByQuery = CAML.OrderBy(new OrderByField("Title")); var caml = new CamlQuery() { ViewXml = CAML.ViewQuery(categoryQuery, orderByQuery, rowLimit: 50) }; var events = list.GetItems(caml); clientContext.Load(events); clientContext.ExecuteQuery(); var eventsList = events.Cast <ListItem>().Select(item => new Event(item)).ToList(); return(View(eventsList)); } }
public ActionResult Featured(int maxCount = 3) { using (var clientContext = HttpContext.GetUserClientContextForSPHost()) { ViewBag.SPHostUrl = HttpContext.Request.QueryString["SPHostUrl"]; var caml = new CamlQuery() { ViewXml = CAML.ViewQuery( CAML.Where( CAML.And( CAML.Geq( CAML.FieldValue(Event.FIELD_DATE, "Date", CAML.Today()) ), CAML.Eq( CAML.FieldValue(Event.FIELD_CATEGORY, "Text", "Featured") ) ) ), CAML.OrderBy(new OrderByField(Event.FIELD_DATE)), rowLimit: maxCount) }; var list = clientContext.Web.Lists.GetByTitle(ListDetails.EventsListName); clientContext.Load(list); clientContext.ExecuteQuery(); var items = list.GetItems(caml); clientContext.Load(items); clientContext.ExecuteQuery(); var result = items.Cast <ListItem>() .Select(i => new Event(i)).ToList(); return(View(result)); } }
/// <summary> /// Query the list to retreive the last ID /// </summary> /// <param name="onlineLibrary">The List we will query</param> /// <param name="lastItemModifiedDate">The date of the last modified list item</param> /// <returns></returns> public static int QueryLastItemId(this List onlineLibrary, Nullable <DateTime> lastItemModifiedDate = null) { var returnId = 0; var camlFieldRefs = new string[] { "ID", "Created", "Modified", }; var camlViewClause = CAML.ViewFields(camlFieldRefs.Select(s => CAML.FieldRef(s)).ToArray()); var camlQuery = new CamlQuery() { ViewXml = CAML.ViewQuery( ViewScope.RecursiveAll, string.Empty, CAML.OrderBy(new OrderByField("ID", false)), camlViewClause, 1) }; ListItemCollectionPosition itemPosition = null; while (true) { camlQuery.ListItemCollectionPosition = itemPosition; var spListItems = onlineLibrary.GetItems(camlQuery); onlineLibrary.Context.Load(spListItems, lti => lti.ListItemCollectionPosition); onlineLibrary.Context.ExecuteQueryRetry(); itemPosition = spListItems.ListItemCollectionPosition; if (spListItems.Any()) { if (lastItemModifiedDate.HasValue) { foreach (var item in spListItems) { var itemModified = item.RetrieveListItemValue("Modified").ToNullableDatetime(); if (itemModified == lastItemModifiedDate) { returnId = item.Id; break; } } } else { returnId = spListItems.OrderByDescending(ob => ob.Id).FirstOrDefault().Id; } if (returnId > 0) { // Found the item ID that matches the specified date - return it break; } } if (itemPosition == null) { break; } } return(returnId); }
public void OrderBy_SendManyFieldRefsInOneString_DoesNotThrow() { const string orderByString = @"<FieldRef Name=""ID1"" /><FieldRef Name=""ID2"" /><FieldRef Name=""ID3"" />"; var correctResult = $"<OrderBy>{orderByString}</OrderBy>"; var res = CAML.OrderBy(orderByString); Assert.AreEqual(correctResult, res); }
/// <summary> /// Query the list to retreive the last ID /// </summary> /// <param name="onlineLibrary">The List we will query</param> /// <param name="lastItemModifiedDate">The date of the last modified list item</param> /// <returns></returns> public static int QueryLastItemId(this List onlineLibrary, Nullable <DateTime> lastItemModifiedDate = null) { var returnId = 0; var camlFieldRefs = new string[] { "ID", "Created", "Modified", }; var camlViewClause = CAML.ViewFields(camlFieldRefs.Select(s => CAML.FieldRef(s)).ToArray()); var camlQuery = new CamlQuery() { ViewXml = CAML.ViewQuery(string.Empty, CAML.OrderBy(new OrderByField("Modified", false)), 10) }; if (!lastItemModifiedDate.HasValue) { onlineLibrary.EnsureProperties(olp => olp.LastItemModifiedDate, olp => olp.LastItemUserModifiedDate); lastItemModifiedDate = onlineLibrary.LastItemModifiedDate; } ListItemCollectionPosition ListItemCollectionPosition = null; while (true) { camlQuery.ListItemCollectionPosition = ListItemCollectionPosition; var spListItems = onlineLibrary.GetItems(camlQuery); onlineLibrary.Context.Load(spListItems, lti => lti.ListItemCollectionPosition); onlineLibrary.Context.ExecuteQueryRetry(); ListItemCollectionPosition = spListItems.ListItemCollectionPosition; if (spListItems.Any()) { foreach (var item in spListItems) { var itemModified = item.RetrieveListItemValue("Modified").ToDateTime(); System.Diagnostics.Trace.TraceInformation("Item {0} Modified {1} IS MATCH:{2}", item.Id, itemModified, (itemModified == lastItemModifiedDate)); } returnId = spListItems.OrderByDescending(ob => ob.Id).FirstOrDefault().Id; break; } if (ListItemCollectionPosition == null) { break; } } return(returnId); }
public void Tag_ComplicatedQuery_DoesNotThrowXmlException() { Assert.DoesNotThrow(() => { var fieldRefId = CAML.FieldRef(BuiltInInternalFieldNames.ID); var orderByAsCaml = CAML.OrderBy( CAML.FieldRef(BuiltInInternalFieldNames.ID, CAML.SortType.Ascending) ); var viewFields = new List <string> { BuiltInInternalFieldNames.ID, BuiltInInternalFieldNames.Title }; var viewFieldsAsCaml = CAML.ViewFieldsByFieldNames(viewFields.ToArray()); const int maxRowLimit = 1000; var rowLimitAsCaml = CAML.RowLimit(maxRowLimit); const int startId = 1; const int endId = maxRowLimit; CAML.View( string.Concat( CAML.Query( string.Concat( CAML.Where( CAML.And( CAML.Geq( fieldRefId, CAML.Value(startId) ), CAML.Leq( fieldRefId, CAML.Value(endId) ) ) ), orderByAsCaml ) ), viewFieldsAsCaml, rowLimitAsCaml ) ); }); }
/// <summary> /// Execute the cmdlet /// </summary> public override void ExecuteCmdlet() { base.ExecuteCmdlet(); // Initialize logging instance with Powershell logger ITraceLogger logger = new DefaultUsageLogger(LogVerbose, LogWarning, LogError); // Construct the model var SiteComponents = new SiteProvisionerModel() { Lists = new List <SPListDefinition>() }; var ctx = this.ClientContext; var contextWeb = this.ClientContext.Web; ctx.Load(contextWeb, ctxw => ctxw.ServerRelativeUrl, wctx => wctx.Url, ctxw => ctxw.Id); ctx.ExecuteQueryRetry(); var _targetList = Identity.GetList(contextWeb, lctx => lctx.ItemCount, lctx => lctx.EnableFolderCreation, lctx => lctx.RootFolder, lctx => lctx.RootFolder.ServerRelativeUrl, lctx => lctx.RootFolder.Folders); var webUri = new Uri(contextWeb.Url); // Will query the list to determine the last item id in the list var lastItemId = _targetList.QueryLastItemId(); logger.LogInformation("List with item count {0} has a last ID of {1}", _targetList.ItemCount, lastItemId); logger.LogInformation("List has folder creation = {0}", _targetList.EnableFolderCreation); // store the OOTB standard fields var ootbCamlFields = new List <string>() { "Title", "ID", "Author", "Editor", "Created", "Modified" }; // Skip these specific fields var skiptypes = new FieldType[] { FieldType.Invalid, FieldType.Computed, FieldType.ContentTypeId, FieldType.Invalid, FieldType.WorkflowStatus, FieldType.WorkflowEventType, FieldType.Threading, FieldType.ThreadIndex, FieldType.Recurrence, FieldType.PageSeparator, FieldType.OutcomeChoice, FieldType.CrossProjectLink, FieldType.ModStat, FieldType.Error, FieldType.MaxItems, FieldType.Attachments }; // pull a small portion of the list // wire up temporary array var sitelists = new List <SPListDefinition>() { ctx.GetListDefinition(_targetList, ExpandObjects, logger, skiptypes) }; if (sitelists.Any()) { var idx = 0; // lets add any list with NO lookups first var nolookups = sitelists.Where(sl => !sl.ListDependency.Any()); nolookups.ToList().ForEach(nolookup => { logger.LogInformation("adding list {0}", nolookup.ListName); nolookup.ProvisionOrder = idx++; SiteComponents.Lists.Add(nolookup); sitelists.Remove(nolookup); }); // order with first in stack var haslookups = sitelists.Where(sl => sl.ListDependency.Any()).OrderBy(ob => ob.ListDependency.Count()).ToList(); while (haslookups.Count() > 0) { var listPopped = haslookups.FirstOrDefault(); haslookups.Remove(listPopped); logger.LogInformation("popping list {0}", listPopped.ListName); if (listPopped.ListDependency.Any(listField => listPopped.ListName.Equals(listField, StringComparison.InvariantCultureIgnoreCase) || listPopped.InternalName.Equals(listField, StringComparison.InvariantCultureIgnoreCase))) { // the list has a dependency on itself logger.LogInformation("Adding list {0} with dependency on itself", listPopped.ListName); listPopped.ProvisionOrder = idx++; SiteComponents.Lists.Add(listPopped); } else if (listPopped.ListDependency.Any(listField => !SiteComponents.Lists.Any(sc => sc.ListName.Equals(listField, StringComparison.InvariantCultureIgnoreCase) || sc.InternalName.Equals(listField, StringComparison.InvariantCultureIgnoreCase)))) { // no list definition exists in the collection with the dependent lookup lists logger.LogWarning("List {0} depends on {1} which do not exist current collection", listPopped.ListName, string.Join(",", listPopped.ListDependency)); foreach (var dependent in listPopped.ListDependency.Where(dlist => !haslookups.Any(hl => hl.ListName == dlist))) { var sitelist = contextWeb.GetListByTitle(dependent, lctx => lctx.Id, lctx => lctx.Title, lctx => lctx.RootFolder.ServerRelativeUrl); var sitelistDefinition = ctx.GetListDefinition(contextWeb, sitelist, true, logger, skiptypes); haslookups.Add(sitelistDefinition); } haslookups.Add(listPopped); // add back to collection listPopped = null; } else { logger.LogInformation("Adding list {0} to collection", listPopped.ListName); listPopped.ProvisionOrder = idx++; SiteComponents.Lists.Add(listPopped); } } } foreach (var listDefinition in SiteComponents.Lists.OrderBy(ob => ob.ProvisionOrder)) { listDefinition.ListItems = new List <SPListItemDefinition>(); var camlFields = new List <string>(); var listTitle = listDefinition.ListName; var etlList = this.ClientContext.Web.GetListByTitle(listTitle, lctx => lctx.Id, lctx => lctx.ItemCount, lctx => lctx.EnableFolderCreation, lctx => lctx.RootFolder.ServerRelativeUrl); if (ExpandObjects) { // list fields var listFields = listDefinition.FieldDefinitions; if (listFields != null && listFields.Any()) { var filteredListFields = listFields.Where(lf => !skiptypes.Any(st => lf.FieldTypeKind == st)).ToList(); var notInCamlFields = listFields.Where(listField => !ootbCamlFields.Any(cf => cf.Equals(listField.InternalName, StringComparison.CurrentCultureIgnoreCase)) && !skiptypes.Any(st => listField.FieldTypeKind == st)); foreach (var listField in notInCamlFields) { logger.LogInformation("Processing list {0} field {1}", etlList.Title, listField.InternalName); camlFields.Add(listField.InternalName); } } } camlFields.AddRange(ootbCamlFields); var camlViewFields = CAML.ViewFields(camlFields.Select(s => CAML.FieldRef(s)).ToArray()); ListItemCollectionPosition itemPosition = null; var camlQueries = etlList.SafeCamlClauseFromThreshold(2000, CamlStatement, 0, lastItemId); foreach (var camlAndValue in camlQueries) { itemPosition = null; var camlWhereClause = (string.IsNullOrEmpty(camlAndValue) ? string.Empty : CAML.Where(camlAndValue)); var camlQuery = new CamlQuery() { ViewXml = CAML.ViewQuery( ViewScope.RecursiveAll, camlWhereClause, CAML.OrderBy(new OrderByField("ID")), camlViewFields, 500) }; try { while (true) { logger.LogWarning("CAML Query {0} at position {1}", camlWhereClause, (itemPosition == null ? string.Empty : itemPosition.PagingInfo)); camlQuery.ListItemCollectionPosition = itemPosition; var listItems = etlList.GetItems(camlQuery); etlList.Context.Load(listItems); etlList.Context.ExecuteQueryRetry(); itemPosition = listItems.ListItemCollectionPosition; foreach (var rbiItem in listItems) { var itemId = rbiItem.Id; var itemTitle = rbiItem.RetrieveListItemValue("Title"); var author = rbiItem.RetrieveListItemUserValue("Author"); var editor = rbiItem.RetrieveListItemUserValue("Editor"); logger.LogInformation("Title: {0}; Item ID: {1}", itemTitle, itemId); var newitem = new SPListItemDefinition() { Id = itemId, Title = itemTitle, Created = rbiItem.RetrieveListItemValue("Created").ToNullableDatetime(), Modified = rbiItem.RetrieveListItemValue("Modified").ToNullableDatetime() }; if (author != null) { newitem.CreatedBy = new SPPrincipalUserDefinition() { Email = author.ToUserEmailValue(), LoginName = author.ToUserValue(), Id = author.LookupId }; } if (editor != null) { newitem.ModifiedBy = new SPPrincipalUserDefinition() { Email = editor.ToUserEmailValue(), LoginName = editor.ToUserValue(), Id = editor.LookupId }; } if (ExpandObjects) { var fieldValuesToWrite = rbiItem.FieldValues.Where(rfield => !ootbCamlFields.Any(oc => rfield.Key.Equals(oc, StringComparison.CurrentCultureIgnoreCase))); foreach (var rbiField in fieldValuesToWrite) { newitem.ColumnValues.Add(new SPListItemFieldDefinition() { FieldName = rbiField.Key, FieldValue = rbiField.Value }); } } listDefinition.ListItems.Add(newitem); } if (itemPosition == null) { break; } } } catch (Exception ex) { logger.LogError(ex, "Failed to retrieve list item collection"); } } } WriteObject(SiteComponents); }