Howto merge DataTable without copies?

Question

Hello I Have two databases generated from the same SQL code and two datasets created from this databases. I want to create one dataset from this 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 resoult: http://img697.imageshack.us/img697/3274/aaaub.jpg App is in Polish but it shouldnt be a problem :P

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).

Im using this metod for filling dataset, adding new rows etc.

        public void SelectGatunek(string 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 + "')");

Answer 1

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.

Answer 2

Thx TGadfly i got it working with the link you provided :D these are the changes i made to the code

            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();
            }
        }