Click here to Skip to main content
12,689,349 members (28,908 online)
Click here to Skip to main content
Add your own
alternative version

Stats

27.6K views
231 downloads
20 bookmarked
Posted

Getting the Changed DataTable

, 18 Jan 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Consider a situation in which you have a datagrid with all or most of its rows in editable mode.

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.

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.

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.

Conclusion

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

History

  • 19th January, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

RamMajeti
Web Developer
India India
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
GeneralGood Pin
Abhijit Jana18-Jan-08 21:24
memberAbhijit Jana18-Jan-08 21:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170113.4 | Last Updated 19 Jan 2008
Article Copyright 2008 by RamMajeti
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid