SqlBulkCopy from Excel

最新推荐文章于 2023-09-14 14:20:09 发布

APP开发王

最新推荐文章于 2023-09-14 14:20:09 发布

阅读量685

点赞数

CC 4.0 BY-SA版权

分类专栏： C# 文章标签： excel string properties object security insert

本文链接：https://blog.youkuaiyun.com/wgsnet/article/details/4059779

C# 专栏收录该内容

46 篇文章

订阅专栏

本文介绍了一种快速将Excel文件中的数据批量导入到SQL Server数据库的方法。通过使用OleDb连接Excel，并结合SqlBulkCopy特性来高效地将大量数据从Excel复制到指定的数据库表中。该方法展示了如何设置连接字符串、执行数据读取及批处理写入操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb; using System.Data.Sql; using System.Data.SqlClient; public partial class Default2 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // block copy to DB from Excel //By wgscd, //step 1 create an excel file C:/Inetpub/wwwroot/test.xls , fill cell(1,1) with "Data",cell(1,2) with "name" //step 2 create table named "Data" with 2 column ("data","name") in your DB //there the code below: DateTime t1 = DateTime.Now; Response.Write("<br>start time:" + t1.ToString()); string ExcelFile = @"C:/Inetpub/wwwroot/test.xls"; string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel 8.0;HDR=YES;'"; using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)) { excelConnection.Open(); //Getting source data OleDbCommand command = new OleDbCommand("Select Data,name FROM [Data$]", excelConnection); // Initialize SqlBulkCopy object using (OleDbDataReader dr = command.ExecuteReader()) { // Copy data to destination string sqlConnectionString = "Data Source=localhost; Initial Catalog=stars;Integrated Security=True"; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = "Data"; bulkCopy.ColumnMappings.Add("Data", "Data"); bulkCopy.ColumnMappings.Add("name", "name"); bulkCopy.WriteToServer((IDataReader)dr); } } //Closing connection excelConnection.Close(); } DateTime t2 = DateTime.Now; Response.Write("<br>End time:" + t2.ToString()); Response.Write("<br>use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds"); Response.Write("<br>inser record count :3307"); } }

it is very fast:

the test result:

start time:2009-4-9 15:50:31
End time:2009-4-9 15:50:31
use time:328 Milliseconds
inser record count :3307

other: export DB data into Excel file:

insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:/test.xls;','select * from [Sheet2$]') (id,name) select a,b from wgscd