I have a vb.net system and I want to insert 10,000 or more records from datagridview to mysql database. But it takes 8mins for 10,000 records when i tried this
For i As Integer = 0 To DataGridView1.Rows.Count - 1
Dim queryInsert As String = "INSERT INTO tbl_shipdetails (ship_date, item_type, item_code, imei1, imei2)" & _
"VALUES('" & DataGridView1.Rows(i).Cells(1).Value & "','" & DataGridView1.Rows(i).Cells(2).Value & "','" & DataGridView1.Rows(i).Cells(3).Value & "','" & DataGridView1.Rows(i).Cells(4).Value & "','" & DataGridView1.Rows(i).Cells(5).Value & "')"
MySqlCmd = New MySqlCommand
MySqlCmd.Connection = Myconnect
MySqlCmd.CommandText = queryInsert
MySqlCmd.ExecuteNonQuery()
Next
I want to know if someone knows what is the fastest way to do this? can anyone please help me. I'm thinking of inserting the whole values in datagridview to mysql with just one insertion (like as bulk) and not using a loop but I don't know if it's possible.
thanks in advance!
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.
