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