Mikes Knowledge Base

JSON, JavaScript and ASP.Net

By Mike Gledhill

Pages:

It's often useful to use JSON in an ASP.Net application without using WCF Services.

For example, I recently had a project where I wanted to draw a diagram on my webpage using a JavaScript and HTML 5 <canvas> for a chosen customer, based on data from SQL Server. If the user selected a different Customer, new JSON data would be loaded, and the diagram would get updated without any postbacks occurring.

As before, using JSON with JavaScript in an ASP.Net page is a straightforward process if you remember to follow certain steps. If you get this right, then you can use JQuery to load your JSON and your JavaScript code can easily access the JSON data.

Let's go through an example.

Our ASP.Net/JSON example

Our example project will do two things.

It'll fill a drop down box with a list of the names of all [Customer]s in the Northwind database
When the user selects a Customer, it'll load and display a list of all Orders that have ever been made by that Customer, including the Products in each order.

Although this is an ASP.Net project, we will load the data from SQL Server into JSON, and the webpage will load the JSON data using JQuery. No Postbacks will occur, so it's a much friendlier experience for the user.

We will create a new project containing contain two ASP.Net (.aspx) pages.

The JSONdata.aspx webpage will contain two functions. One will load a list of all [Customer] records from our Northwind database, and the other will get details about all Orders made by a Customer. Both functions will return a JSON string, and we'll be able to call them using an URL like this:

http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers
http://localhost:21130/JSONdata.aspx?Option=GetOrdersForCustomers&CustomerID=VINET

