Google

Sunday, November 11, 2007

Adding client side insert update delete functionality to a Gridview

All of us have, at some point or the other, worked with providing Insert / Update / Delete functionality in a datagrid, and you can find quite a few good articles on this topic. Each approach uses some sort of server side code when modifying rows of a datagrid. There is nothing wrong with this as such, but it could become frustrating for the user because of the continous round trips to the server when modifying rows. We have decided to start a series of tutorials explaining the task of building up an client side insert / update / delete gridview.

We have split the operation of dynamically adding rows to a gridview into two phases : 1. Create a javascript function that dynamically adds a new row to the gridview. 2. Create a button on your page that calls this function when clicked.

Simple, huh. To get started, create an aspx page as below, and populate the gridview with records from the AdventureWorks.Production.WorkOrder table (You can use any other database, but then you would have to modify the code below to work with the columns in your database). Please be sure of the following factors when you create the gridview : a) The query for your SQLDataSource should be the following : "select top 10 * from Production.WorkOrder" This will limit the number of rows returned, and save you time unnecessarily waiting for the page to load, in addition to putting less burden on your database. b) Disable paging for the gridview, so that we do not have a pager at the bottom of our gridview. c) Make sure that you are not using master pages, or the gridview is not part of a user control. This is because when a control is placed inside a parent control, user control, or a master page, it's HTML id is prepended with some string identifying the parent. More on this in a later post. If you do not know how to do this, please refer to this documentation on msdn to make sure the above simple conditions are met. Note that we have also added an html button named "btnAddRow" with runat = "server" set. Once done, here's what our page looks like.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <input type="button" id="btnAddRow" value="Add" runat="server" />
    <div>
        <asp:GridView ID="myGridView" runat="server" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Modify
                    </HeaderTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT top 10 * FROM [Production].[WorkOrder]"></asp:SqlDataSource>
    </div>
    </div>
    </form>
</body>
</html>

Test the page by running it once, the output should show 10 rows from the AdventureWorks.Production.WorkOrder database table.

Note that I have added a template column as the first column, so that the first column always shows empty when the gridview is rendered on browser. This column has been intentionally left blank so we can show the insert / update / delete buttons.

Next, take a moment to inspect the HTML code of your application. You can do this by right clicking on your page and choosing "View Source". Our gridview now looks like this (text clipped for brevity) :

<div>
    <table cellspacing="0" rules="all" border="1" id="myGridView" style="border-collapse:collapse;">
        <tr>
            <th scope="col">
                        Modify
                    </th><th scope="col">WorkOrderID</th><th scope="col">ProductID</th><th scope="col">OrderQty</th><th scope="col">StockedQty</th><th scope="col">ScrappedQty</th><th scope="col">StartDate</th><th scope="col">EndDate</th><th scope="col">DueDate</th><th scope="col">ScrapReasonID</th><th scope="col">ModifiedDate</th>
        </tr><tr>
            <td>&nbsp;</td><td>1</td><td>722</td><td>8</td><td>8</td><td>0</td><td>7/4/2001 12:00:00 AM</td><td>7/14/2001 12:00:00 AM</td><td>7/15/2001 12:00:00 AM</td><td>&nbsp;</td><td>7/14/2001 12:00:00 AM</td>
    </table>
</div>
This reiterates the fact that our gridview is actually rendered as an html table in the user's browser. It also means that if we want to dynamically add some rows to the gridview, we are actually adding rows to an HTML table. Our goal is that when the user clicks on the "Add" button that we created in our aspx page, a new row should be dynamically added to the gridview. For this, I have created a short pseudocode which we could base our code upon.
{
    // 1. Get the <table> element corresponding to the gridview from the page's DOM.

    // 2. Insert a new row in the table fetched in step 1.

    // 3. Insert an empty cell for the first "Modify" column

    // 4. Insert an empty cell for "WorkOrderID" in the row created in step 2. above.
        // In the cell created above, add a textbox in which the user will input the workorderID value he wishes to insert.
    // 5. Insert an empty cell for ProductID in the row created above.
        // In the cell created above, add a textbox in which the user will input the ProductID value he wishes to insert.
    // 6. Insert an empty cell for OrderQty in the row created above.
        // In the cell created above, add a textbox in which the user will input the OrderQty value he wishes to insert.
    .
    .
    .
    .
    // N. Insert cells similar to the above for the remaining columns (StockedQty,ScrappedQty,StartDate, EndDate, DueDate, ScrapReasonID, ModifiedDate)
}

