JSON, JavaScript and ASP.Net

By Mike Gledhill

Pages:

It's often useful to return JSON data in an ASP.Net application without using WCF Services.
We can get any .aspx page to return raw JSON data (without any HTML headers) for other pages to consume.

This page describes how you can do this, but be aware that some developers really don't recommend doing this. To them, only web service projects should be used to return JSON data, and what I'm about to show you isn't a good idea.

I'll leave you to decide what you think.
To me, sometimes I don't mind having a regular ASP.Net project, containing a few pages which just return JSON data for other pages to consume.

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 of the Customer records 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.
Our example project

Note: on the next page of this tutorial, I'll show you how to do this using AngularJS, and it's much more readable !

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:

JSON data

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.

Adding JSON.Net

JSON.Net is an excellent, free library, which will let us convert a set of data into JSON format.
Its only gotcha is that you download a zip file, but you must then make sure you add just a reference to the library which corresponds to your project's version of .Net.

http://james.newtonking.com/json

For us, we're creating a .Net 4.5 project, so we need to find the correct .dll file, and add it to our project.

Choose the .dll for .Net 4.5

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. In Solution Explorer, right-click on the project name, click on Add, New Item, and in the Data tab, add a new "LINQ to SQL Classes" (.dbml file) to the solution and 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
LINQ TO SQL Class

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.)

Change the Port number


6. Create a new "Lib" folder in your project, and copy the relevant version of the Newtonsoft.Json.dll file into it.


7. 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();
    
    // First, we load the records from the database.
    var listOfCustomers = dc.Customers.OrderBy(s => s.CompanyName).ToList();

    // Then, we build a generic variable containing the parts of the database table which we're interested in.
    var results = new
    {
        NumberOfCustomers = listOfCustomers.Count(),
        Customers = from cust in listOfCustomers
                    select new
                    {
                        CustomerID = cust.CustomerID,
                        CompanyName = cust.CompanyName
                    }
    };

    // And finally, we get JSON.Net to convert it into a JSON string for us.
    string json = JsonConvert.SerializeObject(results);
    return json;
}

Thanks to JSON.Net, loading a list of records from a database, and converting it into a JSON string is really straightforward.
That NumberOfCustomers JSON field is a little irrelevant, as we could always get a record count using Customers.length but it's a nice demonstration of the sort of things we can easily do here.

Here's an example of the kind of data this function returns (with a bit of added spacing, to improve readability): 

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

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();

    // First, we'll load the hierarchical data (Orders \ Order_Details and Products) from the database..
    var results = from order in dc.Orders
                    where (order.CustomerID == customerID)
                    orderby order.OrderDate
                    select new 
                    {
                        OrderID = order.OrderID,
                        OrderDate = order.OrderDate,
                        Basket = from od in order.Order_Details
                                 join product in dc.Products
                                 on od.ProductID equals product.ProductID
                                 where od.OrderID == order.OrderID
                                 select new 
                                 {
                                     ProductID = od.ProductID,
                                     ProductName = product.ProductName,
                                     Quantity = od.Quantity
                                 }
                    };

    // Then we'll get JSON.Net to convert it into a JSON string for us.
    string json = JsonConvert.SerializeObject(results);
    return json;
}

Once again, this is very straightforward, even though we're loading hierarchical data: 

[
    {
        OrderID: 10248,
        OrderDate: "1996-07-04T00:00:00",
        Basket: [
            {
                ProductID: 11,
                ProductName: "Queso Cabrales",
                Quantity: 12
            },
            {
                ProductID: 72,
                ProductName: "Mozzarella di Giovanni",
                Quantity: 5
            }
        ]
    },
    {
        OrderID: 10274,
        OrderDate: "1996-08-06T00:00:00",
        Basket: [
            {
                ProductID: 71,
                ProductName: "Flotemysost",
                Quantity: 20
            },
    ...etc...
]

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.

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 using jQuery

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.

<script type="text/javascript">
  $(document).ready(function () {

    // We're about to populate the "listOfCustomers" control with a list of Customers (using JSON).
    // But first, let's make sure it is empty.
    $("#listOfCustomers").empty();

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

      // Success !
      // We managed to load the JSON, now, let's iterate through the "Customers" records, and add a
      // drop down list item for each.
      $.each(data.Customers, function () {
        $("#listOfCustomers").append($("<option />").val(this.CustomerID).text(this.CompanyName));
      });
    });

  });
</script>

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.

Our example project

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.

<style type="text/css">
  ul ul li
  {
    color: Blue;
    font-size: 12px;
  }
  li, p, span, div, select
  {
    font-family: Arial;
    font-size: 14px;
  }
</style>

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

Our example project


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

blog comments powered by Disqus