Click here to Skip to main content
Click here to Skip to main content
Go to top

Using the Insert feature of SqlDataSource with Gridviews

, 29 Mar 2007
Rate this:
Please Sign up or sign in to vote.
Explains insert command and the advantages and disadvantages of the insert feature of .NET 2.0's SqlDataSource

Screenshot - NorthwindInsert.gif

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

pistasio

United States United States
No Biography provided

Comments and Discussions

 
GeneralError. Pls help me out Pinmembernraji.lak23-Mar-09 22:31 
GeneralRemove OrderId Repetition Pinmembersukkureddy20-Aug-08 23:50 
QuestionHow to avoid header text for all child Gridviews Pinmembersukkureddy20-Aug-08 23:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 29 Mar 2007
Article Copyright 2007 by pistasio
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid