CODEWITHSUNDEEP
c#sql-serverwinforms
Muhamad Jafarnejad
Muhamad Jafarnejad

Reputation: 2571

add new row to dataset.tables[i] and update database

I have two sql servers(one of them is in my web server: server A, another one is local: server B)

I use the code bellow to copy data on a table in SQL SERVER A to another table in SQL SERVER B : (different servers & different databases.. the two tables are same in columns)

ON for server A

OFF for server B

        string strsql = "SELECT * FROM **serverA_table**";
        ONda = new SqlDataAdapter(strsql, ONcon);
        ONds = new DataSet();
        ONda.Fill(ONds, "serverA_table");


        strsql = "SELECT * FROM serverB_table";
        OFFda = new SqlDataAdapter(strsql, OFFcon);
        OFFds = new DataSet();
        OFFda.Fill(OFFds, "serverB_table");

        DataRow[] newRow = ONds.Tables["serverA_table"].Select();

        DataTable dtTarget = new DataTable();
        dtTarget = OFFds.Tables["serverB_table"].Clone();

        foreach (DataRow temp in newRow)
        {
            dtTarget.ImportRow(temp);
        }



        SqlCommandBuilder cb = new SqlCommandBuilder(OFFda);
        OFFda.InsertCommand = cb.GetInsertCommand();
        OFFda.UpdateCommand = cb.GetUpdateCommand();
        OFFda.DeleteCommand = cb.GetDeleteCommand();
        OFFda.Update(OFFds.Tables["serverB_table"]);

But NOTHING happens and no row is added(imported) to my serverB_table !

please help me with this code or let me know a new way to do this action !

thanks in advance.

Upvotes: 0

Views: 1719

Answers (1)

Alex Filipovici
Alex Filipovici

Reputation: 32561

You have to use DataRow.SetAdded before importing the row. Try the following:

string strsql = "SELECT * FROM ServerATable";

using (ONcon = new SqlConnection(conString))
using (ONda = new SqlDataAdapter(strsql, ONcon))
{
    ONcon.Open();
    ONds = new DataSet();
    ONda.Fill(ONds, "serverA_table");
}

strsql = "SELECT * FROM ServerBTable";

using (OFFcon = new SqlConnection(conString))
using (OFFda = new SqlDataAdapter(strsql, OFFcon))
{
    OFFcon.Open();
    OFFds = new DataSet();
    OFFda.Fill(OFFds, "serverB_table");

    DataRow[] newRow = ONds.Tables["serverA_table"].Select();

    foreach (DataRow temp in newRow)
    {
        temp.SetAdded();
        OFFds.Tables["serverB_table"].ImportRow(temp);
    }

    SqlCommandBuilder cb = new SqlCommandBuilder(OFFda);
    OFFda.InsertCommand = cb.GetInsertCommand();
    OFFda.UpdateCommand = cb.GetUpdateCommand();
    OFFda.DeleteCommand = cb.GetDeleteCommand();
    OFFda.Update(OFFds.Tables["serverB_table"]);
}

Upvotes: 1

Related Questions