从数据库中获取数据并构建一个用于JSON的字符串

本文关键字：一个 用于 JSON 字符串 数据库 获取 数据 构建 | 更新日期: 2023-09-27 18:07:21

我想从数据库中返回所有数据并建立一个字符串，或者我想循环并将结果写入Json。

我的代码如下: 
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;
        }
    }
}

只返回json中的一行

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

我的数据库中有大约10条记录，我想返回所有必要的记录。结果应该类似于

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

从数据库中获取数据并构建一个用于JSON的字符串

如果您想返回多个JSON对象(每行一个)，您可以返回一个JSON数组，如下所示:

[
    {"ticketAmount":1,"loggedDate":"2016-09-16T08:03:"}, 
    {"ticketAmount":5,"loggedDate":"2016-09-17T08:03:"},
    {"ticketAmount":4,"loggedDate":"2016-09-18T08:03:"}
]
通过使用
var json = serializer.Serialize(entity);
return json;

无论存在多少条匹配记录，您的程序都只序列化一个实体(最后一个)。因此，我建议将所有匹配的实体存储在List中，并序列化List对象。例句:

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;
}
相关文章：