用Excel导入大量数据,用SqlBulkCopy存储，秒级完成

/// <summary>
    /// 导入捐款信息
    /// </summary>
    /// <param name="context"></param>
    public void ImportMonthDonation(HttpContext context)
    {
        try
        {
            sw.Start();
            HttpPostedFile _upfile = context.Request.Files["ImportFile"];
            //捐款月份
            var month = context.Request.QueryString["Month"];
            if (_upfile == null)
            {
                context.Response.Write("<script>parent.resultdata('请选择要导入的文件！');</script>");
                return;
            }
            string FileName = _upfile.FileName;
            //判断导入文件是否正确
            string Extension = System.IO.Path.GetExtension(FileName).ToString().ToLower();
            if (Extension != ".xls" && Extension != ".xlsx")
            {
                context.Response.Write("<script>parent.resultdata('您导入的Excel文件不正确，请确认后重试！');</script>");
                return;
            }
            string documentno = SysContext.GetGUID();
            //保存路径
            string path = "/upload/Excel/MonthDonation/";
            string _fileExt = Extension;//文件扩展名
            string _fileName = documentno + "." + _fileExt; //随机文件名
            //按日期归类保存
            string filePath = path;
            //获得要保存的文件路径
            string serverFileName = filePath + _fileName;
            //物理完整路径                    
            string toFileFullPath = HttpContext.Current.Server.MapPath(filePath);
            //检查是否有该路径没有就创建
            if (!System.IO.Directory.Exists(toFileFullPath))
            {
                System.IO.Directory.CreateDirectory(toFileFullPath);
            }
            //将要保存的完整文件名                
            string toFile = toFileFullPath + _fileName;
            //将文件保存到服务器上
            _upfile.SaveAs(toFile);
            if (!System.IO.Directory.Exists(context.Server.MapPath(filePath + SysContext.UserId)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(filePath + SysContext.UserId));
            }
            //将Excel中的数据放到DataTable中
            DataTable dt = GetExcelSheet(toFile, FileName);
            //向数据库中插入月捐信息数据
            //2022/06/29 Update
            //int i = InsertMonthDonationData(dt, month);
            int i = InsertMonthDonationDataNew3(dt, month);
            time = sw.ElapsedMilliseconds;
            if (i > 0)
            {
                //提示导入成功数据
                context.Response.Write("<script>parent.resultdata('成功导入" + i + "条数据！共花费时间" + time + "毫秒');</script>");
                //#region 更新断捐信息
                //Method.Wlog("捐款信息导入功能-更新断捐信息开始时间：" + DateTime.Now);
                //UpdateDonationStatis(month);
                //Method.Wlog("捐款信息导入功能-更新断捐信息结束时间：" + DateTime.Now);
                //#endregion 更新断捐信息
            }
            else
            {
                if (i == -1)
                {
                    context.Response.Write("<script>parent.resultdata('导入捐款信息订单编号为空！');</script>");
                }
                else
                {
                    context.Response.Write("<script>parent.resultdata('导入失败');</script>");
                }
            }
        }
        catch (Exception ex)
        {
            huahaocms.com.CORE.Method.Wlog(ex.ToString());
            context.Response.Write("<script>parent.resultdata(\"" + ex.Message.ToString() + "\");</script>");
            return;
        }
    }

