How To Create A JSON Web Service In ASP.NET

ASP.NET makes it easy to create web services but they usually return XML. Like many web developers I now prefer JSON. This article and sample code will show you how to get your web service to return data in the JSON format. The web service is written in C#. Since my web hosting company only provides me with a MySQL database, we’ll make this project a little more challenging by pulling data from a MySQL database. Finally, I will show you how to use jQuery to call your web service. So you get three kinds of value from this article; a web service in C# to return JSON data, how to use a MySQL database, and how to call an ASP.NET web service using jQuery. None of those three tasks are very intuitive or simple when using ASP.NET because they all go against the framework conventions.

The first task to tackle is connecting to the MySQL database. ASP.NET is usually used with SQL Server or SQL Server Express so using MySQL is not always easy. On my local development web server I downloaded the MySQL Connector/ODBC 5.1 from http://dev.mysql.com/downloads/connector/odbc/5.1.html. Then my web.config file merely needed a connection string node:

<connectionStrings>
        <add name="Books" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=books;uid=root;pwd=password;option=3;" providerName="System.Data.Odbc"/>
</connectionStrings>

NOTE: This is not my real password and there is no external access to MySQL server running on my development server.

For this demonstration, I am using a simple database I use to keep track of the books I read. It has a single table “reading” which has only three columns; BookNum, Title, Author. I don’t know if CrystalTech has the MySQL ODBC driver installed, but I was able to create a datasource name using the control panel so the connection string used a “dsn=books” instead of the driver.

The C# code for the web service at http://www.williamsportwebdeveloper.com/BookServices.asmx shows you how an ODBC connection is used to query the MySQL database. What you should notice in the code is that various method attributes are used on the web service class and methods. These method attributes require the System.Web.Script reference. If you get an error concerning missing assemblies then make sure your web.config file has everything it needs for an AJAX enabled ASP.NET site. As you can see, there is a ScriptMethod method attribute that specifies JSON as the response format. The tricky part is converting a dataset into a JSON string. I used a jagged array to accomplish that because it will serialize properly. That is probably the most valuable tip in this entire article.

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.Odbc;
using System.Web.Script.Serialization;
using System.Web.Script.Services;

/// <summary>
/// Web services to query the book database. All methods return JSON data.
/// </summary>
[WebService(Description = "Web services to query the book database.", Namespace = "http://www.williamsportwebdeveloper.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class BookServices : System.Web.Services.WebService {

    public BookServices () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod(Description = "Gets the books matching part of a title.")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string GetBooksByTitle(string strTitle) {
        OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
        OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Title LIKE '%" + strTitle + "%' ORDER BY BookNum;", objConnection);
        DataSet objDataSet = new DataSet();
        OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
        objDataAdapter.Fill(objDataSet, "reading");
        objConnection.Close();

        // Create a multidimensional jagged array
        string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
        int i = 0;
        foreach (DataRow rs in objDataSet.Tables[0].Rows)
        {
            JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
            i = i + 1;
        }

        // Return JSON data
        JavaScriptSerializer js = new JavaScriptSerializer();
        string strJSON = js.Serialize(JaggedArray);
        return strJSON;
    }

    [WebMethod(Description = "Gets the books matching part of an author's name.")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string GetBooksByAuthor(string strAuthor)
    {
        OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
        OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Author LIKE '%" + strAuthor + "%' ORDER BY BookNum;", objConnection);
        DataSet objDataSet = new DataSet();
        OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
        objDataAdapter.Fill(objDataSet, "reading");
        objConnection.Close();

        // Create a multidimensional jagged array
        string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
        int i = 0;
        foreach (DataRow rs in objDataSet.Tables[0].Rows)
        {
            JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
            i = i + 1;
        }

        // Return JSON data
        JavaScriptSerializer js = new JavaScriptSerializer();
        string strJSON = js.Serialize(JaggedArray);
        return strJSON;
    }

}

At this point you have a web service that queries a MySQL database and returns records as JSON data. This is extremely useful if you want to use that data on your home page without making it an ASP.NET page. You can get the data using AJAX and avoid having your home page show an error should your ASP.NET web application develop a problem. Next I will show you the JavaScript code for calling the web service. I’ll use the jQuery library because it is extremely popular with web developers right now. It makes it easy to write JavaScript that works in both browsers but you may not know how to call an ASP.NET web service using jQuery, especially since we need to pass a parameter to the web service methods. This is also tricky because the content type must be JSON.

$(document).ready(function() {
    $("#btnTitleQuery").bind("click", function() {
        $("#query_results").empty();
        $("#query_results").append('<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>');
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "BookServices.asmx/GetBooksByTitle",
            data: '{ strTitle: "' + $("#txtTitle").val() + '" }',
            dataType: "json",
            success: function(msg) {
                var c = eval(msg.d);
                for (var i in c) {
                    $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
                }
            }
        });
    })
    $("#btnAuthorQuery").bind("click", function() {
        $("#query_results").empty();
        $("#query_results").append('<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>');
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "BookServices.asmx/GetBooksByAuthor",
            data: '{ strAuthor: "' + $("#txtAuthor").val() + '" }',
            dataType: "json",
            success: function(msg) {
                var c = eval(msg.d);
                for (var i in c) {
                    $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
                }
            }
        });
    })
});

You can try a working example at: http://www.williamsportwebdeveloper.com/BookQuery.html. This JavaScript is binding function prototypes to the button click events. These functions call the web service methods using the POST method and pass the required parameters as JSON data. The JSON data that it receives in response is used to create a table. You can continue to issue queries without reloading the web page. If you view the source of the BookQuery.html page you will find all the code required for the client page because it does not require any server side code.

S	M	T	W	T	F	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

46 Responses to How To Create A JSON Web Service In ASP.NET

Leave a Reply

Now Reading

Planned books:

Current books:

Recent books: