manu padmanabhan manu padmanabhan - 2 months ago 23
jQuery Question

Converting string builder to json string

I want get data from a SQL Server database using C#. I am trying to get a json string from string builder. I try like this:

public string GetData()
{
using (SqlConnection con = new SqlConnection(this.Connection))
{
con.Open();

SqlCommand command = new SqlCommand("Select TITLE, DURATION, STATUS, TYPE from PROJECTS ", con);

StringBuilder sb = new StringBuilder();
sb.Append("{");

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var k = reader.GetString(3);

if (k == "M")
{
sb.Append("main");
sb.Append("{");
sb.Append("sub:[]");
sb.Append("Tittle:");
sb.AppendFormat("{0}", reader["TITTLE"]);
}

if (k == "S")
{
sb.Append("sub");
sb.Append("{");
sb.Append("task:[]");
sb.Append("Tittle:");
sb.AppendFormat("{0}", reader["TITTLE"]);
}

if (k == "T")
{
sb.Append("task");
sb.Append("{");
sb.Append("Tittle:");
sb.AppendFormat("{0}", reader["TITTLE"]);
}
};
}

sb.Append("}");
sb.AppendLine();

return sb.ToString();
}
}


Now I get the string like

sb = {{main{sub:[]Tittle:newsub{task:[]Tittle:new1task{Tittle:new2}


but my required string is like:

[{"main":{"sub":[{"task":[{"tittle":"new2""}],"tittle":"new1","}],"tittle":"new","}}]


means: my main title is new and sub tittle new1 and task title new2. What change do I need to do to my code to get my required json string??

Answer

There are some problems with your code:

To be a valid JSON string, property names have to be enclosed with double quotes ", so you have to do something like sb.Append("\"main\"");

Every property name has to be followed by a colon :, so you have to do something like sb.Append("\"main\": ");

You are dealing with nested structures, arrays containing objects, which themselves contain arrays ... You can only add the closing bracket ] of an array, after all its items have been added. So do something like

sb.Append("\"sub\": [");
//here you add the subitems in a loop
sb.Append("]");

To be able to do so, you will have to keep track of what structures you have open in the moment and you will have your query to return the rows in the exact required order (ie first the main titles, then the first contained sub, then the tasks contained in that sub, then the second sub, then the tasks ...)

You also never close your higher level open braces {. Same applies here as with arrays. If you add an object, you have to add all its content and then add a closing } brace.

Generally, I would suggest not to create JSON by yourself but use a framework like JSON.net. You can build up your collections as you need them and then call the frameworks serialization method which will generate a valid json string.