SharePoint Learning
Login
Signup Free
User Login
Close
 
 
Keep me logged in on this computer
Signup for a FREE user account
Close
Email
Password
Designation (optional)
First Name
Last Name
Headline (optional)
Online SharePoint training videos and articles
Price: FREE

Query Large List in SharePoint

By Lin Zhang on 06 June 14

How to query Large List programmatically in SharePoint?

using (SPSite currentSite = new SPSite(SPContext.Current.Site.ID))
        {
            using (SPWeb currentWeb = currentSite.OpenWeb("crm"))
            {
                DataTable dtCustomer = new DataTable();
                DataColumn column1 = new DataColumn("customerID");
                DataColumn column2 = new DataColumn("customerName");
                column1.DataType = System.Type.GetType("System.Int32");
                column2.DataType = System.Type.GetType("System.String");
                dtCustomer.Columns.Add(column1);
                dtCustomer.Columns.Add(column2);

                SPQuery qryCustomer = new SPQuery();
                qryCustomer.RowLimit = 1999;
                // Do NOT use this way to sort, some records will be missing. Especially sorting on lookup field
                //qryCustomer.Query = "<OrderBy Override='TRUE'><FieldRef Name='User_x0020_ID' Ascending = 'FALSE' /></OrderBy>";
                SPList oListCustomer = currentWeb.Lists["Customer"];
                do
                {
                    SPListItemCollection listItemCollection = oListCustomer.GetItems(qryCustomer);
                    foreach (SPListItem item in listItemCollection)
                    {
                        //string staffID = item["User Name"].ToString().Substring(0, item["User Name"].ToString().IndexOf(";#"));
                        DataRow dr;
                        dr = dtCustomer.NewRow();
                        dr[dc_dtCustomer1] = Convert.ToInt32(item["ID"]);
                        dr[dc_dtCustomer2] = Convert.ToString(item["Name"]);
                        dtCustomer.Rows.Add(dr);
                    }
                    qryCustomer.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;

                } while (qryCustomer.ListItemCollectionPosition != null);

                // Sort
                DataView dvTemp = dtCustomer.DefaultView;
                dvTemp.Sort = "customerName ASC";
                dtCustomer = dvTemp.ToTable();
            }
        }

 

Comments:

No comments.

Copyright © SharePointLearning.net.au. All Rights Reserved | Contact Us

Powered by DigiLife Studio