DataSet Rebinding to Create New Tables

[Martin]

Big Hello to Extreme.NET forums - new member here :),

 

I am trying to perform the following operations using a MySQL database:

 

I have an access database, I want to load the data from access into a MySQL database by loading the data into a DataSet, then rebinding the DataSet to the ODBC connection instead of the OLE conenction, and then update the ODBC database. The problem is, that the table doesn't already exist.

 

So:

 

Access -> DataSet -> MySQL

 

Will this work? What changes will I need to make so that the DataSet will create the new tables in the MySQL database automatically?

 

Thanks in advance,

Martin

[Nerseus]

There is NO way for ADO.NET to automatically create tables from a DataSet.

 

I know that for Access and SQL Server you can issue commands such as "CREATE TABLE Table1 (Col1 int, Col2 varchar(5))" and such (assuming you have the permissions). I have no idea if that will work in MySql. Assuming MySql is ANSI standard, it will probably work though I don't know the datatypes for the columns you want to create.

 

With custom code you could automate the building of the "CREATE TABLE ..." code by using the DataSet to see the types and sizes of each column, including column names, nullability and such. Again, this code is yours to write :)

 

Once the table exists, you can try a couple of ways to get the data into the new table. You can try the DataAdapter's update method (make sure this is a new DataAdapter pointing to the MySql database). Since you probably filled the DataSet using a DataAdapter, all rows will be marked as Original, no changes. So calling Update directly won't work. You'll have to someone convince the DataSet that the rows need to be added. I can think of a couple of ways, but I'm too lazy and tired to type them just now :)

 

You can also loop through the rows in the DataSet and insert them manually into the MySql database issuing commands like "INSERT INTO Table1...". Or, if this is a helper program only, maybe you can write out the values to a comma separated file and use some kind of batch import program to blast in the data in one shot (like SQL Server's bcp utility).

 

-Nerseus

[Martin]

Nerseus - thanks for the help, I think I'm going to have to do something along those lines:

 

1.) Build CREATE statement using DataSet

2.) Either convince it that is had an update OR just add it row by row.

 

Thanks very much for the response, maybe Microsoft should add this to the next version of .NET framework ;P