[.net] 使用SqlBulkCopy提高匯入資料的效能

使用SqlBulkCopy提高匯入資料的效能

向SQL Server中匯入大量數量可以用bulk insert，但是必須要求插入的檔案在資料庫機器上或者一個資料庫可以訪問的共用資料夾中（我不知道怎麼設定共用資料夾，以使得SQL Server能訪問到）

SqlBulkCopy 是.net中的一個類，提供了匯入大量資料的功能。

基本用法如下：

using (SqlBulkCopy bc = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null)){     bc.BulkCopyTimeout = 10 * 60;     bc.BatchSize = 10000;     bc.DestinationTableName = "dbo.Destination";     bc.WriteToServer(reader); //reader 是一個繼承自IDataReader的類的執行個體}

 

自己可以寫代碼來實現繼承自IDataReader的類。有n多成員要實現。。。

比如FieldCount， Read()， GetValue(int i)， Close()等

下面是一個讀檔案的例子：

 

//返回記錄的列數        public int FieldCount          {             get { return 3; }        }        //讀記錄，此方法會被自動調用        public bool Read()        {            if (_Reader == null)                _Reader = new StreamReader(_FilePath);            string line = _Reader.ReadLine();                        if (line != null)            {                _CurrentQueryItem = GetRawData(line);                _Count++;                while (_CurrentQueryItem == null)//如果讀出的是不滿足條件的記錄，則讀下一條記錄                {                    Read();                }                return true;            }                        return false;        }        //返回一條記錄中第i 列（項）的值，此方法會被自動調用    //SqlBulkCopy內部應該有一個迴圈，從0到FieldCount -1 ，再調用GetValue(int i)這個方法。我猜的。。。        public object GetValue(int i)        {            if (_CurrentQueryItem == null)                return null;            switch (i)            {                //如果資料庫中表的第一列是自增欄位，則會忽略第一列，也就是說此方法被調用時，i只會從1開始，所以不需要case 0的情況。估計.net內部去取目的表的schema，自動判斷哪些列是需要從外部匯入的。有空再研究這個問題                case 0:                    return _CurrentQueryItem.Item1;                case 1:                    return _CurrentQueryItem.Item2;                case 2:                    return _CurrentQueryItem.Item3;                default:                    throw new IndexOutOfRangeException();            }        }        //釋放資源        public void Close()        {            Dispose();        }        public void Dispose()        {            if (_Reader != null)                _Reader.Close();        }

 

有一些其他屬性其方法需要自己實現，當然有的不實現也沒關係。似乎重要的就以上幾個方法了。
對照SqlDataRead，自己可以猜想出會用到哪些方法。

經過實驗，一個檔案如果一行一行插入到資料庫裡，需要大約2分鐘，如果用SqlBulkCopy 10秒左右就完成了。而且可以自己實作類別來指定處理什麼資料，也不用把檔案放在資料庫機器上了。不錯。

忘說了，SqlBulkCopy裡用到的connction對象只能是SqlConnection。SqlBulkCopy.WriteToServer (DataRow]) 和SqlBulkCopy.WriteToServer (DataTable) 都是可以的。