Transfer data of a table from one database, to the same table of another database, using C# dataAdapter.Fill() and dataAdapter.Update()

Question

I need to transfer the table's content to the same table located in another database, and I write this simple code using the C# dataAdapter.Fill() and dataAdapter.Update(), but it's seems not working like I supposed.

SqlConnection sqlConnection = new SqlConnection(strSqlConnectionString);
SqlConnection sqlConnection2 = new SqlConnection(strSqlConnectionString2);

sqlConnection.Open();
sqlConnection2.Open();
DataSet CustomerDataSet = new DataSet();

SqlDataAdapter sqlDA;
SqlDataAdapter sql2DA;

SqlCommandBuilder sqlCmdBuilder;
SqlCommandBuilder sqlCmdBuilder2;

sqlDA = new SqlDataAdapter("SELECT * FROM Articolo;", sqlConnection);
sqlDA2 = new SqlDataAdapter("SELECT * FROM Articolo;", sqlConnection2);

sqlCmdBuilder = new SqlCommandBuilder(sqlDA);
sqlCmdBuilder2 = new SqlCommandBuilder(sqlDA2);

sqlDA.Fill(CustomerDataSet, "Articolo");       

sqlDA2.Fill(CustomerDataSet, "Articolo");

sqlDA2.Update(CustomerDataSet, "Articolo");`

What I want to do is to have the second db(string connection: strSqlConnectionString2) with updated data, taken from the first db, exploiting the functionality of dataAdapter.Fill() + dataAdapter.Update(). Is this possible? And can I do the same things but with Access db as a second db?

Why not do it only using the database, like in a stored procedure? Example here: http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server — Zath, Dec 16 '16 at 15:39
Because I want to update the table rows of the second db when I need, so if I have the same row in both the tables, I ignore it, if the row doesn't exists, I add it to the second table, and then if the row already exists but with differences, I update it, and I thought that with Update I could do it... — Saverio, Dec 16 '16 at 16:01

score 0 · Answer 1 · answered Jan 20 '17 at 18:40

Can you try it this way?

using System;
using System.Data;
using System.Data.OleDb; 
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        string connetionString;
        OleDbConnection connection;
        OleDbDataAdapter oledbAdapter;
        OleDbCommandBuilder oledbCmdBuilder;
        DataSet ds = new DataSet();
        DataSet changes;
        int i;
        string Sql;


        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;";
            connection = new OleDbConnection(connetionString);
            Sql = "select * from tblUsers";
            try
            {
                connection.Open();
                oledbAdapter = new OleDbDataAdapter(Sql, connection);
                oledbAdapter.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                oledbCmdBuilder = new OleDbCommandBuilder(oledbAdapter);
                changes = ds.GetChanges();
                if (changes != null)
                {
                    oledbAdapter.Update(ds.Tables[0]);
                }
                ds.AcceptChanges();
                MessageBox.Show("Save changes");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

Transfer data of a table from one database, to the same table of another database, using C# dataAdapter.Fill() and dataAdapter.Update()

1 Answers1