SQL Server批量写入数据库——SqlBulk方法

要用到大量数据写入数据库中，传统方式太慢，采用SqlBulk方法，具体函数参考了他人：

https://www.cnblogs.com/jiekzou/p/6145550.html

根据自己项目，另做修改 

#region 批量写入数据库
        /// <summary>
        /// SqlBulkCopy批量插入数据
        /// </summary>
        /// <param name="connectionStr">链接字符串</param>
        /// <param name="dataTableName">表名</param>
        /// <param name="sourceDataTable">数据源</param>
        /// <param name="batchSize">一次事务插入的行数</param>
        public static void SqlBulkCopyByDataTable(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)
        {
            using (SqlConnection connection = new SqlConnection(connectionStr))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlBulkCopy.DestinationTableName = dataTableName;
                        sqlBulkCopy.BatchSize = batchSize;
                        for (int i = 0; i < sourceDataTable.Columns.Count; i++)
                        {
                            sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
                        }
                        sqlBulkCopy.WriteToServer(sourceDataTable);
                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                }
            }
        }
        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="connetstr">连接字符串</param>
        /// <param name="dataTableName">表名</param>
        /// <returns></returns>
        public static DataTable GetTableSchema(string connetstr,string dataTableName)
        {
            try
            {
                DbHelperSQLP dbHelperBusiness = new DbHelperSQLP(connetstr);
                string sql = "select top 1 * from " + dataTableName;
                DataTable dt = dbHelperBusiness.Query(sql).Tables[0];
                dt.Clear();
                return dt;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
                throw;
            }
        }
        #endregion

#region 调用方法
//获取表架构
            DataTable dtEXH = DataBaseApp.GetTableSchema(businessDbString, EXHTableName);
            DataTable dtEFF = DataBaseApp.GetTableSchema(businessDbString, EFFTableName); 
            //监测数据表
            foreach (LvMonitorData lmdata in lqd.Data)
            {
                DataRow dr;
                switch (lmdata.Type )
                {
                    case "废气":
                        dr=GetMonitorDataRow(lmdata,dtEXH);
                        dtEXH.Rows.Add(dr);
                        break;
                    case "废水":
                        dr = GetMonitorDataRow(lmdata, dtEFF);
                        dtEFF.Rows.Add(dr);
                        break;
                    default:
                        break;
                }                
            }
            //写入数据库
            if (dtEXH.Rows.Count>0)
            {
                DataBaseApp.SqlBulkCopyByDataTable(businessDbString, EXHTableName, dtEXH, dtEXH.Rows.Count);
            }
            if (dtEFF.Rows.Count > 0)
            {
                DataBaseApp.SqlBulkCopyByDataTable(businessDbString, EFFTableName, dtEFF, dtEFF.Rows.Count);
            }
        }
        /// <summary>
        /// 获取一行监测数据
        /// </summary>
        /// <param name="lmdata">监测数据对象</param>
        /// <param name="dt">表结构</param>
        /// <returns></returns>
        private DataRow GetMonitorDataRow(LvMonitorData  lmdata,DataTable dt)
        {
            try
            {
                string ziduan = datastyledic[lmdata.Name];//二氧化硫->SO2
                string pointid = pointmapdic[lmdata.Outlet];//2#焙烧炉->865C4B8A-C799-4D00-B92D-7075381F1DC7
                DataRow dr = dt.NewRow();
                dr["Id"] = Guid.NewGuid().ToString();
                dr["MonitoringTime"] = Convert.ToDateTime(lmdata.Datetime);
                dr["PointId"] = pointid;
                dr["DataSource"] = Convert.ToInt16(dataSource);
                dr[ziduan] = Convert.ToDecimal(lmdata.Value);
                return dr;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw;
            }
        }
#endregion