Hi there,

In absense of great guides on the web on this subject I’ll be going through the process of a MySQL batch update using VB.Net. To get started, first you’ll need a MySQL server, MySQL/Net Driver and Visual Basic/Studio then up and running. First things first though, I don’t rate VB.Net compared to Java – but some situations dictate it.

So, we’ll be calling this inside a function:

Setup

Try
Using MySQLConn = New MySqlConnection(“Server=example.com;Database=dbname;user id=username;Pwd=password;”)
Dim adapter As New MySqlDataAdapter(“SELECT * FROM table_name”, MySQLConn)

Then we need to put this data into a dataSet, as follows:
Dim ds As DataSet = New DataSet
adapter.Fill(ds, “table_name”)
Dim dt As DataTable = ds.Tables(“table_name”)

This allows us to interact with the data from this source – it does seem somewhat wasteful but I think the benefits are reaped further on, with smarter write operations than your standard Java preparedStatement script.

Testing DataSet
If, like – you want to know this is working so far – throw this script in now to see the data presently inside your DataSet.
Dim maxrows = ds.Tables(“table_name”).Rows.Count
Dim inc = -1

If inc <> maxrows – 1 Then
inc = inc + 1
Debug.Print(ds.Tables(“table_name”).Rows(inc).Item(“column_name”).ToString)
End If

You might have to keep the console open to read your Debug Log, I use a cheeky:
Console.Write()

Appears I’m running out of time, so here’s the rest of the script for now to get inserts working:

Manipulate – To be completed soon, this assumes you have a DataTable of data to merge in called args

See: http://msdn.microsoft.com/en-us/library/5ycd1034(VS.80).aspx for a stronger typed version of this section, for users not trying to add an array into the dataset.

Dim sb As New MysqlCommandBuilder(adapter)
Dim drargs As DataRow
For Each drargs In args.Rows
Dim dr As DataRow
dr = dt.NewRow
dr(“col0″) = drargs(“newcol0″)
dr(“col1″) = drargs(“newcol1″)
dt.Rows.Add(dr)
Next

Update
adapter.UpdateBatchSize = 100
Try
adapter.Update(ds, “table_name”)
ds.AcceptChanges()
Catch a As Exception
Console.WriteLine(“fail ins”)
End Try

I’ll try to remember to post up an update, but at least this should be enough to get you started. Note that with this method you manipulate the DataSet directly.. but that would explain why massive sections of most guides are missing saying just ‘maniplate the dataset here

Here’s to hoping I can go back to my Linux Box soon ;(

If anyone has a leaner method, please comment on here – I’ll switch things around if I verify it as working, and hopefully we can help more people out there – there’s not enough resources out there on this.