Need to change the format of retun data from JSON

Question

0.00/5 (No votes)

See more:

Hi
I'm creating a webservice which should get data from database(sql server) and return it.
Every thing working fine.But what i need is i need to display the data with the format which i need.

Here is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;


namespace Webservice
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        public Service1()
        {
            //Uncomment the following line if using designed components 
            //InitializeComponent(); 
        }
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
       // public string GetEmployees(string SearchTerm)
        public string GetEmployees()
        {
            try
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NSConstr"].ToString());
                con.Open();
                SqlCommand cmd = new SqlCommand();
                //cmd.CommandText = "SELECT *  FROM Contact e WHERE FirstName LIKE '%" + SearchTerm + "%'";
                cmd.CommandText = "SELECT *  FROM Contact e ";
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.SelectCommand.Connection = con;
                da.Fill(ds);
                con.Close();
                // Create a multidimensional array
                string[][] EmpArray = new string[ds.Tables[0].Rows.Count][];
                int i = 0;
               foreach (DataRow rs in ds.Tables[0].Rows)
                {
                        //EmpArray[i] = new string[] { rs["FirstName"].ToString(), rs["LastName"].ToString(), rs["Contactno"].ToString() };
                    EmpArray[i] =  new string[] { "FNAME: " + rs["FirstName"].ToString(), "LName: " + rs["LastName"].ToString(), "Contactno: " + rs["Contactno"].ToString()};
                    i = i + 1;
                }
                // Return JSON data
                JavaScriptSerializer js = new JavaScriptSerializer();
               
                string strJSON = js.Serialize(EmpArray);
                return strJSON;
            }
            catch (Exception ex) { return errmsg(ex); }
        }

        public string errmsg(Exception ex)
        {
            return "[['ERROR','" + ex.Message + "']]";
        }
     }
 }

Here is my output:

[["FNAME: devi","LName: priya ","Contactno: 965577796 "],
["FNAME: arun","LName: kumar  ","Contactno: 9944142109"]"
["FNAME: karu ","LName: ronald","Contactno: 8883205008"]]

but i need the result in following format:(which should contain curly braces and the word cargo at starting.

{ "Cargo": [ {  "FNAME": "devi", "LName": "priya " }, 
{"FNAME": "arun", "LName": "kumar" }, {  "FNAME": "karu ", "LName": "ronald" }] }

can any one please help me

Thanks in advance.

Posted 2-Apr-14 23:26pm

Appdev(Icode)

Add a Solution

2 solutions

Solution 1

Use below helper code

C#

private static Dictionary<string, Dictionary<string, object>> DatatableToDictionary(DataTable dt)
       {
           var cols = dt.Columns.Cast<DataColumn>();
           return dt.Rows.Cast<DataRow>()
                    .ToDictionary(r => dt.Rows.IndexOf(r).ToString(),
                                  r => cols.ToDictionary(c => c.ColumnName, c => r[c.ColumnName]));
       }

public Dictionary<string, Dictionary<string, object>> GetEmployees()
        {
            try
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NSConstr"].ToString());
                con.Open();
                SqlCommand cmd = new SqlCommand();
                //cmd.CommandText = "SELECT *  FROM Contact e WHERE FirstName LIKE '%" + SearchTerm + "%'";
                cmd.CommandText = "SELECT *  FROM Contact e ";
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.SelectCommand.Connection = con;
                da.Fill(ds);
                con.Close();
                
                return DatatableToDictionary(ds.Tables[0]);
            }
            catch (Exception ex) { return errmsg(ex); }
        }

Posted 2-Apr-14 23:37pm

Sanjay K. Gupta

Updated 2-Apr-14 23:56pm

v2

Comments

[no name] 3-Apr-14 5:39am

ok thanks alot.will try

[no name] 3-Apr-14 5:43am

getting 3 errors in this public Dictionary<string,>> GetEmployees() line as Invalid token > in class,struct or interface member declaration,type expected and method must return a avalue

Sanjay K. Gupta 3-Apr-14 5:57am

check again, I have updated the code.

[no name] 3-Apr-14 5:57am

ok thanks

[no name] 3-Apr-14 6:02am

Hi sry for the trouble i'm fresher to json
Now getting error near return errmsg(ex); as "cannot implicitly convert type string to system.collections.generic.dictionary<string,system.collections.generic.dictionary<string,object>>"

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Appdev(Icode) · Accepted Answer · 2014-04-03T02:23:00

Hi
I made few changes in my code now it works perfect

Here is my code:

C#

<pre lang="xml">public string GetEmployees()
        {
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NSConstr"].ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT *  FROM Contact e ";
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.SelectCommand.Connection = con;
            da.Fill(dt);
            con.Close();

            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row = null;
            foreach (DataRow rs in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, rs[col]);
                }
                rows.Add(row);
            }
            return serializer.Serialize(rows);
        }

        public string errmsg(Exception ex)
        {
            return "[['ERROR','" + ex.Message + "']]";
        }

her is my result:

{ "Cargo": [{"Id":1,"FirstName":"devi","LastName":"priya","Contactno":"965577796 "},{"Id":2,"FirstName":"arun","LastName":"kumar","Contactno":"9944142109"},{"Id":3,"FirstName":"karu ","LastName":"ronald","Contactno":"8883205008"}]}