Edit gridview and save in dataset finally update database

Here is the html design for Gridview

Gridview html design

<asp:UpdatePanel ID="upnlRecords" runat="server" > 1:<ContentTemplate > 2: 3:<asp:GridView runat="server" AllowPaging="True" ID="gvRecords" PageSize="20" 4: OnPageIndexChanging="gvRecords_PageIndexChanging" 5: OnRowEditing="gvRecords_RowEditing" 6: OnRowCancelingEdit="gvRecords_RowCancelingEdit" 7: OnRowUpdating="gvRecords_RowUpdating" 8: AutoGenerateColumns="false" > 9: 10:<Columns > 11:<asp:CommandField ButtonType="Button" ShowEditButton="True" EditText="Edit" HeaderText=".." CancelText="Cancel" /> 12:<asp:BoundField DataField="EmployeeID" ReadOnly="true" /> 13:<asp:BoundField DataField="EmpName" /> 14:<asp:BoundField DataField="EmpEmail" /> 15:<asp:BoundField DataField="JoinDate" /> 16: 17:</Columns> 18:</asp:GridView> 19: 20:<asp:Label ID="lblDatasetSize" runat="server" ></asp:Label> 21:</ContentTemplate> 22:</asp:UpdatePanel> 23:<asp:Button runat="server" OnClick="btnSave_Click" ID="btnSave" Text="Save all changes to Database" /> 24:

All gridview code behind events. Update, Edit, Edit Cancel, Pagging

Gridview Update, Edit, Edit Cancel, Pagging

protected void gvRecords_RowUpdating(object sender, GridViewUpdateEventArgs e) 1: { 2: List<string> objValues = new List<string>(); 3: string identity = gvRecords.Rows[e.RowIndex].Cells[1].Text ; 4: TextBox a1 = (TextBox)gvRecords.Rows[e.RowIndex].Cells[2].Controls[0]; 5: TextBox a2 = (TextBox)gvRecords.Rows[e.RowIndex].Cells[3].Controls[0]; 6: TextBox a3 = (TextBox)gvRecords.Rows[e.RowIndex].Cells[4].Controls[0]; 7: objValues.Add(identity); 8: objValues.Add(a1.Text); 9: objValues.Add(a2.Text); 10: objValues.Add(a3.Text); 11: 12: UpdateRowInDataSet(objValues); 13: gvRecords.EditIndex = -1; 14: LoadRecords(); 15: } 16: protected void gvRecords_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) 17: { 18: gvRecords.EditIndex = -1; 19: LoadRecords(); 20: } 21: protected void gvRecords_RowEditing(object sender, GridViewEditEventArgs e) 22: { 23: gvRecords.EditIndex = e.NewEditIndex; 24: LoadRecords(); 25: } 26: protected void gvRecords_PageIndexChanging(object sender, GridViewPageEventArgs e) 27: { 28: gvRecords.PageIndex = e.NewPageIndex; 29: LoadRecords(); 30: 31: } 32:

Lets look at how to Load data in grid, and how to save a row values when row update fired.

How to save row values on row update.

void LoadRecords() 1: { 2: if (object.Equals(Cache["myDS"], null)) 3: { 4: 5: MyDs = new DataSet(); 6: objDA = new SqlDataAdapter("Select top 200 * from tbEmployee", System.Configuration.ConfigurationManager.AppSettings["ConnectionSting"]); 7: objDA.Fill(MyDs, "tbEmployee"); 8: Cache.Insert("myDS", MyDs); 9: objDA.Dispose(); 10: } 11: else 12: { 13: MyDs= (DataSet)Cache["myDS"]; 14: 15: } 16: 17: gvRecords.DataSource = MyDs; 18: gvRecords.DataBind(); 19: 20: } 21: 22: void UpdateRowInDataSet(List<string> values) 23: { 24: MyDs = (DataSet)Cache["myDS"]; 25: 26: for (int i = 0; i < values.Count; i++) 27: { 28: lblDatasetSize.Text += values[i].ToString() + "<br>"; 29: } 30: 31: // pick the right row by some unique id 32: DataRow dr = (DataRow)MyDs.Tables["tbEmployee"].Rows[Convert.ToInt32(values[0]) - 1]; 33: 34: // make all changes in the row. 35: dr["EmpName"] = values[1]; // assign the value at right column. 36: dr["EmpEmail"] = values[2]; 37: 38: 39: 40: Cache.Insert("myDS", MyDs); 41: 42: } 43:

Finally update new values to database.

Now save new values in database.

// Finally save all dataset changes to datasource 1: protected void btnSave_Click(object sender, EventArgs e) 2: { 3: MyDs = (DataSet)Cache["myDS"]; 4: 5: SqlConnection objCon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionSting"]); 6: SqlCommand objCmd = new SqlCommand(); 7: objCmd.Connection = objCon; 8: objCmd.CommandText = "Select top 1 * from tbEmployee"; 9: 10: objDA = new SqlDataAdapter(objCmd); 11: //objDA.ContinueUpdateOnError = true; 12: SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(objDA); 13: objDA.Update(MyDs.Tables["tbEmployee"]); 14: MyDs.Tables["tbEmployee"].AcceptChanges(); 15: objDA.Dispose(); 16: 17: } 18: