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

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.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??? {??????? public Form1()??????? {??????????? InitializeComponent();??????? } ??????? private void button1_Click(object sender, EventArgs e)??????? {??????????? //测试，将excel中的sheet1导入到sqlserver中??????????? string connString = server=localhost;uid=sa;pwd=sqlgis;database=master;??????????? System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();??????????? if (fd.ShowDialog() == DialogResult.OK)??????????? {??????????????? TransferData(fd.FileName, sheet1, connString);??????????? }??????? } ??????? public void TransferData(string excelFile, string sheetName, string connectionString)??????? {??????????? DataSet ds = new DataSet();??????????? try??????????? {??????????????? //获取全部数据??????????????? string strConn = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source= + excelFile + ; + Extended Properties=Excel 8.0;;??????????????? OleDbConnection conn = new OleDbConnection(strConn);??????????????? conn.Open();??????????????? string strExcel = ;??????????????? OleDbDataAdapter myCommand = null;??????????????? strExcel = string.Format(select * from [{0}$], sheetName);??????????????? myCommand = new OleDbDataAdapter(strExcel, strConn);??????????????? myCommand.Fill(ds, sheetName); ??????????????? //如果目标表不存在则创建??????????????? string strSql = string.Format(if object_id({0}) is null create table {0}(, sheetName);??????????????? foreach (System.Data.DataColumn c in ds.Tables[0].Columns)???????????????