elpeyotl elpeyotl - 6 months ago 52
SQL Question

Create Json Array in C# with data from SQL

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

Answer
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.