Ruan Ruan - 2 months ago 6
C# Question

Get data from database and build up a string to use for JSON

I want to return all my data from database and build up a string, or I want to loop and write results to Json.

My code is as follows:

public string GetNewTickets()
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Select LoggedDate, count(ID) as ticketAmount from Tickets WHERE LoggedDate >=dateadd(day,datediff(day,0,GetDate())- 7,0) AND State = '1' group by LoggedDate"))
{
var ticketAmount = 0;
var loggedDate = DateTime.Now;
var ActualloggedDate = string.Empty;
var convertedLoggedDate = string.Empty;

var test = string.Empty;

cmd.Connection = con;
con.Open();
var reader = cmd.ExecuteReader();

if (reader.HasRows)
{
while (reader.Read())
{
ticketAmount = (int)reader["ticketAmount"];
//Convert to JSON format
loggedDate = (DateTime)reader["LoggedDate"];
ActualloggedDate = JsonConvert.SerializeObject(loggedDate, new IsoDateTimeConverter());
convertedLoggedDate = ActualloggedDate.Substring(1, ActualloggedDate.Length - 4);
}
}
var entity = new
{
ticketAmount = ticketAmount,
loggedDate = convertedLoggedDate,
};
var json = serializer.Serialize(entity);
return json;
}
}
}


This returns only one row in json

{"ticketAmount":1,"loggedDate":"2016-09-16T08:03:"}


I have about 10 records in my database and I want to return all the nessesary ones. Result should be something like

{"ticketAmount":1,"loggedDate":"2016-09-16T08:03:"},
{"ticketAmount":5,"loggedDate":"2016-09-17T08:03:"},
{"ticketAmount":4,"loggedDate":"2016-09-18T08:03:"}

Answer

If you want to return multiple JSON objects (one for each row) you can return an JSON array, like this:

[
    {"ticketAmount":1,"loggedDate":"2016-09-16T08:03:"}, 
    {"ticketAmount":5,"loggedDate":"2016-09-17T08:03:"},
    {"ticketAmount":4,"loggedDate":"2016-09-18T08:03:"}
]

By using

var json = serializer.Serialize(entity);
return json;

your program serializes exactly one entity (the last one) regardless of how many matching records exists. Therefore I recommend to store all matching entites in a List and serialize the List object. E.g:

public class EntityDto {
    public int ticketAmount {get; set;}
    public DateTime loggedDate {get; set;}
}

...

public string GetNewTickets()
{
    var entities = new List<EntityDto>();
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("Select LoggedDate, count(ID) as ticketAmount from Tickets WHERE LoggedDate >=dateadd(day,datediff(day,0,GetDate())- 7,0) AND State = '1' group by LoggedDate"))
        {
            var ticketAmount = 0;
            var loggedDate = DateTime.Now;
            var ActualloggedDate = string.Empty;
            var convertedLoggedDate = string.Empty;

            var test = string.Empty;

            cmd.Connection = con;
            con.Open();
            var reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    ticketAmount = (int)reader["ticketAmount"];
                    //Convert to JSON format
                    loggedDate = (DateTime)reader["LoggedDate"];
                    ActualloggedDate = JsonConvert.SerializeObject(loggedDate, new IsoDateTimeConverter());
                    convertedLoggedDate = ActualloggedDate.Substring(1, ActualloggedDate.Length - 4);
                    var entity = new EntityDto
                     {
                         ticketAmount = ticketAmount,
                         loggedDate = convertedLoggedDate,
                     };
                    entites.Add(entity);
                }
            }

        }            
    }
    var json = serializer.Serialize(entities);
    return json;
}
Comments