[RESOLVED] Datatable to MySQL Database

[otniel]

Hello.

I'll be very concise.

I have a problem exporting a datatable to a MySQL Database.
The amount of data can be very large (anyway this does not matter).

How can I save the records directly, witout using a for...next loop or a while.... loop?

I've tried a lot of methods with the MySqlDataAdapter

the name of the datatable is: newtable
it already contains data

the connection to mysql is ok and active: conn
the table structure is already created in the database: tmptbl
but it has no records

.......
If Not conn.State = ConnectionState.Open Then conn.Open()

Dim dadept1 As New MySqlDataAdapter("SELECT * FROM `" & tmptbl & "`", conn)
Dim cb As New MySqlCommandBuilder(dadept1)
dadept1.Update(newtable)
......

this code obviously, doesn't work

Can anybody help me with this ?

Thank you very much.

[stanav]

Manually create an insert command. Now you have 2 options, depending on the rowstate of the datarows in your datatable that you pick the appropriate method.
a) The rowstate of every datarow in the datatable = "RowState.Added". If this is the case, you can use a data adapter and set its InsertCommand property to the insert command created above, and call its update method.
b) Not all the datarow has the rowstate = rowstate.added. This case, you have to loop thru the datatable and execute the command manually.
I know you just want an easy, simple solution but sometimes (or lots of times) it's just not going the way that we want.

[otniel]

I've tried the Insert Command method, but i still need a loop, because i don't know how to input the parameters for the insert values

is that something like this:

....
dadept1.InsertCommand = New MySqlCommand("INSERT INTO `" & tmptbl & "` (field1,field2,...) VALUES (@val1,@val2,...)", conn)

dadept1.InsertCommand.Parameters.Add("@val1", OleDbType.VarChar, 50, newtable.Columns("field1").ToString) ?
....

but it should get only the first value or maybe just one value, not all the values in the "field1"

The datatable newtable is extracted from an excel file that can contain up to 50000 records or so and the for..next loop i've tried works fine, but it takes about a half a day to complete !!!
I don't think the rowstate of the records is rowstate.added !
Should I make a copy or a clone like this:

dim dt1 as datatable
dt1=newtable.copy or
dt1=newtable.clone ? How does copy and clone work ?


Though, with existing data in MySQL, an update works just fine, like this:

(dg1 is a datagidview with records from an existing table in the database)

...
dadept1 = New MySqlDataAdapter("SELECT * FROM `" & tmptbl & "`;", conn)
Dim cb As New MySqlCommandBuilder(dadept1)
Dim dt1 As DataTable

Try
dt1 = CType(dg1.DataSource, DataTable)
dadept1.Update(dt1)
newtable.AcceptChanges()
dg1.Refresh()
Catch ex As Exception
...
End Try
...


The only problem is to get the datatable into the database.

Thanks you very much.

[stanav]

To create a new, empty datatable with the same schema as the old table, you use the datatable.clone method

Dim newTable As DataTable = oldTable.Clone()

Now that you have a new empty datatable, you add rows to its. The data come directly from the old table

Dim row As DataRow = Nothing
For Each oldRow As DataRow In oldTable.Rows
     'Create a new data row of the new table
     row = newTable.NewRow()
     'Loop thru the current old row columns and copy the data to the new row
     'Note that although this loop will simplify your code, but it'll be slower than if 
     'you just assign the items explicitly like this (provided that you know how many
     'columns there are in the old table):
     'row(0) = oldRow(0)
     'row(1) = oldRow(1)
     '
     'row(n) = oldRow(n) where n is the number of columns less 1 in the old table
     
     For Each col As DataColum In oldTable.Columns
          row(col.ColumnName) = oldRow(col.ColumnName)
     Next
     'Add this new row to new table
     newTable.Rows.Add(row)
Next

After this loop finishes, your newTable will contains th same data as the old table. You just need to use a data adapter, set its insert command and call its Update method.

[otniel]

Hello again !

I managed to find a solution for my problem before your last answer , and I guess this is the best I can find, and it is without an insertcommand :


.....
If Not conn.State = ConnectionState.Open Then conn.Open()
dim dadept1 As New MySqlDataAdapter("SELECT * FROM `" & tmptbl & "`", conn)
Dim cb As New MySqlCommandBuilder(dadept1)

Try
For Each rw As DataRow In newtable.Rows
rw.SetAdded()
Next

dadept1.Update(newtable)

Catch ex As Exception
...
End Try
.......


As you (stanav) mentioned earlier (and thank you for your idea), the row state wasn't RowState.Added for neither of the records in the newtable

the "for each...next" loop works fine for 50000 records.

The next thing is the adapter update method. The commandbuilder builds an insert for each record in the datatable, and now, it takes just about 20 minutes for 50000 records to be added in the database.

As I mentioned previously, the table structure is already created and a primary key declared is mandatory !

My next problem is how can i put a progress bar to show the progress of commandbuilder inserting those records in the database ?

Thank you very much.

[stanav]

You need to read inforamtion on the DataRow.SetAdded method in MSDN. The SetAdded will throw an exception if te row being set has a rowstate <> rowstate.unchanged or rowstate.added. In short, if any of the rows in your new table has a rowsate = modified or delete, your program will crash.
For you progress bar, if you use dataadapter.Update method then all you can do is to set its style to marquee before calling update method, then set style back to blocks after update method. You cannot know the exact progress percentage using the update method. If you're to use an inser command, and execute the command in a loop then you can tell how many rows (or calculate the %) to set your progressbar.value property.

[otniel]

it most definetly is row.unchanged beacuse i have filled the datatable from an excel sheet in a previous procedure and the newtable is a public datatable

here's a part of that procedure:

...
Dim ds As New DataSet()
Dim connstr As String
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FName & ";Extended Properties=Excel 8.0;"
Dim objConn As New OleDbConnection(connstr)
Dim objadapter As New OleDbDataAdapter("SELECT * FROM [" & excelsheet & "$]", objConn)
objConn.Open()

Application.DoEvents()

ds.Clear()
objadapter.Fill(ds, excelsheet)
objConn.Close()

newtable = ds.Tables(0)
...


Maybe there is another solution for a progress bar...

Thank you very much.