CODEWITHSUNDEEP
c#asp.netsql-serverdatabaseado.net
Billy
Billy

Reputation: 183

Copy data from one OleDb database to another in Windows Forms App

i'm trying to create a data transfer utility, that transfers data from table of one OleDb database to table of another OleDb database. (could be SQL server or MySql or Access Database).

So far i have been successful in connecting to both databases using appropriate connection string. I have also read all the data from the source database into a OleDbDataReader. Below is the code for that.

 OleDbDataReader reader;
 string query = "SELECT * FROM " + stable.Text;   // text box with source table name

 cmd.CommandText = query;

 try
 {
    reader = cmd.ExecuteReader();
 }
 catch(Exception ex)
 {
    MessageBox.Show(ex, "Error");
    return;
 }

The part i'm stuck at is, how to write the data from the OleDbDataReader to the table in another database (assuming tables have similar columns). The source and data table will be entered by user during the run time.

I know for SqlConnection, we could use SqlBulkCopy for the purpose of copying everything without column mapping. But i could not find much information on how i could do that in OleDbConnection.

I'm fairly new to SQL and ado.net, so i'm really sorry if this is a stupidly simple question. I hope i have provided enough information about my issue, but if i have missed something or something doesn't make sense in my question, please do let me know. Any help will very much appreciated. Thanks.

Upvotes: 1

Views: 1101

Answers (1)

Tronald
Tronald

Reputation: 1585

Iterate through each row with your reader and copy the values. You will have to specify the column names as I don't know what they are, or how many exist on your table. This should lead you in the right direction.

 //Connection to the database to be inserted
 OleDbCommand insertCmd = new OleDbCommand("Your connection");

 //Read each row and execute new query. The Reader was established previously in your code.
 while (reader.Read())
 {
       //Create your query. Each item in the first parenthesis is the column name in your table. 
       //Each item in the second set of parenthesis is a parameter to be specified later.  
       insertCmd.CommandText =  "INSERT INTO table_name (column1Name, column2Name, column3Name, ...) VALUES(@column1Name, @column2Name, @column2Name, ...)";

       //Add the parameter values. The values are pulled from the reader that's reading your "Read" database
       insertCmd.Parameters.AddWithValue("@column1Name", reader["column1Name"]);
       insertCmd.Parameters.AddWithValue("@column2Name", reader["column2Name"]);
       //...Other columns you may have

       //Execute Query
       insertCmd.ExecuteNonQuery();

  }

If the column names are different between tables, the reader (column#Name) will be the name of the desired column for the database being read. Let me know if you need further explanation of this example

Upvotes: 1

Related Questions