将大数据从List复制到SQL时出现超时错误 [英] Time out error on copying large data from List to SQL

问题描述

我正在尝试通过c＃将数据从List复制到SQL。该列表包含375000条记录，包含250列。由于数据量巨大，我面临超时会话问题。我试着增加会话时间。它仍然没有用。

I'm trying to copy data from List to SQL through c#. The list is having 375000 records with 250 columns. As the data is huge I'm facing issue with timeout session. I tried increasing the session time. Still it didn’t work.

 尝试将记录拆分为100000并复制到数据表然后复制到SQL。仍面临同样的问题。请找到以下代码：

 tried splitting the records to 100000 and copying to data table and then copying to SQL. Still facing the same issue. Please find the below code :

using (SPSite site = new SPSite(strListURL.Trim()))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPListItemCollectionPosition itemPosition = null;
                    SPList list = web.Lists[strListName.Trim()];
                    SPQuery qry = new SPQuery();
                    qry.Query = @"<Where><Lt><FieldRef Name='ID' /><Value Type='Counter'>100000</Value></Lt></Where>";                  
                    qry.ViewFields = @"<FieldRef Name='Title' />";

  //Feild names ------ 250 feilds 

                    qry.ViewFieldsOnly = true;
                    SPListItemCollection listItems = list.GetItems(qry1);
                    int itcount = listItems.Count;
                 
                        if (listItems != null)
                    {
                        dataTable = listItems.GetDataTable();
                    }
                    dataTable.TableName = strListName.Trim();
                                    GeneralBatchBulkCopy(dataTable1, strTableName, strDatabase, strServerName, weblog);

public void GeneralBatchBulkCopy(System.Data.DataTable dt, string tablename, string strDatabase, string strServerName, SPWeb logWeb)
        {


            Impersonation imp = new Impersonation();
            if (imp.impersonateValidUser(UserName,
             Domain, Password))
            {

                string strColumnName = string.Empty;
                SqlConnection sqlcon = new SqlConnection();
                sqlcon.ConnectionString = "Data Source=" + strServerName + ";Initial Catalog=" + strDatabase + "; Integrated Security=true;";

                sqlcon.Open();


                SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon);
                try
                {
                    using (bulkcopy = new SqlBulkCopy(sqlcon))
                    {
                        bulkcopy.DestinationTableName = tablename;

                        bulkcopy.WriteToServer(dt);
                    }
                }
                catch (SqlException ex)
                {
                    if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
                    {
                        string pattern = @"\d+";
                        Match match = Regex.Match(ex.Message.ToString(), pattern);
                        var index = Convert.ToInt32(match.Value) - 1;

                        FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
                        var sortedColumns = fi.GetValue(bulkcopy);
                        var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

                        FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
                        var metadata = itemdata.GetValue(items[index]);

                        var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                        var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                        string abn = String.Format("Column: {0} contains data with a length greater than: {1}", column, length);
                        string strErrorLogDetails = "Issue in " + abn;
                        string logDate = DateTime.Now.ToString();
                        DownloadTimerJobLog(logWeb, strErrorLogDetails, logDate);
                    }

                    throw;
                }

                sqlcon.Close();
                imp.undoImpersonation();
            }
        }

非常感谢任何帮助!!

Any help is highly appreciated !!

谢谢

推荐答案

而不是批量复制尝试使用迭代方法：即抛出所有列表项并将它们放入Sql数据库在自己的事务中逐个进行。当然，与批量复制相比需要更多时间，但有助于避免超时错误。

instead of bulk copy try to use iterate approach: i.e. go throw all list items and put them to Sql database one by one in own transaction. Of course it will take more time comparing with bulk copy but will help to avoid timeout error.

这篇关于将大数据从List复制到SQL时出现超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！