need again help with DataTable Merge

[Frank Uray]

Hi all

I am still trying to get this merge done,
but I am completely lost ...

As you see in the code below, I have DataTableCurrent with
3 rows (IDs 1, 2, 3), then I have DataTableChanged with
IDs 1, 3, 4.
Now I would like to merge DataTableCurrent with the DataTableChanged,
ID 1 is still the same, ID 2 should be deleted, ID 3 Text is changed and
ID 4 is a new row.
In my code, just after the merge, DataTableCurrent looks not bad,
just ID 2 is still in there.
Because there was no other chance to get the ID 2 away, I tried
to make a loop and delete the row manually ... What is funny now
after this foreach, all the changes form the merge are gone ????????

Please, if somebody knows how this is gona work,
I would be happy for answers.

Best regards
Frank Uray

// Current Data
System.Data.DataTable DataTableCurrent = new
System.Data.DataTable("Data");
System.Data.DataColumn DataColumnCurrentID = new
System.Data.DataColumn("ID", typeof(System.Int32));
System.Data.DataColumn DataColumnCurrentTEXT = new
System.Data.DataColumn("TEXT", typeof(System.String));
DataTableCurrent.Columns.Add(DataColumnCurrentID);
DataTableCurrent.Columns.Add(DataColumnCurrentTEXT);
DataTableCurrent.PrimaryKey = new System.Data.DataColumn[] {
DataTableCurrent.Columns["ID"] };

System.Data.DataRow DataRowCurrent1 = DataTableCurrent.NewRow();
DataRowCurrent1["ID"] = 1;
DataRowCurrent1["TEXT"] = "Text1";
DataTableCurrent.Rows.Add(DataRowCurrent1);

System.Data.DataRow DataRowCurrent2 = DataTableCurrent.NewRow();
DataRowCurrent2["ID"] = 2;
DataRowCurrent2["TEXT"] = "Text2";
DataTableCurrent.Rows.Add(DataRowCurrent2);

System.Data.DataRow DataRowCurrent3 = DataTableCurrent.NewRow();
DataRowCurrent3["ID"] = 3;
DataRowCurrent3["TEXT"] = "Text3";
DataTableCurrent.Rows.Add(DataRowCurrent3);


// New changed Data
System.Data.DataTable DataTableChanged = new
System.Data.DataTable("Data");
System.Data.DataColumn DataColumnChangedID = new
System.Data.DataColumn("ID", typeof(System.Int32));
System.Data.DataColumn DataColumnChangedTEXT = new
System.Data.DataColumn("TEXT", typeof(System.String));
DataTableChanged.Columns.Add(DataColumnChangedID);
DataTableChanged.Columns.Add(DataColumnChangedTEXT);
DataTableChanged.PrimaryKey = new System.Data.DataColumn[] {
DataTableChanged.Columns["ID"] };

System.Data.DataRow DataRowChanged1 = DataTableChanged.NewRow();
DataRowChanged1["ID"] = 1;
DataRowChanged1["TEXT"] = "Text1";
DataTableChanged.Rows.Add(DataRowChanged1);

System.Data.DataRow DataRowChanged3 = DataTableChanged.NewRow();
DataRowChanged3["ID"] = 3;
DataRowChanged3["TEXT"] = "ChangedText3";
DataTableChanged.Rows.Add(DataRowChanged3);

System.Data.DataRow DataRowChanged4 = DataTableChanged.NewRow();
DataRowChanged4["ID"] = 4;
DataRowChanged4["TEXT"] = "NewText4";
DataTableChanged.Rows.Add(DataRowChanged4);


DataTableCurrent.Merge(DataTableChanged, false);
DataTableCurrent.AcceptChanges();

System.Data.DataTable local_DataTableTemp =
DataTableCurrent.Copy();
foreach (System.Data.DataRow local_DataRow in
local_DataTableTemp.Rows)
{
System.Data.DataView local_DataView = new
System.Data.DataView(DataTableChanged);
local_DataView.RowFilter = "ID = " +
local_DataRow["ID"].ToString() + " ";
if (local_DataView.Count == 0)
{ DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"]); }
}
DataTableCurrent.AcceptChanges();

 

[Family Tree Mike]

if (local_DataView.Count == 0)
{
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"]); }

RemoveAt(int) uses a row index which start at 0, but you are using ID's
which start at 1.

 

[Frank Uray]

Hi Mike

Thanks for your answer,
this works now in the example:
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"] - 1);

But what do you think, is this the right way to handle the deleted rows ??
Or is there a better solution for my issue ??

Thanks and best regards
Frank Uray

if (local_DataView.Count == 0)
{
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"]); }

RemoveAt(int) uses a row index which start at 0, but you are using ID's
which start at 1.

 

[Family Tree Mike]

Hi Mike

Thanks for your answer,
this works now in the example:
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"] - 1);

But what do you think, is this the right way to handle the deleted rows ??
Or is there a better solution for my issue ??

Thanks and best regards
Frank Uray

I would change a couple of things. Here is my version of the delete loop at
the bottom of your routine. You can check the existance in the second table
without creating a dataview. You can also track the rows to delete without
depending on ID - 1 being the row index. Reversing the list of rows is
important as you need to delete the rows "bottom-up".


// loop over the copy of the results finding rows that are not in the second
table
List<int> DelRows = new List<int>();
int rowid = 0;
foreach (System.Data.DataRow local_DataRow in local_DataTableTemp.Rows)
{
// just cound the rows in a select of the table, rather than creating a
dataview
if (DataTableChanged.Select("ID = " +
local_DataRow["ID"].ToString() + " ").Count() == 0)
{
// row is not in the second table, so remove it
DelRows.Add(rowid);
}
++rowid;
}

// remove the rows here.
DelRows.Reverse();
foreach (int id in DelRows)
{
DataTableCurrent.Rows.RemoveAt(id);
}

 

[Family Tree Mike]

Hi Mike

Thanks for your answer,
this works now in the example:
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"] - 1);

But what do you think, is this the right way to handle the deleted rows
??
Or is there a better solution for my issue ??

Thanks and best regards
Frank Uray

I would change a couple of things. Here is my version of the delete loop
at the bottom of your routine. You can check the existance in the second
table without creating a dataview. You can also track the rows to delete
without depending on ID - 1 being the row index. Reversing the list of
rows is important as you need to delete the rows "bottom-up".


// loop over the copy of the results finding rows that are not in the
second table
List<int> DelRows = new List<int>();
int rowid = 0;
foreach (System.Data.DataRow local_DataRow in local_DataTableTemp.Rows)
{
// just cound the rows in a select of the table, rather than creating a
dataview
if (DataTableChanged.Select("ID = " +
local_DataRow["ID"].ToString() + " ").Count() == 0)
{
// row is not in the second table, so remove it
DelRows.Add(rowid);
}
++rowid;
}

// remove the rows here.
DelRows.Reverse();
foreach (int id in DelRows)
{
DataTableCurrent.Rows.RemoveAt(id);
}

Actually, now that I look at this, I don't think you even need to copy the
table and work on the copy if you code the routine as above.

 

[Frank Uray]

Hi Mike

Thanks for your input.
I got one version to work, but I have to take a closer look at yours

Thanks and best regards
Frank Uray

System.Data.DataTable local_DataTableTemp =
DataTableCurrent.Copy();
int local_RowsCount = DataTableCurrent.Rows.Count;
for (int i = 0; i < local_RowsCount; i++)
{

System.Data.DataView local_DataView = new
System.Data.DataView(DataTableChanged);
local_DataView.RowFilter = "ID = " +
local_DataTableTemp.Rows["ID"].ToString() + " ";
if (local_DataView.Count == 0)
{ DataTableCurrent.Rows.Delete(); }

}
DataTableCurrent.AcceptChanges();

Hi Mike

Thanks for your answer,
this works now in the example:
DataTableCurrent.Rows.RemoveAt((int)local_DataRow["ID"] - 1);

But what do you think, is this the right way to handle the deleted rows
??
Or is there a better solution for my issue ??

Thanks and best regards
Frank Uray

I would change a couple of things. Here is my version of the delete loop
at the bottom of your routine. You can check the existance in the second
table without creating a dataview. You can also track the rows to delete
without depending on ID - 1 being the row index. Reversing the list of
rows is important as you need to delete the rows "bottom-up".


// loop over the copy of the results finding rows that are not in the
second table
List<int> DelRows = new List<int>();
int rowid = 0;
foreach (System.Data.DataRow local_DataRow in local_DataTableTemp.Rows)
{
// just cound the rows in a select of the table, rather than creating a
dataview
if (DataTableChanged.Select("ID = " +
local_DataRow["ID"].ToString() + " ").Count() == 0)
{
// row is not in the second table, so remove it
DelRows.Add(rowid);
}
++rowid;
}

// remove the rows here.
DelRows.Reverse();
foreach (int id in DelRows)
{
DataTableCurrent.Rows.RemoveAt(id);
}

Actually, now that I look at this, I don't think you even need to copy the
table and work on the copy if you code the routine as above.