Import a .mbd file having 900000 recods in table in to sql server?

Question

0.00/5 (No votes)

See more:

Hi friends,

I am stuck in serious issue I have implemented a fileupload functionality where user can upload .mdb file having 900000 rows record. I want to Import that .mdb file into mssql server database table.
When I am import .mdb file with having 100 records in. mdb file it imported successfully into MSSQL server table, But when I am import .mdb file having 900000 records it throws
error:"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I have used following code to import .mdb file:

C#

OleDbConnection excelConnection = null;
        OleDbCommand cmd = null;
         string excelConnectionString =
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Rakeshkumar\Uploads\test.mdb;Persist Security Info='False'";
     excelConnection = new OleDbConnection(excelConnectionString);

          //    cmd = new OleDbCommand("Select * from [test]", excelConnection);
             // cmd.CommandTimeout = 0;
          //  excelConnection.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("Select * from [test]", excelConnection);
            DataTable ds = new DataTable();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            sqlBulk.WriteToServer(ds);
          excelConnection.Close();

Is this is a proper solution for import large Records(900000 rows) into MSsql server?If there any optimal solution pls tell me ASAP.

Posted 31-Aug-13 3:19am

nira.parmar

Add a Solution

1 solution

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)

Mehdi Gholam · Answer 1 · 2013-08-31T03:28:00

Solution 1

Do the import in blocks of say 2000 records at a time.

Posted 31-Aug-13 3:28am

Mehdi Gholam

Comments

nira.parmar 31-Aug-13 9:32am

But How could it possible? pls tell me

Mehdi Gholam 31-Aug-13 9:35am

If your test table has a primary key of say pid then you can do "select * from [test] where pid between 0 and 2000" for example.