Copying Tables in ADO.NET

[samsmithnz]

I'd like some advice about the best way of copying a selection of tables or the entire database contents from one database to another using ADO.NET.

 

I have two databases, one is full, the other is empty. They are identical in structure.

 

thanks

[wyrd]

You could juse Clone() the DataSet/DataTable. Or is that not exactly what you're looking for?

[Nerseus]

If you want an entire database copied, I'd use the built in tools of the database (Access and SQL Server both have tools to do this - Access is called file copy :))

 

If you really MUST do it in code, you *could* loop through every table and get all rows in a DataSet then use a DataAdapter to do INSERT them all into the second database. That would work if the database were small. For larger databases, you'd probably need a cursor to loop through all the rows in one Database and copy them. This also requires copying tables in the right order, if you have any foreign keys. Also, you'll need custom code per table to keep the foreign keys in sync. OR, turn off identity columns as you do your inserts.

 

If you also need to copy the structure, then... well, you'd better ask :)

 

-nerseus

[samsmithnz]

Yeh I have the structure, I build it up with ADOx. Sounds like a clolne is the best idea then.

 

thanks.

[dseydel]

I had the same problem and Robby put me onto this web site that has a lot of useful SQL syntax:

 

Here are few examples . . http://www.w3schools.com/sql/sql_select_into.asp

 

 

The specific syntax that I found that works is:

 

cmd.Connection = ConnectioinName

 

cmd.CommandText = "SELECT * INTO " & {TableName} & " IN '" & (path.database.mdb) & " ' FROM " & {TableName}.

 

On Error GoTo ErrExit

Ival = cmd.ExecuteNonQuery()

If Ival <> 0 Then GoTo errexit

 

This copies a table in open connection to another database.

The SQL samples also give syntax for copying within the same

database.

 

Really slick & it greatly beats the ADOX methods. I am forever indebted to Robby for this scheme.