Click here to Skip to main content
14,926,168 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have created a windows application and fetching data from database into datatable.I am using Newtonsoft json.dll for converting data into json format.The format I get is not the intended output I need.I want a certain specific format.How can I achieve this in my application.I also tried replace method to no avail.

My application code ::
<pre lang="c#"> private void button1_Click(object sender, EventArgs e)
        {
            Open_Connection();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter("select ID,Name from TMaster", SQL_Conn);
            da.Fill(dt);
            DataTableToJSONWithJSONNet(dt);
        }

        public string DataTableToJSONWithJSONNet(DataTable table)
        {
            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(table,Formatting.Indented);
           // JSONString = JSONString.Replace(@"\r","'");
            return JSONString;
        } 


The JSON O/P I receive from the above code :
[
  {
    "ID": 1,
    "Name": "TP_G"
  },
  {
    "ID": 2,
    "Name": "TP_S"
  },
  {
    "ID": 3,
    "Name": "TP_Cl"
  },
  {
    "ID": 4,
    "Name": "TPGM"
  }
]



The json format I need somewhat similar like \'id\' in such a manner:
{ \'id\': \'27GSPMH5711G1ZO\', \'name\': \'072017\', \'gt\': 3782969.01, \'cur_gt\': 3782969.01, \'b2b\': [ { \'ctin\': \'27GSPMH5711G1ZO\', \'inv\': [ { \'inum\': \'9005\', \'idt\': \'01-07-2017\', \'val\': 729248.16, \'pos\': \'27\', \'rchrg\': \'N\', \'inv_typ\': \'R\', \'itms\': [ { \'num\': 1, \'itm_det\': { \'rt\': 5, \'txval\': 10000, \'camt\': 500, \'samt\': 900 } } ] }  ] } ] }


What I have tried:

Tried string replace,tried jsonconvert class various methods but to no avail.
Posted
Updated 20-Sep-17 10:54am
Comments
Richard MacCutchan 20-Sep-17 12:48pm
   
What you receive is the correct format for JSON data. If you want it all mashed up then you need to write some code to create that format yourself.
Roy1209 21-Sep-17 1:02am
   
So how do I write the code ?? Should I first arrange the data into the datatable and then try to convert into json format or 1st do the conversion and then try to bring the data into required format.
Richard MacCutchan 21-Sep-17 3:22am
   
It's already in JSON format. I don't understand what you are trying to achieve.
Mehdi Gholam 20-Sep-17 13:03pm
   
Why do you need that formatting? It is not a valid json string.
Roy1209 21-Sep-17 0:55am
   
Requirement of the api to receive the data in the specified format
Mehdi Gholam 21-Sep-17 0:59am
   
No api will force you to have non standard json, you must be mistaken, check with the api authors.
Roy1209 21-Sep-17 1:09am
   
ok will check and revert back to you.
Graeme_Grant 21-Sep-17 1:47am
   
As in my answer, this will confirm if any JSON is valid or not: JSON Formatter & Validator[^]
Richard Deeming 21-Sep-17 14:37pm
   
The data you've loaded from your database seems to bear absolutely no relation to the expected output.

How are you getting from A to B?

1 solution

This is not a full solution, only addressing the JSON data. JSON[^] has a very specific requirement.

As mentioned in the comments above, the desired output is not a valid format. You can check that here: JSON Formatter & Validator[^]

The corrected format should be:
JavaScript
{ "id": "27GSPMH5711G1ZO", "name": "072017", "gt": 3782969.01, "cur_gt": 3782969.01, "b2b": [ { "ctin": "27GSPMH5711G1ZO", "inv": [ { "inum": "9005", "idt": "01-07-2017", "val": 729248.16, "pos": "27", "rchrg": "N", "inv_typ": "R", "itms": [ { "num": 1, "itm_det": { "rt": 5, "txval": 10000, "camt": 500, "samt": 900 } } ] }  ] } ] }

Here is a readable version:
JavaScript
{
   "id":"27GSPMH5711G1ZO",
   "name":"072017",
   "gt":3782969.01,
   "cur_gt":3782969.01,
   "b2b":[
      {
         "ctin":"27GSPMH5711G1ZO",
         "inv":[
            {
               "inum":"9005",
               "idt":"01-07-2017",
               "val":729248.16,
               "pos":"27",
               "rchrg":"N",
               "inv_typ":"R",
               "itms":[
                  {
                     "num":1,
                     "itm_det":{
                        "rt":5,
                        "txval":10000,
                        "camt":500,
                        "samt":900
                     }
                  }
               ]
            }
         ]
      }
   ]
}

Looking at the original data and the desired output, there appears to be a lot of information missing.
   
Comments
Roy1209 21-Sep-17 1:41am
   
How to provide header to json data like id,name,gt,cur_gt outside of [] brackets.
Graeme_Grant 21-Sep-17 1:45am
   
I don't understand the question. [] is a collection/array. Read move here: JSON Specification[^]
Roy1209 21-Sep-17 3:02am
   
The data is in datatable (dt) with columns gt,cur_gt,id,name
Graeme_Grant 21-Sep-17 3:05am
   
Again, you are still missing fields to match the JSON output that you desire.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900