Actually, I'll look at adding data first. To add a new book to the database, we need to provide a title, ISBN number, description and we need to add an author and category. The book will acquire a BookId value automatically as a result of making the BookId field in the database an Identity column. The first 3 values require textboxes within a form:
<form action="" method="post"> <div class="row"> <span class="label"><label for="title">Title:</label></span> <input type="text" name="title" id="title" size="50" /> </div> <div class="row"> <span class="label"><label for="isbn">ISBN:</label></span> <input type="text" name="isbn" id="isbn" size="20" /> </div> <div class="row"> <span class="label"><label for="description">Description:</label></span> <textarea cols="50" rows="8" name="description" id="description"></textarea> </div>
So far so good. The author and category are not so straightforward. A selection of authors and categories already exist in the database in their own separate tables. The only part of tthe author or category record that's stored in the book record is the Primary Key value for the author or category, as a Foreign Key value. This relates the appropriate author or category record the the book record. So, when adding an author to a book, for example, all we need is the ID of the author. However, presenting a list of ID values to the user is not much use. How are they going to know which Id belongs to which author? The answer is to use an html <select> element, also known in ASP.NET circles as a dropdown list. A select element contains one or more <option> elements. Each option has a value attribute (which is used to hold the ID), and it includes some text which defines the options available to the user in a more friendly way. In the case of authors, a combination of first name and last name will probably be the most helpful way to do this. So, in the code area at the top of the page, we need to define a Database object, and then query it for the data for the select list:
var db = Database.Open("Books"); var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");
While the SQL looks a little busy, it only returns two values - the AuthorId for each author, and the first name and last name concatenated with a space between, and delivered under the alias "AuthorName". The thing to do next is to apply these to the select list
<div class="row"> <span class="label"><label for="authorId">Author:</label></span> <select name="authorId" id="authorId"> <option value="">-- Select Author --</option> @{ foreach(var author in authors){ <option value="@author.AuthorId">@author.AuthorName</option> } } </select> </div>
The first option is the default one, and is given no value at all. After that, each option tag is given the ID belonging to the matching author in the list. When the form is submitted, only the ID value is posted back (which is all we are after). The same process is followed for applying a select list of categories:
<div class="row"> <span class="label"><label for="categoryId">Category:</label></span> <select name="categoryId" id="categoryId"> <option value="">-- Select Category --</option> @{ foreach(var category in categories){ <option value="@category.CategoryId">@category.Category</option> } } </select> </div>
Finally the form is finished off:
<div class="row"> <span class="label"> </span> <input type="submit" name="action" id="action" value="Add" /> </div> </form>
When the form is posted back, the values entered by the user are gathered together and inserted into the database:
if (IsPost){ var sql = "INSERT INTO Books (Title, ISBN, Description, AuthorId, CategoryId) VALUES (@0, @1, @2, @3, @4)"; db.Execute(sql, Request["title"], Request["isbn"], Request["description"], Request["authorId"], Request["categoryId"]); }
Notice the values @0, @1 etc in the SQL? They are parameter placeholders, and protet against SQL injection, which is an attempt to pass malicious code to the database. When the Database.Execute() method is called, the SQL is passed in first, followed by the source of the parameter values. In this case, all of the values come from the Request.Form collection (although the shorthand version Request[index] is used. Items are referenced by their index, which is the name of the form field. The values are passed in to the method in the same order that their parameters appear in the SQL.
Editing an existing book requires an almost identical form. However, the form needs to know which book is being edited. This detail is passed in the URL to the EditBook.cshtml page. Links are created in the listing page (Default.cshtml):
foreach(var row in db.Query(sql, Request["CategoryID"])){ <h2>@row.Title</h2> <p><strong>Author:</strong> @row.FirstName @row.LastName<br /> <strong>ISBN:</strong> @row.ISBN <br/> <strong>Description:</strong> @row.Description <br /> <strong>Category: </strong> @row.Category</p> <a href="@Href("~/EditBook", row.BookId)">Edit</a> }
The last line of code shows the Href() helper being used to link to EditBook.cshtml using routing, so the file suffix is not needed, and the Id of the book is appended like this: EditBook/5, or EditBook/7 etc. This value is drawn from the URL using the UrlData() helper and used as a parameter for the SQL that gets the specified book's details, along with the data for the authors and categories drop down lists:
var db = Database.Open("Books"); var Id = UrlData[0].AsInt(); var sql = "SELECT Title, ISBN, Description, AuthorId, CategoryId FROM Books WHERE BookId = @0"; var book = db.QuerySingle(sql, Id); var categories = db.Query("SELECT CategoryId, Category FROM Categories"); var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");
The form itself presents the book to be edited:
<form action="" method="post"> <div class="row"> <span class="label"><label for="title">Title:</label></span> <input type="text" name="title" id="title" value="@book.Title" size="50" /> </div> <div class="row"> <span class="label"><label for="isbn">ISBN:</label></span> <input type="text" name="isbn" id="isbn" value="@book.ISBN" size="20" /> </div> <div class="row"> <span class="label"><label for="description">Description:</label></span> <textarea cols="50" rows="8" name="description" id="description">@book.Description</textarea> </div> <div class="row"> <span class="label"><label for="authorId">Author:</label></span> <select name="authorId" id="authorId"> @{ foreach(var author in authors){ if(author.AuthorId == book.AuthorId){ <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="categoryId"> @{ foreach(var category in categories){ if(category.CategoryId == book.CategoryId){ <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"> </span> <input type="submit" name="action" id="action" value="Edit" /> </div> </form>
Within the code for both the author and category dropdowns, the Id of the book to be edited is compared with each Id of the author or category, and if there is a match, selected="selected" is added as an attribute to the option. By the time WebMatrix is released fully, there may be helpers for drop down lists, but this is as good as it gets within Beta 1. Now we have a form that gets the selected book and displays it for editing, but nothing happens if the user clicks submit. The code block needs to be changed at the top of the file to update the selected book with the new values, and then redisplay it, so it now looks like this:
var db = Database.Open("Books"); var Id = UrlData[0].AsInt(); var sql = ""; if (IsPost) { sql = "UPDATE Books SET Title = @0, ISBN = @1, Description = @2, AuthorId = @3, CategoryId = @4 WHERE BookId = @5"; db.Execute(sql, Request["title"], Request["isbn"], Request["description"], Request["authorId"], Request["categoryId"], Id); } sql = "SELECT Title, ISBN, Description, AuthorId, CategoryId FROM Books WHERE BookId = @0"; var book = db.QuerySingle(sql, Id); var categories = db.Query("SELECT CategoryId, Category FROM Categories"); var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");
A new code test has been applied if the page IsPost(), and in that, the values are obtained from the Request collection and passed in to the SQL to update the database. Then the code continues as before, retrieving the selected book for redisplay with the updates.
