大数据量高效导入数据库（以excel导入sqlserver为例）

本人文章陆续转向本人微信公账号发布 公众号：搬砖码农SmallNNN，期望您要是搬砖码农，一起学习探讨，祝您阅读愉快。 

     最近正在做一个项目，要把excel中的数据导入到sqlserver数据库中，首先想到的就是insert,采用连接Excel对象的Microsoft.ACE.OLEDB.12.0接口引擎连接到excel,然后读取到DataTable中然后每次读取一个Row，insert到数据库表里，但是效率低的让人可怕，一旦数据量过大，卡的要死，我们测试数据是有24万条，文件大小14,249,487 字节(大概14MB)，后来经过一番研究发现了SqlBulkCopy，至于SqlBulkCopy的详细介绍就不说了，都是文字描述，微软的直接MSDN就可以了，但是效率确实比insert效率高很多，这里我们就直接上代码吧：

 

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();
                // 指定相应的打开文档的目录
                odXls.InitialDirectory = "C://";
                // 设置文件格式
                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>
        /// 读取文件具体内容
        /// </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;//每次传输的行数  
                   // bcp.NotifyAfter = 1000;//进度提示的行数  
                    bcp.DestinationTableName = "tb_bigdata";//目标表
                    bcp.WriteToServer(dt);
                    MessageBox.Show("导入完成！");
                }
                //为datagridview设置数据源
                this.xlsExpData.DataSource = dt;


                //设置每一列显示数据模式为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();
        }
    }
}

 

 

 

 

 

这里我们经过多次测试，将24万条数据导入到sqlserver中，因为目前手边没有可以测试的服务器就临时使用自己的计算机当做服务器进行测试，这里是我的电脑配置：

平均导入时间在20秒左右，上下不差1秒，执行时间和BatchSize设置也有关系，如果设置为100，则需要35秒左右，如果是真正的服务器的话相信执行时间会大大缩短，大家都知道服务器的处理事务能力比普通计算机要强大很多。

当然在本地使用时，效率是比insert高的，但是也有很多限制比如：

 

1、导入时会有排它意向锁，易死锁。

2、数据只能从服务器本地导入到本地服务器上的数据库中，无法从客户端将数据导入到远程服务器上等等

那么可能大家会担心了，这样也不行啊，客户端不能导入到远程服务器上很不方便的，那么我又研究了一种实现的方式，就是采用数据适配器，DataSet实现数据导入，我们可以将数据文件先导入到DataTable或者DataSet中，然后提交回数据库，这样也实现了数据导入，这样就不会有上边的限制了，至于具体代码就不说了，相信大家应该都会，就是ADO.NET所谓基本对象的应用，如果真的有需要具体代码的可以联系我，但是导入过程中会瞬间部分内存占用，但是占用的内存应该没啥问题，不会有什么影响。

所以我们可以视情况而定，具体选择哪种导入方式，灵活运用，提高工作效率，当然这里由于自身能力原因，暂时只研究了这几种方式，一定还有效率更高的导入方式，这里我会继续研究，如果有新进展一定及时更新，如果有需要请关注或者私信我，大家一起学习，一起进步，希望会帮助到大家。