I recently came upon a situation in which I needed to copy data from one database to another in ADO.NET.  I found a couple of helpful tips on the way, and thought that I’d share them.

The Task

I was copying production data from a table to its twin in a development database.  Many RDBMSes make this easy, but I was working with Microsoft Access in this instance.  While you can export tables from one database to another in Access, you can’t overwrite tables that are parts of relationships, so I couldn’t export directly to my development database.  I could have worked around this by exporting to an intermediate format and then importing, but I knew that I was going to be doing this task repeatedly and wanted to automate it.

The Approach

There are several ways that I could have solved this problem, but since I spend so much time working with ADO.NET, I decided to write a quick console application in .NET.  Doing so would allow me to add the console problem to a batch file that performed other processes in addition to copying the data.

I created a command object that connected to the source database and read the records.  Using a data adapter, I then filled an instance of DataSet with the information.  A second command with parameters and a connection to the destination database should have written the data to the target table, but didn’t.  I knew that the problem was that the DataSet didn’t think that any changes had been made to it, so it didn’t have anything to insert.  A quick search found “Using ADO.NET to move data from one table to another” by William Ryan, which contained the answer: set DataSet.AcceptChangesDuringFill to false.  This will cause all of the rowstates to be Added instead of Unchanged. When the Update method on the data adapter is called, it will write all of those records to the destination database.

An important point about Mr. Ryan’s article is that while he uses separate data adapters, you don’t have to.  Since the connection is a property of the command, not the data adapter, you don’t need multiple adapters.

The Solution

string connStringSource = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\Source.accdb;Persist Security Info=False;";
string connStringDest = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp\\Dest.accdb;Persist Security Info=False;";

OleDbConnection connSource = new OleDbConnection(connStringSource);
OleDbConnection connDest = new OleDbConnection(connStringDest);

OleDbCommand cmdSel = new OleDbCommand("SELECT ID, Val FROM Table1", connSource);
OleDbCommand cmdIns = new OleDbCommand("INSERT INTO Table1 (ID, Val) VALUES (@ID, @Val)", connDest);
// You must specify the source name for each parameter so that the data adapter can
// map it to the appropriate column in the dataset
cmdIns.Parameters.Add(new OleDbParameter("@ID", OleDbType.Integer, 4, "ID"));
cmdIns.Parameters.Add(new OleDbParameter("@Val", OleDbType.VarWChar, 50, "Val"));

OleDbDataAdapter da = new OleDbDataAdapter();
da.AcceptChangesDuringFill = false; // Important!
da.SelectCommand = cmdSel;
da.InsertCommand = cmdIns;

System.Data.DataSet ds = new System.Data.DataSet();

da.Fill(ds, "Table1");
da.Update(ds, "Table1");

Final Note

As Mr. Ryan pointed out in his article, this method should not be used for bulk copying of data between databases. My use case was fewer than 100 records per table with only five tables, so performance was not an issue. Large datasets, however, would take much longer to copy in this way than could be achieved with the bulk loaders that come with most RDBMSes.