[计算机]c中高效的excel导入sqlserver的方法

1、.c#中高效的excel导入sqlserver的方法时间:2010-02-26 15:15:53来源:网络 作者:未知 点击:343次将oledb读取的excel数据快速插入的sqlserver中，很多人通过循环来拼接sql，这样做不但容易出错而且效率低下，最好的办法是使用bcp，也就是System.Data.SqlClient.SqlBulkCopy 类来实现。不但速度快，而且代码简单，下面测试代码将oledb读取的excel数据快速插入的sqlserver中，很多人通过循环来拼接sql，这样做不但容易出错而且效率低下，最好的办法是使用bcp，也就是System

2、.Data.SqlClient.SqlBulkCopy 类来实现。不但速度快，而且代码简单，下面测试代码导入一个6万多条数据的sheet，包括读取（全部读?冉下谖业目肪持兄恍枰?0秒左右，而真正的导入过程只需要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)                    /测试，将excel中的sheet1导入到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                            /获取全部数据                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);                /如果目标表不存在则创建          &

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导入数据                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;/每次传输的行数           

22、0;        bcp.NotifyAfter = 100;/进度提示的行数                    bcp.DestinationTableName = sheetName;/目标表           

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