/// <summary>
/// 同步excel數(shù)據(jù)到臨時(shí)表
/// </summary>
/// <param name="filepath">excel物理地址</param>
/// <param name="keynum">key隨機(jī)數(shù)</param>
/// <param name="project_title">文件夾id</param>
public bool InsertExcleData(string filepath, int keynum, string project_title)
{
    //連接excel數(shù)據(jù)
    string strCom = " SELECT " + keynum + " as keynum" +
                    " ,合作單位 as partners_pname" +
                    " ,文檔標(biāo)題 as title" +
                    " ,版本版次 as Edition" +
                    " ,文檔編碼 as codeNum" +
                    " ,檔案盒號(hào) as fileboxNum" +
                    " ,文檔屬性 as dAttribute_aname" +
                    " ,文檔分類(lèi) as dtype_tname" +
                    " ,所屬人 as affiliatedman" +
                    " ,留存地點(diǎn) as saveAddress" +
                    " ,總數(shù)量 as znum" +
                    " ,分發(fā)數(shù)量 as fnum" +
                    " ,保留數(shù)量 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(); // 開(kāi)始事務(wù)
                    using (SqlTransaction transaction = destinationConnection.BeginTransaction())// 開(kāi)始事務(wù)
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default,
                       transaction))
                        {
                            bulkCopy.DestinationTableName = "tempdocuments";
                            bulkCopy.BatchSize = 10;//每10條為一個(gè)事務(wù)節(jié)點(diǎn)

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

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

/// <summary>
/// 批量導(dǎo)入(針對(duì)sqlserver)
/// </summary>
/// <param name="dt">數(shù)據(jù)源</param>
/// <param name="tableName">表名稱(chēng)</param>
/// <param name="columDic">字段路由(key為datatable的列名稱(chēng), value 為對(duì)應(yīng)數(shù)據(jù)庫(kù)的列名),注意區(qū)分大小寫(xiě)</param>
/// <param name="batchSize">事務(wù)行數(shù)節(jié)點(diǎn)值</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()) // 開(kāi)始事務(wù)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = tableName;//表名稱(chēng)
                bulkCopy.BatchSize = batchSize; //每N條為一個(gè)事務(wù)節(jié)點(diǎn)
                if (columDic != null && columDic.Count > 0)
                {
                    foreach (var item in columDic)
                    {
                        bulkCopy.ColumnMappings.Add(item.Key, item.Value);//dt列名，數(shù)據(jù)庫(kù)表名
                    }
                }
                try
                {
                    bulkCopy.WriteToServer(dt);
                    transaction.Commit(); //事務(wù)提交
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    transaction.Rollback(); //事務(wù)回滾
                    return ex.Message;
                }
            }
        }
    }
}
        /*
            //errorBulkCopy為同步失敗返回錯(cuò)誤信息
            var errorBulkCopy = BulkCopy(dt, nameof(BoYuan.Entity.SysLoginLog),
                    new Dictionary<string, string>()
                        {
                            {"id","ID"}, //注意大小寫(xiě)
                            {"瀏覽器名稱(chēng)","BrowserInfo"},
                            {"IP地址" ,"IP"},
                            {"系統(tǒng)信息" ,"OSInfo"},
                            {"錯(cuò)誤密碼","PwdShow"},
                            {"請(qǐng)求信息","UserAgent"},
                            {"UserId","UserId"},
                            {"UserName","UserName"},
                        });               
         */

  //將datatable插入到數(shù)據(jù)庫(kù)中
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
    {
         bulkCopy.BatchSize = newDt.Rows.Count;
         bulkCopy.BulkCopyTimeout = 60;
        //指定要插入的列
         bulkCopy.ColumnMappings.Add("info", "info");//dt列名，數(shù)據(jù)庫(kù)表名
         bulkCopy.ColumnMappings.Add("ProductName", "Name");
         bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");
         bulkCopy.DestinationTableName = "TableName";//數(shù)據(jù)庫(kù)表名
         bulkCopy.WriteToServer(newDt);//插入數(shù)據(jù)
    }