(If you wanted to, you could actually split this into two seperate .aspx pages, one per function, but for our example, we'll have both methods in the same ASP.Net page.)

This is what the JSONdata.aspx page will return, when you call it in each of these ways:

Our second ASP.Net page, Default.aspx, loads, parses and displays the JSON data from the JSONdata.aspx page.

When the page first loads, it'll call the GetListOfCustomers function (using the first URL above) to load a list of customers, and populate the drop down list with the Customer Names and Company IDs.
When the user selects a particular Customer Name in the drop down list, it'll call the GetOrdersForCustomers function, passing in the Company ID, to load a list of that Customer's orders, and display details about each of the orders in an unordered list.

Together, this nicely demonstrates how to use JSON with ASP.Net, JQuery and JavaScript, and the pitfalls to avoid.

Walkthrough: Creating JSON data in an ASP.Net page

1. Start by creating a brand new ASP.Net Web Application solution, and call it "ASPNetJSONdemo".

2. Add a new "LINQ to SQL Classes" (.dbml file) to the solution, call it Northwind.dbml, link it to your local copy of the SQL Server Northwind database (as described on Page 2) and drag'n'drop the following tables into it:

Customer
Order
Order_Detail
Product

3. Build the solution (to make sure Visual Studio's Intellisense knows about our table names).

4. Add a new "Web Form" (.aspx page) to the solution, called JSONdata.aspx.

5. In Solution Explorer, right-click on the project name (ASPNetJSONdemo) and select "Properties" to view the project properties. Change the Port number to 21130 (simply so the URLs I quote below will also work for you.)

6. Right-click on the new JSONdata.aspx file, and select "View Code", and lets add two functions to load this data, and return it as a JSON string. At the top of the file, add this reference:

using System.Text;

Replace the Page_Load function with the following code.

protected void Page_Load(object sender, EventArgs e)
{
  if (Request["Option"] == null)
    return;

  string cmd = Request["Option"].ToString();
  if (cmd == "GetListOfCustomers")
  {
    Response.Clear();
    Response.Write(GetListOfCustomers());
    Response.End();
  }
  if (cmd == "GetOrdersForCustomers")
  {
    string customerID = Request["CustomerID"].ToString();
    Response.Clear();
    Response.Write(GetListOfOrdersForCustomer(customerID));
    Response.End();
  }
}

We make these changes, as we specifically want this page to be called with an Option parameter, and, for the GetOrdersForCustomers command, a CustomerID parameter.

http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers
http://localhost:21130/JSONdata.aspx?Option=GetOrdersForCustomers&CustomerID=VINET

Next, let's add the GetListOfCustomers function (below the Page_Load function) which will load a list of all [Customer] records in our Northwind database, and return a JSON string, containing their Customer ID and Company Name values.

public string GetListOfCustomers()
{
  // Load the list of Customer IDs and Names from the SQL Server database from the [Customer] table.
  NorthwindDataContext dc = new NorthwindDataContext();
  var listOfCustomers = dc.Customers.OrderBy(s => s.CompanyName);

  // Create a JSON string, describing our array of customer records.
  StringBuilder JSON = new StringBuilder();

  JSON.Append("{");
  JSON.Append("\"NumberOfCustomers\":" + listOfCustomers.Count().ToString() + ", ");
  JSON.Append("\"Customers\":[");

  foreach (Customer cust in listOfCustomers)
  {
    JSON.Append("{");
    JSON.Append("\"CustomerID\":\"" + cust.CustomerID + "\", ");
    JSON.Append("\"CompanyName\":\"" + cust.CompanyName + "\" ");
    JSON.Append("},");
  }

  if (JSON.ToString().EndsWith(","))
    JSON = JSON.Remove(JSON.Length - 1, 1);

  JSON.Append("]}");

  return JSON.ToString();
}

Finally, let's add the GetListOfOrdersForCustomer function, which will load a list of all orders for a particular [Customer], including which [Product]s each order contained. This function also returns a JSON string.

public string GetListOfOrdersForCustomer(string customerID)
{
  // Load the list of Orders for a particular [Customer] ID from the SQL Server database
  NorthwindDataContext dc = new NorthwindDataContext();
  System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.GetCultureInfo("en-US");
  var listOfOrders = dc.Orders.Where(s => s.CustomerID == customerID).OrderBy(s => s.OrderDate);

  StringBuilder JSON = new StringBuilder();

  JSON.Append("{");
  JSON.Append("\"NumberOfOrders\":" + listOfOrders.Count().ToString() + ", ");
  JSON.Append("\"Orders\":[");

  // Loop through each Order that this Customer has placed..
  foreach (Order order in listOfOrders)
  {
    JSON.Append("{");
    JSON.Append("\"OrderID\":\"" + order.OrderID.ToString() + "\", ");
    JSON.Append("\"OrderDate\":\"" + order.OrderDate.Value.ToString("d", ci) + "\", ");
    JSON.Append("\"Basket\":[");

    // Loop through the details of this Order, and find out which Products it contained..
    foreach (Order_Detail od in dc.Order_Details.Where(s => s.OrderID == order.OrderID))
    {
      Product product = dc.Products.Where(p => p.ProductID == od.ProductID).FirstOrDefault();
      if (product != null)
      {
        JSON.Append("{");
        JSON.Append("\"ProductID\":" + product.ProductID.ToString() + ",");
        JSON.Append("\"ProductName\":\"" + product.ProductName + "\",");
        JSON.Append("\"Quantity\":" + od.Quantity.ToString() + "");
        JSON.Append("},");
      }
    }
    if (JSON.ToString().EndsWith(","))
      JSON = JSON.Remove(JSON.Length - 1, 1);

    JSON.Append("]");

    JSON.Append("},");
  }

  if (JSON.ToString().EndsWith(","))
    JSON = JSON.Remove(JSON.Length - 1, 1);

  JSON.Append("]}");

  return JSON.ToString();
}

Okay, that's a lot of code, but most of it is simply loading some data using LINQ, then writing it in JSON format. The key to this function is making sure that the curly and square brackets which we write to the JSON string do match.

In Solution Explorer, right-click on the "JSONdata.aspx" file, and click on "Set as Start Page".
Now, compile & run this code.

Your browser window should open, but nothing will be displayed.
Why ?

Because our new Page_Load function insists that you call this page with an Option parameter.
Change the URL to the following, and try again.

http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers

Assuming your database connection is alive and kicking, you should now see a lengthy JSON string, containing the Customer names.

Test your JSON string !!

Whenever you write code to manually write out JSON data, the first thing you should do is copy'n'paste the JSON string into the jsonlint website.
If your code has missed off a curly bracket, if you're missing a speechmark or anything else, this website will tell you about it.

It'll save you a huge amount of time to test your JSON string now and fix any problems in your code, rather than later, when we start using JavaScript to load it.

Back to our example.

Pick one of the Customer IDs (in the example below, I'll choose "BERGS"), add it to the GetOrdersForCustomers URL, and get your browser to show you this customer's orders.

http://localhost:21130/JSONdata.aspx?Option=GetOrdersForCustomers&CustomerID=BERGS

Again, we want to test the JSON output from our GetOrdersForCustomers function now, rather than later, so we should copy'n'paste the JSON from this page into the jsonlint website, and check that the JSON is valid.

I know... I know...
We whizzed through that code, with little explanation.

The important point is that we wrote functions to return JSON data, then made sure that our ASP.Net page returned just this string, nothing else. We did this by specifically telling the ASP.Net server exactly what to return:

Response.Clear();
Response.Write(GetListOfCustomers());
Response.End();

By doing this, the ASP.Net server didn't wrap our JSON results in a <body> or add any <style>s, there was no <html> or <header>...
All that was returned from that .aspx page was that one JSON string.

This is very deliberate. Next, we'll be writing some JavaScript to call this webpage for us and parse the JSON results into JavaScript objects. If our JSONdata.aspx page returned the JSON string wrapped up in any kind of HTML formatting, then the deserialization would fail.

Loading and displaying the list of Customers

Next, open up the Default.aspx webpage in Designer, and click on the Source button. It's time to write some JavaScript.

First, we are going to be using JQuery in our JavaScript, and we need to include it's library in our ASP.Net page.
Visual Studio should have added a few JQuery .js files for you in a Scripts folder. Find the file ending in .min.js, and drag'n'drop it into the <head> of your page.

<head runat="server">
<script type="text/javascript" src="Scripts/jquery-1.4.1.min.js"></script>
</head

Now, let's add a few HTML controls to the <form>:

  <form id="form1" runat="server">
  <div>
    <p>Please choose a customer:</p>
    <select id="listOfCustomers">
    </select>
    <br />
    <br />
    <div id="divNumberOfOrders"></div>
    <ul id="listOfOrders">
    </ul>
  </div>
  </form>

Okay, brace yourself. It's time for some JavaScript.
Add the following to the <head>, below where you just dragged the JQuery file to.

In Solution Explorer, right-click on Default.aspx and select "Set as Start Page", and run the project again.
You'll see our drop down list populated with our list of Customers.

How does this work ? First, we use the JQuery "empty" command to remove any items that are currently in our drop down list:

$("#listOfCustomers").empty();

Then we use the JQuery command "$.getJSON" to load the list of Customers, in JSON format, by calling the GetListOfCustomers function in our JSONdata.aspx file:

$.getJSON("http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers", function (data) {

// If the getJSON call was successful, the "data" variable now contains our JSON data.

});

Now, the cool thing about loading JSON data using JavaScript in this way is that the "data" variable which is returned isn't just a long JSON string, but actually an object, ready to access the JSON data fields.

For example, remember that we said earlier that the GetListOfCustomers function returns JSON data which looks like this:

{
  NumberOfCustomers: 91,
  Customers: [
     {
        CustomerID: "ALFKI",
        CompanyName: "Alfreds Futterkiste"
     },
     {
        CustomerID: "ANATR",
        CompanyName: "Ana Trujillo Emparedados y helados"
     },

Well, we could now easily access the "NumberOfCustomers" JSON value just by using the data.NumberOfCustomers value.

$.getJSON("http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers", function (data) {

// If the getJSON call was successful, the "data" variable now contains our JSON data.
alert("Loaded details about " + data.NumberOfCustomers + " customers.");

});

Of course, what we actually want to do is to create a new drop down list entry for each of the Customers records in our JSON data.
How do we do this ?

Let's have a quick refresher on some basic HTML.
To display a drop down list on a webpage, you need some HTML like this:

<select id="listOfCustomers">
  <option value="id_James">James</option>
  <option value="id_Frank">Frank</option>
  <option value="id_Bob">Bob</option>
</select>

This would display a drop down list like this:

In our example, we want to iterate through the Customers records in our JSON data, and for each one, add a new HTML <option> element to our <select> control. It's text will be the CompanyName value, it's value will be the CustomerID value.

Using a couple of JQuery commands, this is really straightforward to do:

$.getJSON("http://localhost:21130/JSONdata.aspx?Option=GetListOfCustomers", function (data) {

  $.each(data.Customers, function () {
      $("#listOfCustomers").append($("<option />").val(this.CustomerID).text(this.CompanyName));
  });

});

. . .

<form id="form1" runat="server">
<div>
  <p>Please choose a customer:</p>

  <select id="listOfCustomers">
  </select>

</div>
</form>

And that's it. With these few lines of code, we now have our list of customer names and IDs in a drop down list.

Loading and displaying the list of Orders

When the user chooses a customer in the drop down list, we want to load the list of Orders for that customer.
Paste the following code below our previous JavaScript, but inside the "$(document).ready(function ()" function.

$("#listOfCustomers").change(function () {
    // Using JQuery, find the text and the value of which dropdown list item was selected
    var chosenCompanyName = $("#listOfCustomers option:selected").text();
    var chosenCustomerID = $("#listOfCustomers option:selected").val();

    $("#listOfOrders").empty();

    var URL = "http://localhost:21130/JSONdata.aspx?Option=GetOrdersForCustomers&CustomerID=" + chosenCustomerID;
    $.getJSON(URL, function (data) {

      $("#divNumberOfOrders").text("This customer has placed " + data.NumberOfOrders + " orders.");

      // Iterate through the list of Orders in the JSON data.
      $.each(data.Orders, function () {
        var divListOfProducts = "Div_" + this.OrderID;
        $("#listOfOrders").append($("<li />").val(this.OrderID).text("Order date: " + this.OrderDate));
        $("#listOfOrders").append($("<ul />").attr("id", divListOfProducts));

        // Iterate through the list of Basket records in this Order record.
        $.each(this.Basket, function () {
            var productString = this.Quantity + " x " + this.ProductName;
            $("#" + divListOfProducts).append($("<li />").val(this.ProductID).text(productString));
        });

      });
    });
});

This uses the same principles as before. We call the GetOrdersForCustomers function on the JSONdata.aspx page, passing it the CompanyID of the user's chosen customer.

We then iterate through the "Orders" records in our JSON, then the "Baskets" records within each of the "Orders", adding the values in unordered lists.

{
  NumberOfOrders: 3,
  Orders: [
     {
       OrderID: "10326",
       OrderDate: "10/10/1996",
       Basket: [
         {
           ProductID: 4,
           ProductName: "Chef Anton's Cajun Seasoning",
           Quantity: 24
         },
         {
           ProductID: 57,
           ProductName: "Ravioli Angelo",
           Quantity: 16
         },
         {
           ProductID: 75,
           ProductName: "Rhönbräu Klosterbier",
           Quantity: 50
         }
       ]
   },

It's slightly more complicated, as we aren't just appending an <li> list item for each Order record, but also appending an <ul> (unordered list) element.

To do this, we create an <li> element for each Order record, then append an <ul> element with a specific ID (so we can find it in the DOM later).

var divListOfProducts = "Div_" + this.OrderID;
$("#listOfOrders").append($("<li />").val(this.OrderID).text("Order date: " + this.OrderDate));
$("#listOfOrders").append($("<ul />").attr("id", divListOfProducts));

In the second $.each() function, we can then append an <li> to our new <ul> for each Product record.

$.each(this.Basket, function () {
var productString = this.Quantity + " x " + this.ProductName;
$("#" + divListOfProducts).append($("<li />").val(this.ProductID).text(productString));
});

The end result is a hierarchical list of Orders and Products, for a particular Customer.

Finally, you might want to add a bit of CSS inside the <head> element, to tidy the output up a bit.

And that's it.
From a SQL Server database, to JSON data, to a simple HTML page.

The most important point to take away from this example is that once you have deployed this JSONdata.aspx page to a hosting site, you can use it to load JSON data from JavaScript, from an iOS device, from an Android application, and so on.
You have managed to expose your SQL Server data to the outside world, and are not restricted to using it just in Microsoft applications.

http://www.SomeHostingSite.com/JSONdata.aspx?Option=GetListOfCustomers
http://www.SomeHostingSite.com/JSONdata.aspx?Option=GetOrdersForCustomers&CustomerID=VINET

I hope all that made sense, and that you found it useful.
As usual, please leave a comment below, if it helped.

< Previous Page

Next Page >

Comments