Upload a excel file into database

Question

0.00/5 (No votes)

See more:

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

C#

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);
}

Thanks & Regards

srinivas

Posted 29-Jul-11 19:24pm

srinualluri

Updated 29-Jul-11 23:47pm

walterhevedeich

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2011-07-29T19:50:00

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.

Member 2839578 · Answer 2 · 2011-07-29T23:41:00

Solution 2

i think SQLSErver 2000 provide file import fecility. U can import excel file into SQLSERVER 2000.right click database & u can see import data option.

Posted 29-Jul-11 23:41pm

Member 2839578

Comments

bhargavpp 30-Jul-11 7:57am

as per my knowlege its not possible because there maybe existing record so duplicate record we need to remove so its time consuming its not possible in one second