Copying data from one database to another - Part 1

Thu, 18/06/2009 - 10:34 — dgrinberg
The task was straightforward, or so I thought; create a small application that transfers an applications configuration information from the test database into the production database

The task was straightforward, or so I thought; create a small application that transfers an applications configuration information from the test database into the production database (before go-live!!). I chose to create a windows application using C# and ADO.NET, I thought that it would be so ridiculously simple that I didn't think I needed objects\frameworks.

The steps are simple and few:

  1. Open source database
  2. Retrieve source configuration
  3. Open destination database
  4. Clear current configuration
  5. Insert new configuration

 

Simple?!?!  I could retrieve the data (into a DataTable) and knew I could use the DataAdapter's 'Update' function to then insert the data into the destination database, but no matter what I tried I could not get it to work.  There were no errors, the DataTable was full of data, the 'Update' function was called but nothing was going into the database...

After a lot of wasted time searching the internet, and trying a lot of different things (trial and error), a colleague pointed me towards another blog written by WATYF <http://www.musicalnerdery.com/nerdery/copying-data-from-one-database-to-another-using-adonet.html>!  Finally someone trying to do exactly what I was trying to do.

As it turns out, none of the flags had been set as 'modified' or 'added', thus there was nothing to update.  Even though I had retrieved the data from one connection and was sending it to a completely different connection, it was still looking at that flag!

When retrieving the data, you need to set 'AcceptChangesDuringFill' flag to false.  Then all of the rows in the DataTable are flagged as new, and will be inserted.

  1. DataTable configResults = new DataTable('Config');
  2.  
  3. SqlConnection sourceConnection = new SqlConnection(_sourceConnection);  
  4. sourceConnection.Open();
  5.  
  6. SqlDataAdapter a = new SqlDataAdapter();
  7. a.SelectCommand = new SqlCommand('SELECT * FROM Config', sourceConnection);
  8. a.AcceptChangesDuringFill = false;    
  9. a.Fill(configResults);
  10. a.Dispose();
  11.  
  12. SqlConnection destConnection = new SqlConnection(_destinationConnection);
  13. destConnection.Open();
  14.  
  15. SqlDataAdapter profileAdapter = new SqlDataAdapter('SELECT * FROM Config', destConnection);
  16. SqlCommandBuilder builder = new SqlCommandBuilder(profileAdapter);
  17. profileAdapter.InsertCommand = builder.GetInsertCommand();
  18.  
  19. profileAdapter.Update(configResults);
  20.  
  21. profileAdapter.Dispose();  
  22. builder.Dispose();

Huge thanks to WATYF, for pointing me in the right direction.

Trackback URL for this post:

http://ontheperiphery.veraida.com/trackback/33
database empire return
from database empire on Sun, 29/11/2009 - 19:13
[...] GOP presidential aspirant Jack Kemp, for instance, warns that "no nation can long remain a world power when its most precious resource is a shrinking resource." Quick,ticket That arrest earlier this month sparked a diplomatic protest from Mexico,...