最近测试一个程序，将EXCEL数据（65535行，4列[ModelName,ModelNumber,ModelCost,ModelProductivity]）导入到数据库表中（3列[aa,bb,cc]），为了解决时间问题，采用了.Net Framework中的SqlBulkCopy类，经过最后测试，执行操作需要8秒左右即可完成，当然测试的数据是比较简单的。
代码如下：
private void button1_Click(object sender, EventArgs e)
{
string connString = "Data Source=XXXX; Initial Catalog = master; Integrated Security = true;";
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;HDR=Yes;IMEX=1\";";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
string strExcel = "";
System.Data.OleDb.OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不在在则创建
string strsql = string.Format("if object_id('{0}') is null create table {0}(", "Table_2");
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strsql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strsql = strsql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strsql;
command.ExecuteNonQuery();
sqlconn.Close();
}
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;
bcp.NotifyAfter = 100;
bcp.DestinationTableName = "Table_2";
bcp.ColumnMappings.Add("ModelName", "aa"); //ModelName表示EXCEL中的某列，与数据库表中的aa列对应
bcp.ColumnMappings.Add("ModelNumber", "bb");//ModelNumber表示EXCEL中的某列，与数据库表中的bb列对应
bcp.ColumnMappings.Add("ModelCost", "cc");//ModelCost表示EXCEL中的某列，与数据库表中的cc列对应
MessageBox.Show(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
bcp.WriteToServer(ds.Tables[0]);
MessageBox.Show(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
}
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}