Getting the Changed DataTable

Introduction

Consider a situation in which you have a datagrid with all or most of its rows in editable mode. You change a single item in the datagrid and press the save button. Most probably you will be sending all the data into the database for the update. This can be very bad for performance if you are sending thousands of rows as we only changed one row and thus only this row should be sent to the DAL layer to perform the update. In this article, we will see how we can only get the changed rows from the datatable object.

Background

Setting the User Interface

The User Interface is pretty simple.

I have three columns which are UserID, UserName and the Score. These columns are created using the "Property Builder" (Right click on DataGrid control in design view and select property builder). If you want to create your columns dynamically than check out my article, Creating bound and template columns dynamically in a datagrid.

As you can see, score is a "TextBox" column which will allow us to make changes. When we press the "Get Changed Rows" button, we will only get the rows that have been changed.

BindGrid Method

BindGrid method is called whenever there is no postback.

 Collapse | Copy Code

private void BindData() 
{
Database db = DatabaseFactory.CreateDatabase(); 
DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("GetGrades");
oldDataSet = db.ExecuteDataSet(selectCommandWrapper); 
DataGrid1.DataSource = oldDataSet; 
DataGrid1.DataBind(); 
// Put the DataSet in the session object 
Session["DataSet"] = oldDataSet;
}

The most important line is the bold one where I have assigned the oldDataSet into a Session object so I can have a copy of the DataSet.

Using the Code

Button Click Code (Getting the Changes)

The main idea behind getting only the changed values from the datagrid is simple. We get the old DataSet. We make a DataTable object from the oldDataSet. We loop through the Datagrid and retrieve values of each row. We assign the oldDataTable with a primary key, which in this case is UserID (It will be a good idea to not display the UserID as it is a primary key). Later we check the old score with the new score using DataRow object. And finally we use the "GetChanges" method of the DataTable to only get the changes into a new DataTable.

DataSet also has a "GetChanges" method which you can use to perform the same actions.

 Collapse | Copy Code

private void Button1_Click(object sender, System.EventArgs e)
{
// Gets the DataSet from the Session
oldDataSet = (DataSet) Session["DataSet"];
// Gets the DataTable out of the DataSet
DataTable oldDataTable = oldDataSet.Tables[0];
DataTable newDataTable = new DataTable();
DataRow dataRow;
int oldScore = 0;
foreach(DataGridItem dgi in DataGrid1.Items)
{
// Gets the text out of the Score column and convert it to Int32
int score = Convert.ToInt32(((TextBox) dgi.FindControl("TextBox1")).Text);
// Get the UserID out of the first column
int userID = Convert.ToInt32(dgi.Cells[0].Text);
// Make a DataColumn object which is used to set the primary key
DataColumn[] userIDColumn = new DataColumn[1];
userIDColumn[0] = (DataColumn) oldDataTable.Columns["UserID"];
// Set the primary key to the oldDataTable
oldDataTable.PrimaryKey = userIDColumn;
dataRow = oldDataTable.Rows.Find(userID);
if(DBNull.Value == dataRow["Test1"])
{ dataRow["Test1"] = score; }
else
{
oldScore = Convert.ToInt32(dataRow["Test1"]);
// Check to see if the score has changed or not
if(score != oldScore)
{ dataRow["Test1"] = score; }
}
}
// Get only the changes from the oldDataTable to the newDataTable
newDataTable = oldDataTable.GetChanges();
// Bind the newDataTable to the DataGrid
DataGrid2.DataSource = newDataTable;
DataGrid2.DataBind();
} 

The Result

You can see in the image below that only the fields I have changed are retrieved from the DataTable object and nothing else.