/// <summary>
    /// 向数据库中插入月捐信息数据 --2022/06/30 新增
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public int InsertMonthDonationDataNew3(DataTable dt, string month)
    {
        int i = 0;
        int s = dt.Rows.Count;
        List<string> listSql = new List<string>();
        //修正 2020/11/06 订单号列表
        List<string> lstOrderNo = new List<string>();
        //创建一个新的DataTale
        DataTable dtDonationIncom = new DataTable();
        dtDonationIncom.Columns.Add("ID", Type.GetType("System.Int32"));
        dtDonationIncom.Columns.Add("OrderNo", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("DonorNo", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("ProductID", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("ProjectID", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("ProjectName", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("Amount", Type.GetType("System.Decimal"));
        dtDonationIncom.Columns.Add("DonationDate", Type.GetType("System.DateTime"));
        dtDonationIncom.Columns.Add("Staus", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("RecordStatus", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("CreateTime", Type.GetType("System.DateTime"));
        dtDonationIncom.Columns.Add("Month", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("DonationChannel", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("IsLooseMoney", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("DonationFlg", Type.GetType("System.String"));
        dtDonationIncom.Columns.Add("IsLock", Type.GetType("System.String"));
        
        Method.Wlog("导入捐款信息资金分流功能----捐款信息条数：" + dt.Rows.Count);
        //取得选择的月份的捐款记录
        var dicDonationInfobyLinq = DbHelperSQL.Query("select * from FPJJ_DonationInformation where  Month='" + month + "' and RecordStatus='ACTIVE'").Tables[0].AsEnumerable();
        //开始循环导入的捐款信息
        for (int j = 0;j <dt.Rows.Count; j++)
        {
            
            var OrderNo = dt.Rows[j]["订单号"].ToString();
            var DonorNo = dt.Rows[j]["捐赠人ID"].ToString();
            var ProductID = dt.Rows[j]["产品ID"].ToString();
            var ProjectID = dt.Rows[j]["产品ID+产品名称"].ToString();
            var ProjectName = dt.Rows[j]["产品名称"].ToString();
            var Amount = decimal.Parse(dt.Rows[j]["捐赠金额"].ToString());
            var DonationDate = dt.Rows[j]["捐赠日期"].ToString();
            var DonationChannel = dt.Rows[j]["捐赠渠道"].ToString();
            string looseMoney = dt.Rows[j]["是否零散资金"].ToString().Replace(" ", "");

            //修正 2020/11/06 检查导入的表格中是否有重复的订单号
            if (!lstOrderNo.Contains(OrderNo))
            {
                //检查导入的捐款信息是否已存在
                var dicDonation = dicDonationInfobyLinq.Where(a => a.Field<string>("OrderNo") == OrderNo).ToList();
                if (dicDonation.Count == 0)
                {
                    DataRow newRow = dtDonationIncom.NewRow();
                    newRow["OrderNo"] = OrderNo;
                    newRow["DonorNo"] = DonorNo;
                    newRow["ProductID"] = ProductID;
                    newRow["ProjectID"] = ProjectID;
                    newRow["ProjectName"] = ProjectName;
                    newRow["Amount"] = Amount;
                    newRow["DonationDate"] = DonationDate;
                    newRow["Staus"] = DonationChannel;
                    newRow["RecordStatus"] = "ACTIVE";
                    newRow["CreateTime"] = DateTime.Now;
                    newRow["Month"] = month;
                    if (looseMoney == "是")
                    {
                        newRow["IsLooseMoney"] = "1";
                    }
                    else
                    {
                        newRow["IsLooseMoney"] = "0";
                    }
                    newRow["DonationFlg"] = "0";
                    newRow["IsLock"] = "0";

                    dtDonationIncom.Rows.Add(newRow);
                }

                //修正 2020/11/06 把导入的订单号加到订单列表中
                lstOrderNo.Add(OrderNo);
                //计算导入的条数
                i++;
            }
        }
        Method.Wlog("新生成的捐款DataTable条数："+dtDonationIncom.Rows.Count);
        if (dtDonationIncom.Rows.Count > 0)
        {
            if (DbHelperSQL.InportExcleData(dtDonationIncom, "FPJJ_DonationInformation") > 0)
            {
                sw.Stop();
                time = sw.ElapsedMilliseconds;
            }
            else
            {
                i = 0;
                sw.Stop();
                time = sw.ElapsedMilliseconds;
            }
        }
        else
        {
            //日志
            Method.Wlog("捐款信息导入功能-捐款信息SQL文列表为空！");
        }
        return i;
    }

/// <summary>
    /// 向数据库中插入导入的数据 
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
public static int InportExcleData(DataTable dt,string tableName)
        {
 //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.
        public static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                using (SqlBulkCopy bcp = new SqlBulkCopy(connection))
                {
                    int i = 0;
                    try
                    {

                        //指定目标数据库的表名
                        bcp.DestinationTableName = tableName;// source.TableName;
                        //建立数据源表字段和目标表中的列之间的映射;
                        foreach (DataColumn dc in dt.Columns)  //传入上述dt
                        {
                            bcp.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将dt中的列与数据库表这的列一一对应
                        }
                        //写入数据库表 dt 是数据源DataTable
                        bcp.WriteToServer(dt);
                        //关闭SqlBulkCopy实例
                        bcp.Close();
                        i = dt.Rows.Count;
                        return i;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        bcp.Close();
                        connection.Close();

                    }
                }

            }

        }