GridView insert update delete in asp.net - Part 23

Suggested Videos 
Part 20 - Keep gridview in edit mode when update fails due to data conflict 
Part 21 - GridView TemplateField in asp.net
Part 22 - Using validation controls with asp.net gridview when editing data




In this video we will discuss about performing an insert, update and delete on asp.net gridview control using sqldatasource. We will be using tblEmployee table for this demo. SQL script to create and populate this table with sample data is available in Part 13 of asp.net gridview tutorial.

Create an asp.net web application. Drag and drop a gridview and a sqldatasource control on WebForm1.aspx. 



Configure "SqlDataSource1" control 
1. Right click on "SqlDataSource1" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Select "Generate INSERT, UPDATE and DELETE statements" checkbox and click OK
8. Click Next and Finish

Drag and drop gridview control on WebForm1.aspx. Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag" 
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Deleting" and "Enable Editing" checkboxes. At this point "Delete" and "Edit" buttons should appear on the gridview control.

We will be using gridview control's footer for inserting a new record. Set "ShowFooter" property of the GridView1 control to "true". This can be done from the properties window, or in the HTML.

By default GridView control has generated bound fields to display EmployeeId, Name, Gender and City columns. 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1" 
                ShowFooter="true">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" 
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" 
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" 
            SortExpression="City" />
    </Columns>
</asp:GridView>

We need to convert these bound fields into template fields. This can be done very easily using the designer. 
1. On the "GridView Tasks" pane click on "Edit Columns" link button.
2. Select "EmployeeId" from "Selected Fields" section and click on "Convert this field into a template field" link
3. Do the same for Name, Gender and City


Now modify the template fields in the HTML, as shown below. Please note
1. In every TemplateField, along with EditItemTemplate and ItemTemplate, we also need FooterTemplate.
2. A dropdownlist is used in EditItemTemplate and FooterTemplate of "Gender" template field, instead of a textbox control.
3. To validate data during edit and insert operations, notice that, we are using validation controls, in EditItemTemplate and FooterTemplate.
4. A linkbutton is used in the footer template of "EmployeeId" template field, to enable the user to insert a new employee row
5. We have used ValidationGroup="Insert" for all the validation controls in FooterTemplates. LinkButton's ValidationGroup is aslo set to "Insert", so that all the validation controls in in FooterTemplates are fired only on Insert LinkButton click.
6. We have set LinkButton's OnClick="lbInsert_Click".
7. After the closing tag of GridView, notice that we are using 2 validationsummary controls, to display all the validation messages in one place. ValidationSummary1 control's ValidationGroup is set to "Insert". ValidationSummary1 control displays all insert related validation errors, and edit related validation errors are displayed using ValidationSummary2 control.

Here is the complete HTML
<div style="font-family: Arial">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
              DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1" 
              ShowFooter="True" BackColor="#DEBA84" BorderColor="#DEBA84"
              BorderStyle="None" BorderWidth="1px" CellPadding="3" 
              CellSpacing="2">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:TemplateField HeaderText="EmployeeId" InsertVisible="False" 
                           SortExpression="EmployeeId">
            <EditItemTemplate>
                <asp:Label ID="Label1" runat="server" 
                           Text='<%# Eval("EmployeeId") %>'>
                </asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" 
                           Text='<%# Bind("EmployeeId") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:LinkButton ID="lbInsert" ValidationGroup="Insert" 
                    runat="server" OnClick="lbInsert_Click">Insert
                </asp:LinkButton>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name" SortExpression="Name">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server" 
                             Text='<%# Bind("Name") %>'>
                </asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEditName" runat="server" 
                    ErrorMessage="Name is a required field"
                    ControlToValidate="TextBox1" Text="*" ForeColor="Red">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" 
                    Text='<%# Bind("Name") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvInsertName" runat="server" 
                    ErrorMessage="Name is a required field" 
                    ControlToValidate="txtName" Text="*" ForeColor="Red"
                    ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Gender" SortExpression="Gender">
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownList1" runat="server" 
                    SelectedValue='<%# Bind("Gender") %>'>
                    <asp:ListItem>Select Gender</asp:ListItem>
                    <asp:ListItem>Male</asp:ListItem>
                    <asp:ListItem>Female</asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvEditGender" runat="server" 
                    ErrorMessage="Gender is a required field" Text="*"
                    ControlToValidate="DropDownList1" ForeColor="Red" 
                    InitialValue="Select Gender">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" 
                    Text='<%# Bind("Gender") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertGender" runat="server">
                    <asp:ListItem>Select Gender</asp:ListItem>
                    <asp:ListItem>Male</asp:ListItem>
                    <asp:ListItem>Female</asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvInsertGender" runat="server" 
                    ErrorMessage="Gender is a required field" Text="*"
                    ControlToValidate="ddlInsertGender" ForeColor="Red" 
                    InitialValue="Select Gender" ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City" SortExpression="City">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox3" runat="server" 
                    Text='<%# Bind("City") %>'></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEditCity" runat="server" 
                    ErrorMessage="City is a required field" Text="*"
                    ControlToValidate="TextBox3" ForeColor="Red">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label4" runat="server" 
                    Text='<%# Bind("City") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvInsertCity" runat="server" 
                    ErrorMessage="City is a required field" Text="*" 
                    ControlToValidate="txtCity" ForeColor="Red" 
                    ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ValidationSummary ID="ValidationSummary1" ValidationGroup="Insert" 
    ForeColor="Red" runat="server" />
<asp:ValidationSummary ID="ValidationSummary2" ForeColor="Red" 
    runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    DeleteCommand="DELETE FROM [tblEmployee] WHERE [EmployeeId] = @EmployeeId" 
    InsertCommand="INSERT INTO [tblEmployee] ([Name], [Gender], [City]) 
        VALUES (@Name, @Gender, @City)"
    SelectCommand="SELECT * FROM [tblEmployee]" 
    UpdateCommand="UPDATE [tblEmployee] SET [Name] = @Name, [Gender] = @Gender,
        [City] = @City WHERE [EmployeeId] = @EmployeeId">
    <DeleteParameters>
        <asp:Parameter Name="EmployeeId" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
        <asp:Parameter Name="EmployeeId" Type="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>
</div>

Finally copy and paste the following event handler method in WebForm1.aspx.cs
protected void lbInsert_Click(object sender, EventArgs e)
{
    SqlDataSource1.InsertParameters["Name"].DefaultValue = 
        ((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
    SqlDataSource1.InsertParameters["Gender"].DefaultValue = 
        ((DropDownList)GridView1.FooterRow.FindControl("ddlInsertGender")).SelectedValue;
    SqlDataSource1.InsertParameters["City"].DefaultValue = 
        ((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;
    SqlDataSource1.Insert();
}