So, basically we're getting the table object from the DOM, and adding a new row and new cells dynamically using javascript. Before you look at the code, recall that you can add a child element to a parent element using the parentElement.appendChild(childElement) method. But if you are adding a new table row (child), to a table (parent), you can directly use the tableName.insertRow() method. Similarly, to add a new table cell to a row, use the tableRow.insertCell() method. Let us see the code now :

            function fn_AddFilterRow()
            {
        // 1. Get the <table> element corresponding to the gridview from the document
            var table = document.getElementById('myGridView');

        // 2. Insert a new row in the table fetched in step 1.
                var newRow = table.insertRow();

        // 3. Insert an empty cell for the first "Modify" column
                // Column 1 : An empty cell for the "Modify" column
                var newCell = newRow.insertCell();
                newCell.appendChild(btnDelete);

        // 4. Insert an empty cell for "WorkOrderID" in the row created in step 2. above.                                    
                // Column 1 : WorkOrderID
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the  workorderID value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);
    
        // 5. Insert an empty cell for ProductID in the row created above.
                // Column 2 : ProductID 
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the ProductID value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);

        // 6. Insert an empty cell for OrderQty in the row created above.                                
                // Column3 : OrderQty
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the OrderQty value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);
        .
        .
        .
        .
        .
        .
        .
        // Do the same for the remaining columns.

        }

The last step here is to write some code so that when the button "btnAddRow" gets clicked , our javascript function fn_AddFilterRow gets called. This is easy. Go to your code behind page. In the Page_Load method, add the following line

 btnAddRow.Attributes.Add("onclick", "fn_AddFilterRow();");

That's it. Let it rip, and see for yourself. Launch the page in IE, and click on the button to add a new row to the gridview.

Note that each time you click on the button, you'd get a new row added to the gridview, which is pretty cool if users want to add multiple rows at a time.

Okay, that's half the battle won. The other half is accessing the contents of the newly inserted row on the server side, and eventually inserting the row into the database. We shall discuss that in our next post.

Just so you know, the above is a very basic implementation of the client side editing capability. Notably, you might want to experiment a little bit to fix the following flaws 1. The new row always comes in as a standard with no formatting applied. But if the table has some formatting applied, your new row should also have the same color, font size, alignment etc. as the other gridview rows. 2. Currently we're adding textboxes for all columns in the new row. But there may be situations where you would want the user to choose from a dropdown list, or a checkbox (e.g. a column for sex can have values Male / Female / Yes, Please ;) ). Our gridview does not handle this. 3. If there was a footer/pager present, the code would have to be adjusted. The reason being the new row is always added after the last row of the <table&gr;. The new row should ideally be added just above the footer, not below it. 4. Code does not handle the situation where the gridview is empty, i.e. no rows are being displayed.

Nevertheless, this is a good starting point and you can definitely build up on the fundamentals you learnt here. In the next post, we'll complete the circle by persisting the row entries we added here to the database.

4 comments:

Anonymous said...

Just keep in mind with this that IE has major issues setting attributes like "name" on an input element. (not in your example)

http://webbugtrack.blogspot.com/2007/08/bug-242-setattribute-doesnt-always-work.html

What was a simple bit of code, becomes a nightmare to manage if you need to support other browsers besides Firefox and Safari.

Unknown said...

I would like to say a big
"THANKS" to u, u r article shown me a very good solution to my project
jugs

robin said...

Thanks!

I'm looking for the update part, on how to modify an existing row.
Anyone got a sample or instruction for that?

Cheers

Kolossalservices said...

Very gd work you have done. keep is up.
Don't use add attribute it will not support in IE, So assign events static.