使用SqlBulkCopy进行批量插入数据操作遇到的问题

 　　最近我在开发一个业务信息统计页面，由于数据存储在多个不同服务器的数据库中，直接跨库查询很明显不合适，实际情况也不允许。遇到这种情况，我的常规思路是将各部分需要的数据先放到内存中然后关联查询。这里我想到自己之前碰到的一个坑，当时我是使用内存中List<T>数据和数据表的实体直接使用Linq关联查询。本地测试时可以的，所以就这么做了。但是到了生产环境就遇到了问题，系统报错提示内存溢出。由于生产环境数据量大，内存数据关联实体查询实际上是将实体表的数据也拿到内存中然后进行操作，结果导致内存溢出。

      回到这次的问题。bulk方法就是这个。在测试环境，完全没有问题，数据能正常插入,说明程序逻辑是没问题的。

 public static bool ExecuteSQLBulkInsert(DataTable dt, SqlConnection Connection)
        {
            try
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(Connection))
                {
                    //服务器上目标表的名称     
                    sbc.DestinationTableName = dt.TableName;
                    sbc.BatchSize = 1;
                    sbc.BulkCopyTimeout = 10;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        //列映射定义数据源中的列和目标表中的列之间的关系     
                        sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }

                    sbc.WriteToServer(dt);

                }
                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }

调用方法：

  public static bool InsertIntoStaffInfo(string cityName)
        {
            CityBase citySite = new CityBase(cityName, "");

            DataTable StaffLearninginfo = GetStaffLearninginfo(citySite);
            DataTable UserInfo = GetUserInfo(citySite);   
            var queryList = from x in StaffLearninginfo.AsEnumerable().DefaultIfEmpty()
                            join y in UserInfo.AsEnumerable() on x.Field<int>("UserID") equals y.Field<int>("UserID")
                            select new
                            {
                                UserID = x.Field<int>("UserID"),
                                UserRealName = y.Field<string>("UserRealName"),
                                CityName = x.Field<string>("CityName"),
                                EnrollCourseNum = x.Field<int>("EnrollCourseNum"),
                                FinishCourseNum = x.Field<int>("LearnedCourseNum"),
                                FinishRate = x.Field<decimal>("FinishRate"),
                                LearningTimeSpan = x.Field<int>("TotalLearnTime"),
                                ExamParticipantNum = x.Field<int>("ExamTimes"),
                                ExamPassNum = x.Field<int>("PassExamTimes"),
                                ExamPassRate = x.Field<decimal>("PassRate"),
                                BonusPoint = x.Field<decimal>("BonusPoints"),
                                EmployeeEntryDate = y.Field<DateTime>("EmployeeEntryDate"),
                                CreateTime = x.Field<DateTime>("CreateTime")

                            };

            DataTable StaffStatInfo = DataTableExtensions.ToDataTable(queryList);

            StaffStatInfo.TableName = "EB_ExamCourse_StaffStatInfo";
            

            using (SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection(DataFactory.GetConnectionStr(BaseClass.BusinessType.EBDataStatisticWrite, citySite)))
            {
                sqlcon.Open();
             //   StaffStatInfo.TableName = string.Format("[{0}]..[{1}]", sqlcon.Database, "ExamCourse_StaffStatInfo");  

                try
                {
                    SQLHelper.ExecuteCommand(string.Format("DELETE FROM EB_ExamCourse_StaffStatInfo WHERE CityName='{0}'",cityName), sqlcon);
                  //  SQLHelper.ExecuteSQLBulkInsert(StaffStatInfo, sqlcon);  //正式库权限不够，无法查看表结构
                    SQLHelper.WriteTableDataToDB(StaffStatInfo,sqlcon);
                    return true;
                }
                catch
                {
                    return false;
                }
               
            }

        }

我根据报错信息找到了微软官方给出的问题解决方法 。bulk目标表名中间不能有 . 符号，例如 [test.3]

报错信息是：无法获取目标表的列排序信息。如果该表不在当前数据库中，名称必须使用数据库名称限定(例如 [mydb]..[mytable])；此规则也适用于临时表(例如 #mytable 将指定为 tempdb..#mytable)。

https://support.microsoft.com/en-us/kb/944389 

将bulk的目标表名按要求改为server.database.schema.tablename 

然并卵！

当然，后来我google到了一个哥们遇到了和我同样的问题,但也没有给出解决方法。

我对比开发环境和生产环境最大的不同就是数据库账号权限有差异，生产数据库没法查看数据表结构，装了插件也识别不出来。不知道这算不算一个bug，反正我给微软提了反馈。

最终只好用效率较低的笨方法完成的，逐条插入。方法是上网找的，不过做了一些修改和优化，其实就是将字符串拼接换了StringBuilder。如下

 /// <summary>
        /// 将DataTable中数据写入数据库中
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool WriteTableDataToDB(DataTable dt, SqlConnection Connection)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return true;
            }
            string tname = dt.TableName;
            string colNames = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colNames += dt.Columns[i].ColumnName + ",";
            }
            colNames = colNames.TrimEnd(',');
            StringBuilder cmd = new StringBuilder();
            StringBuilder colValues=new StringBuilder();
            string cmdmode = string.Format("insert into {0}({1}) values({{0}});", tname, colNames);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                colValues.Clear();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    
                    if (dt.Rows[i][j].GetType() == typeof(DBNull))
                    {
                        colValues.AppendLine( "NULL,");
                        continue;
                    }
                    if (dt.Columns[j].DataType == typeof(string))
                        colValues.Append(string.Format("'{0}',", dt.Rows[i][j])); 
                    else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))
                    {
                        colValues.Append(string.Format("{0},", dt.Rows[i][j])); 

                    }
                    else if (dt.Columns[j].DataType == typeof(DateTime))
                    {
                        colValues.Append(string.Format("cast('{0}' as datetime),", dt.Rows[i][j])); 

                    }
                    else if (dt.Columns[j].DataType == typeof(bool))
                    {
                        colValues.Append(string.Format("{0},", dt.Rows[i][j].ToString())); 


                    }
                    else
                    {
                        colValues.Append(string.Format("'{0}',", dt.Rows[i][j])); 

                    }

                }
                cmd.AppendLine(string.Format(cmdmode, colValues.ToString().TrimEnd(',')));
             
            }
            try
            {
                 ExecuteCommand(cmd.ToString(), Connection);
                
            }
            catch (Exception e)
            {
                return false;

            }

            return true;

        }

如果有园友碰到类似问题并且解决麻烦指点下啦，THX.