CODEWITHSUNDEEP
c#datagridviewdatatabledataset
TAB
TAB

Reputation: 187

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(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 + "')");

Upvotes: 0

Views: 1758

Answers (3)

TAB
TAB

Reputation: 187

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

Upvotes: 0

Yevhen
Yevhen

Reputation: 1965

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

Upvotes: 1

Fiona - myaccessible.website
Fiona - myaccessible.website

Reputation: 14921

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.

Upvotes: 0

Related Questions