Mark Berseth wrote a very interested article about Bulk Insert Data Using ListView Control. Basically, the ListView control is displayed with the TextBox controls in each row (like MS Excel). Now, the user can write in the TextBox and then use the submit button to insert all the new rows to the database. This works well when you are inserting the data into the database. The problem starts when you try to update the data which is already in the database. Let's say you populate the ListView with some data and now you want to edit it. You will change only one row and press the submit button. The ListView control does not know which row has been changed and hence you will have to send all the rows back to the database even though only a single row got changed.
There should be some way to only update the changed row. In this post I am going to discuss a simple technique that solves this problem. Let's start by populating the GridView control (You can use ListView if you prefer).
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 HTML view of the GridView control.
<asp:GridView ID="gvCategories" runat="server" DataKeyNames="id" AutoGenerateColumns="false"
>
<Columns>
<asp:TemplateField HeaderText="CategoryID">
<ItemTemplate>
<%# Eval("id") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CategoryName">
<ItemTemplate>
<asp:TextBox ID="txtCategoryName" onchange='<%# AttachFunction(Container.DataItemIndex) %>' runat="server" Text='<%# Eval("CategoryName") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<asp:TextBox ID="txtCategoryDescription" onchange='<%# AttachFunction(Container.DataItemIndex) %>' runat="server" Text='<%# Eval("Description") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
I am attaching the onchange JavaScript function to the TextBoxes. The onchange event will be fired whenever the text inside the TextBox will change. I am also passing the current RowIndex of the GridView row. This will keep track which row has been changed. Now, let's check out the AttachFunction which is a server side function.
protected string AttachFunction(int rowIndex)
{
string function = "saveRowIndex(";
return function + rowIndex + ")";
}
function saveRowIndex(rowIndex)
{
AjaxWithJQuery.SimpleService.NoteChangedRows(rowIndex);
}
In the above code I am firing an Ajax method named NoteChangedRows.
[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);
}
}
NoteChangedRows simply stores the row index of the rows that have been changed. Now, let's see the Update method which is fired when the user clicks the update button.
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>;
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 looks into the RowIndex list and finds the changed rows from that list. Now, we only have to update those changed rows and not all the rows.
I will write an article on this technique and publish it on GridViewGuy. So, stay tunned!