OleDbDataAdapter.Update() to copy one table to another and update database

See more:

SQL-CE

C#

SQL-Server

Hi, I have a problem when using the OleDbAdapter.Update(table), what I'm trying to do is fill a table from a SQL server database and copy the contents of that table to a SqlServerCe (.sdf) table with the same schema first, I'll handle differnet schemas later but right now I'm testing it like this.

I've tried the update on the SQL Server by creating a new row on the table and calling the .Update Method and the changes are reflected on my database, but when trying to do the exact same thing on the .sdf, the application stops working, (vshost.exe stopped working, windows is searching for....) so I have no idea where the error is, I can't debug.

I can do inserts with the command.ExecuteNonQuery(), but I was thinking that the update method could improve performance since we're trying to migrate from DTS to CLR SPs and some resources are limited, so I'm looking for the performance alternatie here.

Here's my code:

<code>
 SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["PC"].ConnectionString);
            SqlCommand command2 = new SqlCommand("Select * from t_i_Association", conn2);
            DataTable table2 = new DataTable();
            conn2.Open();
            SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
            adapter2.Fill(table2);
            
            OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Movil"].ConnectionString);
            OleDbCommand command = new OleDbCommand("Select * from t_i_Association", conn);
            DataTable table = new DataTable();
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(table);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
            
            table.Merge(table2, true);

            //foreach (DataRow r in table.Rows)
            //{
            //    r.SetAdded();
            //}

            int rows = adapter.Update(table);
            conn.Close();
            conn2.Close();
</code>

The overall idea is to make a select from both tables, one contains data, the other is created but has no data, so I fill both tables, then I merge the empty table with the data and try to update the .sdf database.
If I leave the comment on the r.setAdded() the Update throws no error but returns 0 rows updated. If I call the SetAdded() on each row, the application stops working and it does the same when adding a new row by code to the table and updating the changes. I tried the table.copy, adding a new row from the table with data, almost everything I could think of and I have no idea what's the problem :/

Should I stick to the update method or is making an insert for each row better? what do you think?

thanks in advance!