Using the Insert feature of SqlDataSource with Gridviews

• Download source files - 3.0 KB

Introduction

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 Gridview and 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.

Background

For this demo, I'm extending Microsofts Nested Gridview walkthrough to include the ability to add orders to the customer row. 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 ShowInsertButton 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.

<asp:TemplateField HeaderText="Orders"> 
    <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,
            OrderDate)VALUES(@CustomerID,GETDATE())" 
        UpdateCommand="UPDATE Orders SET [OrderDate] = 
            @OrderDate WHERE ([CustomerID] = @CustomerID)"> 
            <SelectParameters> 
                <asp:Parameter Name="CustomerID" Type="String" /> 
            </SelectParameters> 
            <DeleteParameters> 
                <asp:Parameter Name="OrderID" /> 
            </DeleteParameters> 
            <UpdateParameters> 
                <asp:Parameter Name="OrderDate" /> 
                <asp:Parameter Name="CustomerID" /> 
            </UpdateParameters> 
            <InsertParameters> 
                <asp:Parameter Name="CustomerID" /> 
            </InsertParameters> 
              </asp:SqlDataSource>
            <asp:GridView ID="GridView2" HeaderStyle-BackColor=
                "dodgerblue" HeaderStyle-ForeColor="white" runat="server" 
            AutoGenerateColumns="False" DataKeyNames=
                "OrderID" DataSourceID="SqlDataSource2"> 
            <Columns> 
                <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" />
            </Columns> 
        </asp:GridView> 
        <asp:Button ID="btnAddOrder" Text="Add Order" OnCommand=
            "GridView1_AddOrder" CommandName="AddOrder" 
            CommandArgument='<%#DataBinder.Eval(Container.DataItem,
                "CustomerID")%>' runat="server" /> 
    </ItemTemplate> 
</asp:TemplateField>

The native functionality of the insert comand is provided in the GridView's <asp:CommandField> 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 "Add Order" Button in the row and handled the CommandEvent in the code below.

protected void GridView1_AddOrder(object sender, 
    System.Web.UI.WebControls.CommandEventArgs e)
{
    if (e.CommandName == "AddOrder")
    {
        String strCustomerNumber = e.CommandArgument.ToString();

        // The "Add Order" button is in the same- cell as the 

        // GridView and SqlDataSource Controls.

        DataControlFieldCell controlParent = 
            (DataControlFieldCell)((Button) sender).Parent;

        // Insert the new order

        SqlDataSource s = (SqlDataSource)cell.FindControl("SqlDataSource2");
        s.InsertParameters[0].DefaultValue = strCustomerNumber;
        s.Insert();

        // After the order has been inserted, put the order in edit mode.

        GridView gv = (GridView) cell.FindControl("GridView2");
        gv.EditIndex = gv.Rows.Count;
        gv.DataBind();
    }
}

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:

1. the column variables are known upfront (writing an audit table, log or tracking visits)
2. the user is in control of adding and deleting records from the table and can remove an accidental/incorrect entries at will
3. 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.

History

This demo was uploaded March 2007.