How to merge DataTable without copies?

I Have two databases generated from the same SQL code and two datasets created from this databases. I want to create one dataset from these two datasets, and i thought i could do this like this:

public void MergeGatunek()
{
    DB1.DataSetGatunek.Tables[0].Merge(DB2.DataSetGatunek.Tables[0], true);
    DataSetGatunek.Tables[0].Merge(DB1.DataSetGatunek.Tables[0], true);
    //DataSetGatunek is the final DataSet
}

and this is the result:

App is in Polish but it shouldnt be a problem.

Every next new row just multiplies the results of previous adds. My goal is to show data from two databases in one DataGridView, and to add missing rows(if there are any).

I'm using this method for filling dataset, adding new rows etc.

public void SelectGatunek(s开发者_开发技巧tring SQL)
{
    try
    {
        Connection.Open();
        DataAdapter = new SqlDataAdapter(SQL, Connection);
        commandBuilder = new SqlCommandBuilder(DataAdapter);
        DataSetGatunek.Clear();
        DataAdapter.Fill(DataSetGatunek);

        Connection.Close();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
}

SelectGatunek("Select * FROM t_gatunek");

SelectGatunek("INSERT INTO t_gatunek (gatunek) VALUES ('" + DG.GetGatunek + "')");

To avoid duplicating 'id_gatunek' field, create column in database as identity field.

http://support.microsoft.com/kb/313540

• Check and update each row manually instead of using the AcceptChanges method on the DataSet.
• Use the RowUpdated event of the DataAdapter object and skip the duplicate rows.

If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow

I think it is because you need to specify DataTable.PrimaryKey, so that they merge correctly.

This should be done for you if the field is a primary key in your DB.

Using TGadfly's solution i got this working:

public void FillGatunek()
{
    try
    {
        Connection.Open();
        GatunekDataAdapter = new SqlDataAdapter("SELECT * FROM t_gatunek", Connection);
        commandBuilder = new SqlCommandBuilder(GatunekDataAdapter);
        GatunekDataAdapter.FillSchema(DataSetGatunek, SchemaType.Mapped, "t_gatunek");
        DataSetGatunek.Tables["t_gatunek"].Columns["id_gatunek"].AutoIncrement = true;
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
    finally
    {
        Connection.Close();
    }
}

public void InsertGatunek(string Gatunek)
{
    try
    {
        Connection.Open();
        DataRow R = DataSetGatunek.Tables["t_gatunek"].NewRow();
        R["gatunek"] = Gatunek;
        DataSetGatunek.Tables["t_gatunek"].Rows.Add(R);
        DataSetGatunek.GetChanges();
        GatunekDataAdapter.Update(DataSetGatunek, "t_gatunek");
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
    finally
    {
        Connection.Close();
    }
}