Pimping RowState in ADO.NET 2.0
There are two new methods for the DataRow object in ADO.NET 2.0 that haven't gotten much coverage but can be quite useful. The two methods are SetAdded and SetModified . I consider myself to be an ADO.NET afficionado and until last week, I wasn't aware of these (yes, lame on my part). I was eating lunch with a few guys from the ADO.NET team and I mentioned to David Sceppa that it would be really cool if there was a way to manually set RowState. I've had the need to manually manipulate RowState in the past and each solution was pretty lame. Basically I'd go to the row, then change the value to itself. This was really awkward b/c well, it looks screwy. The thought of setting a variable to itself shouldn't sit well with anyone b/c with very limited exceptions, it's a totally useless operation. But when it comes to Rowstate, it was sometimes necessary. For the uninitiated, a DataAdapter object depends on Rowstate exclusively to determine what action(s) it's going to take during a call to Update. So let's say that you wanted to take the data from one table and move it to another one. The 'right' way to do this is using DTS or SqlBulkCopy (and yes, somehow I got ranked #1 on google for SqlBulkCopy ;-) ) but that's another story. Anyway, one of the other ways which was kind of a kluge albeit a pretty good one, was to set the AcceptChangesDuringFill property of your adapter to false. This would in effect fill your DataTable with rows that had a DataRowState of Added. You could then configure a second adapter, pointing to your destination table and simply call the Update method on it, passing in your just filled DataTable. Just as a note of caution - it's not typically advisable to use disconnected architecture to move data within the same database, after all you are using network bandwidth and resources that aren't necessary, but that's another issue.
Well, if you want to do manipulations like these, you were very limited in the past. That is no longer the case. With the new SetAdded and SetModified methods, you can change the DataRowState of a given row to either added or modified without having to resort to setting a value back to itself. Admittedly this is a really simple operation and doesn't need much explanation, but I figured if I missed the new features someone else might to.
private void button1_Click(object sender, EventArgs e)
{
//Create a DataSet
DataSet DemoDataSet = new DataSet("CuckoozDataSet");
//Create a DataTable
DataTable DemoTable = new DataTable("Cuckoo");
//Add the Table to the DataSet
DemoDataSet.Tables.Add(DemoTable);
//Add a new Column and Set its Autoincrement property to true
//just so that it has a value in it.
DataColumn KeyColumn = new DataColumn("MainColumn", typeof(System.Int32));
KeyColumn.AutoIncrement = true;
DemoTable.Columns.Add(KeyColumn);
//Add a row
DataRow dro = DemoTable.NewRow();
DemoTable.Rows.Add(dro);
//Add 9 more so that we have 10 rows total
for (Int32 x = 0; x < 9; x++)
{
dro = DemoTable.NewRow();
DemoTable.Rows.Add(dro);
}
//Verify the Row Count
Debug.Assert(DemoTable.Rows.Count == 10, "Row Count is screwy");
// Verify that there are changes present
Debug.Assert(DemoDataSet.HasChanges(), "No Changes are present");
//Call AcceptChanges to reset the Rowstate of each row
DemoTable.AcceptChanges();
//Verify that there are no changes present
Debug.Assert(!DemoDataSet.HasChanges(), "Changes are present but shouldn't be");
//Call the SetAdded method on first 3 rows
for (Int32 i = 0; i < 3; i++)
{
DemoTable.Rows[i].SetAdded();
}
//Call the SetModified method on the next 3 rows
for (Int32 z = 3; z < 6; z++)
{
DemoTable.Rows[z].SetModified();
}
//Show the rowstate for each row
for (Int32 y = 0; y < DemoTable.Rows.Count; y++)
{
Debug.WriteLine(DemoTable.Rows[y].RowState.ToString());
}
//Show output
//0 - Added
//1 - Added
//2 - Added
//3 - Modified
//4 - Modified
//5 - Modified
//6 - Unchanged
//7 - Unchanged
//8 - Unchanged
//9 - Unchanged
//Verify that there are changes present
Debug.Assert(DemoDataSet.HasChanges(), "There aren't changes but should be");
}