How to swap primary key values in a DataTable?

[Radoslav Bielik]

Hello, Is it possible to easily swap values of primary key column between two rows of one DataTable object? I've been trying it like this; DataRow dr1=dt.Rows.Find(5); //dt being a DataTable instance DataRow dr2=dt.Rows.Find(10); dr1.BeginEdit(); dr2.BeginEdit(); dr1["item_Id"]=10; dr2["item_Id"]=5; dr1.EndEdit(); //this will throw an exception,                //because dr2's PK still has a value of 10 dr2.EndEdit(); Same thing happens with dt.AcceptChanges(); Is there a way how to achieve this? Any clues are highly appreciated! Rado

[Tom Archer]

DataRow dr1=dt.Rows.Find(5); //dt being a DataTable instance
DataRow dr2=dt.Rows.Find(10);

dr1.BeginEdit();
dr1["item_Id"]=-1; // or any unused value
dr1.EndEdit();

dr2.BeginEdit();
dr2["item_Id"]=5;
dr2.EndEdit();

dr1.BeginEdit();
dr1["item_Id"]=10;
dr2.EndEdit();

dt.AcceptChanges();

Cheers, Tom Archer Inside C#,
Extending MFC Applications with the .NET Framework It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson

[Radoslav Bielik]

Oh thanks a lot! How come this didn't come to my mind :) Rado

[Tom Archer]

Because it's common sometimes to overlook the easy stuff in search of the more complex solution. We all do it ;) Cheers, Tom Archer Inside C#,
Extending MFC Applications with the .NET Framework It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson

[StealthyMark]

Before editing your DataRows, set DataSet.EnforceConstraints to false.

try
{
dt.DataSet.EnforceConstraints = false;
// Edit your Rows
// EndEdit
}
finally
{
// If you violated any constraints, like PKs,
// this statement will throw an exception.
dt.DataSet.EnforceConstraints = true;
}

[Radoslav Bielik]

Thanks for the tip! Rado

[Tom Archer]

(Repeated from our private email so that others searching for this answer will find it.) Hi Rado, Ever have one of those problem that just eats at you? Well your situation has bugged me for some time because I just knew there had to be an easier, more elegant way. There is...... The problem was that BeginEdit calls for the two DataRow objects were disabling the constraints for the respective rows, but once you called EndEdit for one DataRow, the primary key value for that first row conflicted with the (not yet committed) second DataRow object. Therefore, I had to assume that there was a higher level way of turning off constraints - either the DataRowCollection or the DataTable. Finally, I found the DataTable::BeginLoadData and DataTable::EndLoadData method, which turn off and on, respectively, constraint checking and index maintenance for the entire table. table.BeginLoadData(); // switch the primary keys for your two DataRow objects table.EndLoadData(); Note: You mentioned the fact that someone suggested turning constraints off for the entire DataSet. As this technicall will work, using BeginLoadData and EndLoadData will affect only one table instead of all the tables for a given DataSet. Cheers, Tom Archer Inside C#,
Extending MFC Applications with the .NET Framework It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson