大數(shù)據(jù)量高效導(dǎo)入數(shù)據(jù)庫（以excel導(dǎo)入sqlserver為例）

最近正在做一個項目，要把excel中的數(shù)據(jù)導(dǎo)入到sqlserver數(shù)據(jù)庫中，首先想到的就是insert,采用連接Excel對象的Microsoft.ACE.OLEDB.12.0接口引擎連接到excel,然后讀取到DataTable中然后每次讀取一個Row，insert到數(shù)據(jù)庫表里，但是效率低的讓人可怕，一旦數(shù)據(jù)量過大，卡的要死，我們測試數(shù)據(jù)是有24萬條，文件大小14,249,487
 字節(jié)(大概14MB)，后來經(jīng)過一番研究發(fā)現(xiàn)了SqlBulkCopy，至于SqlBulkCopy的詳細介紹就不說了，都是文字描述，微軟的直接MSDN就可以了，但是效率確實比insert效率高很多，這里我們就直接上代碼吧：
[html] view plain copy

    using System;  
    using System.Collections.Generic;  
    using System.ComponentModel;  
    using System.Data;  
    using System.Data.OleDb;  
    using System.Drawing;  
    using System.Linq;  
    using System.Text;  
    using System.Windows.Forms;  
      
      
    namespace 讀取excel到datagridview  
    {  
        public partial class Form1 : Form  
        {  
            public Form1()  
            {  
                InitializeComponent();  
            }  
            private void Form1_Load(object sender, EventArgs e)  
            {  
            }  
      
            /// <summary>  
            /// 選擇文件，并且讀取excel中sheet  
            /// </summary>  
            /// <param name="sender"></param>  
            /// <param name="e"></param>  
            private void button1_Click(object sender, EventArgs e)  
            {    
                 try  
                 {  
                    //獲取Excel文件路徑和名稱  
                    OpenFileDialog odXls = new OpenFileDialog();  
                    // 指定相應(yīng)的打開文檔的目錄  
                    odXls.InitialDirectory = "C://";  
                    // 設(shè)置文件格式  
                    odXls.Filter = "Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";  
                    odXls.FilterIndex = 2;  
                    odXls.RestoreDirectory = true;  
                    if (odXls.ShowDialog() == DialogResult.OK)  
                    {  
                       txtFilePath.Text = odXls.FileName;  
                       OleDbConnection oledbConn = null;  
                       string sConnString = "provider=Microsoft.ACE.OLEDB.12.0;data source=" + odXls.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";  
                       oledbConn = new OleDbConnection(sConnString);  
                       oledbConn.Open();  
                       DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });  
                       combox1.Items.Clear();  
                       foreach (DataRow dr in dt.Rows)  
                       {  
                           //MessageBox.Show((String)dr["TABLE_NAME"]);  
                          combox1.Items.Add((String)dr["TABLE_NAME"]);  
                       }  
                       if (combox1.Items.Count > 0)  
                          combox1.SelectedIndex = 0;  
                    }  
                 }  
                 catch (Exception Ex)  
                 {  
                     MessageBox.Show(Ex.Message);  
                     richTextBox1.Text = Ex.Message;  
                 }      
            }  
      
            /// <summary>  
            /// 讀取文件具體內(nèi)容  
            /// </summary>  
            /// <param name="sender"></param>  
            /// <param name="e"></param>  
            private void button2_Click(object sender, EventArgs e)  
            {  
                string connectionString = @"Data Source=702-01;Initial Catalog=DBUser;Integrated Security=True";  
                OleDbConnection ole = null;  
                OleDbDataAdapter da = null;  
                DataTable dt = null;  
      
      
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;"  
                                + "Data Source=" + txtFilePath.Text.Trim() + ";"  
                                + "Extended Properties=Excel 12.0";  
                string sTableName = combox1.Text.Trim();  
                string strExcel = "select * from [" + sTableName + "]";  
                try  
                {  
                    ole = new OleDbConnection(strConn);  
                    ole.Open();  
                    da = new OleDbDataAdapter(strExcel, ole);  
                    dt = new DataTable();  
                    da.Fill(dt);  
                    using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))  
                    {  
                        //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);    
                        //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);  
                        bcp.BatchSize = 1000;//每次傳輸?shù)男袛?shù)    
                       // bcp.NotifyAfter = 1000;//進度提示的行數(shù)    
                        bcp.DestinationTableName = "tb_bigdata";//目標(biāo)表  
                        bcp.WriteToServer(dt);  
                        MessageBox.Show("導(dǎo)入完成！");  
                    }  
                    //為datagridview設(shè)置數(shù)據(jù)源  
                    this.xlsExpData.DataSource = dt;  
      
      
                    //設(shè)置每一列顯示數(shù)據(jù)模式為AllCells  
                    //for (int i = 0; i < dt.Columns.Count; i++)  
                    //{  
                    //   xlsExpData.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;  
                    //}  
                    ole.Close();  
                }  
                catch (Exception Ex)  
                {  
                    MessageBox.Show(Ex.Message);  
                }  
                finally  
                {  
                    if (ole != null)  
                        ole.Close();  
                }      
            }  
      
            //進度顯示    
            void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)  
            {  
                this.Text = e.RowsCopied.ToString();  
                this.Update();  
            }  
        }  
    }  

這里我們經(jīng)過多次測試，將24萬條數(shù)據(jù)導(dǎo)入到sqlserver中，因為目前手邊沒有可以測試的服務(wù)器就臨時使用自己的計算機當(dāng)做服務(wù)器進行測試，這里是我的電腦配置：

平均導(dǎo)入時間在20秒左右，上下不差1秒，執(zhí)行時間和BatchSize設(shè)置也有關(guān)系，如果設(shè)置為100，則需要35秒左右，如果是真正的服務(wù)器的話相信執(zhí)行時間會大大縮短，大家都知道服務(wù)器的處理事務(wù)能力比普通計算機要強大很多。
當(dāng)然在本地使用時，效率是比insert高的，但是也有很多限制比如：
1、導(dǎo)入時會有排它意向鎖，易死鎖。
2、數(shù)據(jù)只能從服務(wù)器本地導(dǎo)入到本地服務(wù)器上的數(shù)據(jù)庫中，無法從客戶端將數(shù)據(jù)導(dǎo)入到遠程服務(wù)器上等等
那么可能大家會擔(dān)心了，這樣也不行啊，客戶端不能導(dǎo)入到遠程服務(wù)器上很不方便的，那么我又研究了一種實現(xiàn)的方式，就是采用數(shù)據(jù)適配器，DataSet實現(xiàn)數(shù)據(jù)導(dǎo)入，我們可以將數(shù)據(jù)文件先導(dǎo)入到DataTable或者DataSet中，然后提交回數(shù)據(jù)庫，這樣也實現(xiàn)了數(shù)據(jù)導(dǎo)入，這樣就不會有上邊的限制了，至于具體代碼就不說了，相信大家應(yīng)該都會，就是ADO.NET所謂基本對象的應(yīng)用，如果真的有需要具體代碼的可以聯(lián)系我，但是導(dǎo)入過程中會瞬間部分內(nèi)存占用，但是占用的內(nèi)存應(yīng)該沒啥問題，不會有什么影響。
所以我們可以視情況而定，具體選擇哪種導(dǎo)入方式，靈活運用，提高工作效率，當(dāng)然這里由于自身能力原因，暫時只研究了這幾種方式，一定還有效率更高的導(dǎo)入方式，這里我會繼續(xù)研究，如果有新進展一定及時更新，如果有需要請關(guān)注或者私信我，大家一起學(xué)習(xí)，一起進步，希望會幫助到大家。