Using jQuery to Insert a New Database Record
- by Stephen Walther
The goal of this blog entry is to explore the easiest way of inserting a new record into a database using jQuery and .NET. I’m going to explore two approaches: using Generic Handlers and using a WCF service (In a future blog entry I’ll take a look at OData and WCF Data Services). Create the ASP.NET Project I’ll start by creating a new empty ASP.NET application with Visual Studio 2010. Select the menu option File, New Project and select the ASP.NET Empty Web Application project template. Setup the Database and Data Model I’ll use my standard MoviesDB.mdf movies database. This database contains one table named Movies that looks like this: I’ll use the ADO.NET Entity Framework to represent my database data: Select the menu option Project, Add New Item and select the ADO.NET Entity Data Model project item. Name the data model MoviesDB.edmx and click the Add button. In the Choose Model Contents step, select Generate from database and click the Next button. In the Choose Your Data Connection step, leave all of the defaults and click the Next button. In the Choose Your Data Objects step, select the Movies table and click the Finish button. Unfortunately, Visual Studio 2010 cannot spell movie correctly :) You need to click on Movy and change the name of the class to Movie. In the Properties window, change the Entity Set Name to Movies. Using a Generic Handler In this section, we’ll use jQuery with an ASP.NET generic handler to insert a new record into the database. A generic handler is similar to an ASP.NET page, but it does not have any of the overhead. It consists of one method named ProcessRequest(). Select the menu option Project, Add New Item and select the Generic Handler project item. Name your new generic handler InsertMovie.ashx and click the Add button. Modify your handler so it looks like Listing 1: Listing 1 – InsertMovie.ashx using System.Web;
namespace WebApplication1 {
/// <summary>
/// Inserts a new movie into the database
/// </summary>
public class InsertMovie : IHttpHandler {
private MoviesDBEntities _dataContext = new MoviesDBEntities();
public void ProcessRequest(HttpContext context) {
context.Response.ContentType = "text/plain";
// Extract form fields
var title = context.Request["title"];
var director = context.Request["director"];
// Create movie to insert
var movieToInsert = new Movie { Title = title, Director = director };
// Save new movie to DB
_dataContext.AddToMovies(movieToInsert);
_dataContext.SaveChanges();
// Return success
context.Response.Write("success");
}
public bool IsReusable {
get {
return true;
}
}
}
}
In Listing 1, the ProcessRequest() method is used to retrieve a title and director from form parameters. Next, a new Movie is created with the form values. Finally, the new movie is saved to the database and the string “success” is returned.
Using jQuery with the Generic Handler
We can call the InsertMovie.ashx generic handler from jQuery by using the standard jQuery post() method. The following HTML page illustrates how you can retrieve form field values and post the values to the generic handler:
Listing 2 – Default.htm
<!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>
<title>Add Movie</title>
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.js" type="text/javascript"></script>
</head>
<body>
<form>
<label>Title:</label>
<input name="title" />
<br />
<label>Director:</label>
<input name="director" />
</form>
<button id="btnAdd">Add Movie</button>
<script type="text/javascript">
$("#btnAdd").click(function () {
$.post("InsertMovie.ashx", $("form").serialize(), insertCallback);
});
function insertCallback(result) {
if (result == "success") {
alert("Movie added!");
} else {
alert("Could not add movie!");
}
}
</script>
</body>
</html>
When you open the page in Listing 2 in a web browser, you get a simple HTML form:
Notice that the page in Listing 2 includes the jQuery library. The jQuery library is included with the following SCRIPT tag:
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.js" type="text/javascript"></script>
The jQuery library is included on the Microsoft Ajax CDN so you can always easily include the jQuery library in your applications. You can learn more about the CDN at this website:
http://www.asp.net/ajaxLibrary/cdn.ashx
When you click the Add Movie button, the jQuery post() method is called to post the form data to the InsertMovie.ashx generic handler. Notice that the form values are serialized into a URL encoded string by calling the jQuery serialize() method. The serialize() method uses the name attribute of form fields and not the id attribute.
Notes on this Approach
This is a very low-level approach to interacting with .NET through jQuery – but it is simple and it works! And, you don’t need to use any JavaScript libraries in addition to the jQuery library to use this approach.
The signature for the jQuery post() callback method looks like this:
callback(data, textStatus, XmlHttpRequest)
The second parameter, textStatus, returns the HTTP status code from the server. I tried returning different status codes from the generic handler with an eye towards implementing server validation by returning a status code such as 400 Bad Request when validation fails (see http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html ). I finally figured out that the callback is not invoked when the textStatus has any value other than “success”.
Using a WCF Service
As an alternative to posting to a generic handler, you can create a WCF service.
You create a new WCF service by selecting the menu option Project, Add New Item and selecting the Ajax-enabled WCF Service project item. Name your WCF service InsertMovie.svc and click the Add button.
Modify the WCF service so that it looks like Listing 3:
Listing 3 – InsertMovie.svc
using System.ServiceModel;
using System.ServiceModel.Activation;
namespace WebApplication1 {
[ServiceBehavior(IncludeExceptionDetailInFaults=true)]
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class MovieService {
private MoviesDBEntities _dataContext = new MoviesDBEntities();
[OperationContract]
public bool Insert(string title, string director) {
// Create movie to insert
var movieToInsert = new Movie { Title = title, Director = director };
// Save new movie to DB
_dataContext.AddToMovies(movieToInsert);
_dataContext.SaveChanges();
// Return movie (with primary key)
return true;
}
}
}
The WCF service in Listing 3 uses the Entity Framework to insert a record into the Movies database table. The service always returns the value true.
Notice that the service in Listing 3 includes the following attribute:
[ServiceBehavior(IncludeExceptionDetailInFaults=true)]
You need to include this attribute if you want to get detailed error information back to the client. When you are building an application, you should always include this attribute. When you are ready to release your application, you should remove this attribute for security reasons.
Using jQuery with the WCF Service
Calling a WCF service from jQuery requires a little more work than calling a generic handler from jQuery. Here are some good blog posts on some of the issues with using jQuery with WCF:
http://encosia.com/2008/06/05/3-mistakes-to-avoid-when-using-jquery-with-aspnet-ajax/
http://encosia.com/2008/03/27/using-jquery-to-consume-aspnet-json-web-services/
http://weblogs.asp.net/scottgu/archive/2007/04/04/json-hijacking-and-how-asp-net-ajax-1-0-mitigates-these-attacks.aspx
http://www.west-wind.com/Weblog/posts/896411.aspx
http://www.west-wind.com/weblog/posts/324917.aspx
http://professionalaspnet.com/archive/tags/WCF/default.aspx
The primary requirement when calling WCF from jQuery is that the request use JSON:
The request must include a content-type:application/json header.
Any parameters included with the request must be JSON encoded.
Unfortunately, jQuery does not include a method for serializing JSON (Although, oddly, jQuery does include a parseJSON() method for deserializing JSON). Therefore, we need to use an additional library to handle the JSON serialization.
The page in Listing 4 illustrates how you can call a WCF service from jQuery.
Listing 4 – Default2.aspx
<!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>
<title>Add Movie</title>
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.js" type="text/javascript"></script>
<script src="Scripts/json2.js" type="text/javascript"></script>
</head>
<body>
<form>
<label>Title:</label>
<input id="title" />
<br />
<label>Director:</label>
<input id="director" />
</form>
<button id="btnAdd">Add Movie</button>
<script type="text/javascript">
$("#btnAdd").click(function () {
// Convert the form into an object
var data = { title: $("#title").val(), director: $("#director").val() };
// JSONify the data
data = JSON.stringify(data);
// Post it
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "MovieService.svc/Insert",
data: data,
dataType: "json",
success: insertCallback
});
});
function insertCallback(result) {
// unwrap result
result = result["d"];
if (result === true) {
alert("Movie added!");
} else {
alert("Could not add movie!");
}
}
</script>
</body>
</html>
There are several things to notice about Listing 4. First, notice that the page includes both the jQuery library and Douglas Crockford’s JSON2 library:
<script src="Scripts/json2.js" type="text/javascript"></script>
You need to include the JSON2 library to serialize the form values into JSON. You can download the JSON2 library from the following location:
http://www.json.org/js.html
When you click the button to submit the form, the form data is converted into a JavaScript object:
// Convert the form into an object
var data = { title: $("#title").val(), director: $("#director").val() };
Next, the data is serialized into JSON using the JSON2 library:
// JSONify the data
var data = JSON.stringify(data);
Finally, the form data is posted to the WCF service by calling the jQuery ajax() method:
// Post it
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "MovieService.svc/Insert",
data: data,
dataType: "json",
success: insertCallback
});
You can’t use the standard jQuery post() method because you must set the content-type of the request to be application/json. Otherwise, the WCF service will reject the request for security reasons. For details, see the Scott Guthrie blog post:
http://weblogs.asp.net/scottgu/archive/2007/04/04/json-hijacking-and-how-asp-net-ajax-1-0-mitigates-these-attacks.aspx
The insertCallback() method is called when the WCF service returns a response. This method looks like this:
function insertCallback(result) {
// unwrap result
result = result["d"];
if (result === true) {
alert("Movie added!");
} else {
alert("Could not add movie!");
}
}
When we called the jQuery ajax() method, we set the dataType to JSON. That causes the jQuery ajax() method to deserialize the response from the WCF service from JSON into a JavaScript object automatically. The following value is passed to the insertCallback method:
{"d":true}
For security reasons, a WCF service always returns a response with a “d” wrapper. The following line of code removes the “d” wrapper:
// unwrap result
result = result["d"];
To learn more about the “d” wrapper, I recommend that you read the following blog posts:
http://encosia.com/2009/02/10/a-breaking-change-between-versions-of-aspnet-ajax/
http://encosia.com/2009/06/29/never-worry-about-asp-net-ajaxs-d-again/
Summary
In this blog entry, I explored two methods of inserting a database record using jQuery and .NET. First, we created a generic handler and called the handler from jQuery. This is a very low-level approach. However, it is a simple approach that works.
Next, we looked at how you can call a WCF service using jQuery. This approach required a little more work because you need to serialize objects into JSON. We used the JSON2 library to perform the serialization.
In the next blog post, I want to explore how you can use jQuery with OData and WCF Data Services.
