【大資料處理】高效能，大資料量儲存方案SqlBulkCopy

標籤：comm   com   map   ons   解決   建立   man   尋找   nbsp   

前些日子，公司要求做一個資料匯入程式，要求將Excel資料，大批量的匯入到資料庫中，盡量少的訪問資料庫，高效能的對資料庫進行儲存。於是在網上進行尋找，發現了一個比較好的解決方案，就是採用SqlBulkCopy來處理儲存資料。SqlBulkCopy儲存大批量的資料非常的高效，就像這個方法的名字一樣，可以將記憶體中的資料表直接的一次性的儲存到資料庫中，而不需要一次一次的向資料庫Insert資料。初次實驗，百萬層級的資料表，也只需幾秒時間內就可以完全的存入資料庫中，其速度，比傳統的Insert方法不止快很多倍。下面，我將用代碼，介紹其用法。

/// <summary>/// LaborReport Data Import To Database/// </summary>/// <param name="laborReport">laborReport data table</param>/// <param name="laborReportDetail">laborReportDetail data table</param>public void LaborReportInsert(DataTable laborReport, DataTable laborReportDetail){using (SqlConnection conn = new SqlConnection(this.Connection)){if (conn.State != ConnectionState.Open)conn.Open(); using (SqlBulkCopy sqlBCLaborReport = new SqlBulkCopy(conn)){sqlBCLaborReport.BatchSize = laborReport.Rows.Count;sqlBCLaborReport.BulkCopyTimeout = 60;sqlBCLaborReport.DestinationTableName = "LaborReport";sqlBCLaborReport.WriteToServer(laborReport);}using (SqlBulkCopy sqlBCLaborReportDetails = new SqlBulkCopy(conn)){sqlBCLaborReportDetails.BatchSize = laborReportDetail.Rows.Count;sqlBCLaborReportDetails.BulkCopyTimeout = 60;sqlBCLaborReportDetails.DestinationTableName = "LaborReportDetails";sqlBCLaborReportDetails.WriteToServer(laborReportDetail);}if (conn.State != ConnectionState.Closed)conn.Close();}}

上邊的範例程式碼，將記憶體中的兩個DataTable 資料一次性的存入資料庫中，只要記憶體中的資料表格的結構跟資料庫中的表格結構一樣，就可以了，如果資料庫中包含自增欄欄位，我們可以不需要在記憶體表中定義，當資料儲存到資料庫中的時候，自增列會自動產生資料的。

//資料大量匯入sqlserver,建立執行個體 System.Data.SqlClient.SqlBulkCopy?sqlbulk?=?new?System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ToString());//?目標資料庫表 名 sqlbulk.DestinationTableName?=?"tablename"; //?資料集欄位索 引與資料庫欄位索引映射 sqlbulk.ColumnMappings.Add(0,?5); sqlbulk.ColumnMappings.Add(1,?4); sqlbulk.ColumnMappings.Add(2,?7); sqlbulk.ColumnMappings.Add(3,?1); sqlbulk.ColumnMappings.Add(4,?10); sqlbulk.ColumnMappings.Add(5,?6); sqlbulk.ColumnMappings.Add(6,?2); //?匯入 sqlbulk.WriteToServer(sqldb); sqlbulk.Close(); 

 

        public void SqlBulkCopyDomo()        {            Dal.DalSql dal = new Dal.DalSql();            //擷取來源資料            string gongPanSql = @" select a,b,c,d  from T where 條件過濾";            DataTable dtGongPan = dal.QueryByFind(gongPanSql).Tables[0];//sql協助類                        //資料複製            using (SqlConnection conn = new SqlConnection(SqlConnString))            {                // Open the connection.                conn.Open();                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))                {                    // 設定目標表名稱                    bulkCopy.DestinationTableName = "HouseBase";                    bulkCopy.BulkCopyTimeout = 3600;//逾時時間設定                    bulkCopy.BatchSize = 1000;//分批提交記錄數，可不設                    // 列名映射        bulkCopy.ColumnMappings.Add("來源資料列", "目標表對應列名稱");                                                                bulkCopy.ColumnMappings.Add("a", "HouseCode");                    bulkCopy.ColumnMappings.Add("b", "CommName");                    bulkCopy.ColumnMappings.Add("c", "FenQiName");                    bulkCopy.ColumnMappings.Add("d", "Seat");                   //複製資料                    bulkCopy.WriteToServer(dtGongPan);                }            }        }

 

【大資料處理】高效能，大資料量儲存方案SqlBulkCopy