Create Json Array in C# with data from SQL

Question

I have to get Data from SQL and send it as JSON to the frontend of the app. I'm not good in C# so I was Messing around with it. At the moment I get not Valid JSON:

  {"name":"Perez","company":"test"}{"name":"Jespersen","company":"Codeparc"}

As you can see it is not coming as an Array. How can I achieve that? My Code:

 using System;
 using System.Data.SqlClient;
 using Newtonsoft.Json;

 namespace eltklt_webapp
{
//Create the Client Object
public class Client
{
    public string name;
    public string company;

}
public partial class getData : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["type"] == "clients")
        {
                // SQL Server Read Statement Sample
                using (SqlConnection conn = new SqlConnection(Gizmos.getConnectionString()))
            {
                SqlCommand command = new SqlCommand("select * from clients", conn);
                conn.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Client Client = new Client();
                        Client.name = reader["name"].ToString();
                        Client.company = reader["company"].ToString();
                        string jsonString = JsonConvert.SerializeObject(Client);
                        Response.Write(jsonString);
                    }
                }
            }
        Response.End();
        }//end if Clients
    }
}
}

Nagaraj · Accepted Answer · 2016-05-26 13:52:46Z

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["type"] == "clients")
    {
            List<Client> clients=new List<Client>();
            // SQL Server Read Statement Sample
            using (SqlConnection conn = new SqlConnection(Gizmos.getConnectionString()))
        {
            SqlCommand command = new SqlCommand("select * from clients", conn);
            conn.Open();

            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Client Client = new Client();
                    Client.name = reader["name"].ToString();
                    Client.company = reader["company"].ToString();
                    clients.Add(Client);
                    //string jsonString =                     JsonConvert.SerializeObject(Client);

                }
            }
        }
      var jsonString = JsonConvert.SerializeObject(clients);
      Response.Write(jsonString);
    Response.End();
    }//end if Clients
}

Hope this could give you basic idea.

Oofpez · Accepted Answer · 2016-05-26 13:43:20Z

2

Have you tried to put your Client objects into an array or list and call JsonConvert.SerializeObject on the list?

answered May 26, 2016 at 13:43

Oofpez

5241 gold badge7 silver badges18 bronze badges

Comments

elpeyotl · Accepted Answer · 2016-05-26 14:02:52Z

0

Thanks! adding

 List<Client> clients=new List<Client>();

Doing the while loop and pushing the while into the client list with:

                clients.Add(Client);

Then Serialize it, solved the problem! Thank you guys!

answered May 26, 2016 at 14:02

elpeyotl

3721 gold badge3 silver badges15 bronze badges

Comments

Vincent · Accepted Answer · 2016-09-23 06:32:50Z

0

If you can use SQL 2016 there is a much better way.

SELECT name, company from clients FOR JSON PATH

There is also a nice framework for streamlining the new JSON functionality in SQL 2016 here: https://github.com/ahhsoftware/EzAdoVSSolution

answered Sep 23, 2016 at 6:32

Vincent

8827 silver badges13 bronze badges

Collectives™ on Stack Overflow

Create Json Array in C# with data from SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related