SqlBulkCopy快速批量上传大量数据到SqlServer

    /// <summary>
    /// 上传File
    /// </summary>
    protected void UploadFile()
    {
        HttpFileCollection httpFiles = Request.Files;

        string couponid = Request["couponid"];
        HttpPostedFile httpFile = httpFiles["uploadFile"];
        if (string.IsNullOrEmpty(couponid))
        {
            Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index); parent.layer.alert('参数丢失，请刷新重试！');</script>");
            return;
        }

        if (httpFile != null && httpFile.ContentLength > 0)
        {
            System.IO.StreamReader sr = new System.IO.StreamReader(httpFile.InputStream);
            string strLine = "";
            string batchNumber = DateTime.Now.ToString("yyyyMMddHHmmssfff");

            int importNum = 0;
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("CouponID", typeof(int));//为新的Datatable添加一个新的列名
            dt.Columns.Add("VoucherCode", typeof(string));//为新的Datatable添加一个新的列名
            dt.Columns.Add("BatchNumber", typeof(string));//为新的Datatable添加一个新的列名

            while (!string.IsNullOrEmpty((strLine = sr.ReadLine())))
            {
                System.Data.DataRow row = dt.NewRow(); //创建一个行
                row["CouponID"] = couponid.ToInt(); //从总的Datatable中读取行数据赋值给新的Datatable
                row["VoucherCode"] = strLine;
                row["BatchNumber"] = batchNumber;
                dt.Rows.Add(row);//添加次行
            }
            var importSuccess = MySqlBulkCopy(dt, "Trades_CouponLists_VoucherCode");


            // 去重
            string sqlStr = @"WITH TEST AS
 (
    SELECT ROW_NUMBER() 
    OVER(PARTITION BY CouponID,VoucherCode ORDER BY ID ) 
    AS NUM,* FROM Trades_CouponLists_VoucherCode
 )
 DELETE FROM TEST
 WHERE NUM != 1";
            int deleteNum = sqlStr.ExecuteNonQuery();
            string str = "";
            if (deleteNum > 0)
            {
                str = "存在【" + deleteNum + "】张重复券码！";
            }
            Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index);parent.layer.alert('" + str + "导入【" + (dt.Rows.Count - deleteNum) + "】张券码！');</script>");

            return;
        }
        else
        {
            Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index);parent.layer.alert('请上传券码券文件！');</script>");
            return;
        }
    }
    /// <summary>
    /// DataTable批量添加(有事务)
    /// </summary>
    /// <param name="Table">数据源</param>
    /// <param name="DestinationTableName">目标表即需要插入数据的数据表名称如"User_1"</param>
    public static bool MySqlBulkCopy(System.Data.DataTable Table, string DestinationTableName)
    {
        bool Bool = true;
        string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConnectionString))
        {
            con.Open();
            using (System.Data.SqlClient.SqlTransaction Tran = con.BeginTransaction())//应用事物
            {
                using (System.Data.SqlClient.SqlBulkCopy Copy = new System.Data.SqlClient.SqlBulkCopy(con, System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, Tran))
                {
                    Copy.DestinationTableName = DestinationTableName;//指定目标表
                    System.Data.SqlClient.SqlBulkCopyColumnMapping[] Mapping = new System.Data.SqlClient.SqlBulkCopyColumnMapping[3];
                    Mapping[0] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("CouponID", "CouponID");
                    Mapping[1] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("VoucherCode", "VoucherCode");
                    Mapping[2] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("BatchNumber", "BatchNumber");
                    if (Mapping != null)
                    {
                        //如果有数据
                        foreach (System.Data.SqlClient.SqlBulkCopyColumnMapping Map in Mapping)
                        {
                            Copy.ColumnMappings.Add(Map);
                        }
                    }
                    try
                    {
                        Copy.WriteToServer(Table);//批量添加
                        Tran.Commit();//提交事务
                    }
                    catch
                    {
                        Tran.Rollback();//回滚事务
                        Bool = false;
                    }
                }
            }
        }
        return Bool;
    }