将Excel文件上传到数据库 [英] Upload a excel file into database

问题描述

尊敬的先生，


我正在使用asp.net和C#和sqlserver在Web应用程序中工作.
我想将50000名员工详细信息(手机号码，姓名)excel文件上传到sqlserver数据库.我在我的项目中使用了上载控件.但是我花了很多时间来赞美.请帮助我以任何简单的方法在1或2秒钟内将excel文件升级到数据库.
请帮我.我在我的项目中编写了以下代码，但上传了50000名员工却花了15像素或20像素

Dear sir,


i am working in webapplication useing asp.net and c# and sqlserver.
i want to upload 50000 employee details(mobile no,name) excel file to sqlserver database. i done using upload controle in my project.but i was taken so much time to uplaod. please help me any easy way to uplaod excel file into database within 1 or 2 seconds .
pleas help me. i wrote below code in my project but it was taken 15 minits or 20 minits uploading 50000 employees

if (FileUpload1.HasFile)
                {
                    int aaa = FileUpload1.PostedFile.ContentLength;
                    String savePath = (Server.MapPath("..\\Admin\\excel\\"));
                    savePath += FileUpload1.FileName;
                    FileUpload1.SaveAs(savePath);
                    String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended       Properties=Excel 8.0";
                    OleDbConnection olcon = new OleDbConnection(conStr);
                    olcon.Open();
                    OleDbCommand olcom = new OleDbCommand("SELECT Count(*) FROM [Sheet1$]", olcon);
                    long count = Convert.ToInt32(olcom.ExecuteScalar());
                    Session["cou1"] = count;
                    olcon.Close();
                    olcon.Open();
                    OleDbCommand olcom1 = new OleDbCommand("SELECT * FROM [Sheet1$]", olcon);
                    OleDbDataReader dr;
                    dr = olcom1.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    SqlConnection sqlConn = new SqlConnection(Con1);
                    sqlConn.Open();
                    SqlBulkCopy bk = new SqlBulkCopy(sqlConn);
                    bk.DestinationTableName = "Customer";
                    bk.WriteToServer(dt);
}

谢谢&问候

srinivas

Thanks & Regards

srinivas

推荐答案

"，olcon)； long count = Convert.ToInt32(olcom.ExecuteScalar()); 会话[" ] =计数； olcon.Close(); olcon.Open(); OleDbCommand olcom1 = 新 OleDbCommand("

", olcon); long count = Convert.ToInt32(olcom.ExecuteScalar()); Session["cou1"] = count; olcon.Close(); olcon.Open(); OleDbCommand olcom1 = new OleDbCommand("SELECT * FROM [Sheet1

"，olcon)； OleDbDataReader dr; dr = olcom1.ExecuteReader(CommandBehavior.CloseConnection); DataTable dt = 新 DataTable(); dt.Load(dr); SqlConnection sqlConn = 新 SqlConnection(Con1); sqlConn.Open(); SqlBulkCopy bk = 新 SqlBulkCopy(sqlConn); bk.DestinationTableName = " ; bk.WriteToServer(dt); }

", olcon); OleDbDataReader dr; dr = olcom1.ExecuteReader(CommandBehavior.CloseConnection); DataTable dt = new DataTable(); dt.Load(dr); SqlConnection sqlConn = new SqlConnection(Con1); sqlConn.Open(); SqlBulkCopy bk = new SqlBulkCopy(sqlConn); bk.DestinationTableName = "Customer"; bk.WriteToServer(dt); }

谢谢&问候

srinivas

Thanks & Regards

srinivas

我看不到一种在一秒钟内将60000条记录上传到数据库的可能方法.
如果使用SQLCommand而不是OleDB命令(因为使用的是Sql Server)，则可能会获得较小的性能改进，但不会是1-2秒.

最好的选择是在屏幕上放置一个进度条或其他内容，以告知用户正在进行某些处理，并且他们需要等待直到完成为止.

I don''t see a possible way to upload 60000 records to a database in one second.
If you use SQLCommand instead of an OleDB command (since you are using Sql Server), you might get a minor performance improvement, but its not going to be 1-2 second.

The best bet would be to put a progress bar or something on the screen that tells the user some processing is going on and that they need to wait until it completes.

这篇关于将Excel文件上传到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！