Web Development 101: Part 8, Creating a Delete Data page

So you've reached the point now where you have created a data-driven list of favorite movies, styled it, and added the ability to add new movies and edit existing movies in the database. The next step in creating this application is to give your users the ability to delete records from the database.

You may have heard of a term called CRUD used when applied to web applications development and data. CRUD stands for Create Retrieve Update Delete, and that's precisely what you've built using WebMatrix.

Add the Delete Page

To start, create a new CSHTML page and call it DeleteMovie.cshtml.

Replace the HTML in it with the following:

<h1>Delete a movie</h1>
<p>Are you sure you want to delete the movie <strong>@Movie.Name ?</strong></p>
<form action="" method="post">
    <input type="submit" value="Yes"/>
    <input type="button" value="No" onclick="window.location = 'dataMovies.cshtml'" />
</form>
 
  

This creates a basic form containing 2 buttons, a submit button that triggers an HTTP POST (just like the edit page in the last chapter), and another button that redirects you back to the movies list when clicked.

Just like the EditMovie.cshtml page, this page will be called and passed a parameter which is the ID of the movie to delete. So, as you can see the text reads 'Are you sure you want to delete the movie @Movie.Name?' where the value of Movie.Name will be inserted by the server. So, we need to tell the server how to get this value:

To do that, as before, you put some Razor code at the top of the page to catch the input parameter.

 

   1:  @{
   2:   
   3:  var id=Request["id"];
   4:   
   5:  var SQLSELECT = "SELECT * FROM Favorites where ID=@0";
   6:   
   7:  var db = Database.Open("Movies");
   8:   
   9:  var Movie = db.QuerySingle(SQLSELECT,id);
  10:   
  11:  var MovieName=Movie.Name;
  12:   
  13:  }
  14:   

Here you can see that the parameter was passed to the page as 'id' (using DeleteMovie.cshtml?id=<whatever>), and this is used to find the specific movie. A query is run against the database, and the record for that movie is retrieved. Now you can derive the name of that movie, and render it when the page is rendered.

 

Give it a try, run DeleteMovie.cshtml?id=<something>, and you'll see this screen, provided the <something> is a valid ID in your database:

clip_image001

If you press the 'No' button, you'll be redirected back to the dataMovies.cshtml page. If you press 'Yes', nothing happens, because you haven't written the code to handle the deletion yet.

To Delete a record from a database, you use the DELETE SQL command. This has the following syntax

DELETE FROM <Table> WHERE <Field>=<Value>

So, if we wanted to delete the movie with the id=2, we could say:

DELETE FROM Favorites WHERE ID=2

When the user presses the 'Yes' button, the form will be submitted and the deletion will occour, so we can easily do this on the postback as follows:

 

 

   1:  if(IsPost){
   2:   
   3:  var SQLDELETE = "DELETE FROM Favorites WHERE ID=@0";
   4:   
   5:  db.Execute(SQLDELETE,id);
   6:   
   7:  Response.Redirect("dataMovies.cshtml");
   8:   
   9:  }
  10:   

This will delete the movie, and redirect us back to the listing page so that we can see it's gone.

 

Here's the full listing for DeleteMovie.cshtml:

   1:  @{
   2:   
   3:  var id=Request["id"];
   4:   
   5:  var SQLSELECT = "SELECT * FROM Favorites where ID=@0";
   6:   
   7:  var db = Database.Open("Movies");
   8:   
   9:  var Movie = db.QuerySingle(SQLSELECT,id);
  10:   
  11:  var MovieName=Movie.Name;
  12:   
  13:  if(IsPost){
  14:   
  15:  var SQLDELETE = "DELETE FROM Favorites WHERE ID=@0";
  16:   
  17:  db.Execute(SQLDELETE,id);
  18:   
  19:  Response.Redirect("dataMovies.cshtml");
  20:   
  21:  }
  22:   
  23:  }
  24:   
  25:  <h1>Delete a movie</h1>
  26:   
  27:  <p>Are you sure you want to delete the movie <strong>@Movie.Name ?</strong></p>
  28:   
  29:  <form action="" method="post">
  30:   
  31:  <p><input type="submit" value="Yes"/>
  32:   
  33:  <input type="button" value="No" onclick="window.location = 'dataMovies.cshtml'" /></p>
  34:   
  35:  <br/>
  36:   
  37:  </form>
  38:   

Calling the Delete Page from the Movies List

Now that we have our working delete page, let's wire it up to the movies list page so that the user can select an item from that list, and request to delete it.

On the movies list page, we simply have to add a hyperlink to each list entry, where the hyperlink is to the DeleteMovie.cshtml page, passing it the id of the current movie.

Here's the complete code for dataMovies.cshtml:

   1:  @{
   2:   
   3:  var db= Database.Open("Movies");
   4:   
   5:  var sqlQ = "SELECT * FROM Favorites";
   6:   
   7:  var data = db.Query(sqlQ);
   8:   
   9:  }
  10:   
  11:  <div id="movieslist">
  12:   
  13:  <ol>
  14:   
  15:  @foreach(var row in data){
  16:   
  17:  <li>
  18:   
  19:  <a href="EditMovie.cshtml?id=@row.id">@row.Name, @row.Genre, @row.ReleaseYear
  20:   
  21:  </a>
  22:   
  23:  <a href="DeleteMovie.cshtml?id=@row.id">Delete</a>
  24:   
  25:  </li>
  26:   
  27:  }
  28:   
  29:  </ol>
  30:   
  31:  <a href="AddMovie.cshtml">Add a new movie</a>
  32:   
  33:  </div>
  34:   

Run this page and see the workflow for deleting. First, here's the new dataMovies.cshtml:

clip_image003

If you now select the delete link on any of these movies, you'll be taken to the 'Delete a Movie' page, where you'll be asked if you really want to delete that movie from the list:

clip_image004

If you press 'No', you'll be returned to the list with the movie still there. If you say yes, then you'll be returned to the list and the movie will be gone. Here's the result of saying 'yes'.

clip_image005

Summary

Well done! You've created a full application that has Create, Retrieve, Update and Delete functionality on a database, and which uses server side code to dynamically generate HTML. It sounds a lot more complex than it was, right?

You can learn a lot more about the Razor syntax, and how to build applications using it at the http://www.asp.net/webmatrix/fundamentals site.

In the next, and final step, you'll publish your work to the Internet, and you'll see how with WebMatrix you'll be able to keep your development machine and production server synchronized!

Continue to Part 9, where you will learn how to publish this web site to the internet!

You can discuss this article using the adjacent Facebook talkback.

For technical questions please visit our discussion forums, where we have a vibrant community of developers like you, as well as Microsoft engineers who are ready to answer your questions!