1、.c#中高效的excel導(dǎo)入sqlserver的方法時(shí)間:2010-02-26 15:15:53來(lái)源:網(wǎng)絡(luò) 作者:未知 點(diǎn)擊:343次將oledb讀取的excel數(shù)據(jù)快速插入的sqlserver中，很多人通過(guò)循環(huán)來(lái)拼接sql，這樣做不但容易出錯(cuò)而且效率低下，最好的辦法是使用bcp，也就是System.Data.SqlClient.SqlBulkCopy 類(lèi)來(lái)實(shí)現(xiàn)。不但速度快，而且代碼簡(jiǎn)單，下面測(cè)試代碼將oledb讀取的excel數(shù)據(jù)快速插入的sqlserver中，很多人通過(guò)循環(huán)來(lái)拼接sql，這樣做不但容易出錯(cuò)而且效率低下，最好的辦法是使用bcp，也就是System

2、.Data.SqlClient.SqlBulkCopy 類(lèi)來(lái)實(shí)現(xiàn)。不但速度快，而且代碼簡(jiǎn)單，下面測(cè)試代碼導(dǎo)入一個(gè)6萬(wàn)多條數(shù)據(jù)的sheet，包括讀取（全部讀?冉下諼業(yè)目肪持兄恍枰?0秒左右，而真正的導(dǎo)入過(guò)程只需要4.5秒。using System;using System.Data;using System.Windows.Forms;using System.Data.OleDb;namespace WindowsApplication2    public partial class Form1 : Form     &#

3、160;      public Form1()                    InitializeComponent();                private void button1_Click(object

4、 sender, EventArgs e)                    /測(cè)試，將excel中的sheet1導(dǎo)入到sqlserver中            string connString = "server=localhost;uid=sa;pwd=sqlgis;database=m

5、aster"            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();            if (fd.ShowDialog() = DialogResult.OK)         &

6、#160;                  TransferData(fd.FileName, "sheet1", connString);                         

7、;   public void TransferData(string excelFile, string sheetName, string connectionString)                    DataSet ds = new DataSet();          

8、0; try                            /獲取全部數(shù)據(jù)                string strConn = "Provider=Micros

9、oft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + "" + "Extended Properties=Excel 8.0;"                OleDbConnection conn = new OleDbConnection(strConn);     

10、0;          conn.Open();                string strExcel = ""                OleDbDataAdapter my

11、Command = null;                strExcel = string.Format("select * from 0$", sheetName);                myCommand = new OleDbDataAdapter(strE

12、xcel, strConn);                myCommand.Fill(ds, sheetName);                /如果目標(biāo)表不存在則創(chuàng)建          &

13、#160;     string strSql = string.Format("if object_id('0') is null create table 0(", sheetName);                foreach (System.Data.DataColumn c in ds.Tables0.Columns)   &

14、#160;                                strSql += string.Format("0 varchar(255),", c.ColumnName);       

15、0;                        strSql = strSql.Trim(',') + ")"                using (System.Data.SqlCl

16、ient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)                                    sqlconn.

17、Open();                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();                  

18、60; command.CommandText = strSql;                    command.ExecuteNonQuery();                    sqlconn.Clo

19、se();                                /用bcp導(dǎo)入數(shù)據(jù)                using (System

20、.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)                                    bcp.S

21、qlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);                    bcp.BatchSize = 100;/每次傳輸?shù)男袛?shù)           

22、0;        bcp.NotifyAfter = 100;/進(jìn)度提示的行數(shù)                    bcp.DestinationTableName = sheetName;/目標(biāo)表           

23、60;        bcp.WriteToServer(ds.Tables0);                                        catch (Exception ex)                    &