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:

   1: <connectionStrings>
   2:         <add name="Books" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=books;uid=root;pwd=password;option=3;" providerName="System.Data.Odbc"/>
   3: </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.

   1: using System;
   2: using System.Web;
   3: using System.Collections;
   4: using System.Web.Services;
   5: using System.Web.Services.Protocols;
   6: using System.Data;
   7: using System.Data.Odbc;
   8: using System.Web.Script.Serialization;
   9: using System.Web.Script.Services;
  10:  
  11: /// <summary>
  12: /// Web services to query the book database. All methods return JSON data.
  13: /// </summary>
  14: [WebService(Description = "Web services to query the book database.", Namespace = "http://www.williamsportwebdeveloper.com/")]
  15: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  16: [ScriptService]
  17: public class BookServices : System.Web.Services.WebService {
  18:  
  19:     public BookServices () {
  20:  
  21:         //Uncomment the following line if using designed components 
  22:         //InitializeComponent(); 
  23:     }
  24:  
  25:     [WebMethod(Description = "Gets the books matching part of a title.")]
  26:     [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
  27:     public string GetBooksByTitle(string strTitle) {
  28:         OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
  29:         OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Title LIKE '%" + strTitle + "%' ORDER BY BookNum;", objConnection);
  30:         DataSet objDataSet = new DataSet();
  31:         OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
  32:         objDataAdapter.Fill(objDataSet, "reading");
  33:         objConnection.Close();
  34:  
  35:         // Create a multidimensional jagged array
  36:         string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
  37:         int i = 0;
  38:         foreach (DataRow rs in objDataSet.Tables[0].Rows)
  39:         {
  40:             JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
  41:             i = i + 1;
  42:         }
  43:  
  44:         // Return JSON data
  45:         JavaScriptSerializer js = new JavaScriptSerializer();
  46:         string strJSON = js.Serialize(JaggedArray);
  47:         return strJSON;
  48:     }
  49:  
  50:     [WebMethod(Description = "Gets the books matching part of an author's name.")]
  51:     [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
  52:     public string GetBooksByAuthor(string strAuthor)
  53:     {
  54:         OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
  55:         OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Author LIKE '%" + strAuthor + "%' ORDER BY BookNum;", objConnection);
  56:         DataSet objDataSet = new DataSet();
  57:         OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
  58:         objDataAdapter.Fill(objDataSet, "reading");
  59:         objConnection.Close();
  60:  
  61:         // Create a multidimensional jagged array
  62:         string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
  63:         int i = 0;
  64:         foreach (DataRow rs in objDataSet.Tables[0].Rows)
  65:         {
  66:             JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
  67:             i = i + 1;
  68:         }
  69:  
  70:         // Return JSON data
  71:         JavaScriptSerializer js = new JavaScriptSerializer();
  72:         string strJSON = js.Serialize(JaggedArray);
  73:         return strJSON;
  74:     }
  75:     
  76: }

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.

   1: $(document).ready(function() { 
   2:     $("#btnTitleQuery").bind("click", function() {
   3:         $("#query_results").empty();
   4:         $("#query_results").append('<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>');
   5:         $.ajax({
   6:             type: "POST",
   7:             contentType: "application/json; charset=utf-8",
   8:             url: "BookServices.asmx/GetBooksByTitle",
   9:             data: '{ strTitle: "' + $("#txtTitle").val() + '" }',
  10:             dataType: "json",
  11:             success: function(msg) {
  12:                 var c = eval(msg.d);
  13:                 for (var i in c) {
  14:                     $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
  15:                 }
  16:             }
  17:         });
  18:     })
  19:     $("#btnAuthorQuery").bind("click", function() {
  20:         $("#query_results").empty();
  21:         $("#query_results").append('<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>');
  22:         $.ajax({
  23:             type: "POST",
  24:             contentType: "application/json; charset=utf-8",
  25:             url: "BookServices.asmx/GetBooksByAuthor",
  26:             data: '{ strAuthor: "' + $("#txtAuthor").val() + '" }',
  27:             dataType: "json",
  28:             success: function(msg) {
  29:                 var c = eval(msg.d);
  30:                 for (var i in c) {
  31:                     $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
  32:                 }
  33:             }
  34:         });
  35:     })
  36: }); 

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.

This entry was posted in ASP.NET, Databases, JavaScript, Programming, Web and tagged , , , , , . Bookmark the permalink.

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

  1. Pingback: You are now listed on FAQPAL

  2. Sandra Dee says:

    I prefer JSON when communicating with my web services as well. One difficulty that I encountered is that when the JSON came across the wire it was very difficult to read because it was compressed unlike the XML which was well-formatted. I found a tool called JSON Pro Viewer which helps drastically to view and edit large/deeply nested JSON documents:
    http://www.jsonpro.com

    [Reply]

  3. Sandra Dee, I use a free JSON Viewer available at: http://www.codeplex.com/JsonViewer I even made a minor improvement to it.

    [Reply]

  4. Pingback: Brothel Locator Web Service In COBOL – Williamsport Web Developer Weblog

  5. Pingback: ContinousLearner: Links (7/30/2009) | Astha

  6. Nick Tulett says:

    I don’t think the jagged array or serializer are necessary if you return a List, rather than a string:
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public List getSQLRowByValue(string strTable, string strCol, string strValue)
    {
    DataTable dt = getSQLrow(strTable, strCol, strValue);//returns a DataTable – could be from MySQL ODBC in your case
    List RowList = new List();
    foreach (DataRow dr in dt.Rows)
    {
    Dictionary ColList = new Dictionary();
    foreach (DataColumn dc in dt.Columns)
    {
    ColList.Add(dc.ColumnName,
    (string.Empty == dr[dc].ToString()) ? null : dr[dc]); ;
    }
    RowList.Add(ColList);
    }
    return RowList;
    }

    [Reply]

    Nick Tulett Reply:

    Well, I got to work and my code broke spectacularly ;) Seems my bodged VM at home has an old version of ASP.NET and returning Dictionary isn’t allowed now.

    I don’t know whether JaggedArray or Dictionary has any performance implications, but I’m now using:

    public string DataTable2JSON(DataTable dt)
    {
    List RowList = new List();
    foreach (DataRow dr in dt.Rows)
    {
    Dictionary ColList = new Dictionary();
    foreach (DataColumn dc in dt.Columns)
    {
    ColList.Add(dc.ColumnName,
    (string.Empty == dr[dc].ToString()) ? null : dr[dc]);
    }
    RowList.Add(ColList);
    }
    JavaScriptSerializer js = new JavaScriptSerializer();
    string JSON = js.Serialize(RowList);
    return JSON;
    }

    [Reply]

  7. Andre says:

    can i call this service from a different domain or must the service and consuming application be within the same domain?

    [Reply]

  8. TG says:

    hehe… Another free tool: http://jsonviewer.stack.hu

    [Reply]

  9. Pingback: Sites to help you learn how to make .Net web services | Blue Crystal Web Design Blog

  10. Cady says:

    var c = eval(msg.d);
    for (var i in c) {
    $(“#ResultsTable tr:last”).after(“” + c[i][0] + “” + c[i][1] + “” + c[i][2] + “”);
    }

    above throw undefined

    [Reply]

  11. jcnet says:

    Thank you for this blog. I’m trying to build a json web service that will talk to Android’s App Inventor tinywebdb GetValue and StoreValue methods. I’m looking for a simple example to test with.

    http://sites.google.com/site/appinventorhelp/tutorials/tinywebdb-demo

    Their free host is here and i’ve tested my client code against it and it works.

    http://appinvtinywebdb.appspot.com

    What I’m not clear on is, what arguments do I need to set up in the Service? Their source is in Python and not really understanding if and how I can host that service directly in ASP.NET/ IIS.

    I’m finding very little information since app Inventor is somewhat new.

    Thank you.

    [Reply]

  12. Jenn says:

    Excellent article! was a life saver =D … brought me over a huge road block for asmx, mysql and json.

    [Reply]

  13. Kunal says:

    Nice Article.

    Instead of ” string[][] JaggedArray = ”
    you can use List lstPerson = new List();

    then add person object to this list as many you want.

    Person obj = new Person();
    obj.name = “xyz”;
    obj.age=12;
    lstPerson.add(obj);

    after completing adding of object.
    you serialize with same methoad as defined.

    string strJson = string.Empty;
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    strJson += serializer.Serialize(lst);
    return strJson;

    then on client side , jquery replace this code

    $(“#ResultsTable tr:last”).after(“” + c[i][0] + “” + c[i][1] + “” + c[i][2] + “”);

    with

    $(“#ResultsTable tr:last”).after(“” + c[i].name + “” + c[i].age + “”);

    [Reply]

  14. Ebru says:

    Please HELP(!)

    [WebMethod(Description = "Gets all informations about users with JSON")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true, XmlSerializeString = false)]
    public string GetUsersInfoJSON() {
    try{
    SqlCommand sqlCommand = new SqlCommand(“SELECT * FROM Kullanici”, cnn);
    cnn.Open();

    SqlDataReader r = sqlCommand.ExecuteReader();
    int x = 0;
    while (r.Read())
    {
    x++;
    }
    r.Close();
    // Create a multidimensional jagged array
    string[][] JaggedArray = new string[x][];
    int i = 0;

    SqlDataReader reader = sqlCommand.ExecuteReader();
    // Call Read before accessing data.
    while (reader.Read())
    {
    JaggedArray = new string[] { reader["U_id"].ToString(), reader["U_ad"].ToString(), reader["U_soyad"].ToString(), reader["U_Kullaniciad"].ToString() , reader["U_pass"].ToString() };
    i = i + 1;
    }

    // Call Close when done reading.
    cnn.Close();
    reader.Close();

    // Return JSON data
    JavaScriptSerializer js = new JavaScriptSerializer();
    string strJSON = js.Serialize(JaggedArray);
    return strJSON;
    }
    catch(Exception ex){
    return “hata”;
    }}
    this is my web methods to return json ,but my code return json with string tag ,i do not want to tag to get json string .

    [["1","pınar","hoşyumruk","phosyumruk","0033 "],["2","Emre","Işın","emrei","nywmzs "],["5","Ercan","Eren","eeren","7920471 "]]

    there are string tag but i want to send only that
    [["1","pınar","hoşyumruk","phosyumruk","0033 "],["2","Emre","Işın","emrei","nywmzs "],["5","Ercan","Eren","eeren","7920471 "]] json ,,How can do this please help me it is very important for me please!!!! Anyone can help me to solve this problem????

    [Reply]

  15. siva says:

    Hi today oni i startted to learn abt jquery, and i teried to get collection of datas from the webservice and i successful help of this article, Really simple and helpful :)

    [Reply]

  16. Reece says:

    Great Article! Really Helped!

    [Reply]

  17. Alan says:

    Do you really need the serializer? I thought that the MS AJAX framework takes care of that for you and that manually serializing will result in double serialization. What do you think of: ASP.NET web services mistake: manual JSON serialization by David Ward?

    [Reply]

  18. Well more than the post, it looks like comments are more helpful! @Alan that’s a good one, I liked the ref you pointed out…

    [Reply]

  19. Radu says:

    Is not working with DataSets:

    The value for column ‘ArticleID’ in table ‘Vendors’ is DBNull.

    [Reply]

  20. Prayoga says:

    I’ve try this JSON web service, but mine problem is status text OK parse error. Can anyone help to fix this problem? Please help.

    [Reply]

  21. prashiddha says:

    I tried the post and got the result…….CODE HERE

    [Reply]

  22. ton says:

    I’m not getting the example running
    I just started VS2008 new website in VB, removed the html, copy pated the example website. Added style.css and jquery-1.3.2.min.js
    and nothing happens.
    Only the table heading is shown. trying to show an alert in a function does not work either.
    Do i miss something?

    Ton

    [Reply]

  23. dila says:

    Very good tutorial
    thax a lot

    [Reply]

  24. Ketan says:

    Hello

    i put web service and html on diffrent domain and i change the url in side jqury as:

    i change
    url: “BookServices.asmx/GetBooksByAuthor”, to
    url: “http://localhost:53664/IncidentManagement/WebServices/GetIncidentJquery.asmx/GetIncidentData”,

    Now i get the Access-control-allow-origin error as :
    XMLHttpRequest cannot load http://localhost:53664/IncidentManagement/WebServices/GetIncidentJquery.asmx/GetIncidentData. Origin http://localhost:49960 is not allowed by Access-Control-Allow-Origin.

    Please Help me..

    Thank in Advance.

    [Reply]

  25. Ketan says:

    Hello guys,

    please reply me … from last few days m facing this problem….. please help me…..

    Thanks…

    [Reply]

  26. Jason says:

    nothing like a little sql injection friendly code.

    [Reply]

  27. Coding Kiwi says:

    Very helpful. It’s not very well documented on the jQuery site exactly how you get the thing to work with JSON!

    [Reply]

  28. Nick says:

    I serialize a class containing AD account details

    object oa = runspace.SessionStateProxy.GetVariable(request.OutputObjectName);

    //Check if the response is an array of objects
    //i.e. normal and priv users accounts on same EMPLID and same eperson
    try
    {
    Output = (PSObject)oa;
    response.Add(new PSresponse(Output));
    }
    catch
    {
    foreach (object o in ((object[])oa))
    {
    Output = (PSObject)o;
    response.Add(new PSresponse(Output));
    }
    }

    [Serializable()]
    public class PSresponse
    {
    public IDictionary PSmembers = new Dictionary();
    public IDictionary PSmemberArray = new Dictionary();

    public PSresponse(PSObject PSobj)
    {
    if(PSobj == null)
    throw new Exception(“PSresponse PSobj is null”);

    foreach (PSMemberInfo mi in PSobj.Members)
    {
    if (mi.Value == null)
    continue;

    if( mi.Value.GetType() == typeof(string))
    {
    string key = mi.Name;
    string value = (string)mi.Value;

    PSmembers.Add(new KeyValuePair(key, value));

    }
    else if (mi.Value.GetType() == typeof(string[]))
    {
    string key = mi.Name;
    string[] values = (string[])mi.Value;

    PSmemberArray.Add(new KeyValuePair(key, values));

    }
    }
    }

    }

    public static string Serialize(T obj)
    {
    DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
    MemoryStream ms = new MemoryStream();
    serializer.WriteObject(ms, obj);
    string retVal = Encoding.Default.GetString(ms.ToArray());
    ms.Dispose();
    return retVal;
    }

    The Javascript from the test harness

    function GetGroupMembership() {

    var xmlhttp;

    document.getElementById(“ajaxdata”).innerHTML = “”;

    if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp = new XMLHttpRequest();
    }
    else {// code for IE6, IE5
    xmlhttp = new ActiveXObject(“Microsoft.XMLHTTP”);
    }

    xmlhttp.onreadystatechange = function () {
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
    raw = JSON.parse(xmlhttp.responseText);

    //Display all the id’s
    for (var id = 0; id < raw.length; id++) {

    var obj = raw[id];

    var s = "”;

    s += “AD VariableAD Value”;

    for (var i = 0; i < obj.PSmembers.length; i++) {
    s += "” + obj.PSmembers[i].Key + “” + obj.PSmembers[i].Value + “”;
    }

    s += “”;

    usertable += “” + s + “”;

    /*** AD GROUP TABLE ***/
    var count = 0;

    var GroupCount = obj.PSmemberArray[0].Value.length;

    var s = “”;

    s += “ID: ” + userid + “Group Name?????”;

    for (var i = 0; i < GroupCount; i++) {

    var Group = obj.PSmemberArray[0].Value[i];

    var sgroup = Group.split(",");

    var PairCount = sgroup.length;

    s += "”;

    for (var j = 0; j < PairCount; j++) {
    s += "” + sgroup[j] + “”;
    }

    count++;

    s += “”;
    }

    s += “”;

    grouptable += “” + s + “”;

    /*** COURSE TABLE ***/

    count = 0;

    var GroupCount = obj.PSmemberArray[0].Value.length;

    var s = “”;

    s += “ID: ” + userid + “Course Name?????”;

    for (var i = 0; i < GroupCount; i++) {

    var Group = obj.PSmemberArray[0].Value[i];

    var sgroup = Group.split(",");

    if (sgroup[1] == "OU=Courses") {

    s += "”;
    s += “” + sgroup[0].split(“=”)[1] + “”;
    s += “” + sgroup[0].split(“=”)[1] + ” Long Description”;
    s += “ Long Description “;
    s += “”;

    count++;
    }

    }

    s += “”;

    coursetable += “” + s + “”;

    //document.getElementById(“coursetable”).innerHTML += s;
    //document.getElementById(“ajaxcoursedetail”).innerHTML = count;
    }

    }

    usertable += “”;
    grouptable += “”;
    coursetable += “”;

    [Reply]

  29. Pingback: asp.net asmx web service returning xml instead of json

  30. Krishna says:

    Robert,

    You example was very helpful to me.

    I have tried your example on my local IIS server. Created two websites – one that hosts the webservice and another that hosts the html that calls the webservice. Works on my local PC. When I move the same setup to my Windows 2008 r2 iis7.5 web server, works as long as I test the html call from the web server. So I know my database connectivity from the web server is working as I get results when I run this from the web server itself…..

    When I try to access the webservice from my PC with the webservice hosted on the webserver hosted in the same domain, the results from the db do not show up.

    Tried checking calls using Fiddler but did not see any issues. Checked the windows logs on my desktop and the web server and did not see anything recorded. The database binaries installed on the webserver have been given read and execute access to all authenticated users.

    Any help is apprciated,
    Krishna

    [Reply]

  31. Pingback: asp.net asmx web service returning xml instead of json - Tech Forum Network

  32. There are many programs and hosting companies out there and
    it can be very hard to choose which one is best for you and your business.
    Therefore, owners should never be put off by a low cost price in assuming
    that the hosting service is of poor quality; this is simply not the case
    with cloud hosting. While we evaluate a hosting business, it is necessary to identify their services with regard to their performance, reliability, service, scalability and security considerations.

    [Reply]

  33. Nirav Joshi says:

    Very Nice and precise article really like it.

    [Reply]

  34. I have developed similar project. It is working perfect on IE but facing problem on other browsers like FireFox and Chrome. To test purpose i have used your web service and t is giving below error
    “NetworkError: 403 Forbidden – http://www.williamsportwebdeveloper.com/BookServices.asmx/GetBooksByTitle

    its been 5 days now but i am unable to catch it. Could you please help me out how i can fix this using FireFox.

    [Reply]

  35. Same error i am facing if i use my own webs services located at web browser.

    I’ll highly appreciate your help.

    [Reply]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>