Recently while working on a order processing system, I needed a quick and dirty method
to insert rows of a child table. I thought this would be the ideal time to
use .NET 2.0's
SqlDataSource feature set. While I quickly picked up on the update,
delete, and select capabilites, the insert functionality didn't appear to work.
A search of the internet uncovered only disclaimers and workarounds, including the
templated column footer that I had used with 1.1 Datagrids. What I needed was
an expanation of how to use the SqlDatSource's insert fuctionality with my gridview.
This article details the advantages and disadvantages of using this feature.
For this demo, I'm extending Microsofts
Nested Gridview walkthrough to include the ability to add orders to the
One issue with using
SqlDataSource insert with a
Gridview is that the gridview
control does not allow for the direct addition of a bound row. Another is the
feature of asp:CommandField control assumes that your gridview has at least one row.
I solved these issues by setting the
ShowInsertButton to false and creating a Command
Button to fire the insert event.
Using the code
In this scenario, I'm including the ability to insert, update and delete a order
for a customer. Below is the
SqlDataSource for the child table Orders, the child
GridView and "
Add Order" button control.
<ItemTemplate> <asp:templatefield headertext="Orders"><itemtemplate />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [OrderID], [OrderDate] FROM [Orders]
WHERE ([CustomerID] = @CustomerID)"
DeleteCommand="DELETE FROM ORDERS WHERE ([OrderID] = @OrderID)"
InsertCommand="INSERT INTO ORDERS (CustomerID,
UpdateCommand="UPDATE Orders SET [OrderDate] =
@OrderDate WHERE ([CustomerID] = @CustomerID)">
<asp:Parameter Name="CustomerID" Type="String" />
<asp:Parameter Name="OrderID" />
<asp:Parameter Name="OrderDate" />
<asp:Parameter Name="CustomerID" />
<asp:Parameter Name="CustomerID" />
<asp:GridView ID="GridView2" HeaderStyle-BackColor=
"dodgerblue" HeaderStyle-ForeColor="white" runat="server"
<asp:BoundField DataField="OrderID" HeaderText=
"OrderID" InsertVisible="False" ReadOnly=
"True" SortExpression="OrderID" />
<asp:BoundField DataField="OrderDate" HeaderText=
"OrderDate" SortExpression="OrderDate" />
<asp:CommandField ShowInsertButton="False" ShowEditButton=
"True" ShowDeleteButton="True" />
<asp:Button ID="btnAddOrder" Text="Add Order" OnCommand=
"CustomerID")%>' runat="server" />
The native functionality of the insert comand is provided in the GridView's
control. However, this control is bound to a row, and a table with
no rows will prevent you from adding one. For this reason, I included a "
Button in the row and handled the CommandEvent in the code below.
protected void GridView1_AddOrder(object sender,
if (e.CommandName == "AddOrder")
String strCustomerNumber = e.CommandArgument.ToString();
DataControlFieldCell controlParent =
SqlDataSource s = (SqlDataSource)cell.FindControl("SqlDataSource2");
s.InsertParameters.DefaultValue = strCustomerNumber;
GridView gv = (GridView) cell.FindControl("GridView2");
gv.EditIndex = gv.Rows.Count;
Points of Interest
The advantage of using the built-in insert capability is that you can get your code
inserting, updating and deleting from your data source working very quickly. The disadvantage is that adding a row to the datasource will require you to add a blank record to your table unless you
can provide the column values upfront. Most of the time, you'll only want to write
a record when the user confirms his entry.
There are three scenarios I can think of when you might allow this insert behavior:
- the column variables are known upfront (writing an audit table, log or tracking
- the user is in control of adding and deleting records from the table
and can remove an accidental/incorrect entries at will
- prototyping or agile
development where delivery the insert capability is included early and optimized
later for database usage.
For my projects needs, the table additions are projected to be small (approximately
40 per month) and the users are trusted to add and delete records at will. Minimizing development time was the determining factor in selecting this method. As the UI matures,
I may need to include a template columns solution, including substituting textboxes
with dropdown controls. But for quick prototyping, it's nice to let the native .NET
conventions do the work for you.
This demo was uploaded March 2007.