sqlbulkCopy 批量插入数据，excel导入sqlser

/// <summary>
/// 同步excel数据到临时表
/// </summary>
/// <param name="filepath">excel物理地址</param>
/// <param name="keynum">key随机数</param>
/// <param name="project_title">文件夹id</param>
public bool InsertExcleData(string filepath, int keynum, string project_title)
{
    //连接excel数据
    string strCom = " SELECT " + keynum + " as keynum" +
                    " ,合作单位 as partners_pname" +
                    " ,文档标题 as title" +
                    " ,版本版次 as Edition" +
                    " ,文档编码 as codeNum" +
                    " ,档案盒号 as fileboxNum" +
                    " ,文档属性 as dAttribute_aname" +
                    " ,文档分类 as dtype_tname" +
                    " ,所属人 as affiliatedman" +
                    " ,留存地点 as saveAddress" +
                    " ,总数量 as znum" +
                    " ,分发数量 as fnum" +
                    " ,保留数量 as bnum" +
                    //" ,文件夹 as project_title" +
                    " ," + project_title + " as project_title" +
                    "  FROM [Sheet1$]";



    string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                    ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
    if (getLastName(filepath) == "xlsx") //07excel
    {
        strCon = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filepath +
                 ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
    }
    using (OleDbConnection excelConn = new OleDbConnection(strCon))
    {
        excelConn.Open();

        OleDbCommand commandSourceData = new OleDbCommand(strCom, excelConn);
        using (OleDbDataReader olerReader = commandSourceData.ExecuteReader())
        {
            if (olerReader != null)
            {
                string connectionString = PubConstant.ConnectionString;
                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
                {
                    destinationConnection.Open();
                    //SqlTransaction sqlTran = destinationConnection.BeginTransaction(); // 开始事务
                    using (SqlTransaction transaction = destinationConnection.BeginTransaction())// 开始事务
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default,
                       transaction))
                        {
                            bulkCopy.DestinationTableName = "tempdocuments";
                            bulkCopy.BatchSize = 10;//每10条为一个事务节点

                            try
                            {
                                bulkCopy.WriteToServer(olerReader);
                                transaction.Commit();//事务提交
                                return true;
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();//事务回滚
                            }
                        }
                    }
                }
            }
        }
    }
    return false;
}

=========================================================

/// <summary>
/// 批量导入(针对sqlserver)
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="tableName">表名称</param>
/// <param name="columDic">字段路由(key为datatable的列名称, value 为对应数据库的列名),注意区分大小写</param>
/// <param name="batchSize">事务行数节点值</param>
public static string BulkCopy(DataTable dt, string tableName, Dictionary<string, string> columDic, int batchSize = 100)
{
    using (SqlConnection destinationConnection = new SqlConnection(FreeSqlHelper.Fsql.Ado.ConnectionString))
    {
        destinationConnection.Open();
        using (SqlTransaction transaction = destinationConnection.BeginTransaction()) // 开始事务
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = tableName;//表名称
                bulkCopy.BatchSize = batchSize; //每N条为一个事务节点
                if (columDic != null && columDic.Count > 0)
                {
                    foreach (var item in columDic)
                    {
                        bulkCopy.ColumnMappings.Add(item.Key, item.Value);//dt列名，数据库表名
                    }
                }
                try
                {
                    bulkCopy.WriteToServer(dt);
                    transaction.Commit(); //事务提交
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    transaction.Rollback(); //事务回滚
                    return ex.Message;
                }
            }
        }
    }
}
        /*
            //errorBulkCopy为同步失败返回错误信息
            var errorBulkCopy = BulkCopy(dt, nameof(BoYuan.Entity.SysLoginLog),
                    new Dictionary<string, string>()
                        {
                            {"id","ID"}, //注意大小写
                            {"浏览器名称","BrowserInfo"},
                            {"IP地址" ,"IP"},
                            {"系统信息" ,"OSInfo"},
                            {"错误密码","PwdShow"},
                            {"请求信息","UserAgent"},
                            {"UserId","UserId"},
                            {"UserName","UserName"},
                        });               
         */

  //将datatable插入到数据库中
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
    {
         bulkCopy.BatchSize = newDt.Rows.Count;
         bulkCopy.BulkCopyTimeout = 60;
        //指定要插入的列
         bulkCopy.ColumnMappings.Add("info", "info");//dt列名，数据库表名
         bulkCopy.ColumnMappings.Add("ProductName", "Name");
         bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");
         bulkCopy.DestinationTableName = "TableName";//数据库表名
         bulkCopy.WriteToServer(newDt);//插入数据
    }