Introduction
By default the GridView control doesn’t have support of inserting records. In this post I’m going to show you an example how you can use the GridView’s FooterTemplate and the SqlDataSourceControl to insert a record.
The FooterTemplate in a GridView is column based (It will not be a separate table at the end of the GridView, instead if will be added at the end of the GridView but into a column). By making the example in this post as simple as possible, I’m going to use the TemplateField to create the GridView. The GridView in the example will not support editing. I will only focus on inserting.
The following code is a GridView where the Customer table located in the Northwind database is used. The GridView will have three columns (CustomerID, CompanyName and ContactTitle). To each column there is a FooterTemplate with editable fields:
<%@ Page Language="C#" ClassName="Default_aspx" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void Button1_Click(object sender, EventArgs e)
{
TextBox customerID = GridView1.FooterRow.FindControl("CustomerIDTextBox") as TextBox;
TextBox companyName = GridView1.FooterRow.FindControl("CompanyNameTextBox") as TextBox;
DropDownList ContactTitle = GridView1.FooterRow.FindControl("ContactTitleDropDownList") as DropDownList;
SqlDataSource1.InsertParameters["CustomerID"].DefaultValue = customerID.Text;
SqlDataSource1.InsertParameters["CompanyName"].DefaultValue = companyName.Text;
SqlDataSource1.InsertParameters["ContactTitle"].DefaultValue = ContactTitle.SelectedValue;
SqlDataSource1.Insert();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1" DataKeyNames="CustomerID"
AutoGenerateColumns="False" ShowFooter="True">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CustomerIDLabel" Runat="Server"><%# Eval("CustomerID") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CustomerIDTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CompanyNameLabel" Runat="Server"><%# Eval("CompanyName") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CompanyNameTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:DropDownList ID="ContactTitleDropDownList" Runat="server" DataSourceID="SqlDataSource2" DataTextField="ContactTitle" DataValueField="ContactTitle">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
</asp:SqlDataSource>
<asp:Button ID="Button1" Runat="server" Text="Add" OnClick="Button1_Click" />
</FooterTemplate>
<ItemTemplate>
<asp:DropDownList ID="ContactTitleDropDown" SelectedValue='<%# Bind("ContactTitle") %>' Runat="Server" DataSourceID="SqlDataSource3" DataTextField="ContactTitle" DataValueField="ContactTitle" ></asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" EnableCaching="True">
</asp:SqlDataSource>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactTitle]) VALUES (@CustomerID, @CompanyName, @ContactTitle)"
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
<DeleteParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
<InsertParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
</InsertParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
As I have mentioned before, the GridView do not have support of inserting like the DetailView and FormView etc. So we have to programmatically get the value from the controls located in the FooterTemplate, and set the value of the SqlDataSource insert parameters. When this is done we add the record by calling the SqlDataSource insert method. This method will execute the data source’s InsertCommand (This will be done when the Add button located in the FooterTemplate is pressed):
void Button1_Click(object sender, EventArgs e)
{
TextBox customerID = GridView1.FooterRow.FindControl("CustomerIDTextBox") as TextBox;
TextBox companyName = GridView1.FooterRow.FindControl("CompanyNameTextBox") as TextBox;
DropDownList ContactTitle = GridView1.FooterRow.FindControl("ContactTitleDropDownList") as DropDownList;
SqlDataSource1.InsertParameters["CustomerID"].DefaultValue = customerID.Text;
SqlDataSource1.InsertParameters["CompanyName"].DefaultValue = companyName.Text;
SqlDataSource1.InsertParameters["ContactTitle"].DefaultValue = ContactTitle.SelectedValue;
SqlDataSource1.Insert();
}
To get the Control from the FooterTemplate, we can use the FooterRow property of the GridView control. This property represents a GridViewRow and by using the FindControl method, we can get a control by its Id.
Showing and Hiding footer
By default you maybe want to hide the footer until the user presses a button control for adding a new record. This could be done by setting the ShowFooter property of the GridView control. By default the ShowFooter property is false:
<asp:Button ID="AddButton1" runat="Server" Text="Add new Item"
OnClick="AddButton1_Click" />
void AddButton1_Click(object sender, EventArgs e)
{
GridView1.ShowFooter = true;
}
The following is an example where the footer will be displayed if the user presses the “Add Item” button. There is also a Cancel button next to the Add button in the footer to make it possible to cancel the insert (Hide the footer):
<%@ Page Language="C#" ClassName="Default_aspx" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void Button1_Click(object sender, EventArgs e)
{
TextBox customerID = GridView1.FooterRow.FindControl("CustomerIDTextBox") as TextBox;
TextBox companyName = GridView1.FooterRow.FindControl("CompanyNameTextBox") as TextBox;
DropDownList ContactTitle = GridView1.FooterRow.FindControl("ContactTitleDropDownList") as DropDownList;
SqlDataSource1.InsertParameters["CustomerID"].DefaultValue = customerID.Text;
SqlDataSource1.InsertParameters["CompanyName"].DefaultValue = companyName.Text;
SqlDataSource1.InsertParameters["ContactTitle"].DefaultValue = ContactTitle.SelectedValue;
SqlDataSource1.Insert();
}
void AddButton1_Click(object sender, EventArgs e)
{
GridView1.ShowFooter = true;
}
void CancelButton1_Click(object sender, EventArgs e)
{
GridView1.ShowFooter = false;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="AddButton1" runat="Server" Text="Add new Item" OnClick="AddButton1_Click" />
<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1" DataKeyNames="CustomerID"
AutoGenerateColumns="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CustomerIDLabel" Runat="Server"><%# Eval("CustomerID") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CustomerIDTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CompanyNameLabel" Runat="Server"><%# Eval("CompanyName") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CompanyNameTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:DropDownList ID="ContactTitleDropDownList" Runat="server" DataSourceID="SqlDataSource2" DataTextField="ContactTitle" DataValueField="ContactTitle">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
</asp:SqlDataSource>
<asp:Button ID="Button1" Runat="server" Text="Add" OnClick="Button1_Click" />
<asp:Button ID="CancelButton1" Runat="server" Text="Cancel" OnClick="CancelButton1_Click" />
</FooterTemplate>
<ItemTemplate>
<asp:DropDownList ID="ContactTitleDropDown" SelectedValue='<%# Bind("ContactTitle") %>' Runat="Server" DataSourceID="SqlDataSource3" DataTextField="ContactTitle" DataValueField="ContactTitle" ></asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" EnableCaching="True">
</asp:SqlDataSource>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactTitle]) VALUES (@CustomerID, @CompanyName, @ContactTitle)"
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactTitle] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>">
<DeleteParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
<InsertParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
</InsertParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
In the examples in this post, I don’t focus on performance optimization. One thing you can do to increase the performance, is to enable caching for the data source controls that will be used to fill the DropDownList controls. To enable caching you set the EnableCaching attribute of the data source to true. If you enable caching for the data source controls that is associated to a DropDownList, you maybe want the cache to be discarded if there have bean changes in the table where the data source gets its data from. This can be done by using the Sql cache dependency with the data source control.
