JSON data from ASP.NET Webservice – Part 1

JSON is fairly a simple alternative to XML file, in a more human readable form. One can use JSON data in a website, in an Android app, in an iOS app and many more form. Here’s a link explaining the usage of JSON in details.
In this post I will be showing how I parse data from MS SQL or MySQL database in form of JSON data using ASP.NET webservice. I will be also showing how to use the generated data in an ANDROID APPLICATION. Fairly simple.

SQL Database table

You will have to create like following to use in MS SQL database :

create table City
(
Id int identity(1,1) not null,
Name nvarchar(50) not null,
Country nvarchar(50) not null
)

 

The Webservice :

Go ahead and create a new or open your already created ASP.NET website and make a new Webservice file and name it as you wish.

 

YourFileName.asmx file :

using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.Odbc;
using System.Data;
using System.Configuration;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections.Generic;

//conn

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

public class MyService : System.Web.Services.WebService
{
private static String conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection con = new SqlConnection(conn);
DataTable dt;
SqlCommand cmd;
SqlDataReader reader;

public MyService()
{

}

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
public void HelloJSON()
{
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");

if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
{
//These headers are handling the "pre-flight" OPTIONS call sent by the browser
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Accept");
HttpContext.Current.Response.AddHeader("Access-Control-Max-Age", "1728000");
HttpContext.Current.Response.End();
}
String resultJSON = "";
JavaScriptSerializer js = new JavaScriptSerializer();
try
{
Context.Response.Clear();
Context.Response.ContentType = "application/json";

con.Open();
cmd = new SqlCommand("Select * from City", con);
reader = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(reader);
con.Close();

JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<String, Object>> tableRows = new List<Dictionary<string, object>>();
Dictionary<String, Object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col].ToString());
}
tableRows.Add(row);
}
resultJSON = serializer.Serialize(tableRows).ToString();

}
catch (Exception ex)
{
resultJSON = ex.Message.ToString();
}
Context.Response.Write(resultJSON);
// return resultJSON;
}
}

 

Note

Note that the following lines are important to allow Asp.net cross domain authentication

HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");

if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
{
//These headers are handling the "pre-flight" OPTIONS call sent by the browser
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Accept");
HttpContext.Current.Response.AddHeader("Access-Control-Max-Age", "1728000");
HttpContext.Current.Response.End();
}

 

Web.config

Also this is also important :

<system.web>
<webServices>
<protocols>
<add name="HttpGet" />
<add name="HttpPost" />
</protocols>
</webServices>

and declare your connection string. Replace with your Data source name, Database name, user and password :

<connectionStrings>
<add name="conn" connectionString="Data Source=192.168.0.100;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=hitesh;Password=789" />
</connectionStrings>

Your result should look as such when you will be running this in the web browser:

You can now host this service in Windows IIS server.
Here’s a link explaining How to host ASP.NET website in Windows IIS server

Usage of following data in Android Application.