.NET實現(xiàn)大批量Excel數(shù)據(jù)導(dǎo)入SQL SERVER

［摘要］ 人們習(xí)慣將待添加的記錄保存在Excel文件的作為存檔，然后再利用管理系統(tǒng)的數(shù)據(jù)導(dǎo)入功能將Excel數(shù)據(jù)批量的導(dǎo)入到數(shù)據(jù)庫中。實現(xiàn)Excel數(shù)據(jù)導(dǎo)入到SQL SERVER主要有兩種方法，使用DTS界面導(dǎo)入和編寫數(shù)據(jù)導(dǎo)入代碼。

［關(guān)鍵詞］ 數(shù)據(jù)導(dǎo)入管理系統(tǒng)Excel工作效率

一、引言

數(shù)據(jù)導(dǎo)入在目前的管理信息系統(tǒng)中是較常見的功能。比如工作人員經(jīng)常會將下屬單位上報的數(shù)據(jù)保存在Excel文件中，然后利用系統(tǒng)的數(shù)據(jù)導(dǎo)入功能將相關(guān)數(shù)據(jù)一次性導(dǎo)入到數(shù)據(jù)庫管理系統(tǒng)中，避免了大量數(shù)據(jù)的逐條入錄，提高工作效率。然而數(shù)據(jù)導(dǎo)入的代碼編寫好壞也會對數(shù)據(jù)導(dǎo)入的性能產(chǎn)生影響。

二、SQL SERVER導(dǎo)入Excel

在應(yīng)用程序中經(jīng)常需要將外部的數(shù)據(jù)一次性導(dǎo)入到SQL SERVER 中，使用SQL SERVER導(dǎo)入數(shù)據(jù)的方法有很多種，對于DBA可以直接使用DBMS自帶的DTS工具完成數(shù)據(jù)的導(dǎo)入，操作非常方便，然而對于管理系統(tǒng)的使用人員是不能直接接觸數(shù)據(jù)庫管理系統(tǒng)的，無法采用這種方法實現(xiàn)數(shù)據(jù)的導(dǎo)入。因此，程序設(shè)計員應(yīng)該要將數(shù)據(jù)導(dǎo)入功能在程序中做好，直接提供給用戶去實現(xiàn)，程序?qū)崿F(xiàn)數(shù)據(jù)導(dǎo)入的方法主要有三種，下面介紹各方法實現(xiàn)的原理。

1. insert into導(dǎo)入法

在SQL SERVER 中這種方法是最基本的數(shù)據(jù)導(dǎo)入方法，大多數(shù)程序開發(fā)人員在實現(xiàn)數(shù)據(jù)導(dǎo)入時首先都會想到這種方法。

語法：insert into tablename(字段名列表) values(值列表)

這種方法雖然簡單，但是不適合在實現(xiàn)插入大批量數(shù)據(jù)的操作上，根據(jù)實驗測試表明，如果插入100百萬條記錄，這種方法將耗時901599豪秒，約15分鐘。嚴(yán)重影響了工作效率，也會由于耗時過長而對導(dǎo)入的數(shù)據(jù)的正確性產(chǎn)生影響。

2. BULK insert導(dǎo)入法

BULK insert導(dǎo)入法對于大家可能不是很熟悉，但是當(dāng)要實現(xiàn)大批量數(shù)據(jù)的導(dǎo)入時采用這種方法將會給你帶來極高的效率。

語法：BULK insert tablename from '數(shù)據(jù)文件' WITH (FIELDTERMINATOR = '，'，ROWTER /.，mbMINATOR=''，BATCHSIZE = 100000)

還是以100萬條數(shù)據(jù)為測試，將其分五個線程，每個線程擔(dān)任20萬條數(shù)據(jù)，每5萬條一個事物，五個線程同時啟動。根據(jù)測試實驗的數(shù)據(jù)表明，該方法耗時是21099豪秒，約21秒，速度提高了近42倍。

3. SqlBulkCopy類導(dǎo)入法

SqlBulkCopy類導(dǎo)入法引用了System.Data.SqlClient名稱空間，通過該空間創(chuàng)建SqlBulkCopy類來實現(xiàn)大批量數(shù)據(jù)導(dǎo)入的，具體的數(shù)據(jù)導(dǎo)入程序?qū)崿F(xiàn)如下所示。

//源數(shù)據(jù)

SqlDataReader reader = myCommand.ExecuteReader();

//目的數(shù)據(jù)

using (SqlBulkCopy bulkCopy = new

SqlBulkCopy(destinationConnection.ConnectionString))

{

bulkCopy.ColumnMappings.Add(\"ProductID\"， \"ProductID\");

bulkCopy.ColumnMappings.Add(\"ProductName\"， \"Name\");

bulkCopy.ColumnMappings.Add(\"QuantityPerUnit\"， \"Quantity\");

bulkCopy.DestinationTableName = \"Products_TopSelling\";

bulkCopy.WriteToServer(reader);

}

使用SqlBulkCopy類導(dǎo)入法將大大的改善了大批量數(shù)據(jù)的導(dǎo)入性能，100萬條記錄的導(dǎo)入時間將縮短至4989耗秒，盡5秒鐘即可完成100完條數(shù)據(jù)的導(dǎo)入。

三、效率分析

針對以上三種導(dǎo)入法，同樣以100萬條記錄為例，在相同的軟件和硬件環(huán)境下進(jìn)行測試，結(jié)果顯示每種導(dǎo)入法的運行時間見表1所示。

表1 數(shù)據(jù)導(dǎo)入效率

導(dǎo)入法耗時（單位豪秒）

基本的insert into901599

BULK insert21099

SqlBulkCopy4989

四、結(jié)論

.NET實現(xiàn)將Excel數(shù)據(jù)導(dǎo)入到SQL SERVER的方法有多種，但是執(zhí)行的效率各不相同，實驗表明，選擇了好的數(shù)據(jù)導(dǎo)入法將會極大程度的提高工作效率，保證數(shù)據(jù)的完整性。特別是對于大批量數(shù)據(jù)導(dǎo)入時，更需要我們注意改善導(dǎo)入的性能，否則當(dāng)導(dǎo)入了大部分?jǐn)?shù)據(jù)后由于網(wǎng)絡(luò)或電腦故障等原因停止了數(shù)據(jù)的導(dǎo)入，下次又要花大量的時間來處理這些大批量數(shù)據(jù)，嚴(yán)重的影響了工作效率。

參考文獻(xiàn)：

[1]吳蓮貴 易瑜：基于ASP_NET的_Web網(wǎng)站設(shè)計_網(wǎng)絡(luò)教學(xué)系統(tǒng)的應(yīng)用與研究[J].計算機教育，2007.10

[2]鄭阿奇：SQL SERVER 使用教程.電子工業(yè)出版社.2005.5