Getting Only Changes from DataGrid

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 send to the DAL layer to perform the update. In this article we will see that how we can only get the changed rows from the datatable object.

Getting the Changed DataTable

Introduction:

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 that 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.

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.

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 don't 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 same actions.

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 the image below that only the fields that I have changed are retrieved from the DataTable object and nothing else.

Conclusion:

Its always better to get only the rows that have been changed or modified rather than sending all the data to the database.