Bulk insert

Question

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.

Kirtan Patel · 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 kakria · Answer

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 Patel · Answer

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 SHANMUGASUNDARA · Answer

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.