how to fetch Large sharepoint list item(>10000) and write them in text file

Question

My SharePoint site having a Large List that contains large data I have to fetch all items and show them in gridview? I am using below code and getting the below error

"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator"

private void GetData()
    {
        using (SPSite site = new SPSite("URL"))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList list = web.Lists.TryGetList("BulkData");

                if (list != null)
                {
                    SPQuery query = new SPQuery();
                    query.Query = "<Where><IsNotNull><FieldRef Name=\"Title\" /></IsNotNull></Where>";
                    query.QueryThrottleMode = SPQueryThrottleOption.Override;
                    SPListItemCollection items = list.GetItems(query);
                    int itemCount = items.Count;
                    StringBuilder sb = new StringBuilder();
                    string str1 = string.Empty;
                    foreach (SPListItem item in items)
                    {
                        int i = 1;
                        sb.Append("\r\n").Append(Convert.ToString(item["Title"])).Append("\r\n");
                        i++;
                    }
                    Log(sb);
                }
            }
        }
    }

region Log File

    public void Log(StringBuilder ErrorMessage)
    {

        string LogFileTime = DateTime.Now.ToString("ddMMyyyyHHmmss");
        string LogFilePath = Server.MapPath(@"~\\Logs\");
        if (!File.Exists(LogFilePath + "BulkData" + LogFileTime + ".txt"))
        {
            var LogFileName = File.Create(LogFilePath + "BulkData" + LogFileTime + ".txt");
            var WriteToLogFile = new StreamWriter(LogFileName);
            WriteToLogFile.Write(ErrorMessage);
            WriteToLogFile.Close();
            LogFileName.Close();
        }


    }
    #endregion

Max Max · Accepted Answer · 2014-11-04T10:10:39

You have to modify the List View threshold in Central Admin, setup by default to 5000 elements.

To minimize database contention, SQL Server often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows.

However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily escalate the lock to the entire table until the database operation is completed.

enter image description here

See this link on MSDN

See this link for instructions step-by-step

However if you must change this value by code you can

SPQuery q1 = new SPQuery();
q1.QueryThrottleMode = SPQueryThrottleOption.Override;

Caution! Remember to grant privileges to the account that will run the code.

see this link for details.

how to fetch Large sharepoint list item(>10000) and write them in text file

region Log File

3 Answers