Insert/Update data from asp.net GridView
(Originally coded in Visual Studio 2008 (asp.net 3.5), unknown compatibility with VS2005/.net 2.0)
Here I’ll show you how to allow users to insert and update database rows right from a gridview with minimal work. This provides users a consistent layout across your application, is simple to understand right off the bat, and is easily reusable.
We’re going to start out as simple as possible, though you’d want to eventually add things like input validation and styles, the idea here is to focus on the core functionality.
Start by making all of the fields you’ll need to manipulate Template Fields. To make things even easier in your code, give all the field-bound controls (labels, textboxes) identical IDs (yes, you can have multiple controls with the same ID on different templates) across your EmptyDataTemplate/EditItemTemplate/FooterTemplate, like so:
<asp:GridView ID="gvItems" runat="server" AutoGenerateColumns="False"> <Columns> <asp:TemplateField HeaderText="Item Name"> <EditItemTemplate> <asp:TextBox ID="txtItemName" runat="server" Text='<%# Bind("item_nm") %>' /> </EditItemTemplate> <FooterTemplate> <asp:TextBox ID="txtItemName" runat="server" /> </FooterTemplate> <ItemTemplate> <asp:Label ID="lblItemName" runat="server" Text='<%# Eval("item_nm") %>' /> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Commands"> <EditItemTemplate> <asp:Button ID="btnUpdate" runat="server" CommandName="Update" CommandArgument='<%# Bind("item_id") %>' /> </EditItemTemplate> <FooterTemplate> <asp:Button ID="btnInsert" runat="server" CommandName="Insert" /> </FooterTemplate> <ItemTemplate> <asp:Button ID="btnEdit" runat="server" CommandName="Edit" /> </ItemTemplate> </asp:TemplateField> </Columns> <EmptyDataTemplate> <asp:TextBox ID="txtItemName" runat="server" /> <asp:Button ID="btnInsertEmpty" runat="server" CommandName="insertEmpty" /> </EmptyDataTemplate> </asp:GridView>
Next, we’ll add the RowCommand attribute to our gridview to call our procedure that will populate the SQL parameters:
<asp:GridView ID="gvItems" runat="server" OnRowCommand="gvItems_RowCommand">
Then, we’ll write the procedure in the code-behind. First make sure to add a ‘using’ directive for System.Collections.Generic;
Then, inside your class, declare a variable for SQL parameters, and add the gvItems_RowCommand procedure, as follows:
private List<SqlParameter> spParams = new List<SqlParameter>(); protected void gvItems_RowCommand(object sender, GridViewCommandEventArgs e) { Int32 intItemID; SqlParameter param; TextBox txtItemName; switch (e.CommandName) { case "insertEmpty": spParams.Clear(); txtItemName = gvItems.Controls[0].Controls[0].FindControl("txtItemName") as TextBox; param = new SqlParameter("@ITEM_NM", SqlDbType.VarChar); param.Direction = ParameterDirection.Input; param.Value = txtItemName.Text.ToString(); //validate! spParams.Add(param); sqlItemSource.Insert(); gvItems.DataBind(); break; case "Insert": spParams.Clear(); txtItemName = gvItems.FooterRow.FindControl("txtItemName") as TextBox; param = new SqlParameter("@ITEM_NM", SqlDbType.VarChar); param.Direction = ParameterDirection.Input; param.Value = txtItemName.Text.ToString(); //validate! spParams.Add(param); sqlItemSource.Insert(); gvItems.DataBind(); break; case "Update": spParams.Clear(); txtItemName = gvItems.Rows[gvItems.EditIndex].FindControl("txtItemName") as TextBox; intItemID = Convert.ToInt32(e.CommandArgument.ToString()); param = new SqlParameter("@ITEM_NM", SqlDbType.VarChar); param.Direction = ParameterDirection.Input; param.Value = txtItemName.Text.ToString(); //validate! spParams.Add(param); param = new SqlParameter("@ITEM_ID", SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = intItemID; spParams.Add(param); break; } }
Now, let’s say you have two simple SQL Stored Procs, one for inserts, one for updates. Your SqlDataSource for the GridView should look similar to this (select removed for clarity):
<asp:SqlDataSource ID="sqlItemSource" runat="server" ConnectionString="connStr" InsertCommand="SP_ITEM_INSERT" InsertCommandType="StoredProcedure" UpdateCommand="SP_ITEM_UPDATE" UpdateCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="ITEM_NM" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ITEM_ID" Type="Int32" /> <asp:Parameter Name="ITEM_NM" Type="String" /> </UpdateParameters> </asp:SqlDataSource>
Now we’re going to add the following attributes to the SqlDataSource element, which will call a procedure that uses our sqlParamList from the code-behind to populate the datasource params:
OnInserting="sp_Fire" OnUpdating="sp_Fire"
Of course, now we have to add the really simple sp_Fire proc to the code-behind:
protected void sp_Fire(object sender, SqlDataSourceCommandEventArgs e) { e.Command.Parameters.Clear(); foreach (SqlParameter p in spParams) e.Command.Parameters.Add(p); }
Leave your response!