What is the fastest way to insert values from datagridview to mysql database

I can't tell how much faster this will be, however there are simple optimizations to your query

Dim queryInsert As String = "INSERT INTO tbl_shipdetails (ship_date, " & _ "item_type, item_code, imei1, imei2)" & _ "VALUES(@p1,'@p2,@p3,@p4,@p5)" Dim cmd = New MySqlCommand(queryInsert, Myconnect) cmd.Parameters.Add("@p1", MySqlDbType.VarChar) cmd.Parameters.Add("@p2", MySqlDbType.VarChar) cmd.Parameters.Add("@p3", MySqlDbType.VarChar) cmd.Parameters.Add("@p4", MySqlDbType.VarChar) cmd.Parameters.Add("@p5", MySqlDbType.VarChar) For i As Integer = 0 To DataGridView1.Rows.Count - 1 cmd.Parameters("@p1").Value = DataGridView1.Rows(i).Cells(1).Value cmd.Parameters("@p2").Value = DataGridView1.Rows(i).Cells(2).Value cmd.Parameters("@p3").Value = DataGridView1.Rows(i).Cells(3).Value cmd.Parameters("@p4").Value = DataGridView1.Rows(i).Cells(4).Value cmd.Parameters("@p5").Value = DataGridView1.Rows(i).Cells(5).Value cmd.ExecuteNonQuery() Next 

Using parameters allows you to build the MySqlCommand just one time outside the loop and avoids also the work needed to concatenate the strings. (Not to mention the problem of Sql Injection)

Notice that I have followed your hint in the sql text where all of your fields seems to be of string(VarChar) type. If your fields are of different datatype then you should adjust the MySqlDbType enum to your correct datatype (and convert the input values=

Note: Steve's answer shows the correct way to create SQL queries rather than concatenating SQL with bits of string. Its not wrong.

However, if I wanted to insert a bunch of rows really quickly, I would not want to have to reset the parameter values nor fish out the values from the DGV. Nor would I want to spend the time putting the data into the DGV.

Its not clear where the data comes from. Given the size, it sounds like an import operation, which may afford other options. But even with the original approach, I think something else is amiss because 8 mins is far too long.

Insert 10,000 new rows in under 15 secs

My table has a 1 or 2 fewer columns ({Id, text(15), Int, Text(30), bool}) but that should not result in a 40 fold increase. This method uses several of the provider tools:

' class level variables Private dtDemo As DataTable Private cbDemo As MySqlCommandBuilder Private daDemo As MySqlDataAdapter 

Initializing them:

Dim SQL = <sql> SELECT Id, ItemName, ItemValue, Descr, Active, LastUpdated FROM simple WHERE 1 = 0 </sql>.Value Using dbcon = MySQLDB.GetMySQLConnection dbcon.Open() daDemo = New MySqlDataAdapter(SQL, dbcon) cbDemo = New MySqlCommandBuilder(daDemo) cbDemo.QuotePrefix = "`" cbDemo.QuoteSuffix = "`" daDemo.InsertCommand = cbDemo.GetInsertCommand() dtDemo = New DataTable daDemo.Fill(dtDemo) End Using dgv1.DataSource = dtDemo dgv1.Columns.RemoveAt(0) ' remove ID from display dgv1.Columns.RemoveAt(dgv1.Columns.Count - 1) ' remove LastUpdated 

The WHERE clause creates a table with no rows, just the meta data needed for CommandBuilder to build the various commands. It does/will show the contents in a DGV when populated.

Note that the columns removed are just removed from the display, not the datatable. The DB will set the Id and the LastUpdated column has a trigger on it (left over from another answer), so such columns dont need to be shown.

Get the data

For this, I imported some fake data I generated. Add the data to the DataTable, not the DataGridView:

Dim csvFile As String = "C:\Temp\mysqlbatch.csv" Dim lines = File.ReadAllLines(csvFile) Dim dr As DataRow For Each s As String In lines ' not the best way to parse a csv Dim data = s.Split(","c) dr = dtDemo.NewRow ' create new row with all the Cols dr("ItemName") = Data(0) dr("ItemValue") = Convert.ToInt32(Data(1)) dr("Descr") = Data(2) dr("Active") = If(Convert.ToInt32(data(3)) = 0, False, True) dtDemo.Rows.Add(dr) Next 

When this completes (a few seconds), the data will show in the DGV. I guess the user edits or fixes the data maybe?

Insert the Data

The DataAdapter knows how to insert (from the setup), the DataTable has the data already and it knows which rows are new (all of them). So let them sort it out:

Dim sw As New Stopwatch sw.Start() Dim rows As Int32 Using dbcon = MySQLDB.GetMySQLConnection dbcon.Open() daDemo.InsertCommand.Connection = dbcon rows = daDemo.Update(dtDemo) End Using sw.Stop() Console.WriteLine("Inserted {0} rows in {1}ms", rows, sw.ElapsedMilliseconds) 

Inserted 10000 rows in 10464ms
Inserted 10000 rows in 9419ms

Note: Explicitly creating a connection to update/insert may not be needed. Mine works just fine without it.

Apparently, the MySqlDataAdapter holds onto the connection string or connection object from when it was created for later use. Not all DataAdapters do; OleDB requires you create, open and set the connection object as shown.

MySQL allows insert multiple set-of-rows in single insert command. Create a string in following formation:

INSERT INTO yourTable(c1,c2,c3) VALUES (r1v1,r1v2,r1v3), (r2v1,r2v2,r2v3), (r3v1,r3v2,r3v3), . . . (rNv1,rNv2,rNv3) 

This command inserts multiple rows.