WebMatrix and jQuery Forms Part 2 - Editing Data

This article continues on from one I wrote a while ago, showing how to use jQuery to create a data entry form in conjunction with the WebGrid. The original article prompted a number of requests to show how to extend the example to provide editing functions, and now I have found some time to answer those requests.

The initial article uses a sample database I created which contains details of books and their authors. The example code in that article is quite straightforward. Once you have worked out how to use the jQuery Dialog, you use that to display a data entry form, and when the new book is added, you give the first row of the grid a highlight effect. The task of adding Edit features poses are few new challenges. For one thing, the dialog form must know which book's details it should display so that the user can alter them. Secondly, the items location in the grid (row and page) must be known so that the correct row can be highlighted.

The edit form works in the same way as the Add form, in that it is defined in the Default.cshtml file and the jQuery dialog command is what gives it life:

    <div id="dialog-form-edit" title="Edit Book">
    <form id="edit-book-form" action="@Href("~/Methods/UpdateBook")">
         <div class="row">
                <span class="label"><label for="title">Title:</label></span>
                <input type="text" name="title" id="edit-title" size="50" />
            </div>
            <div class="row">
                <span class="label"><label for="isbn">ISBN:</label></span>
                <input type="text" name="isbn" id="edit-isbn" size="20" />
            </div>
            <div class="row">
                <span class="label"><label for="description">Description:</label></span>
                <textarea cols="50" rows="8" name="description" id="edit-description"></textarea>
            </div>
            <div class="row">
                <span class="label"><label for="authorId">Author:</label></span>
                <select name="authorId" id="edit-authorId">
                    <option value="">-- Select Author --</option>
                @{
                    foreach(var author in authors){
                        if(author.AuthorId == Request["authorId"].AsInt()){
                            <option value="@author.AuthorId" selected="selected">@author.AuthorName</option>
                        } else {
                            <option value="@author.AuthorId">@author.AuthorName</option>
                        }
                    }
                }
                </select>
            </div>
            <div class="row">
                <span class="label"><label for="categoryId">Category:</label></span>
                <select name="categoryId" id="edit-categoryId">
                    <option value="">-- Select Category --</option>
                @{
                    foreach(var category in categories){
                        if(category.CategoryId == Request["categoryId"].AsInt()){
                            <option value="@category.CategoryId" selected="selected">@category.Category</option>
                        } else {
                            <option value="@category.CategoryId">@category.Category</option>
                        }        
                    }
                }
                </select>
            </div>
            <div class="row">
                <span class="label"><label for="datePublished">Date Published:</label></span>
                <input type="text" id="edit-datePublished" name="datePublished" />
                <input type="hidden" id="edit-bookId" name="bookId" />
            </div>    
        </form>
    </div>    

The form is pretty much identical to the Add Book form, except that the action has changed, and the id values for the form elements have been prefixed with "edit-". With a bit more effort, you could probably find a way to reuse the same form instead of creating a separate form for each operation. In the meantime, you need something to instantiate this form in a dialog, and it needs to know which book it should populate itself with so that you can amend the details. That is the job of the following piece of jQuery:

$('.edit-book').live('click', function(){
    $.getJSON('/Methods/GetBook/' + $(this).attr('id'), function(data){
        var book = data;
        $('#edit-title').val(book.Title);
        $('#edit-isbn').val(book.ISBN);
        $('#edit-description').val(book.Description);
        $('#edit-authorId').val(book.AuthorId);
        $('#edit-categoryId').val(book.CategoryId);
        var date = new Date(parseInt(book.DatePublished.substr(6)));
        var year = date.getFullYear();
        var month = date.getMonth() + 1;
        var day = date.getDate();
        $('#edit-datePublished').val(year + '-' + month + '-' + day);
        $('#edit-datePublished').datepicker({ dateFormat: 'yy-mm-dd' });
        $('#edit-bookId').val(book.BookId);
    });
    $('#dialog-form-edit').dialog('open');
});

This block of code looks for items with a class of edit-book, and attaches a click event handler to them. Those items are buttons in the first column of a revised WebGrid:

@grid.GetHtml(tableStyle: "ui-widget ui-widget-content",
              headerStyle : "ui-widget-header",
              columns : grid.Columns(
                grid.Column("", format: @<button class="edit-book" id="@item.BookId">Edit</button>),
                grid.Column("Title"),
                grid.Column("AuthorName",header : "Author"),
                grid.Column("Category"),
                grid.Column("ISBN")
                )    
              )

The buttons have the Id of the current book applied as their id attribute value. The preceding block of jquery uses live to bind the event handler. This ensures that all existing items - and all items that are created in the future - with the specified class have the event handler bound to them. This is important when you have AJAX paging enabled. When a button is clicked, the Id of the current book is passed to an AJAX call to GetBook.cshtml in the Methods folder. The content of that file is as follows:

@{
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    if(UrlData[0].IsInt()){
        var db = Database.Open("Books");
        var sql = "SELECT * FROM Books WHERE BookId = @0";
        var book = db.QuerySingle(sql,UrlData[0]);
        Json.Write(book, Response.Output);
    }
}

This code simply queries the database for details of the book which is to be edited, and whose Id was passed in the URL. The top line of code prevents the browser (especially IE) from caching the result, which is output using the JSON helper. Once the JSON has been received by the calling block of jQuery, it is used to populate the edit form, and finally the dialog command is used to invoke the form in a dialog box. The code that controls the edit form once it has been created is as follows:

$('#dialog-form-edit').dialog({
    autoOpen: false,
    modal: true,
    height: 425,
    width: 475,
    buttons: {
        'Edit Book' : function(){
            $.ajax({
                type: "POST",
                url: $("#edit-book-form").attr('action'),
                data: $("#edit-book-form").serialize(),
                dataType: "text/plain",
                success: function(response) {
                    $('#dialog-form-edit').dialog('close');
                    $("#grid").load('/Default/?page=' + page + ' #grid', function(){
                    var id = $('#edit-bookId').val();
                        $('#' + id).parent('td').parent('tr')
                        .effect("highlight", {}, 2000);
                    });
                },
                error: function(response) {
                    alert(response);
                    $('#dialog-form-edit').dialog('close');
                }
            });
        },
        Cancel: function() {
            $('#dialog-form-edit').dialog('close');
        }
    }// end buttons
});

This is much the same as the add form in the previous article. The data in the form is sent to Methods/UpdateBook, which takes care of ensuring changes are committed to the database:

@{
    if(IsPost){
        var db = Database.Open("Books");
        var sql = @"UPDATE Books SET Title = @0, ISBN = @1, Description = @2, AuthorId = @3, 
                    CategoryId = @4, DatePublished = @5 WHERE BookId = @6";
        var title = Request["title"];
        var isbn = Request["isbn"];
        var description = Request["description"];
        var authorId = Request["authorId"];
        var categoryId = Request["categoryId"];
        var datePublished = Request["datePublished"];
        var bookId = Request["bookid"];
        db.Execute(sql, title, isbn, description, authorId, categoryId, datePublished, bookId);
    }
}

If that operation was successful, the code makes an AJAX request for Default.cshtml (the page that the code is actually in) and passes a querystring value for the WebGrid page it wants to display. This value was obtained from the paging link via jQuery if one was used, otherwise it is assumed that the grid is still on page 1:

var page = 1;
$('tfoot a').live('click', function () {
    page = $(this).text();
});

Once the revised grid, complete with updated book details appears, the row containing the Id of the book that was updated is located, and the highlight effect is applied to the whole row.

The code is available as a sample site at GitHub