I've data within an SQL Server 2005 database which I have to copy for an Access 2007 database. It's a database conversion tool. Basically each table matches a table of the different title and within each table each column must be planned to some corresponding column within the Access 2007 database.

Just wondering what's the simplest method to acheive this in C#. I must write as couple of SQL claims as you possibly can.

A Few Things I have thought about:

  • "Place INTO ... VALUES ..." SqlCommands in loops with parameters
  • Filling a DataTable for every table in every database and adding NewRows towards the Access table
  • Using DataTableMappings

Basically I'd much like to have the ability to specify the mappings of source table/posts to destination table/posts and also have it carry out the conversion instantly.

Things I am searching for may be the easiest way to do this, and when there's a category that performs this already.

Thanks.

The simplest method of doing this in C# is to avoid it in C#. Obviously you can, but that's like driving inside a nail having a screwdriver. Try SQL Server Integration Services (SSIS). It is built to do this kind of data extraction, translation and loading. If you have to do it in C#, use C# to spend to your SSIS package.