Introduction:
Matt Berseth wrote a very interesting article on “Bulk Inserting Data Using the ListView Control". The idea is to give the user an Excel like interface where they can edit the rows with custom data and finally click the update button to persist the data in the database. Matt’s solution was great but lacks a very important detail. There was no way to know what rows were changed by the user. Matt took the road to update all the rows whether they were changed or not. In this article we are going to take a look at an alternative method of updating only the changed rows.
Populating the GridView Control:
Let’s start by populating the GridView control. We will be using a simple DataSet to perform this task but you are free to use any type of data access method.
private void BindData()
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories", conn);
DataSet ds = new DataSet();
ad.Fill(ds);
gvCategories.DataSource = ds;
gvCategories.DataBind();
}
And here is the corresponding HTML code for the GridView control.
<asp:GridView ID="gvCategories" PageSize="5" AllowPaging="true" runat="server"
DataKeyNames="id" AutoGenerateColumns="false"
onpageindexchanging="gvCategories_PageIndexChanging">
<Columns>
<asp:TemplateField HeaderText="CategoryID">
<ItemTemplate>
<%# Eval("id") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CategoryName">
<ItemTemplate>
<asp:TextBox ID="txtCategoryName" onchange='<%# InjectSaveRowIndexFunction(Container.DisplayIndex) %>' runat="server" Text='<%# Eval("CategoryName") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<asp:TextBox ID="txtCategoryDescription" onchange='<%# InjectSaveRowIndexFunction(Container.DisplayIndex) %>' runat="server" Text='<%# Eval("Description") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
The GridView contains three TemplateField columns namely, ID, CategoryName and CategoryDescription. The ItemTemplate of the CategoryName and CategoryDescription contains a TextBox control since we want to update these fields. The onChange event of the TextBox is hooked up with a server side method. This method will be evaluated when the rows are data bound. Let’s take a look at the InjectSaveRowIndexFunction method.
Injecting Row Index to the JavaScript Function:
The InjectSaveRowIndexFunction is responsible for injecting the JavaScript code for the onChange event of the TextBox control.
protected string InjectSaveRowIndexFunction(int rowIndex)
{
string function = "saveRowIndex(";
return function + rowIndex + ")";
}
This means when the page is served on the browser the HTML for the TextBox would look like this:
<input name="gvCategories$ctl02$txtCategoryName" type="text" value="Beverages Edite" id="gvCategories_ctl02_txtCategoryName" onchange="saveRowIndex(0)" />
The saveRowIndex function is responsible for firing the Ajax call which saves the rowIndex in a Session object.
saveRowIndex Function And the Ajax Call:
Take a look at the implementation of the saveRowIndex function below:
<script language="javascript" type="text/javascript">
function saveRowIndex(rowIndex)
{
AjaxWithJQuery.SimpleService.NoteChangedRows(rowIndex);
}
</script>
As, you can see the saveRowIndex function calls the web service “SimpleService” which calls the NoteChangedRows method. The NoteChangedRows method is responsible for adding the row index to the Session object. Let’s take a look at the implementation below:
[WebMethod(true)]
public void NoteChangedRows(int rowIndex)
{
List<Int32> list = null;
if (Session["List"] == null)
{
list = new List<int>();
if (list.Contains(rowIndex)) return;
list.Add(rowIndex);
Session["List"] = list;
}
else
{
list = Session["List"] as List<Int32>;
if (list.Contains(rowIndex)) return;
list.Add(rowIndex);
}
}
The web method “NoteChangedRows” is marked with [WebMethod(true)] attribute which indicates that it is a web method and the session state is enabled for this method.
The purpose of NoteChangedRows method is to simply add the rowIndex into the Session object. This will keep track of the rows which have been changed by the user.
The Update Method:
Now, let’s see the update method which extracts only the changed rows from the GridView control. Here is the implementation of the Update method.
protected void Update(object sender, EventArgs e)
{
// get only the changed rows and not all the rows
List<Int32> list = Session["List"] as List<Int32>;
if (list == null) return;
for (int i = 0; i < list.Count; i++)
{
GridViewRow row = gvCategories.Rows[list[i]];
int id = (int) gvCategories.DataKeys[list[i]].Value;
string categoryName = (row.FindControl("txtCategoryName") as TextBox).Text;
string categoryDescription = (row.FindControl("txtCategoryDescription") as TextBox).Text;
// DO THE ACTUAL UPDATE HERE!
lblFinal.Text += id + "<BR>" + categoryName + "<BR>" + categoryDescription;
}
// reset the list!
Session["List"] = null;
}
The update method simply checks the Session object for the changed rows index and then only updates the changed rows (We have not included the actual code for update but you get the idea!).
You will notice that we used Container.DisplayIndex and not Container.DataItemIndex. Actually, if you use Container.DataItemIndex and your GridView has paging enabled then the above solution will not work. The tip to use Container.DisplayIndex was provided by DLT. Here is the explanation as given by DLT:
“Just a heads up: readers should know that Container.DataItemIndex returns the index position in the entire dataset, which is not what you want if you are using paging and you have not used a sproc that pulls only one page of value at a time. In this case you need to use Container.DisplayIndex to get the row position on the page being displayed / updated. If your PageSize is 10 and you've edited the first record on the second page, DataItemIndex returns 10 while DisplayIndex returns 0, and since each page only has index values of 0-9, you get an index out of bounds error if you use the DataItemIndex to find a DataKey value to use in your update code. I learned this the hard way.”
Conclusion:
In this article we learned how to update only the changed rows of the GridView control by using Ajax calls. This will improve performance in cases where you display a large number of rows to the user and the user only updates few rows.
[Download Sample]