Bulk insert

MEHA SHANMUGASUNDARA

Hi all,
I have millions of records import from excel to grid & insert records to DB table using DataAdapter
How can I?
Plz. reply to me.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel16y ago
Accepted answer
Hi Meha

Here is your solution

if my answer helps you then please check "Do you like this answer" check box :)

thanks :)

you can Import Xcel data

by Executing following Query on Database

following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport2
```
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,'SELECT * FROM [Customers$]')

if want more info then just visit 

http://support.microsoft.com/kb/321686
```
rohit kakria16y ago
Check this article. It is showing with example

http://www.eggheadcafe.com/community/aspnet/7/10039757/bulk-insert-in-aspnet-20.aspx

Kind regards,
Kirtan Patel16y ago
Meha,

you have Shown Records in DataGridView then Follow the below Step to get Desired Output

Iterate trough each row of DataGridView and Execute Insert Commnad in it .

let me show you example Suppose we have two columns username and password in database to insert
---


SqlConnection con = new SqlConnection("Your Connection String");

con.Open();

   foreach(DataGridViewRow r in dataGridView1.Rows)

{

       //Get the Row Cell Values and Insert into Database

        string username = r.Cells[1].Value.ToString();

        string password = r.Cells[2].Value.ToString();

    // Now insert in into database

       SqlCommand comm = new SqlCommand("insert into Users values("@username,@password",con);

    comm.Parameters.AddWithValue("@username",username);

    comm.Parameters.AddWithValue("@username",password);

     comm.ExecuteNonQuery();

}

//Finally Close the Connection

con.Close();
MEHA SHANMUGASUNDARA16y ago
Hi kirtan,
Thank you very much for Your reply.
But I'm getting excel records to grid & Show then Edit after that i'll import the data into DB.
So, Bulk Record insert & Update. Using dataadapter & commandbuilder.
Plz. reply to me.