Converting string builder to json string

Question

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??

i want update it in my page table using loop for that i need output in json format — manu padmanabhan
– manu padmanabhan, Commented Sep 17, 2016 at 5:45

derpirscher · Accepted Answer · 2016-09-17 06:33:08Z

2

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.

answered Sep 17, 2016 at 6:33

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

manu padmanabhan Over a year ago

and more doubt. i want quarts("") for column value . sb.AppendFormat("{0}", reader["TITTLE"]); here i get value a new . instead of new i want like "new"

derpirscher Over a year ago

Yes, if your propertyvalue is a string, you also have to add quotes so use sb.AppendFormat("\"{0}\"", reader["TITTLE"]); But again, I strongly encourage you to use a library to create json. What if for instance one of your TITTLE values contains a quote ". You'll have to take care of this too, or it will break you jsonstring.

Collectives™ on Stack Overflow

Converting string builder to json string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related