Many ways to communicate with your database using jQuery AJAX and ASP.NET
Many ways to communicate with your database using jQuery AJAX and ASP.NET
Each example will feature the same requirement, and that is to obtain and display the Northwind Customer details relating to a specific CustomerID selected from a DropDownList on a page called Customer.aspx.
The bare bones of Customer.aspx are as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customer.aspx.cs" Inherits="Customer" %> <!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"> <div id="SelectCustomers"> <asp:DropDownList ID="Customers" runat="server"> </asp:DropDownList> </div> <div id="CustomerDetails"> </div> </form> </body> </html>
This has a code-behind in which the data is obtained and bound to the DropDownList to give the list of customers:
using System; using System.Data.SqlClient; public partial class Customer : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string connect = "Server=MyServer;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CustomerID, CompanyName FROM Customers"; using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { conn.Open(); Customers.DataSource = cmd.ExecuteReader(); Customers.DataValueField = "CustomerID"; Customers.DataTextField = "CompanyName"; Customers.DataBind(); } } } }
ASPX File
I'll start by saying that this is not something you might see very often. However, I referred to it in one of my first articles on using AJAX and ASP.NET. The aspx page does nothing but communicate with the database and prepare html as a response to the calling code. The page is called FetchCustomer.aspx, and I de-selected the option to use code-behind. This is shown below along with the code:
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> protected void Page_Load(object sender, EventArgs e) { string connect = "Server=MIKE;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CompanyName, Address, City, Region, PostalCode," + "Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID"; string id = Request.QueryString["CustomerID"]; if (id != null && id.Length == 5) { using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("CustomerID", Request.QueryString["CustomerID"]); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { Response.Write("<p>"); Response.Write("<strong>" + rdr["CompanyName"].ToString() + "</strong><br />"); Response.Write(rdr["Address"].ToString() + "<br />"); Response.Write(rdr["City"].ToString() + "<br />"); Response.Write(rdr["Region"].ToString() + "<br />"); Response.Write(rdr["PostalCode"].ToString() + "<br />"); Response.Write(rdr["Country"].ToString() + "<br />"); Response.Write("Phone: " + rdr["Phone"].ToString() + "<br />"); Response.Write("Fax: " + rdr["Fax"].ToString() + "</p>"); } } } } } else { Response.Write("<p>No customer selected</p>"); } Response.End(); } </script>
This file is solely responsible for generating a response to the AJAX call, and presents no UI itself, so using a code-behind page is unnecessary. That's why the file makes use of <script runat="server">. It takes the value passed into the CustomerID querystring value and gets the relevant customer details, and then goes through the fields returned in the DataReader, and Response.Writes the values with a little html mixed in. There are a number of ways that jQuery can request this page and handle the response. The first way to look at is the load() function, which loads html from a remote file:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $('#CustomerDetails').load("FetchCustomer.aspx?CustomerID=" + $('#Customers').val()); }); }); </script>
The code above will go into the head section of Customer.aspx. First, the latest version of the jQuery library is referenced, and then as the page loads - $(document).ready() - a function is applied to the onchange event of the drop down list. This function simply gets the div with the ID of CustomerDetails to load the html returned from FetchCustomer.aspx, and passes the currently selected dropdown list value in the querystring.
An alternative to load() is $.get(). This does exactly the same thing, except that the callback argument specifies what is to be done with the response from the AJAX request. Just replace the javascript code on the head of the Customer.aspx file with the following:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $.get("FetchCustomer.aspx", { CustomerID: "" + $('#Customers').val() + "" }, function(data) { $('#CustomerDetails').html(data); }); }); }); </script>
Here, the querystring value is passed along with the querystring name in { } brackets, with the name and the value separated by a colon. jQuery takes these values and constructs a querystring as part of the HTTP request, so that the page called is FetchCustomer.aspx?CustomerID=SomeValue. It's interesting to note at this point that if you were to pass the { } brackets into the load method, you would force an HTTP POST request, rather than a GET request. In the $.get() example, the response is available in the variable data and the jQuery html() function is used to place this in the CustomerDetails div.
The final calling method I will look at is the one that I have been using in previous articles: $.ajax(). This is a more feature rich method in that it allows a range of options to be applied to manage different types of call, and error handling. As such, it can (and has - if previous comments are anything to go by) prove a little confusing. Nevertheless, we'll look at its use in the context of the current requirement to call an aspx file:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $.ajax({ contentType: "text/html; charset=utf-8", data: "CustomerID=" + $('#Customers').val(), url: "FetchCustomer.aspx", dataType: "html", success: function(data) { $("#CustomerDetails").html(data); } }); }); }); </script>
Only a limited number of options have been applied in the preceding code, but it's already clear to see that the load() and $.get() alternatives are much simpler to use. We'll use the load() option with the next approach, ASHX files.
ASHX Files
ASHX files are convenient ways to deliver partial content to a web page. They are actually HttpHandlers, and are responsible for processing incoming HTTP requests and providing the appropriate response. Quite often, they are used for delivering binary content such as images, or files that are stored in a database or outside of the web application file system. For delivering small amounts of html to be plugged into a particular position on a web page, they can be extremely useful. Once you have chosen Add New Item -> Generic Handler, you should get a template for a class that inherits from IHttpHandler. It will contain one method - ProcessRequest() and one property - IsReusable(). The logic to render the output will go into ProcessRequest() as follows:
<%@ WebHandler Language="C#" Class="FetchCustomer" %> using System; using System.Web; using System.Data; using System.Data.SqlClient; public class FetchCustomer : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html"; string connect = "Server=MIKE;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CompanyName, Address, City, Region, PostalCode," + "Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID"; string id = context.Request.QueryString["CustomerID"]; if (id != null && id.Length == 5) { using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("CustomerID", context.Request.QueryString["CustomerID"]); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { context.Response.Write("<p>"); context.Response.Write("<strong>" + rdr["CompanyName"].ToString() + "</strong><br />"); context.Response.Write(rdr["Address"].ToString() + "<br />"); context.Response.Write(rdr["City"].ToString() + "<br />"); context.Response.Write(rdr["Region"].ToString() + "<br />"); context.Response.Write(rdr["PostalCode"].ToString() + "<br />"); context.Response.Write(rdr["Country"].ToString() + "<br />"); context.Response.Write("Phone: " + rdr["Phone"].ToString() + "<br />"); context.Response.Write("Fax: " + rdr["Fax"].ToString() + "</p>"); } } } } } else { context.Response.Write("<p>No customer selected</p>"); } context.Response.End(); } public bool IsReusable { get { return false; } } }
The method is hardly any different to the ASPX file approach, and the jQuery load() approach is also identical, except for the endpoint it references:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $('#CustomerDetails').load("FetchCustomer.ashx?CustomerID=" + $('#Customers').val()); }); }); </script>
Page Methods
A Page Method is a static method that belongs to its Page class. As such, it can be placed in a <script runat="server"> block, or in code-behind. Since I am already using code-behind to populate the DropDownList on PageLoad() in Customer.aspx, I'll stick with the code-behind approach. ASP.NET 3.5 methods will always serialize and return a JSON object wrapped inside another one: d, if the request contentType is set to application/json.
To add the method to the code behind, two additional references are needed:
using System.Text; using System.Web.Services;
These will allow me to use a StringBuilder object to build the return value, and to adorn the Page Method with the [WebMethod] attribute. The full method is as follows:
[WebMethod] public static string FetchCustomer(string CustomerID) { string response = "<p>No customer selected</p>"; string connect = "Server=MyServer;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CompanyName, Address, City, Region, PostalCode," + "Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID"; if (CustomerID != null && CustomerID.Length == 5) { StringBuilder sb = new StringBuilder(); using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("CustomerID", CustomerID); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { sb.Append("<p>"); sb.Append("<strong>" + rdr["CompanyName"].ToString() + "</strong><br />"); sb.Append(rdr["Address"].ToString() + "<br />"); sb.Append(rdr["City"].ToString() + "<br />"); sb.Append(rdr["Region"].ToString() + "<br />"); sb.Append(rdr["PostalCode"].ToString() + "<br />"); sb.Append(rdr["Country"].ToString() + "<br />"); sb.Append("Phone: " + rdr["Phone"].ToString() + "<br />"); sb.Append("Fax: " + rdr["Fax"].ToString() + "</p>"); response = sb.ToString(); } } } } } return response; }
It's more or less identical to the ASPX version. The jQuery code is too:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $.ajax({ type: "POST", contentType: "application/json; charset=utf-8", data: "{ CustomerID: '" + $('#Customers').val() + "'}", url: "Customer.aspx/FetchCustomer", dataType: "json", success: function(data) { $("#CustomerDetails").html(data.d); } }); }); }); </script>
The response obtained from this pseudo-Web Service is a serialized JSON object:
{"d":"\u003cp\u003e\u003cstrong\u003eQUICK-Stop\u003c/strong\u003e\u003cbr / \u003eTaucherstraße 10\u003cbr /\u003eCunewalde\u003cbr /\u003e\u003cbr / \u003e01307\u003cbr /\u003eGermany\u003cbr /\u003ePhone: 0372-035188 \u003cbr /\u003eFax: \u003c/p\u003e"}
Unicode escape characters appear in place of non-ASCII characters - principally the "<" (\u003c) and ">" (\u003e) tag characters. As you can see, the html returned from the method is represented as the value of a single property: d. An alternative to returning partial html is to return a custom business object. This is what we will look at next. Within the Customer Page class, I'll define the properties of a Company object:
public class Company { public string CompanyID { get; set; } public string CompanyName { get; set; } public string Address { get; set; } public string City { get; set; } public string Region { get; set; } public string PostalCode { get; set; } public string Country { get; set; } public string Phone { get; set; } public string Fax { get; set; } }
This is followed by the revised Page Method which returns a Company object, populated from the DataReader:
[WebMethod] public static Company FetchCustomer(string CustomerID) { Company c = new Company(); string connect = "Server=MyServer;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CompanyName, Address, City, Region, PostalCode," + "Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID"; if (CustomerID != null && CustomerID.Length == 5) { using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("CustomerID", CustomerID); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { c.CompanyName = rdr["CompanyName"].ToString(); c.Address = rdr["Address"].ToString(); c.City = rdr["City"].ToString(); c.Region = rdr["Region"].ToString(); c.PostalCode = rdr["PostalCode"].ToString(); c.Country = rdr["Country"].ToString(); c.Phone = rdr["Phone"].ToString(); c.Fax = rdr["Fax"].ToString(); } } } } } return c; }
The result of this call is the object d again, which has one property - another object of type Company:
{"d":{"__type":"Company","CompanyID":null,"CompanyName":"Old World Delicatessen", "Address":"2743 Bering St.","City":"Anchorage","Region":"AK","PostalCode":"99508", "Country":"USA","Phone":"(907) 555-7584","Fax":"(907) 555-2880"}}
Since we are no longer returning html, we have to parse the nested object and create the html within the client script:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $.ajax({ type: "POST", contentType: "application/json; charset=utf-8", data: "{ CustomerID: '" + $('#Customers').val() + "'}", url: "Customer.aspx/FetchCustomer", dataType: "json", success: function(data) { var Company = data.d; $('#CustomerDetails').append ('<p><strong>' + Company.CompanyName + "</strong><br />" + Company.Address + "<br />" + Company.City+ "<br />" + Company.Region + "<br />" + Company.PostalCode + "<br />" + Company.Country + "<br />" + Company.Phone + "<br />" + Company.Fax + "</p>" ) } }); }); }); </script>
ASP.NET Web Services
I have already detailed how to use ASP.NET 3.5 Web Services with jQuery in this article, but for completeness, we'll create one here by adding a new item to the project:
And within the file that has just been created, we add a method. Here's the complete code:
<%@ WebService Language="C#" Class="FetchCustomer" %> using System; using System.Web; using System.Web.Services; using System.Web.Services.Protocols; using System.Data.SqlClient; using System.Text; using System.Web.Script.Services; [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. [ScriptService] public class FetchCustomer : WebService { [WebMethod] public string GetCustomer(string CustomerID) { string response = "<p>No customer selected</p>"; string connect = "Server=MyServer;Database=Northwind;Trusted_Connection=True"; string query = "SELECT CompanyName, Address, City, Region, PostalCode," + "Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID"; if (CustomerID != null && CustomerID.Length == 5) { StringBuilder sb = new StringBuilder(); using (SqlConnection conn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("CustomerID", CustomerID); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { sb.Append("<p>"); sb.Append("<strong>" + rdr["CompanyName"].ToString() + "</strong><br />"); sb.Append(rdr["Address"].ToString() + "<br />"); sb.Append(rdr["City"].ToString() + "<br />"); sb.Append(rdr["Region"].ToString() + "<br />"); sb.Append(rdr["PostalCode"].ToString() + "<br />"); sb.Append(rdr["Country"].ToString() + "<br />"); sb.Append("Phone: " + rdr["Phone"].ToString() + "<br />"); sb.Append("Fax: " + rdr["Fax"].ToString() + "</p>"); response = sb.ToString(); } } } } } return response; } }
This method will return the partial html that we have used before, but the main points to note are that the [ScriptService] attribute has been uncommented, which allows Javascript to call the method, and that the method is NOT static (as it must be with the Page Method). The jQuery code is almost the same as with the Page Method approach:
<script type="text/javascript" src="script/jquery-1.3.2.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $('#Customers').change(function() { $.ajax({ type: "POST", contentType: "application/json; charset=utf-8", url: "FetchCustomer.asmx/GetCustomer", data: "{ CustomerID: '" + $('#Customers').val() + "'}", dataType: "json", success: function(data) { $("#CustomerDetails").html(data.d); } }); }); }); </script>
Summary
We've looked at a number of ways to perform data access within ASP.NET to work with jQuery AJAX: ASPX file, ASHX file, Page Method and Web Service, with a choice of how to call the ASPX file in particular. So which should you use and when?
The ASPX file approach is the one that will be most familiar to developers coming from another technology such as classic ASP or PHP. It also provides easier access, or shorter code with the load() method. Although not shown here, you can also return custom business objects serialized to JSON, and use the getJSON() method that comes with jQuery. This helps to maintain a separation of concerns. To aid this further, there is no reason why you cannot group your ASPX files in a separate folder. They will be accessible to all pages in your application. One final point in favour of the ASPX approach is that you can use an inline coding method to return HTML rather than using Response.Write() to render the output. This again will be most familiar to those migrating from other technologies.
ASHX files are nice and neat. They are more lightweight than ASPX files in that they won't cause an entire Page class to be instantiated on the web server. However, just like ASPX files, each one can only be responsible for one method. If you want overloaded methods, each one will need to be in its own separate file.
Page Methods are great if you don't mind mixing data access with your presentation logic, or want to keep a slimmed down approach with just 2 layers. Ideally they should be placed in the same page where the method is going to be used, and not if they might be needed ny multiple pages. That way may lead to confusion.
Full Web Services are at their most useful when you want to allow other applications to make use of your data access services. They are also a good way to keep clear separation of different functionality within your application, or if you have an aversion to the ASPX file approach. Finally, web services allow multiple related methods to reside in the same place which makes logical grouping and maintenance easier.