I have been using ADO.NET for long time and using quite often, as it is required to connect to database in .NET. However, I came across few features recently while studying. I haven't used them so far. I am sharing those features related data manipulations here. Most of the people well aware of them and definitely used them. I am going to talk about below things:
Everybody knows how to add rows in DataTable. One way is using DataTable.NewRow() method which creates new
DataRow object having the column defined in the DataTable. Another way directly adding rows with object array.
1 DataRow newRow = myTable.NewRow();
2 newRow["Name"] = "ABC";
3 myTable.Add( newRow);
When newRow created at line
1 then it is in DataRowState.Detached state, because it hasn't been added to the table yet. Once it has added to table at line
3 state changes to DataRowState.Added.
There are again two ways to remove:
However, when row removed with first method, after committing changes back to database then it doesn't get deleted from there as there was no reference of this row in the in-Memory DataTable. In this case status of row become
DataRowState.Detached. But in second case this row marked as
DataRowState.Deleted and performed actual delete from database also when commit happens.
Everybody aware of modification as well. It is updated using indexer property
dataRow.Items["<columnName>"]. Items Properties are able to provide different versions of the row. That will allow to revert back the changes if not matching the criteria. New Changed values, added rows and deleted rows can be reverted back if the DataTable.RejectChanges() method is called at the end. All changes are committed to the table and can't be reverted back.
When we modify or add a new row, it will throw exception in below cases:
It will throw exception when anyone of above happens.
In this above case, if we want to cancel any update done in the row, we should call
DataRow.BeginEdit() method and should call
DataRow.EndEdit() when edit completes. If any error occurs in between and want to cancel all changes done in the row, than we should call
DataRow.CancelEdit(). It will revert all changes done in selected row. If we want cancel changes done in whole table than call
dr["FirstName"] = "NityaPrakash";
dr["ModifiedDate"] = DateTime.Now;
catch (Exception ex)
DataTable has a event
DataTable.ColumnChanging. This event fire when values of any row is about to change but not completed.
DataColumnChangeEventArg provide property called
ProposedValue which is new value about to be set in the column. We can implement validation like below coke snippet. Than, we can check if row has any error or not. Based on that we can decide if we want to cancel or continue with changes.
public void Update()
DataTable dt = GetData();
dt.ColumnChanging += dt_ColumnChanging;
DataRow dr = dt.Rows;
dr["Age"] = 40;
void dt_ColumnChanging(object sender, DataColumnChangeEventArgs e)
if (e.Column.ColumnName == "Age")
int age = (int) e.ProposedValue;
if (age < 20 || age > 40)
e.Row.RowError = "Age must be between 20 and 30";
|Nitya Prakash Sharma has over 10 years of experience in .NET technology. He is currently working as Senior Consultant in industry. He is always keen to learn new things in Technology and eager to apply wherever is possible. He is also has interest in Photography, sketching and painting.