get data from sql server database in json using wcf,c#

Question

I am able to get json data from a SQL Server table but the format of json seems to be in incorrect.

There is extra " " after json:

"[{\"deviceid\":\"jafjajf17841278947\"},
 {\"deviceid\":\"ahfaj2528\"},
 {\"deviceid\":\"hefhsf9872987572\"},
 {\"deviceid\":\"22\"},
 {\"deviceid\":\"23\"}]"

Inverted commas at end and beginning of json Now problem is that when we call this json is called in android and browser we get these inverted quotes but not in fiddler. Do i need to make changes in code.

Code I tried:

GetDeviceId.svc.cs:

public string GetDeviceIds()
{
    try
    {
        MySqlCommand command = default(MySqlCommand);
        MySqlDataAdapter adaptor = new MySqlDataAdapter();

        DataTable dt = new DataTable();
        string sql = "select  deviceid from userreg";

        digitalindia.Open();
        command = new MySqlCommand(sql, digitalindia);
        adaptor.SelectCommand = command;

        adaptor.Fill(dt);
        adaptor.Dispose();

        command.Dispose();
        digitalindia.Close();

        if (dt.Rows.Count > 0)
        {
             string json = GetJson(dt);
             //var j = JsonConvert.SerializeObject(json);
             //j = j.Substring(2);
             //json
             //string json = GetJson(dt);
             //json.Remove(1, json.Length - 1);
             //var json1 = EvaluateException(json);
             //ArrayList json = new ArrayList();
             //ArrayList json = new ArrayList();
             //json.Add(DataTableToJsonWithStringBuilder(dt));
             return json;
             //return string.Format("You entered: {0}", json); ;
         }
         else
         {
             string json ="null";
             return json;
             //return "No Party Found";
         }
     }
     catch (Exception ex)
     {
         //ArrayList json = new ArrayList();
         //return ex;
         return ex.Message.ToString();
     }
}

private string GetJson(DataTable dt)
{
    System.Web.Script.Serialization.JavaScriptSerializer Jserializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rowsList = new List<Dictionary<string, object>>();
    Dictionary<string, object> row = null;

    foreach (DataRow dr in dt.Rows)
    {
        row = new Dictionary<string, object>();

        foreach (DataColumn col in dt.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }

        rowsList.Add(row);
    }

    return Jserializer.Serialize(rowsList);
}

IGetDeviceId.svc:

[ServiceContract]
public interface IGetDeviceId
{
    [OperationContract()]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare, UriTemplate = "GetDeviceId")]
    string GetDeviceIds();       
}

In URL i get json but between " " so we have to remove this inverted commas in android which calls this services can we remove these " " in our code(wcf service made in c# code).Or we have to do that in android app only. — Navdeep
– Navdeep, Commented Dec 3, 2015 at 15:24
Link To Service : gps.traxistar.net/GetDeviceId.svc/GetDeviceId — Navdeep
– Navdeep, Commented Dec 3, 2015 at 15:46

Osama Ibrahim · Accepted Answer · 2016-05-02 06:23:19Z

you can use this code :its take a query dynamically and return json

  public List<List<string>> GetDataAsList(string query)
    {
        try
        {
            intializeConnection();



                    DataTable dataTable;
                    DataSet dataset = new DataSet();
                    List<List<string>> data;
                    query = query.Trim().ToUpper();
                    DataRow row = null;
                    string[] commasCount;
                    string[] commasCountWithoutFrom;

                    if (query.Contains('*'))
                    {
                        return null;
                    }
                    commasCount = query.Trim().Split(new string[] { "FROM" }, StringSplitOptions.None);
                    string commasString = commasCount[0].Trim();
                    commasCountWithoutFrom = commasString.Trim().Split(',');
                    ArrayList columnsName2 = new ArrayList();

                    for (int c = 0; c < commasCountWithoutFrom.Length; c++)
                    {
                        string raw = commasCountWithoutFrom[c].Trim();
                        string[] parts = raw.Trim().Split(' ');
                        string sub_parts = parts[parts.Length - 1];
                        columnsName2.Add(sub_parts.ToString());
                    }


            List<List<string>> resultArr=null;

            string cmdStr = String.Format(query);
            command = new SqlCommand(cmdStr, connection);
            set = new DataSet();
            adapter = new SqlDataAdapter(command);
            adapter.Fill(set);

            if (set.Tables[0].Rows.Count > 0)
            {
                resultArr = new List<List<string>>();


                for (int i = 0; i < set.Tables[0].Rows.Count; i++)
                {

                    resultArr.Add(new List<string>());
                    for (int col = 0; col < columnsName2.Count; col++)
                    {

                        resultArr[i].Add(set.Tables[0].Rows[i][columnsName2[col].ToString()].ToString().Trim());
                    }


                }
            }
            else
            {

                    resultArr = new List<List<string>>();
                    resultArr.Add(new List<string>());
                    for (int col = 0; col < columnsName2.Count; col++)
                    {
                        resultArr[0].Add("No Data");
                    }



            }

            connection.Close();
            return resultArr;
        }
        catch (SystemException ex)
        {
            connection.Close();
            return null;
        }
    }

and use this one in ISERVICE.cs

 [WebInvoke(Method = "GET", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "GetDataAsList/{query}")]

    List<List<string>> GetDataAsList(string query);

Collectives™ on Stack Overflow

get data from sql server database in json using wcf,c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related