使用 SqlBulkCopy 快速上傳 DataTable 到資料庫, upload datatable

SqlBulkCopy_upload_to_DB.cs

using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace IMPORT_EXCEL_TO_DATATABLE
{
    public class BizBehavior
    {
        private string mDB_ConnString = "";
        private SqlDatabase mDB = null; 

        public BizBehavior(string connString)
        {
            //initial 相關參數
            mDB_ConnString = connString;
            mDB = new SqlDatabase(connString);
        }

        public int Import_ENGLISH_STREET_by_xls(Stream fileContent)
        {
            try
            {
                //# read Excel file and transfer to DataTable
                DataTable dtSource = Misc.LoadExcel97ToDataTable(fileContent, 0);

                // 檢查
                if (dtSource.Rows.Count < 1)
                    throw new ApplicationException("Excel筆數<1");
                    
                if (dtSource.Columns.Count != 3)
                    throw new ApplicationException("Excel檔欄位數(" + dtSource.Columns.Count.ToString() + ")不是正確的欄位數(3)");

                //## 使用 SqlBulkCopy 快速上傳 DataTable 到資料庫
                using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
                {
                    DbCommand cmd = mDB.GetSqlStringCommand("DELETE FROM <Target_Table_Name> ");
                    //cmd.CommandTimeout = 600;
                    mDB.ExecuteNonQuery(cmd);

                    using (SqlBulkCopy bcp = new SqlBulkCopy(mDB_ConnString))
                    {
                        //更新哪個資料庫 (名稱) 目的資料庫資料表名
                        bcp.DestinationTableName = "<Target_Table_Name>";
                        bcp.BatchSize = 100;

                        #region 設定 column對應
                        bcp.ColumnMappings.Add("F1", "<Column_Name_1>");
                        bcp.ColumnMappings.Add("F2", "<Column_Name_2>");
                        bcp.ColumnMappings.Add("F3", "<Column_Name_3>");
                        #endregion 設定 column對應

                        bcp.WriteToServer(dtSource);
                    }

                    Ts.Complete();
                }

                // success
                return dtStreet.Rows.Count;
            }
            catch (Exception ex)
            {
                throw new Exception(new System.Diagnostics.StackFrame().GetMethod().Name + " error:" + ex.Message);
            }
        }
    }
}