Manipulating Data in ADO-NET DataTable

 02-Apr-2014   nityaprakash     ADO.NET    Comments  0

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:

  • Adding new rows.
  • Deleting rows.
  • Changing values existing rows.

Adding New Row

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.

Removing Row From the Table

There are again two ways to remove:

myTable.Rows[index].Delete();
and
myTable.Rows.Remove( deletRow);
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.

Modifying Data

Everybody aware of modification as well. It is updated using indexer property dataRow["<column>"] or 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.

Exception Based Validation

When we modify or add a new row, it will throw exception in below cases:

  • If any column violating unique constraints
  • Modified value is not a valid type.
  • Attempting to store NULL value in primary key column.
  • Supplying string data to a column that exceeds the maximum length defined.

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 DataTable.RejectChanges().

    dr.BeginEdit();
    try
    {
        dr["FirstName"] = "NityaPrakash";
        dr["ModifiedDate"] = DateTime.Now;
        dr.EndEdit();
    }
    catch (Exception ex)
    {
        dr.CancelEdit();
        Console.WriteLine(ex.Message);
    }

Validation Based Errors

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[0];
            dr.BeginEdit();
            dr["Age"] = 40;
            if (dr.HasErrors)
            {
                Console.WriteLine(dr.RowError);
                dr.CancelEdit();
            }
            else
            {
                dr.EndEdit();
            }
       }
        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.

My Blog
Post Comment

COMMENTS