Why webservice can't return datatable in ASP.NET C#

Question

I use this code to call an ASP.NET C# web service to access a SQL Server table:

HttpResponseMessage message2 = client.GetAsync("GetAll?Columns=*&Tbl=Coding_Sell").Result;

string userJson2 = message2.Content.ReadAsStringAsync().Result;
MessageBox.Show(userJson2);

DataTable DT = JsonConvert.DeserializeObject<DataTable>(userJson2);

This is my web method:

[WebMethod]
public string GetAll(string Columns, string Tbl)
{
    return ObjdBAccess.Get(Columns, Tbl);
}

And its related class is as follows:

public string Get(string Columns, string TableName)
{
    String query = "SELECT " + Columns + " From " + TableName;
    createConn();

    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

    DataSet DS = new DataSet();
    adapter.Fill(DS);

    DataTable DT = DS.Tables[0];

    closeConn();

    return JsonConvert.SerializeObject(DS);
}

But the program fails in this line:

DataTable DT = JsonConvert.DeserializeObject<DataTable>(userJson2);

with the following error:

Unexpected character encountered while parsing value: <. Path '', line 0, position 0.

And this is my userJson2 variable:

<?xml version="1.0" encoding="utf-8"?>
<string xmlns="http://tempuri.org/">{"Table":[{"Main_Code":51,"Property_Name":"Sell","Sub_Code":"1"},{"Main_Code":52,"Property_Name":"Sell2","Sub_Code":"2"}]}</string>

Please tell me what is wrong with my code.

Answer 1

By default, ASP.NET web method returns the response in XML. You should modify the response so that it returns as JSON:

[WebMethod]
public static void GetAll(string Columns, string Tbl)
{
    HttpContext.Current.Response.ContentType = "application/json";
    HttpContext.Current.Response.Write(ObjdBAccess.Get(Columns, Tbl));
    HttpContext.Current.Response.End();
}

Or you may work with the ScriptMethod attribute.

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
public static string GetAll(string Columns, string Tbl)
{
    return ObjdBAccess.Get(Columns, Tbl);
}

For deserializing the JSON response as it is an object instead of array:

using Newtonsoft.Json.Linq;

JObject jObj = JObject.Parse(userJson2);
DataTable DT = jObj["Table"].ToObject<DataTable>();