Thursday, May 12, 2011

select all colum from sharepoint webservice list and fetch more than 5000 rows by paging

        public .DataTable GetSchedulerCalendarDataTable()
        {
            System.Data.DataTable dtTable = null;
            try
            {
                SharePointList.Lists listService = new SharePointList.Lists();
                string listName = "Scheduler Calendar";
                listService.Credentials = new NetworkCredential(UserName, Password, Domain);
                XmlNamespaceManager nsmgr;
                XmlNode listView = listService.GetListAndView(listName, null);
                nsmgr = new XmlNamespaceManager(listView.OwnerDocument.NameTable);
                nsmgr.AddNamespace("a", "http://schemas.microsoft.com/sharepoint/soap/");
                List<String> fieldNames = new List<string>();
                foreach (XmlNode field in listView.SelectNodes("a:List/a:Fields/a:Field", nsmgr))
                {
                    XmlAttribute attr = field.Attributes["Name"];
                    // it should not be null, but we check it
                    if (attr != null)
                    {
                        // we store all fields in a collection for later use
                        fieldNames.Add(attr.Value);
                    }
                }
                // get data from list
                XmlDocument xmlDoc = new System.Xml.XmlDocument();
                XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
                XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
                XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");
                ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>";
                ndQuery.InnerXml = "<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>";
                XmlNode positionNext = null;
                while (true)
                {
                    XmlNode items = listService.GetListItems(listName, null, ndQuery, ndViewFields, "1000", ndQueryOptions, null);
                    nsmgr = new XmlNamespaceManager(items.OwnerDocument.NameTable);
                    nsmgr.AddNamespace("z", "#RowsetSchema");
                    nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                    XmlNodeList itemNodeList = items.SelectNodes("rs:data/z:row", nsmgr);
                    foreach (XmlNode itemNode in itemNodeList)
                    {
                        foreach (String fieldName in fieldNames)
                        {
                            String wsFieldName = "ows_" + fieldName;
                            XmlAttribute attr = itemNode.Attributes[wsFieldName];
                            // if the attribute is missing, we should add it
                            if (attr == null)
                            {
                                attr = itemNode.OwnerDocument.CreateAttribute(wsFieldName);
                                itemNode.Attributes.Append(attr);
                            }
                        }
                    }
                    DataSet listDataSet = new DataSet();
                    // read the result into a data set
                    XmlTextReader readerListDataSet = new XmlTextReader(items.OuterXml, XmlNodeType.Document, null);
                    listDataSet.ReadXml(readerListDataSet);
                    if (listDataSet.Tables[1].Rows.Count > 0)
                    {
                        foreach (DataRow item in listDataSet.Tables[1].Rows)
                        {
                            if (dtTable == null)
                            {
                                dtTable = GetExcelSheetDataTable();
                            }
                            DataRow dr = dtTable.NewRow();
                            string strartDate = string.Empty;
                            if (item["ows_EventDate"] != null) strartDate = (item["ows_EventDate"].ToString() == string.Empty) ? string.Empty : Convert.ToDateTime(item["ows_EventDate"].ToString()).ToString("MM/dd/yyyy");                           
dtTable.Rows.Add(dr);
                        }
                    }

                    positionNext = items.SelectSingleNode("//@ListItemCollectionPositionNext");
                    if (positionNext == null)
                        break; // break if it is the last page
                    else
                        // set next page info if there is more page
                        ndQueryOptions.InnerXml = "<Paging ListItemCollectionPositionNext='" + positionNext.InnerXml + "' />";
                }

            }
            catch (Exception ex)
            {
                Err.ErrorLog(HttpContext.Current.Server.MapPath("Logs/ErrorLog"), "getSchedulerCalendarDataTable Method:" + ex.Message);
            }
            finally
            {
            }
            return dtTable;
        }

No comments:

101% shares

101% shares
about me