public static StringBuilder FindChanges() { StringBuilder sb = new StringBuilder(); SPSecurity.RunWithElevatedPrivileges(delegate() { SPSiteDataQuery query1 = new SPSiteDataQuery(); //query1.ViewFields ="<OrderBy><FieldRef Name=\"Modified\" Ascending=\"False\" /></OrderBy><Where><Gt><FieldRef Name='Modified' /><Value Type='DateTime'><Today OffsetDays='-7' /></Value></Gt></Where>"; query1.RowLimit = 100; //query1.Webs = "<Webs Scope=\"Recursive\" />"; query1.Lists = "<Lists BaseType=\"0\" />"; using (SPSite site = new SPSite(SPContext.Current.Web.Url)) { DataTable dt = site.RootWeb.GetSiteData(query1); foreach (DataRow row in dt.Rows) { foreach (DataColumn column in dt.Columns) { sb.Append("dt[" + column.ColumnName + "]: " + row[column]); } } } }); return(sb); }
public static DataTable GetSPListItemsBySiteSelf(Guid siteId, string webUrl, string listTemplate, string querystr, string viewFieldsstr, uint rowLimit) { DataTable dataTable = null; try { SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSite site = new SPSite(siteId)) { using (SPWeb web = site.OpenWeb(webUrl)) { SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate='" + listTemplate + "' />"; query.Webs = "<Webs Scope='Recursive' />"; query.Query = querystr; query.ViewFields = viewFieldsstr; if (rowLimit != 0) { query.RowLimit = rowLimit; } dataTable = web.GetSiteData(query); } } }); } catch (Exception ex) { throw ex; } return(dataTable); }
public void QueryMyWorkData_WhenCalled_ReturnsDataTable() { // Arrange var spSiteDataQuery = new SPSiteDataQuery(); var userToken = new ShimSPUserToken().Instance; var dataTable = new DataTable(); dataTable.Columns.Add(ListIdColumn); var row = dataTable.NewRow(); row[ListIdColumn] = guid.ToString(); dataTable.Rows.Add(row); spWeb.GetSiteDataSPSiteDataQuery = _ => dataTable; // Act var actual = (DataTable)privateObj.Invoke( QueryMyWorkDataMethodName, BindingFlags.Static | BindingFlags.NonPublic, new object[] { spSiteDataQuery, string.Empty, guid, userToken }); // Assert actual.ShouldSatisfyAllConditions( () => actual.Rows.Count.ShouldBe(1), () => actual.Rows[0][ListIdColumn].ShouldBe(guid.ToString())); }
public void getSiteItems_HasListIds_GetSiteDataExecuted() { // Arrange var resultExpected = new DataTable(); SPSiteDataQuery queryUsed = null; _sharepointShims.WebShim.GetSiteDataSPSiteDataQuery = query => { queryUsed = query; return(resultExpected); }; // Act var result = CoreFunctions.getSiteItems( _sharepointShims.WebShim, _sharepointShims.ViewShim, _spQuery, _filterFieldName, _useWbs, _listTitlePattern, _groupByFieldNames); // Assert Assert.AreEqual(resultExpected, result); Assert.IsNotNull(queryUsed); Assert.AreEqual($"<Lists MaxListLimit='0'><List ID='{Guid.Empty}'/></Lists>", queryUsed.Lists); Assert.AreEqual(_spQuery, queryUsed.Query); Assert.AreEqual(SPQueryThrottleOption.Override, queryUsed.QueryThrottleMode); }
/// <summary> /// Binds this query to an SPGridView control. /// </summary> /// <param name="web"></param> /// <param name="gridView"></param> /// <param name="query"></param> /// <param name="initColumns"></param> private void BindToGridView(SPWeb web, SPGridView gridView, SPSiteDataQuery query, bool initColumns) { // Execute the query. DataTable table = web.GetSiteData(query); // Initialize the gridview columns, if requested if (initColumns) { gridView.AutoGenerateColumns = false; gridView.Columns.Clear(); foreach (var col in table.Columns.Cast <DataColumn>() .Where(IsBindableColumn) .Select(column => new BoundField { DataField = column.ColumnName, HeaderText = string.IsNullOrEmpty(column.Caption) ? column.ColumnName : column.Caption })) { gridView.Columns.Add(col); } } // Bind the data to the view. gridView.DataSource = table; gridView.DataBind(); }
private static SPSiteDataQuery SpSiteDataQuery(string query, List <string> selectedFields) { Guard.ArgumentIsNotNull(selectedFields, nameof(selectedFields)); var dataQuery = new SPSiteDataQuery { Webs = @"<Webs Scope='Recursive'>", Query = query, QueryThrottleMode = SPQueryThrottleOption.Override }; var viewFieldsBuilder = new StringBuilder(dataQuery.ViewFields); foreach (var selectedField in selectedFields) { viewFieldsBuilder.Append($@"<FieldRef Name='{selectedField}' Nullable='TRUE'/>"); } foreach (var field in new[] { CompletedField, WorkingOnField }.Where(field => !selectedFields.Exists(f => f.Equals(field)))) { viewFieldsBuilder.Append($"<FieldRef Name='{field}' Nullable='TRUE'/>"); } dataQuery.ViewFields = viewFieldsBuilder.ToString(); return(dataQuery); }
public void ConstructDataQuery(SPSiteDataQuery q) { if (_listIds.Count == 0) { q.Lists = string.Format("<Lists BaseType='{0}'/>", (int)_listType); } else { string lists = "<Lists>"; foreach (Guid listId in _listIds) { lists += string.Format("<List ID='{0}' />", listId); } lists += "</Lists>"; q.Lists = lists; } q.Query = _query; if (_webs != Scope.Current) { q.Webs = string.Format("<Webs Scope='{0}' />", _webs); } if (_viewFields.Count > 0) { string viewFields = ""; foreach (string viewField in _viewFields) { viewFields += string.Format("<FieldRef Name='{0}'/>", viewField); } q.ViewFields = viewFields; } }
/// <summary> /// Build a CAML SPSiteDataQuery object, based on the filter expressions that have been added to it. /// </summary> /// <returns></returns> public SPSiteDataQuery BuildSiteDataQuery() { var query = new SPSiteDataQuery { Query = Build() }; return(query); }
public SPSiteDataQueryInstance(ObjectInstance prototype) : base(prototype) { this.PopulateFields(); this.PopulateFunctions(); m_siteDataQuery = new SPSiteDataQuery(); }
public virtual DataTable Get(string queryString) { var query = new SPSiteDataQuery() { Lists = ListTemplate, Webs = WebScope, ViewFields = ViewFields, Query = queryString, QueryThrottleMode = SPQueryThrottleOption.Override }; return Get(query); }
public void GetDataFromSP_WhenCalled_ReturnsDataTables() { // Arrange var selectedListIds = new List <string>(); var spSiteDataQuery = new SPSiteDataQuery(); var row = default(DataRow); var dataTable = new DataTable(); dataTable.Columns.Add(ListIdColumn); dataTable.Columns.Add(IDColumn); dataTable.Columns.Add(WorkingOnColumn); row = dataTable.NewRow(); row[ListIdColumn] = DummyString; row[IDColumn] = DummyString; row[WorkingOnColumn] = DummyString; dataTable.Rows.Add(row); var workingTable = new DataTable(); workingTable.Columns.Add(ListIdColumn); workingTable.Columns.Add(ItemIdColumn); row = workingTable.NewRow(); row[ListIdColumn] = DummyString; row[ItemIdColumn] = DummyString; workingTable.Rows.Add(row); ShimMyWork.GetListIdsFromDbStringSPWebListOfGuid = (_, _1, _2) => new List <string>() { $"{DummyString}{DummyString}" }; spWeb.GetSiteDataSPSiteDataQuery = _ => dataTable; ShimMyWork.GetWorkingOnSPWeb = _ => workingTable; GetMyWorkParams.SelectedLists = new List <string>() { DummyString }; GetMyWorkParams.ArchivedWebs = new List <Guid>() { guid }; // Act var actual = (List <DataTable>)privateObj.Invoke( GetDataFromSPMethodName, BindingFlags.Static | BindingFlags.NonPublic, new object[] { selectedListIds, spSiteDataQuery, spWeb.Instance, spSite.Instance }); // Assert actual.ShouldSatisfyAllConditions( () => actual.Count.ShouldBe(1), () => actual[0].Rows.Count.ShouldBe(1), () => actual[0].Rows[0][WorkingOnColumn].ToString().ToLower().ShouldBe("true")); }
public EstimatesService() { query = new SPSiteDataQuery(); query.Lists = "<Lists BaseType='1' />"; query.ViewFields = "<FieldRef Name='SOWStatus' />" + "<FieldRef Name='EstimateValue' />"; query.Query = "<OrderBy>" + "<FieldRef Name='EstimateValue' />" + "</OrderBy>"; query.Webs = "<Webs Scope='SiteCollection' />"; }
/// <summary> /// Gets the data from SP. /// </summary> /// <param name="selectedListIds">The selected list ids.</param> /// <param name="dataQuery">The data query.</param> /// <param name="spWeb">The sp web.</param> /// <param name="spSite">The sp site.</param> /// <param name="archivedWebs">The archived webs.</param> /// <param name="selectedLists">The selected lists.</param> /// <returns></returns> private static IEnumerable <DataTable> GetDataFromSp( IList <string> selectedListIds, SPSiteDataQuery dataQuery, SPWeb spWeb, SPSite spSite) { Guard.ArgumentIsNotNull(spWeb, nameof(spWeb)); Guard.ArgumentIsNotNull(selectedListIds, nameof(selectedListIds)); Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery)); Guard.ArgumentIsNotNull(spSite, nameof(spSite)); var locker = new object(); var eventWaitHandles = new List <EventWaitHandle>(); var dataTables = new List <DataTable>(); var spExceptionOccured = false; foreach (var selectedList in GetMyWorkParams.SelectedLists.Distinct().OrderBy(l => l)) { var theSelectedList = selectedList; var listIdsBuilder = new StringBuilder(); foreach ( var listId in GetListIdsFromDb(selectedList, spWeb, GetMyWorkParams.ArchivedWebs) .Where(listId => !selectedListIds.Contains(listId))) { selectedListIds.Add(listId); listIdsBuilder.Append($@"<List ID='{listId}'/>"); } var listIds = listIdsBuilder.ToString(); if (string.IsNullOrWhiteSpace(listIds)) { continue; } spExceptionOccured = SpExceptionOccured(dataQuery, spWeb, spSite, listIds, eventWaitHandles, selectedList, locker, dataTables, theSelectedList); } WaitHandle.WaitAll(eventWaitHandles.ToArray()); foreach (var eventWaitHandle in eventWaitHandles) { eventWaitHandle?.Dispose(); } if (spExceptionOccured) { throw new APIException(2016, CannotRunSpDataQuery); } ProcessDictWorkingOn(spWeb, dataTables); return(dataTables); }
private string BuildSiteMap(string siteUrl, bool recursive) { string sitemap = string.Empty; try { if (recursive) { //using (SPWeb web = SPContext.Current.Site.RootWeb) //{ StringBuilder mapWriter = new StringBuilder(string.Empty); SPSiteDataQuery sdquery = new SPSiteDataQuery(); sdquery.Query = "<Where><Neq><FieldRef Name=\"ContentType\" /><Value Type=\"Text\"></Value></Neq></Where>"; sdquery.ViewFields = "<FieldRef Name=\"Title\"/>"; //<FieldRef Name=\"EncodedAbsUrl\"/><FieldRef Name=\"FileRef\"/><FieldRef Name=\"ContentType\"/><FieldRef Name=\"Modified\"/>"; sdquery.Lists = "<Lists ServerTemplate=\"20002\"/>"; //sdquery.Lists = "<Lists BaseType=\"1\" />"; //sdquery.Webs = "<Webs Scope=\"SiteCollection\" />"; //sdquery.QueryThrottleMode = SPQueryThrottleOption.Override; DataTable pages = SPContext.Current.Site.RootWeb.GetSiteData(sdquery); // web.GetSiteData(sdquery); pages.TableName = "SiteMap"; StringWriter sw = new StringWriter(mapWriter); pages.WriteXml(sw); sw.Flush(); sitemap = mapWriter.ToString(); //} } //using (SPSite site = new SPSite(siteUrl)) //{ // using (SPWeb web = site.OpenWeb()) // { // textWriter.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); // textWriter.Append("<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">"); // LoadTreeViewForSubWebs(web); // kick it off with the root web // textWriter.Append("</urlset>"); // MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(textWriter.ToString())); // web.Files.Add("sitemap.xml", stream, true); // stream.Close(); // } //} }//try catch (Exception ex) { ex.ToString(); //errorHandler(ex, "BuildSitemap"); } return(sitemap); }
public virtual DataTable Get(string queryString, uint startRow, uint maxRows) { var query = new SPSiteDataQuery() { Lists = ListTemplate, Webs = WebScope, ViewFields = ViewFields, Query = queryString, RowLimit = startRow + maxRows, QueryThrottleMode = SPQueryThrottleOption.Override }; return Get(query, startRow, maxRows); }
public virtual SPSiteDataQuery BuildCaml(SiteQuery query) { SPSiteDataQuery resultQuery = new SPSiteDataQuery(); resultQuery.Webs = BuildCaml(query.WebScope); resultQuery.Lists = BuildCaml(query.ListsReference); resultQuery.Query = BuildCaml(query.Query); resultQuery.ViewFields = BuildCaml(query.ViewFields.ToArray()); return(resultQuery); }
/// <summary> /// Provide the view fields as a string[]. /// Example: query.SetViewFields(new string[] {"ID", "Title"}); /// </summary> /// <param name="viewFields"></param> /// <returns></returns> public static void SetViewFields(this SPSiteDataQuery query, string[] viewFields) { StringBuilder sb = new StringBuilder(); foreach (string s in viewFields) { sb.AppendFormat(VIEW_FIELD_FORMAT, s); } query.ViewFields = sb.ToString(); }
private int getCount(string list, string query, SPWeb curWeb) { string siteurl = curWeb.ServerRelativeUrl.Substring(1); SPSiteDataQuery dq = new SPSiteDataQuery(); int count = 0; try { string lists = ""; string squery = ""; if (siteurl == "") { squery = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE webs.siteid='" + web.Site.ID + "' AND (dbo.AllLists.tp_Title like '" + list.Replace("'", "''") + "')"; } else { squery = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '" + siteurl + "/%' OR dbo.Webs.FullUrl = '" + siteurl + "') AND (dbo.AllLists.tp_Title like '" + list.Replace("'", "''") + "')"; } SqlCommand cmd = new SqlCommand(squery, cn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { lists += "<List ID='" + dr.GetGuid(0).ToString() + "'/>"; } dr.Close(); //dq.Lists = "<Lists ServerTemplate='10701'/>"; dq.Lists = "<Lists>" + lists + "</Lists>"; dq.Query = "<Where>" + query + "</Where>"; dq.Webs = "<Webs Scope='Recursive'/>"; dq.QueryThrottleMode = SPQueryThrottleOption.Override; DataTable dt = new DataTable(); SPSecurity.RunWithElevatedPrivileges(delegate() { dt = curWeb.GetSiteData(dq); }); count = dt.Rows.Count; } catch (Exception ex) { sb.Append("ERROR: " + ex.Message + ex.StackTrace); } return(count); }
static void Main(string[] args) { var array = "隐患下达任务;隐患整改任务;隐患复查任务;隐患验收任务;隐患签字任务;隐患签字任务;".Trim(';').Split(';').AsEnumerable(); var siteUrl = "http://58.132.202.99/sites/yanglijun"; using (SPSite site = new SPSite(siteUrl)) { using (SPWeb web = site.OpenWeb()) { var queryString = new CAMLQueryBuilder() .AddCurrentUser(SPBuiltInFieldName.AssignedTo) .OrCurrentUserGroups(SPBuiltInFieldName.AssignedTo) .AddNotEqual(SPBuiltInFieldName.TaskStatus, "Completed") .AddIsNotNull(SPBuiltInFieldName.RelatedItems).Build(); var query = new SPSiteDataQuery() { Webs = "<Webs Scope='SiteCollection' />", Lists = "<Lists ServerTemplate='171' BaseType='0' />", ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ContentType' />", Query = queryString, QueryThrottleMode = SPQueryThrottleOption.Override, RowLimit = 100 }; var data = web.GetSiteData(query); Console.WriteLine(data.Rows.Count); Console.ReadKey(); //foreach (SPGroup group in web.SiteGroups) { // var roleDefinitions = web.RoleDefinitions; // var roleAssignments = web.RoleAssignments; // var assignment = new SPRoleAssignment(group); // var bindings = assignment.RoleDefinitionBindings; // bindings.Add(roleDefinitions["参与讨论"]); // roleAssignments.Add(assignment); // Console.WriteLine(group.Name); //} // SPList list = web.Lists["Workflow Tasks"]; // SPView view = list.Views["所有任务"]; // view.Query = @"<Where> // <Or> // <Membership Type=""CurrentUserGroups"" > // <FieldRef Name=""AssignedTo"" /> // </Membership> // <In> // <FieldRef Name=""AssignedTo"" LookupId=""TRUE"" /> // <Values> // <Value Type=""Integer"" > // <UserID /> // </Value> // </Values> // </In> // </Or> // </Where>"; // view.Update(); } } }
private void AppendLookupQueryIfApplicable(SPSiteDataQuery dq) { if (!IsLookupQuery()) { return; } var queryXml = new XmlDocument(); queryXml.LoadXml(dq.Query); LookupFilterHelper.AppendLookupQueryToExistingQuery(ref queryXml, ChartLookupField, ChartLookupFieldList); dq.Query = queryXml.OuterXml; }
protected void loadFromList() { using (SPSite oSPsite = new SPSite(SPContext.Current.Web.Url)) { using (SPWeb oSPWeb = oSPsite.OpenWeb()) { // Fetch using SPSiteDataQuery SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"104\" />";//104=List template ID for "Anouncements" query.ViewFields = "<FieldRef Name=\"Title\" />";//Add other fields which you want to get. query.Query = "";//you can add your caml query if you want to filter the list items returned query.Webs = "<Webs Scope=\"SiteCollection\" />";//Scope is set to site collection to get data from all anouncement lists in that site collection. DataTable dataTable = oSPWeb.GetSiteData(query); } } }
protected void loadFromList() { using (SPSite oSPsite = new SPSite(SPContext.Current.Web.Url)) { using (SPWeb oSPWeb = oSPsite.OpenWeb()) { // Fetch using SPSiteDataQuery SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"104\" />"; //104=List template ID for "Anouncements" query.ViewFields = "<FieldRef Name=\"Title\" />"; //Add other fields which you want to get. query.Query = ""; //you can add your caml query if you want to filter the list items returned query.Webs = "<Webs Scope=\"SiteCollection\" />"; //Scope is set to site collection to get data from all anouncement lists in that site collection. DataTable dataTable = oSPWeb.GetSiteData(query); } } }
/// <summary> /// Creates and initializes an SPSiteDataQuery object for a given scope. /// </summary> public SPSiteDataQuery CreateSiteDataQuery(CAML.QueryScope scope) { var query = new SPSiteDataQuery { Query = QueryXml, ViewFields = ViewFieldsXml, Lists = ListsXml }; // Only use the Webs clause if the scope is different than the default. if (scope != CAML.QueryScope.WebOnly) { query.Webs = CAML.Webs(scope); } return(query); }
/// <summary> /// Synchronous before event that occurs before an existing item is completely deleted. /// </summary> /// <param name="properties"> /// A Microsoft.SharePoint.SPItemEventProperties object that represents properties of the event handler. /// </param> public override void ItemDeleting(SPItemEventProperties properties) { //props.projectname = properties.ListItem.ID.ToString() + ";#" + properties.ListItem.Title; //props.web = properties.ListItem.Web; propStruct props = new propStruct(); SPWeb web = properties.ListItem.Web; { SortedList <string, DataTable> lstListDataToDelete = new SortedList <string, DataTable>(); foreach (SPList list in web.Lists) { string fieldname = ""; foreach (SPField field in list.Fields) { if (field.SchemaXml.Contains("Type=\"Lookup") && field.SchemaXml.ToLower().Contains("{" + properties.ListId.ToString().ToLower() + "}")) { fieldname = field.InternalName; SPSiteDataQuery query = new SPSiteDataQuery(); query.QueryThrottleMode = SPQueryThrottleOption.Override; //Used to set/reset throttling while retrieving records using CAML query. query.Lists = "<Lists><List ID=\"" + list.ID.ToString() + "\"/></Lists>"; //query.RowLimit = (uint)0; query.Query = "<Where><Eq><FieldRef Name=\"" + field.InternalName + "\" LookupId=\"True\"/><Value Type=\"Lookup\">" + properties.ListItemId + "</Value></Eq></Where>"; query.ViewFields = "<FieldRef Name=\"Title\"/>"; DataTable dt = web.GetSiteData(query); if (dt != null && dt.Rows.Count > 0) { lstListDataToDelete.Add(list.Title, dt); } } } if (fieldname != "") { } } props.lstListDataToDelete = lstListDataToDelete; props.weburl = web.Url; } Thread thrDownload = new Thread(new ParameterizedThreadStart(processDelete)); thrDownload.IsBackground = true; thrDownload.Start(props); }
public EstimatesService() { query = new SPSiteDataQuery(); query.Lists = "<Lists BaseType='1' />"; query.ViewFields = "<FieldRef Name='SOWStatus' />" + "<FieldRef Name='EstimateValue' />" + "<FieldRef Name='VendorName' />"; query.Query = "<Where><And><Eq><FieldRef Name='SOWStatus' /><Value Type='Choice'>Approved</Value></Eq>" + "<Eq><FieldRef Name='ContentType' /><Value Type='Text'>" + Constants.estimateContentTypeName + "</Value></Eq></And></Where>" + "<OrderBy>" + "<FieldRef Name='EstimateValue' />" + "</OrderBy>"; query.Webs = "<Webs Scope='SiteCollection' />"; }
private static SPSiteDataQuery GetAllApprovedEstimatesFromSiteCollection() { var query = new SPSiteDataQuery(); query.Lists = "<Lists BaseType='1' />"; query.ViewFields = "<FieldRef Name='Title' Nullable='TRUE' />" + "<FieldRef Name='FileRef' Nullable='TRUE' />"; query.Query = "<Where>" + "<Eq>" + "<FieldRef Name='SOWStatus' />" + "<Value Type='Choice'>Approved</Value>" + "</Eq>" + "</Where>"; query.Webs = "<Webs Scope='SiteCollection' />"; return(query); }
protected void btnQuery_Click(object sender, EventArgs e) { //Retrieve Items From Multiple Lists using (SPSite site = new SPSite(SPContext.Current.Web.Url)) ; using (SPWeb web = site.OpenWeb()) ; SPSiteDataQuery dataQuery = new SPSiteDataQuery(); dataQuery.Webs = @"<Webs Scope='Recursive'/>"; dataQuery.Lists = @"<Lists ServerTempate='171'/>"; dataQuery.ViewFields = @"<FieldRef Name='Product' /><FieldRef Name='Status' />"; DataTable dt = web.GetSiteData(dataQuery); DataView dv = new DataView(dt); grdTasks.DataSource = dv; grdTasks.DataBind(); }
public DataTable GetItems(string caml, int rowLimit) { // Fetch using SPSiteDataQuery SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = string.Format("<Lists ServerTemplate=\"{0}\" />", this.TemplateId); query.ViewFields = GetViewFields(); query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = caml; if (rowLimit != 0) { query.RowLimit = (uint)rowLimit; } var data = Source.Site.RootWeb.GetSiteData(query); return(data); }
/// <summary> /// Queries my work data. /// </summary> /// <param name="dataQuery">The data query.</param> /// <param name="siteUrl">The site URL.</param> /// <param name="webId">The web id.</param> /// <param name="userToken">The user token.</param> /// <returns></returns> private static DataTable QueryMyWorkData( SPSiteDataQuery dataQuery, string siteUrl, Guid webId, SPUserToken userToken) { Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery)); DataTable dataTable = null; using (var spSite = new SPSite(siteUrl, userToken)) { using (var spWeb = spSite.OpenWeb(webId)) { SPSecurity.RunWithElevatedPrivileges(() => dataTable = spWeb.GetSiteData(dataQuery)); } } return(dataTable); }
public SPSiteDataQueryExecutor(string lists, string viewFields, string query, string webs, int rowLimit) { _dataQuery = new SPSiteDataQuery(); if (string.IsNullOrEmpty(lists) == false) { _dataQuery.Lists = lists; } if (string.IsNullOrEmpty(viewFields) == false) { _dataQuery.ViewFields = viewFields; } if (string.IsNullOrEmpty(query) == false) { _dataQuery.Query = query; } _dataQuery.RowLimit = (uint)rowLimit; }
private static void ProcessListFromDataTable(SPWeb web, ref string errors, Action <DataTable> processListAction, SPList list, string dquery, string dqFields) { var spSiteDataQuery = new SPSiteDataQuery { Lists = string.Format("<Lists MaxListLimit='0'><List ID='{0}'/></Lists>", list.ID), Query = dquery, ViewFields = dqFields, QueryThrottleMode = SPQueryThrottleOption.Override }; try { var dataTable = web.GetSiteData(spSiteDataQuery); processListAction?.Invoke(dataTable); } catch (Exception ex) { Trace.TraceError("Exception Suppressed {0}", ex); errors += string.Format("Get Site Data Error: {0}<br>", ex.Message); } }
protected void GetData() { DataTable dtResults = null; using (SPWeb Web = SPContext.Current.Site.RootWeb) { SPSiteDataQuery Query = new SPSiteDataQuery(); Query.Lists = "<Lists ServerTemplate=\"108\" />"; //"<Lists BaseType=\"1\" />"; //Query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"ID\" /><FieldRef Name=\"Location\" /><FieldRef Name=\"EventDate\" /><FieldRef Name=\"Description\" /><FieldRef Name=\"ContentTypeId\" />"; //content type & property //Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + ToolsContentType + "</Value></Eq></Where>"; // Query.Webs = "<Webs Scope=\"Recursive\" />"; Query.Webs = "<Webs Scope=\"Recursive\" />"; dtResults = Web.GetSiteData(Query); //dtResults.Columns.Add("LinkField"); ////fixes the links and only uses 150 chars of the PublishingPageContentField //foreach (DataRow Row in dtResults.Rows) //{ // SPSite Site = SPContext.Current.Site; // Guid WebGuid = new Guid(Row["WebId"].ToString()); // using (SPWeb LinkWeb = Site.OpenWeb(WebGuid)) // { // Guid ListGuid = new Guid(Row["ListId"].ToString()); // SPList List = LinkWeb.Lists[ListGuid]; // Row["LinkField"] = LinkWeb.Url + "/" + List.RootFolder.Url + "/DispForm.aspx?ID=" + Row["ID"].ToString() + "&ContentTypeID=" + Row["ContentTypeId"].ToString() + "&IsDlg=1"; // } //} dtResults.TableName = "dtResults"; dtResults.WriteXml("C:\\temp\\events.xml"); } //DataView dvResults = new DataView(dtResults); //return FilterSearchData(dvResults); }
protected DataTable Get(SPSiteDataQuery query) { return Web.GetSiteData(query); }
public virtual SPGENRepositoryDataItemCollection FindDataItems(SPWeb web, SPSiteDataQuery query, string[] fieldNames) { DataTable dataTable = null; try { dataTable = web.GetSiteData(query); return new SPGENRepositoryDataItemCollection(dataTable, fieldNames); } catch { if (dataTable != null) dataTable.Dispose(); throw; } }
private static void DiscussionWebPartTest(string url) { try { string siteUrl = url; SPUser CurrentUser = null; using (SPSite site = new SPSite(url)) { CurrentUser = site.RootWeb.CurrentUser; } //GetData(); //SPSiteDataQuery query = new SPSiteDataQuery(); ////query.ViewFields = "<FieldRef Name=\"LinkDiscussionTitle\"><FieldRef Name=\"ID\">"; //query.Lists = "<Lists ServerTemplate=\"108\">"; //query.Webs = "<Webs Scope=\"SiteCollection\" />"; //SPWeb web = SPContext.Current.Site.RootWeb; //DataTable table = web.GetSiteData(query); //table.TableName = "Discussions"; //table.WriteXml("c:\\log.txt"); DateTime recentDate = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).AddDays(-30); List<string> communitiesWebUrl = new List<string>(); List<string> userCommunities = null; string html = string.Empty; string webRelUrl = String.Empty; //html += "Current Web url: " + SPContext.Current.Web.Url + "<br/>"; //html += "Initial current user: "******"<br/>"; SPSecurity.RunWithElevatedPrivileges(delegate() { //Rks: This was changed over from using SPContext.Current.Site.Id to using the url because // The Site object was opening against the default zone and the permissions there were not working // properly. using the siteurl zone means the objects are being opened in code against the same // zone that they are seeing through the browser. using (SPSite site = new SPSite(siteUrl)) { // Fetch using SPSiteDataQuery using (SPWeb web = site.OpenWeb()) { //Distinct webs from SiteDataQuery for responses during the last 7 days. SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. //RKS - 2013-09-04 - this was commented out because the standard discussion list does not include the AverageRating field. If this viewfield is not present, the discussion is not returned. //query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */ query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>"; DataTable dataTable = web.GetSiteData(query); foreach (DataRow row in dataTable.Rows) { webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl.Contains(webRelUrl)) { communitiesWebUrl.Add(webRelUrl); } } //Distinct webs from SiteDataQuery for responses to my posts. I got this CAML from Chris' code. // Fetch using SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. //RKS - 2013-09-04 - this was commented out because the standard discussion list does not include the AverageRating field. If this viewfield is not present, the discussion is not returned. //query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */ query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = string.Format(@"<Where> <And> <Eq> <FieldRef Name='ContentType' /> <Value Type='Computed'>Message</Value> </Eq> <Eq> <FieldRef Name='Author' LookupId='True' /> <Value Type='Integer'>{0}</Value> </Eq> </And> </Where>", CurrentUser.ID); dataTable = web.GetSiteData(query); foreach (DataRow row in dataTable.Rows) { webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl.Contains(webRelUrl)) { communitiesWebUrl.Add(webRelUrl); } } } //html += "using Site url: " + site.Url + "<br/>"; bool discussionExist = false; foreach (var communityWebUrl in communitiesWebUrl) { //html += "> " + communityWebUrl + "<br/>"; using (SPWeb communityWeb = site.OpenWeb(communityWebUrl)) { //html += ">--- Url..: " + communityWeb.Url + "<br/>"; //html += ">--- Title: " + communityWeb.Title + "<br/>"; // html += ">--- CurrentUserWeb: " + communityWeb.CurrentUser + "<br/>"; if (communityWeb.DoesUserHavePermissions(CurrentUser.LoginName, SPBasePermissions.AddListItems)) { //html += ">--- Has Permissions to: " + communityWeb.Title + "<br/>"; //html += "<p>" + communityWeb.Title + "</p>"; List<SPList> discussionBoards = GetDiscussionBoards(communityWeb); foreach (SPList discussionBoard in discussionBoards) { discussionExist = true; html += "<tr><td><div><b>" + discussionBoard.Title + "</b></div><hr/>"; string queryText = GetQueryForCurrentUserReplies(discussionBoard, CurrentUser, recentDate); SPView view = discussionBoard.DefaultView; view.Paged = false; if (queryText != null) { html += "<div>Discussions replied by me: </div>"; SPQuery query = new SPQuery(view); //'query.Query = queryText; query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */ query.Query = queryText; //RKS: I removed this line of code because Replies are not Dicussions so the queryText was more appropriate //query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>"; query.RowLimit = 3; // query.ViewFields = "<OrderBy><FieldRef Name='Modified' Ascending='False' /></OrderBy><Fieldref Name='Subject'/><Fieldref Name='Body'/><Fieldref Name='AverageRating'/>"; query.ViewFields = "<FieldRef Name='LinkDiscussionTitle'/><FieldRef Name='DiscussionLastUpdated'/><FieldRef Name='ItemChildCount'/><FieldRef Name='AverageRating'/>"; query.ViewFieldsOnly = true; string discussionHtml = discussionBoard.RenderAsHtml(query); html += discussionHtml; } string recentForumsQuery = GetQueryForRecentPosts(discussionBoard, recentDate); if (recentForumsQuery != null) { html += "<div>Recent Discussions: </div>"; SPQuery query = new SPQuery(view); query.Query = recentForumsQuery; query.ViewFields = "<FieldRef Name='LinkDiscussionTitle'/><FieldRef Name='DiscussionLastUpdated'/>";//<FieldRef Name='ItemChildCount'/><FieldRef Name='AverageRating'/>"; query.ViewFieldsOnly = true; query.RowLimit = 3; string discussionHtml = discussionBoard.RenderAsHtml(query); html += discussionHtml; } html += "</td></tr>"; } } //else //{ // html += ">--- No Permissions to: " + communityWeb.Title + "<br/>"; //} } } //removed pagination and context menu // Commented out for console app //discussionBoardsDiv.InnerHtml = "<table id='tblDiscussions'><tbody>" + html.Replace(@"""ms-bottompaging""", @"""ms-bottompaging"" style='display:none;'").Replace("OnItem(this)", "") + "</tbody></table>"; if (discussionExist == false) ShowNoRecords(); } }); } catch (Exception ex) { ConsoleOut(ex.ToString()); // Commented out for console app //Controls.Add(new LiteralControl(ex.Message)); } }
private void DataBindMyTasks() { var dtBind = EnsureDataTableRow(); var web = SPContext.Current.Site.RootWeb; var query = new SPSiteDataQuery(); var swhere = @"<Where> <And> <And> <Eq> <FieldRef Name=""AssignedTo"" LookupId=""TRUE""/> <Value Type=""Integer"">{0}</Value> </Eq> <Neq> <FieldRef Name=""Status"" /> <Value Type=""Text"">{1}</Value> </Neq> </And> <Neq> <FieldRef Name='ID' /> <Value Type='Counter'>{2}</Value> </Neq> </And> </Where>"; var scompleted = SPUtility.GetLocalizedString("$Resources:core,Tasks_Completed;", "core", web.Language); string currTaskId = Request.QueryString["TaskId"].IsNullOrWhitespace() ? "0" : Request.QueryString["TaskId"]; swhere = String.Format(swhere, web.CurrentUser.ID, scompleted, currTaskId); //var sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.WorkflowName.ToString("B") + "\"/><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\" Ascending=\"FALSE\"/></OrderBy>"; var sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\" Ascending=\"FALSE\"/></OrderBy>"; query.Query = swhere + sorderby; query.Lists = "<Lists ServerTemplate=\"107\"/>"; query.ViewFields = "<FieldRef ID=\"" + SPBuiltInFieldId.Title.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskDueDate.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.UniqueId.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Completed.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.PercentComplete.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskStatus.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowLink.ToString("B") + "\" Nullable=\"TRUE\" Type=\"URL\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FileRef.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FSObjType.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowName.ToString("B") + "\"/>"; //query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_By.ToString("B") + "\"/>"; query.Webs = "<Webs Scope=\"Recursive\" />"; var createdDateFieldId = SPBuiltInFieldId.Created_x0020_Date.ToString("B"); var uIdField = SPBuiltInFieldId.UniqueId.ToString("B"); var listField = SPBuiltInFieldId.FileRef.ToString("B"); var t = web.GetSiteData(query); if (t != null && t.Rows.Count > 0) { t.DefaultView.Sort = createdDateFieldId + " Desc"; } var sep = new string[] { ";#" }; t.Columns.Add("WorkFlowUrl"); var mxw = new Hashtable(); var workflowcenter = SPContext.Current.Site.OpenWeb("workflowcenter"); var modules = workflowcenter.Lists["Modules"]; foreach (SPListItem item in modules.Items) { var workflownames = (item["WorkflowNames"].AsString() + "").Replace("<p>", "").Replace("</p>", ""); if (!string.IsNullOrEmpty(workflownames.Trim())) { foreach (var workflowname in workflownames.Split(';')) { if (!string.IsNullOrEmpty(workflowname.Trim()) && !mxw.Contains(workflowname.Trim())) { mxw.Add(workflowname.Trim(), item["Title"] + ""); } } } } DataRow dr = null; foreach (DataRow row in t.Rows) { dr = dtBind.Rows.Add(); var createdDate = "" + row[createdDateFieldId]; var tempArr = createdDate.Split(sep, StringSplitOptions.None); if (tempArr.Length > 1) row[createdDateFieldId] = Convert.ToDateTime(tempArr[1]).ToString("yyyy-MM-dd"); row[uIdField] = row[uIdField].ToString().Split(sep, StringSplitOptions.None)[1]; //35;#WorkFlowCenter/Lists/Tasks/35_.000 var workflowUrl = row[listField].ToString().Split(sep, StringSplitOptions.None)[1]; var index = workflowUrl.LastIndexOf(@"/"); workflowUrl = SPContext.Current.Site.RootWeb.Url + "/" + workflowUrl.Remove(index) + "/DispForm.aspx?ID=" + row[SPBuiltInFieldId.ID.ToString("B")]; row["WorkFlowUrl"] = workflowUrl; dr["TaskId"] = row[SPBuiltInFieldId.ID.ToString("B")]; dr["TaskTitle"] = row[SPBuiltInFieldId.Title.ToString("B")]; if (IsNeedDelete(row[SPBuiltInFieldId.Title.ToString("B")].AsString())) { dr["MenuTemplateIdField"] = "MenuList"; } else { dr["MenuTemplateIdField"] = "MenuTemplate1"; } dr["StartTime"] = row[createdDateFieldId]; // dr["CreatedBy"] = row[SPBuiltInFieldId.Created_x0020_By.ToString("B")]; dr["WorkflowName"] = row[SPBuiltInFieldId.WorkflowName.ToString("B")]; dr["WorkflowUrl"] = workflowUrl + "&Source=" + this.Page.Request.RawUrl; if (mxw.Contains(row[SPBuiltInFieldId.WorkflowName.ToString("B")])) { dr["ModuleTitle"] = mxw[row[SPBuiltInFieldId.WorkflowName.ToString("B")]]; } else { dr["ModuleTitle"] = row[SPBuiltInFieldId.WorkflowName.ToString("B")]; } dr["Status"] = row[SPBuiltInFieldId.TaskStatus.ToString("B")]; } //this.gvList.DataSource = dtBind; this.gvList.DataSource = dtBind.AsEnumerable().OrderBy(d => d["ModuleTitle"]).AsDataView(); this.gvList.DataBind(); }
private static string EnumerateDistinctWebsWithDiscussions(SPSite site) { string html2 = String.Empty; using (SPWeb web = site.RootWeb) { //Distinct webs from SiteDataQuery for responses during the last 7 days. // Fetch using SPSiteDataQuery string today = DateTime.Now.ToString("MM/dd/yyyy"); SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; //Title is LastName column query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(today).AddDays(-30)) + "</Value></Geq></Where>"; DataTable dataTable = web.GetSiteData(query); //48 List<string> communitiesWebUrl = new List<string>(); string webRelUrl = String.Empty; foreach (DataRow row in dataTable.Rows) { webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl.Contains(webRelUrl)) { communitiesWebUrl.Add(webRelUrl); html2 += webRelUrl + "\n"; } } SPUser currentUser = web.CurrentUser; //Distinct webs from SiteDataQuery for responses during the last 7 days. // Fetch using SPSiteDataQuery SPSiteDataQuery query2 = new SPSiteDataQuery(); query2.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. query2.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; // Title is LastName column query2.Webs = "<Webs Scope=\"SiteCollection\" />"; query2.Query = string.Format(@"<Where> <And> <Eq> <FieldRef Name='ContentType' /> <Value Type='Computed'>Message</Value> </Eq> <Eq> <FieldRef Name='Author' LookupId='True' /> <Value Type='Integer'>{0}</Value> </Eq> </And> </Where>", currentUser.ID); DataTable dataTable2 = web.GetSiteData(query2); //48 List<string> communitiesWebUrl2 = new List<string>(); string webRelUrl2 = String.Empty; foreach (DataRow row in dataTable.Rows) { webRelUrl2 = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl2.Contains(webRelUrl)) { communitiesWebUrl2.Add(webRelUrl); html2 += webRelUrl + "\n"; } } } return html2; }
DataTable GetRootTasks() { SPWeb web = SPContext.Current.Site.RootWeb; SPSiteDataQuery query = new SPSiteDataQuery(); string swhere = @"<Where><And> <Eq> <FieldRef Name=""AssignedTo"" LookupId=""TRUE""/> <Value Type=""Integer"">{0}</Value> </Eq> <Neq> <FieldRef Name=""Status"" /> <Value Type=""Text"">{1}</Value> </Neq> </And></Where>"; string scompleted = SPUtility.GetLocalizedString("$Resources:core,Tasks_Completed;", "core", web.Language); swhere = String.Format(swhere, web.CurrentUser.ID, scompleted); string sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/></OrderBy>"; query.Query = swhere + sorderby; query.Lists = "<Lists ServerTemplate=\"107\"/>"; query.ViewFields = "<FieldRef ID=\"" + SPBuiltInFieldId.Title.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskDueDate.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.UniqueId.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Completed.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.PercentComplete.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskStatus.ToString("B") + "\" Nullable=\"TRUE\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowLink.ToString("B") + "\" Nullable=\"TRUE\" Type=\"URL\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FileRef.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/>"; query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FSObjType.ToString("B") + "\"/>"; query.Webs = "<Webs Scope=\"Recursive\" />"; string createdDateFieldId = SPBuiltInFieldId.Created_x0020_Date.ToString("B"); string uIdField = SPBuiltInFieldId.UniqueId.ToString("B"); string listField = SPBuiltInFieldId.FileRef.ToString("B"); query.RowLimit = 5; DataTable t = web.GetSiteData(query); if (t != null && t.Rows.Count > 0) { t.DefaultView.Sort = createdDateFieldId + " Desc"; } string[] sep = new string[] { ";#" }; t.Columns.Add("WorkFlowUrl"); foreach (DataRow row in t.Rows) { string createdDate = "" + row[createdDateFieldId]; string[] tempArr = createdDate.Split(sep, StringSplitOptions.None); if (tempArr.Length > 1) row[createdDateFieldId] = Convert.ToDateTime(tempArr[1]).ToString("yyyy-MM-dd"); row[uIdField] = row[uIdField].ToString().Split(sep, StringSplitOptions.None)[1]; //35;#WorkFlowCenter/Lists/Tasks/35_.000 string workflowUrl = row[listField].ToString().Split(sep, StringSplitOptions.None)[1]; int index = workflowUrl.LastIndexOf(@"/"); workflowUrl = SPContext.Current.Site.RootWeb.Url + "/DispForm.aspx?ID=" + row[SPBuiltInFieldId.ID.ToString("B")]; row["WorkFlowUrl"] = workflowUrl + "&Source=" + this.Page.Request.RawUrl; } return t; }
private void GetAllActionsOwed(string userID) { List<ActionOwed> actionsOwed = new List<ActionOwed>(); SPSecurity.RunWithElevatedPrivileges(delegate() { string[] actionsOwedSites = sites.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); foreach (string site in actionsOwedSites) { using (SPSite emisSite = new SPSite(site)) { using (SPWeb emisRootWeb = emisSite.OpenWeb()) { SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"107\" />"; query.ViewFields = "<FieldRef Name=\"Title\" />" + "<FieldRef Name=\"ID\" />" + "<FieldRef Name=\"AssignedTo\" Nullable=\"TRUE\" Type=\"User\" />" + "<FieldRef Name=\"Status\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"DueDate\" Nullable=\"TRUE\" Type=\"DateTime\" />" + "<FieldRef Name=\"IsDelegated\" Nullable=\"TRUE\" Type=\"Boolean\" />" + "<FieldRef Name=\"OriginalAssignee\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"ActionOwed\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"TaskSource\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"FileRef\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"Related Content\" Nullable=\"TRUE\" Type=\"Text\" />"; query.Query = "<Where>" + "<And>" + "<Eq>" + "<FieldRef Name='AssignedTo' />" + " <Value Type='User'>" + userID + "</Value>" + "</Eq>" + "<Neq>" + "<FieldRef Name='Status' />" + "<Value Type='Choice'>Completed</Value>" + "</Neq>" + "</And>" + "</Where>" + "<OrderBy>" + "<FieldRef Name=\"DueDate\" />" + "</OrderBy>"; query.Webs = "<Webs Scope=\"SiteCollection\" />"; Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: About to get data from all subsites for {0}", site); Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: SPSiteDataQuery = {0}", query.Query); DataTable dt = emisRootWeb.GetSiteData(query); Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: Got data from all subsites for {0}", site); foreach (DataRow row in dt.Rows) { ActionOwed action = new ActionOwed() { TaskID = row["ID"].ToString(), Title = row["Title"].ToString(), DueDate = DateTime.Parse(row["DueDate"].ToString()), Status = row["Status"].ToString(), OriginalAssignee = row["OriginalAssignee"].ToString(), Action = row["ActionOwed"].ToString(), Source = row["TaskSource"].ToString(), FileRef = row["FileRef"].ToString(), TaskSite = site }; if (string.IsNullOrEmpty(row["IsDelegated"].ToString())) { action.IsDelegated = false; } else if (row["IsDelegated"].ToString() == "1") { action.IsDelegated = true; } else if (bool.Parse(row["IsDelegated"].ToString())) { action.IsDelegated = true; } string taskRelativeLink = row["FileRef"].ToString().Substring(row["FileRef"].ToString().IndexOf("#") + 1, (row["FileRef"].ToString().Length - row["FileRef"].ToString().IndexOf("#") - 1)); action.TaskLink = taskRelativeLink; action.AssignedTo = row["AssignedTo"].ToString().Substring(row["AssignedTo"].ToString().IndexOf("#") + 1, (row["AssignedTo"].ToString().Length - row["AssignedTo"].ToString().IndexOf("#") - 1)); actionsOwed.Add(action); } } } } }); grdActionsOwed.DataSource = actionsOwed; grdActionsOwed.DataBind(); }
//This console application searches for all entries in all Tasks folders //that are assigned to the current user. Try creating tasks in several sites //within the site collection, assign them to your own account then run this //project. All tasks should be returned in a single operation static void Main(string[] args) { //Start by formulating the query in CAML SPSiteDataQuery query = new SPSiteDataQuery(); //Get IDs for the SharePoint built-in fields we want to use string assignedToID = SPBuiltInFieldId.AssignedTo.ToString("B"); string taskDueDateID = SPBuiltInFieldId.TaskDueDate.ToString("B"); string titleID = SPBuiltInFieldId.Title.ToString("B"); string taskStatusID = SPBuiltInFieldId.TaskStatus.ToString("B"); string percentCompleteID = SPBuiltInFieldId.PercentComplete.ToString("B"); //This is the selection creterion string whereClause = "<Where><Eq><FieldRef ID='" + assignedToID + "' />" + "<Value Type='Integer'><UserID/></Value>" + "</Eq></Where>"; //This is the sort order string orderByClause = "<OrderBy><FieldRef ID='" + taskDueDateID + "' /></OrderBy>"; //Set the query CAML query.Query = whereClause + orderByClause; //We will query all the Tasks lists query.Lists = "<Lists ServerTemplate='107' />"; //Define the view fields in the result set string viewFields = "<FieldRef ID='" + titleID + "' />" + "<FieldRef ID='" + taskDueDateID + "' Nullable='TRUE' />" + "<FieldRef ID='" + taskStatusID + "' Nullable='TRUE' />" + "<FieldRef ID='" + percentCompleteID + "' Nullable='TRUE' />"; query.ViewFields = viewFields; //Query all the SPWebs in this SPSite query.Webs = "<Webs Scope='SiteCollection'>"; //Get the SPSite and SPWeb, ensuring correct disposal //Replace the URL with your own site collection using (SPSite site = new SPSite("http://intranet.contoso.com/")) { using (SPWeb web = site.OpenWeb()) { //Run the query DataTable resultsTable = web.GetSiteData(query); //Print a heading line Console.WriteLine("{0, -10} {1, -30} {2, -20} {3}", "Date Due", "Task", "Status", "% Complete"); //Loop through the results and print them foreach (DataRow currentRow in resultsTable.Rows) { //Extract various values string dueDate = (string)currentRow[taskDueDateID]; string task = (string)currentRow[titleID]; string status = (string)currentRow[taskStatusID]; string percentComplete = (string)currentRow[percentCompleteID]; //Format the due date DateTime dueDateTime; bool hasDate = DateTime.TryParse(dueDate, out dueDateTime); if (hasDate) { dueDate = dueDateTime.ToShortDateString(); } else { dueDate = String.Empty; } //Format the percent complete string decimal pctDec; bool hasValue = decimal.TryParse(percentComplete, out pctDec); if (hasValue) { percentComplete = pctDec.ToString("P0"); } else { percentComplete = "0%"; } //Print a line for this row Console.WriteLine("{0, -10} {1, -30} {2, -20} {3, 10}", dueDate, task, status, percentComplete); } } } //Wait for the user to press a key before closing Console.ReadKey(); }
public static DataTable GetNewsRecordItems(string query, uint newsNumber, string[] listNames) { DataTable allItems = null; SPSecurity.RunWithElevatedPrivileges(() => { using (var site = new SPSite(SPContext.Current.Web.Site.ID)) { using (var web = site.OpenWeb(SPContext.Current.Web.ID)) { try { var siteDataQuery = new SPSiteDataQuery(); var sb = new StringBuilder(); // Lists sb.Append("<Lists>"); foreach (var listName in listNames) { SPList list = Utilities.GetListFromUrl(web, listName); if (list != null) { sb.AppendFormat("<List ID='{0}' />", list.ID.ToString("D")); } } sb.Append("</Lists>"); siteDataQuery.Lists = sb.ToString(); //siteDataQuery.Lists = "<Lists ServerTemplate='101'/>"; siteDataQuery.Webs = "<Webs Scope='SiteCollection' />"; siteDataQuery.Query = query; siteDataQuery.ViewFields = "" + "<ListProperty Name='ListId' Nullable='TRUE' />" + "<FieldRef Name='ArticleStartDates' Nullable='TRUE' />" + "<FieldRef Name='Title' Nullable='TRUE' />" + "<FieldRef Name='ID' Nullable='TRUE' />" + "<FieldRef Name='Thumbnail' Nullable='TRUE' />" + "<FieldRef Name='CategoryName' Nullable='TRUE' />" + "<FieldRef Name='ShortContent' Nullable='TRUE' /><FieldRef Name='Status' Nullable='TRUE' />" + "<FieldRef Name='FocusNews' Nullable='TRUE' /><FieldRef Name='ViewsCount' Nullable='TRUE' />" + "<FieldRef Name='PublishingPageImage' Nullable='TRUE' /><FieldRef Name='PublishingPageContent' Nullable='TRUE' />" + "<FieldRef Name='LatestNewsOnHomePage' Nullable='TRUE' /><FieldRef Name='_ModerationStatus' Nullable='TRUE' />"; siteDataQuery.RowLimit = newsNumber; allItems = web.GetSiteData(siteDataQuery); } catch (Exception ex) { allItems = null; } } } }); return allItems; }
private static bool SpExceptionOccured( SPSiteDataQuery dataQuery, SPWeb spWeb, SPSite spSite, string listIds, IList <EventWaitHandle> eventWaitHandles, string selectedList, object locker, IList <DataTable> dataTables, string theSelectedList) { Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery)); Guard.ArgumentIsNotNull(spWeb, nameof(spWeb)); Guard.ArgumentIsNotNull(spSite, nameof(spSite)); Guard.ArgumentIsNotNull(eventWaitHandles, nameof(eventWaitHandles)); Guard.ArgumentIsNotNull(dataTables, nameof(dataTables)); var spExceptionOccured = false; dataQuery.Lists = $"<Lists MaxListLimit='0'>{listIds}</Lists>"; var siteUrl = (string)spSite.Url.Clone(); var webId = new Guid(spWeb.ID.ToString()); var spUserToken = spWeb.CurrentUser.UserToken; var spSiteDataQuery = new SPSiteDataQuery { Webs = dataQuery.Webs, Query = dataQuery.Query, QueryThrottleMode = dataQuery.QueryThrottleMode, ViewFields = dataQuery.ViewFields, Lists = dataQuery.Lists }; var eventWaitHandle = new EventWaitHandle(false, EventResetMode.ManualReset); eventWaitHandles.Add(eventWaitHandle); var list = selectedList; var thread = new Thread( () => { try { var dataTable = QueryMyWorkData(spSiteDataQuery, siteUrl, webId, spUserToken); dataTable.TableName = list; lock (locker) { dataTables.Add(dataTable); } } catch (SPException exception) { Trace.WriteLine(exception); spExceptionOccured = true; } eventWaitHandle.Set(); }) { Name = theSelectedList, IsBackground = true }; thread.Start(); return(spExceptionOccured); }
// Specific files according to user criteria in all sites public List<string> _getSpecificDocsInSiteCollection() { List<string> FileRefs = new List<string>(); SPSiteDataQuery DocFilesQuery; using (SPSite site = new SPSite(SiteUrl)) { //using (SPWeb web = site.OpenWeb("/")) using (SPWeb web = site.OpenWeb()) { user = null; if (UserLoginName != "") user = web.EnsureUser(UserLoginName);// only when specific user DocFilesQuery = new SPSiteDataQuery(); DocFilesQuery.Lists = "<Lists BaseType='1' /><Lists Hidden = \"TRUE\" />";// generic list // better! //query.Lists = "<Lists ServerTemplate='101' /><Lists Hidden = \"TRUE\" />";// doc lib DocFilesQuery.Query = CriteriaQuery(); // Select only needed columns: file reference DocFilesQuery.ViewFields = "<FieldRef Name='FileRef' />"; // Search in all webs of the site collection DocFilesQuery.Webs = "<Webs Scope='SiteCollection' />"; // constructring file url from GetSiteDataQuery DataRowCollection filerows = web.GetSiteData(DocFilesQuery).Rows; foreach (DataRow row in filerows) { // add relativeUrl into FileRef collection FileRefs.Add(getRelativeFileRef(row["FileRef"].ToString())); } // specific attachments SPListCollection listcoll = web.Lists; foreach (SPList list in listcoll) { SPFolderCollection foldercoll = list.RootFolder.SubFolders; foreach (SPFolder folder in foldercoll) { if (folder.Name == "Attachments") { SPFolderCollection attachmentFolders = folder.SubFolders; foreach (SPFolder itemFolder in attachmentFolders) { SPFileCollection files = itemFolder.Files; foreach (SPFile file in files) { if (CriteriaApply(file)) FileRefs.Add(file.ServerRelativeUrl); } } break; } } } } } return (FileRefs); }
public override void populateGroups(string query, SortedList arrGTemp, SPWeb curWeb) { SPSiteDataQuery dq = new SPSiteDataQuery(); dq.ViewFields = "<FieldRef Name='Title' Nullable='TRUE'/>"; dq.Webs = "<Webs Scope='Recursive'/>"; dq.Lists = "<Lists ServerTemplate='10701'/>"; dq.Query = "<Where><Eq><FieldRef Name=\"ProjectManagers\" /><Value Type=\"User\"><UserID/></Value></Eq></Where>"; DataTable dtData = curWeb.GetSiteData(dq); DataTable dtItems = new DataTable(); SPSecurity.RunWithElevatedPrivileges(delegate() { try { using (cn = new SqlConnection(EPMLiveCore.CoreFunctions.getConnectionString(curWeb.Site.WebApplication.Id))) { cn.Open(); using (SqlCommand cmd = new SqlCommand("select ts_item_uid,columnname,columnvalue from vwTSItemMeta where period_id=@period_id and site_uid=@siteid", cn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@period_id", period); cmd.Parameters.AddWithValue("@siteid", curWeb.Site.ID); dsTimesheetMeta = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dsTimesheetMeta); } using (SqlCommand cmd = new SqlCommand("select web_uid, list_uid,item_id,username,resourcename,project,title,list_uid from vwTSTasks where web_uid=@webuid and period_id=@period_id and totalhours > 0", cn)) { cmd.Parameters.AddWithValue("@webuid", curWeb.ID.ToString()); cmd.Parameters.AddWithValue("@period_id", period); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); DataTable dtFilteredRecords = new DataTable(); foreach (DataRow drProjects in dtData.Rows) { DataView dv = new DataView(ds.Tables[0]); string filterquery = string.Format("(project='{0}' or (title='{0}' and list_uid='{1}'))", drProjects["Title"].ToString(), drProjects["ListId"].ToString()); dv.RowFilter = filterquery; if (dv.Count > 0) { dtFilteredRecords.Merge(dv.ToTable(), false); } } dtItems.Merge(dtFilteredRecords, false); } using (SqlCommand cmd = new SqlCommand("select title,project,ts_uid,web_uid,list_uid,item_id,ts_item_uid,approval_status,resourcename,username from vwTSTasks where period_id=@period_id and site_uid=@siteid order by project", cn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@period_id", period); cmd.Parameters.AddWithValue("@siteid", curWeb.Site.ID); dsTimesheetTasks = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dsTimesheetTasks); } } } catch { } }); Guid webGuid = new Guid(); Guid listGuid = new Guid(); SPWeb iWeb = null; SPList iList = null; foreach (DataRow dr in dtItems.Rows) { try { Guid wGuid = new Guid(dr[0].ToString()); Guid lGuid = new Guid(dr[1].ToString()); if (webGuid != wGuid) { if (iWeb != null) { iWeb.Close(); iWeb = site.OpenWeb(wGuid); } else { iWeb = site.OpenWeb(wGuid); } webGuid = iWeb.ID; } if (listGuid != lGuid) { iList = iWeb.Lists[lGuid]; listGuid = iList.ID; } SPListItem li = iList.GetItemById(int.Parse(dr[2].ToString())); addTSItem(li, arrGTemp, dr[3].ToString(), dr[4].ToString()); } catch { } } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { int errorIndex = 0; try { var siteContext = SPContext.Current.Site; //var impersonationUser = siteContext.RootWeb.AllUsers[0]; var site = siteContext; var query = new SPSiteDataQuery { Query = "", ViewFields = "<FieldRef Name=\"LinkFilename\"/>" + "<FieldRef Name=\"Created\"/>" + "<FieldRef Name=\"Modified\"/>" + "<FieldRef Name=\"Editor\"/>" + "<FieldRef Name=\"FileLeafRef\"/>", Lists = "<Lists ServerTemplate=\"101\"/>", RowLimit = 500, Webs = "<Webs Scope=\"SiteCollection\"/>" }; //var productiondate = new DateTime(2012, 09, 12); //string proddatestring = // SPUtility.CreateISO8601DateTimeFromSystemDateTime( // productiondate.ToUniversalTime()); //var date = Convert.ToDateTime(proddatestring); //date.AddMonths(-1).ToUniversalTime(); string convertedTime = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddMonths(-1).ToUniversalTime()); query.Query = @"<Where> <And> <Geq> <FieldRef Name='Modified' /> <Value IncludeTimeValue='TRUE' Type='DateTime'>" + convertedTime + @"</Value> </Geq> <Eq> <FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value> </Eq> </And> </Where>"; var resultTable = new DataTable(); resultTable.Columns.Add("DocLink"); resultTable.Columns.Add("Docname"); resultTable.Columns.Add("Sitename"); resultTable.Columns.Add("WebURL"); errorIndex = 1; foreach (DataRow row in site.RootWeb.GetSiteData(query).Rows) { try { string filename = row["LinkFilename"].ToString(); string extension = Path.GetExtension(filename); if (extension != null) switch (extension.ToLower()) { case ".jpg": case ".xsl": case ".css": case ".png": case ".xaml": case ".gif": case ".js": //case ".dotm": { continue; } default: { var newRow = resultTable.NewRow(); using (SPWeb web = site.OpenWeb(new Guid(row["WebId"].ToString()))) { var item = web.Lists[new Guid(row["listId"].ToString())].GetItemById( Convert.ToInt32(row["ID"])); var url = (string) item[SPBuiltInFieldId.EncodedAbsUrl]; newRow["DocLink"] = Uri.EscapeUriString(url); newRow["Docname"] = filename; newRow["Sitename"] = web.Name; newRow["WebURL"] = web.Url; if (string.IsNullOrEmpty(newRow["Sitename"].ToString())) newRow["Sitename"] = "Root Site"; resultTable.Rows.Add(newRow); break; } } } ViewState["Result"] = resultTable; } catch (Exception ex) { Label1.Text += ex.StackTrace; } } } catch (Exception ex) { Label1.Text = "<br />" + ex.Message; // Label1.Text += ex.StackTrace; Label1.Text += "<br />" + "Error message is" + errorIndex; } } }
protected DataTable Get(SPSiteDataQuery query, int startRow, int maxRows) { var data = Web.GetSiteData(query); if (data.Rows.Count > 0) { return data.Select().Skip(startRow).Take(maxRows).CopyToDataTable(); } else { return data; } }
//public string GetLatestMessageNews(DateTime beforetime, out string nextBeforeTime, bool publicmessage) public static string GetLatestMessageNews(DateTime beforetime, bool publicmessage) { using (new SPMonitoredScope("Weixin.GetLatestMessageNews", 5000)) { //Guid siteid = SPContext.Current.Site.ID; //Guid webid = SPContext.Current.Web.ID; Guid siteid = new Guid("fbf5935b-6717-4036-8bfc-856c1ef075a7"); Guid webid = new Guid("3acebb12-b9c8-4a00-94f7-4d779743e805"); SPUserToken t = null; SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSite s = new SPSite(siteid)) { using (SPWeb w = s.OpenWeb(webid)) { t = w.GetUserToken("i:0#.f|fbamember|233173287"); } } }); StringBuilder ret = new StringBuilder(); //using (SPSite site = new SPSite(siteid, SPFBAUser.usertoken)) using (SPSite site = new SPSite(siteid, t)) { using (SPWeb web = site.OpenWeb(webid)) { SPSiteDataQuery query = new SPSiteDataQuery(); //query.Lists = string.Concat("<Lists><List ID = '", web.GetList(publicmessage ? PublicMessageListUrl : PrivateMessageListUrl).ID.ToString(), "' /><List ID = '", web.GetList(ImageLibUrl).ID.ToString(), "' /></Lists>"); //query.Webs = "<Webs Scope=\"SiteCollection\"/>"; query.Lists = string.Concat("<Lists><List ID = '", web.GetList(publicmessage ? "/sites/public/lists/list/" : "/sites/public/lists/Private%20Message/").ID.ToString(), "' /><List ID = '", web.GetList("/sites/public/Images1/").ID.ToString(), "' /></Lists>"); query.ViewFields = @"<FieldRef Name=""Author"" Nullable=""TRUE"" /><FieldRef Name=""ItemChildCount"" Nullable=""TRUE"" /><FieldRef Name=""Modified""/><FieldRef Name=""Title"" Nullable=""TRUE"" />"; query.ViewFields += @"<FieldRef Name=""WeChat61"" Nullable=""TRUE""/>"; query.RowLimit = 5; query.Query = "<OrderBy><FieldRef Name=\"Modified\" Ascending=\"FALSE\" /></OrderBy><Where>" + "<And>" + "<Or>" // + "<And><IsNotNull><FieldRef Name=\"WeChatPicUrl\" Nullable=\"TRUE\"/></IsNotNull>" + "<IsNotNull><FieldRef Name=\"WeChat61\" Nullable=\"TRUE\"/></IsNotNull>" // + (publicmessage? string.Concat("<Eq>< FieldRef Name = \"FileDirRef\" ></FieldRef ><Value Type = \"Text\" >",ImageLibUrl,"</Value></Eq>"): string.Concat("<Contains>< FieldRef Name = \"FileDirRef\" ></FieldRef ><Value Type = \"Text\" >", SPFBAUserName, "</Value><Contains>")) // + (publicmessage ? string.Concat("<Eq><FieldRef Name = \"FileDirRef\" /><Value Type = \"Text\" >", "/sites/public/Images1/", "</Value></Eq>") : string.Concat("<Contains>< FieldRef Name = \"FileDirRef\" /><Value Type = \"Text\" >", "233173287", "</Value></Contains>")) // + "</And>" + "<BeginsWith><FieldRef Name = \"ContentTypeId\" /><Value Type = \"ContentTypeId\" >0x012002</Value></BeginsWith>" + "</Or>" + "<Lt><FieldRef Name='Created'/><Value IncludeTimeValue ='TRUE' Type ='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforetime) + "</Value></Lt>" + "</And></Where>"; DataTable results = web.GetSiteData(query); if (results != null && results.Rows.Count > 0) { //var responseMessageNews = CreateResponseMessage<ResponseMessageNews>(); //Article a = new Article(); ////string prevAuthor = string.Empty; //int i = 0; //StringBuilder ret = new StringBuilder(); //foreach (DataRow r in results.Rows) //{ // string author = r["Author"] == null ? string.Empty : r["Author"].ToString().Trim(); // //SPFieldUserValue au = author.Equals(string.Empty) ? null : // // (item.Fields.GetFieldByInternalName("Author").GetFieldValue(item["Author"].ToString()) as SPFieldUserValue); // //string u = au == null ? string.Empty // // : (au.User == null ? string.Empty : au.User.Name); // if (string.IsNullOrEmpty((r["WeChatPicUrl"] ?? string.Empty).ToString())) // { // ret.AppendLine(string.Concat(author, ":", r["Title"])); // ret.AppendLine(string.Concat(" 修改时间", r["Modified"].ToString())); // } //} //responseMessageNews.Articles.Add(a); string lastTime = string.Empty; foreach (DataRow item in results.Rows) { string author = item["Author"] == null ? string.Empty : item["Author"].ToString().Trim(); //SPFieldUserValue au = author.Equals(string.Empty) ? null : // (item.Fields.GetFieldByInternalName("Author").GetFieldValue(item["Author"].ToString()) as SPFieldUserValue); //string u = au == null ? string.Empty // : (au.User == null ? string.Empty : au.User.Name); if (item["WeChat61"] == null) { ret.AppendLine(string.Concat("主题:", item["Title"])); ret.AppendLine(string.Concat("作者:", author)); ret.AppendLine(string.Concat("修改时间", item["Modified"].ToString())); ret.AppendLine(string.Concat("回复数:", item["ItemChildCount"])); } else { ret.AppendLine(string.Concat("作者:", author)); ret.AppendLine(string.Concat("修改时间", item["Modified"].ToString())); ret.AppendLine(string.Concat("PicUrl", item["WeChat61"].ToString())); } ret.AppendLine(); lastTime = item["Modified"].ToString(); } //nextBeforeTime = lastTime; return(ret.ToString()); } else { //nextBeforeTime = string.Empty; return("已无更早留言"); } //SPList ImageLib = web.GetList(ImageLibUrl); //SPQuery qry = new SPQuery(); //qry.RowLimit = 1; //qry.Query = "<OrderBy Override=\"TRUE\"><FieldRef Name=\"Modified\" Ascending=\"FALSE\" /></OrderBy><Where>" // + "<Lt><FieldRef Name='Created'/><Value IncludeTimeValue='TRUE' Type='DateTime'>" // + SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforetime) // + "</Value></Lt></Where>"; //qry.ViewFields = @"<FieldRef Name=""Author"" /><FieldRef Name=""Modified"" /><FieldRef Name=""WeChatPicUrl"" />"; } } } }
private static void ProcessRollupList( SPWeb web, string rollupList, ref string errors, Action <DataTable> processListAction, string siteUrl, string dQuery, string dqFields) { SqlConnection sqlConnection = null; try { SPSecurity.RunWithElevatedPrivileges( delegate { using (var spSite = new SPSite(web.Site.ID)) { var dbConnection = spSite.ContentDatabase.DatabaseConnectionString; sqlConnection = new SqlConnection(dbConnection); sqlConnection.Open(); } }); var projectedRollup = rollupList.Replace("\r\n", "\n").Split('\n').Distinct().ToArray(); foreach (var rollup in projectedRollup) { try { var lists = string.Empty; var query = string.IsNullOrWhiteSpace(siteUrl) ? string.Format( "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE webs.siteid='{0}' AND (dbo.AllLists.tp_Title like '{1}')", web.Site.ID, rollup.Replace("'", "''")) : string.Format( "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '{0}/%' OR dbo.Webs.FullUrl = '{0}') AND (dbo.AllLists.tp_Title like '{1}')", siteUrl, rollup.Replace("'", "''")); using (var sqlCommand = new SqlCommand(query, sqlConnection)) { using (var dataReader = sqlCommand.ExecuteReader()) { while (dataReader.Read()) { lists += "<List ID='" + dataReader.GetGuid(0) + "'/>"; } } } if (!string.IsNullOrWhiteSpace(lists)) { var dataQuery = new SPSiteDataQuery { Lists = string.Format("<Lists MaxListLimit='0'>{0}</Lists>", lists), Query = dQuery, Webs = "<Webs Scope='Recursive'/>", ViewFields = dqFields, QueryThrottleMode = SPQueryThrottleOption.Override }; try { //CC-77556: I don't know how to handle the below scenario regarding Dispose. //The first assignment seems useless as the variable is replaced right away, //and creating a variable and disposing it right away also doesn't make sense, //so I'm keeping the current behavior intact var dataTable = new DataTable(); SPSecurity.RunWithElevatedPrivileges(delegate { dataTable = web.GetSiteData(dataQuery); }); processListAction?.Invoke(dataTable); } catch (Exception ex) { errors += "Get Rollup Site Data Error: " + ex.Message + "<br>"; } } } catch (Exception ex) { Trace.TraceError("Exception Suppressed {0}", ex); errors += "Rollup List Error: " + ex.Message + "<br>"; } } } finally { sqlConnection.Close(); } }
private static string FixFor_GettingDistinctWebs(SPSite site) { string webRelUrl = String.Empty; DateTime recentDate = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).AddDays(-30); List<string> communitiesWebUrl = new List<string>(); // Fetch using SPSiteDataQuery using (SPWeb web = site.OpenWeb()) { SPUser CurrentUser = web.CurrentUser; //Distinct webs from SiteDataQuery for responses during the last 7 days. SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. //query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */ query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>"; DataTable dataTable = web.GetSiteData(query); foreach (DataRow row in dataTable.Rows) { webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl.Contains(webRelUrl)) { communitiesWebUrl.Add(webRelUrl); } } //Distinct webs from SiteDataQuery for responses to my posts. I got this CAML from Chris' code. // Fetch using SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions. query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */ query.Webs = "<Webs Scope=\"SiteCollection\" />"; query.Query = string.Format(@"<Where> <And> <Eq> <FieldRef Name='ContentType' /> <Value Type='Computed'>Message</Value> </Eq> <Eq> <FieldRef Name='Author' LookupId='True' /> <Value Type='Integer'>{0}</Value> </Eq> </And> </Where>", CurrentUser.ID); dataTable = web.GetSiteData(query); foreach (DataRow row in dataTable.Rows) { webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl; if (!communitiesWebUrl.Contains(webRelUrl)) { communitiesWebUrl.Add(webRelUrl); } } } return communitiesWebUrl.ToArray().ToString(); }
static void Main(string[] args) { using (var Site = new SPSite("http://epm2007demo/pwa01")) { string ConnectionString = Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID, Utilities.DatabaseType.PublishedDatabase); using (var sqlConnection = new SqlConnection(ConnectionString.Replace("15", "60"))) { sqlConnection.Open(); } } return; using (var site = new SPSite("http://epm2007demo/pwa01")) { var query = new SPSiteDataQuery(); } return; using (SPSite Site = new SPSite("http://epm2007demo/pwa02")) { string constr = Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID, Utilities.DatabaseType. PublishedDatabase); using (SqlConnection connection = new SqlConnection(constr)) { connection.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("ProjectUID", "6b08e331-a75e-42f7-98ab-7eede9e4c9e0")); cmd.Parameters.Add(new SqlParameter("ModifiedBy", "Administrator")); cmd.CommandText = "TaskAuditTrail"; cmd.ExecuteNonQuery(); } } return; SqlConnection connection_Push = new SqlConnection("Server=epm2007demo;Database=ITXBaseLineLogs;Trusted_Connection=True;"); connection_Push.Open(); SqlConnection connection_Pull = null; //SqlConnection connection_Push = null; SqlDataAdapter dataAdapter = new SqlDataAdapter(); SqlCommand command = new SqlCommand(); DataSet resultSet = new DataSet(); string Qry = string.Empty; try { using (SPSite Site = new SPSite("http://epm2007demo/pwa02")) { Guid Project_UID = new Guid(Site.AllWebs["Project1"].AllProperties[Utilities.ProjWSSUIDProperty].ToString()); connection_Pull = new SqlConnection(Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID, Utilities. DatabaseType. PublishedDatabase)); connection_Pull.Open(); connection_Push = new SqlConnection(Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID, Utilities. DatabaseType. ReportingDatabase)); connection_Push.Open(); command.Connection = connection_Pull; command.CommandText = "Select "; for (int i = 0; i < 11; i++) { command.CommandText += "TB_BASE_DUR_" + i + ",TB_BASE_DUR_IS_ESTIMATE_" + i + ",TB_BASE_START_" + i + ",TB_BASE_FINISH_" + i + ",TB_BASE_WORK_" + i + ",TB_BASE_COST_" + i + ",TB_BASE_BUDGET_WORK_" + i + ",TB_BASE_BUDGET_COST_" + i + ","; } command.CommandText = command.CommandText.Substring(0, command.CommandText.Length - 1); command.CommandText += " from ProjectSummaries where PROJ_UID='" + Project_UID.ToString() + "'"; dataAdapter.SelectCommand = command; dataAdapter.Fill(resultSet); if (resultSet.Tables.Count > 0 && resultSet.Tables[0].Rows.Count > 0) { Qry = "Select * from ITXBaseLineDetail where "; } else { // Log says that empty in Publish DB } } } catch (Exception) { } finally { if (connection_Pull != null && connection_Pull.State == ConnectionState.Open) connection_Pull.Close(); if (connection_Push != null && connection_Push.State == ConnectionState.Open) connection_Push.Close(); } }
private void SPSiteDataQuery_Click(object sender, EventArgs e) { using (SPSite _site = new SPSite("http://home/")) { using (SPWeb _web = _site.OpenWeb()) { SPSiteDataQuery query = new SPSiteDataQuery(); // Search in doclibs only //////////////////////////////////////// /////// ------------------------- ////// /////// BaseType Value ////// /////// ------------------------- ////// /////// Generic List 0 ////// /////// Document Library 1 ////// /////// Discussion Board 3 ////// /////// Survey 4 ////// /////// Issue 5 ////// //////////////////////////////////////// query.Lists = "<Lists BaseType='1' />"; // Only .doc files query.Query = @"<Where> <Eq> <FieldRef Name='DocIcon' /> <Value Type='Computed'>docx</Value> </Eq> </Where>"; // Select only needed columns: file reference query.ViewFields = "<FieldRef Name='FileRef' />"; // Search in all webs of the site collection query.Webs = "<Webs Scope='SiteCollection' />"; // Perform the query DataTable table = _web.GetSiteData(query); // Generate an absolute url for each document foreach (DataRow row in table.Rows) { string relativeUrl = row["FileRef"].ToString().Substring( row["FileRef"].ToString().IndexOf("#") + 1); string fullUrl = _site.MakeFullUrl(relativeUrl); // Write urls to window MessageBox.Show(fullUrl); } } } }
/// <summary> /// Gets data using SPSiteDataQuery. /// </summary> /// <returns></returns> protected DataView GetData() { DataTable dtResults = null; DataView dView = null; using (SPWeb toolsWeb = SPContext.Current.Site.OpenWeb("/tools-catalog")) { SPSiteDataQuery Query = new SPSiteDataQuery(); Query.Lists = "<Lists ServerTemplate=\"100\" />"; //"<Lists BaseType=\"1\" />"; Query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"ID\" /><FieldRef Name=\"_Comments\" /><FieldRef Name=\"ContentTypeId\" /><FieldRef Name='MPD_x0020_Classes' /><FieldRef Name='IEPD_x0020_Lifecycle_x0020_Phase' /><FieldRef Name='Artifacts_x0020_Produced' /><FieldRef Name='EMail' /><FieldRef Name='EncodedAbsUrl' /><FieldRef Name='URL' /><FieldRef Name='FileRef' /><FieldRef Name='Latest_x0020_Verison' /><FieldRef Name='Created' /><FieldRef Name='AverageRating' nullable='true'/>"; //content type & property Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + ToolsContentType + "</Value></Eq></Where>"; Query.Webs = "<Webs Scope=\"Recursive\" />"; dtResults = toolsWeb.GetSiteData(Query); dtResults = GetLikedTools(dtResults); LogText("dtResults1:" + dtResults.Rows.Count.ToString()); dtResults = AddDisplayLinks(dtResults); LogText("dtResults2:" + dtResults.Rows.Count.ToString()); dtResults = GetUserTools(dtResults); LogText("dtResults3:" + dtResults.Rows.Count.ToString()); dView = new DataView(dtResults); dView.Sort = "Created desc"; } return dView; }
void chkRecentTasksOnly_CheckedChanged(object sender, EventArgs e) { chkMyDelegatedTasks.Checked = false; InitializeWebAppProperties(); if (chkRecentTasksOnly.Checked) { string[] actionsOwedSites = sites.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); List<ActionOwed> actionsOwed = new List<ActionOwed>(); DateTime yesterday = DateTime.Today.AddDays(-1); foreach (string site in actionsOwedSites) { using (SPSite emisSite = new SPSite(site)) { using (SPWeb emisRootWeb = emisSite.OpenWeb()) { SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate=\"107\" />"; query.ViewFields = "<FieldRef Name=\"Title\" />" + "<FieldRef Name=\"ID\" />" + "<FieldRef Name=\"AssignedTo\" Nullable=\"TRUE\" Type=\"User\" />" + "<FieldRef Name=\"Status\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"DueDate\" Nullable=\"TRUE\" Type=\"DateTime\" />" + "<FieldRef Name=\"IsDelegated\" Nullable=\"TRUE\" Type=\"Boolean\" />" + "<FieldRef Name=\"OriginalAssignee\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"ActionOwed\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"TaskSource\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"FileRef\" Nullable=\"TRUE\" Type=\"Text\" />" + "<FieldRef Name=\"Related Content\" Nullable=\"TRUE\" Type=\"Text\" />"; query.Query = "<Where>" + "<And>" + "<Geq>" + "<FieldRef Name='Created' />" + "<Value IncludeTimeValue='FALSE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(yesterday) + "</Value>" + "</Geq>" + "<And>" + "<Eq>" + "<FieldRef Name='AssignedTo' />" + " <Value Type='User'><UserID/></Value>" + "</Eq>" + "<Neq>" + "<FieldRef Name='Status' />" + "<Value Type='Choice'>Completed</Value>" + "</Neq>" + "</And>" + "</And>" + "</Where>" + "<OrderBy>" + "<FieldRef Name=\"DueDate\" />" + "</OrderBy>"; query.Webs = "<Webs Scope=\"SiteCollection\" />"; DataTable dt = emisRootWeb.GetSiteData(query); foreach (DataRow row in dt.Rows) { ActionOwed action = new ActionOwed() { TaskID = row["ID"].ToString(), Title = row["Title"].ToString(), DueDate = DateTime.Parse(row["DueDate"].ToString()), Status = row["Status"].ToString(), OriginalAssignee = row["OriginalAssignee"].ToString(), Action = row["ActionOwed"].ToString(), Source = row["TaskSource"].ToString(), FileRef = row["FileRef"].ToString(), TaskSite = site }; if (string.IsNullOrEmpty(row["IsDelegated"].ToString())) { action.IsDelegated = false; } else if (row["IsDelegated"].ToString() == "1") { action.IsDelegated = true; } else if (bool.Parse(row["IsDelegated"].ToString())) { action.IsDelegated = true; } string taskRelativeLink = row["FileRef"].ToString().Substring(row["FileRef"].ToString().IndexOf("#") + 1, (row["FileRef"].ToString().Length - row["FileRef"].ToString().IndexOf("#") - 1)); action.TaskLink = taskRelativeLink; action.AssignedTo = row["AssignedTo"].ToString().Substring(row["AssignedTo"].ToString().IndexOf("#") + 1, (row["AssignedTo"].ToString().Length - row["AssignedTo"].ToString().IndexOf("#") - 1)); actionsOwed.Add(action); } } } } grdActionsOwed.DataSource = actionsOwed; grdActionsOwed.DataBind(); } else { GetAllActionsOwed(SPContext.Current.Web.CurrentUser.Name); } }
/// <summary> /// Gets data using SPSiteDataQuery. /// </summary> /// <returns></returns> protected DataView GetDataOld() { DataTable dtResults = null; DataView dvResults = null; using (SPWeb aboutNiemWeb = SPContext.Current.Site.OpenWeb("/aboutniem")) { SPSiteDataQuery Query = new SPSiteDataQuery(); Query.Lists = "<Lists ServerTemplate=\"100\" />"; //"<Lists BaseType=\"1\" />"; Query.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='StartDate' /><FieldRef Name='Category_x0020_Domains' /><FieldRef Name='Category_x0020_Committee' /><FieldRef Name='Comments' /><FieldRef Name='EndDate' /><FieldRef Name='ContentTypeId' /><FieldRef Name='Category_x0020_Subject_x0020_Are' />"; //content type & property Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + EventsContentType + "</Value></Eq></Where>"; Query.Webs = "<Webs Scope=\"Recursive\" />"; dtResults = aboutNiemWeb.GetSiteData(Query); dtResults.Columns.Add("LinkField"); dtResults.Columns.Add("EventDate"); //fixes the links and only uses 150 chars of the PublishingPageContentField foreach (DataRow Row in dtResults.Rows) { string[] CatDomains = Row["Category_x0020_Domains"].ToString().Split('#'); string[] CatAudiences = Row["Category_x0020_Subject_x0020_Are"].ToString().Split('#'); string[] CatCommittees = Row["Category_x0020_Committee"].ToString().Split('#'); Row["Category_x0020_Domains"] = CatDomains[1]; Row["Category_x0020_Subject_x0020_Are"] = CatAudiences[1]; Row["Category_x0020_Committee"] = CatCommittees[1]; DateTime dtStartDate = DateTime.Parse(Row["StartDate"].ToString()); DateTime dtEndDate = DateTime.Parse(Row["EndDate"].ToString()); string DateFormatted = dtStartDate.ToString("MMMM d, yyyy") + " until " + dtEndDate.ToString("MMMM d, yyyy"); //String.Format("{0:M d, yyyy}", Row["StartDate"].ToString()) + " until " + String.Format("{0:M d, yyyy}", Row["EndDate"].ToString()); Row["EventDate"] = DateFormatted; Row["Comments"] = Row["Comments"].ToString().Substring(0, 150); SPSite Site = SPContext.Current.Site; Guid WebGuid = new Guid(Row["WebId"].ToString()); using (SPWeb LinkWeb = Site.OpenWeb(WebGuid)) { Guid ListGuid = new Guid(Row["ListId"].ToString()); SPList List = LinkWeb.Lists[ListGuid]; Row["LinkField"] = LinkWeb.Url + "/" + List.RootFolder.Url + "/DispForm.aspx?ID=" + Row["ID"].ToString() + "&ContentTypeID=" + Row["ContentTypeId"].ToString() + "&IsDlg=1"; } } dvResults = new DataView(dtResults); /* ////gets profile data Microsoft.SharePoint.SPServiceContext serviceContext = Microsoft.SharePoint.SPServiceContext.Current; UserProfileManager upm = new Microsoft.Office.Server.UserProfiles.UserProfileManager(serviceContext); UserProfile CurrentUser = upm.GetUserProfile(true); ProfileValueCollectionBase pvbCommunities = CurrentUser.GetProfileValueCollection("EstablishedCommunities"); ProfileValueCollectionBase pvbAudiences = CurrentUser.GetProfileValueCollection("YourAudience"); //grabs established communities and your audiences for filtering StringBuilder sbQuery = new StringBuilder(); foreach (var value in pvbAudiences) { if (sbQuery.Length == 0) { sbQuery.Append("Category_x0020_Subject_x0020_Are='" + value.ToString() + "'"); } else { sbQuery.Append(" OR Category_x0020_Subject_x0020_Are='" + value.ToString() + "'"); } } foreach (var value in pvbCommunities) { if (sbQuery.Length == 0) { sbQuery.Append("Category_x0020_Domains='" + value.ToString() + "'"); } else { sbQuery.Append(" OR Category_x0020_Domains='" + value.ToString() + "'"); } } dvResults.RowFilter = sbQuery.ToString(); LogText("Returns fine"); * **/ } return dvResults; }
public DataTable Get(string queryString, int startRow, int maxRows) { var query = new SPSiteDataQuery() { Lists = view.ListScope, Webs = view.WebScope, ViewFields = string.Join("", view.ViewFieldIds.Select(fieldId => string.Format("<FieldRef ID='{0}' />", fieldId))), Query = queryString, RowLimit = (uint)(startRow + maxRows) }; return Get(query, startRow, maxRows); }
protected DataTable Get(SPSiteDataQuery query, uint startRow, uint maxRows) { var data = Web.GetSiteData(query); if (data.Rows.Count > 0) { return data.Select(). Skip(int.Parse(startRow.ToString())).Take(int.Parse(maxRows.ToString())) .CopyToDataTable(); } else { return data; } }