Problem med at hive json formatteret data ud af sql server
HejsaJeg er åbenbart totalt idiot, for jeg fatter ikke hvorfor jeg hele tiden for dette i mit output, når jeg kører min kode:
{"Capacity":4066,"MaxCapacity":2147483647,"Length":2644}}]}
sql'en virker fint og korrekt data kan sagtens udskrives til consolen, så hvad pokker nosser jeg rundt i?
Min kode:
private StringBuilder SELECTFSR(string vat_number)
{
var jsonResult = new StringBuilder();
string commandText = "SELECT label AS[values.label], value AS[values.value], unit AS[values.unit], valid_from AS[values.valid_from], valid_until AS[values.valid_until] "
+ "FROM FSRtest1 "
+ "WHERE vat_number = @ID "
//+ "GROUP BY vat_number "
+ "FOR JSON PATH;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.NVarChar);
command.Parameters["@ID"].Value = vat_number;
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: " + rowsAffected);
SqlDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
jsonResult.Append("[]");
}
else
{
while (reader.Read())
{
jsonResult.Append(reader.GetValue(0).ToString());
Console.WriteLine("raw db output: " + reader.GetValue(0).ToString());
}
var obj = new
{
dataset = new[]
{
new
{
vat_number = vat_number,
values = jsonResult
}
}
};
var json = JsonSerializer.Serialize(obj);
Console.WriteLine("formatted json: " + json);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
connection.Close();
return jsonResult;
}
}
Det fulde debug output:
raw db output: [{"values":{"label":"CO2 1.1.1.1","value":"405","unit":"ton","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"CO2 1.1.2.1","value":"211","unit":"ton","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 1.4","value":"37.6","unit":"GJ","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 1.4.3","value":"54","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.1","value":"1","unit":"FTE","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.1.1","value":"0","unit":"FTE","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.2","value":"0","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.3","value":"0","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.4","value":"0","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.7.0","value":"2","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.7.1","value":"1","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.8","value":"0","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"CO2 1.1.1.1","value":"5630","unit":"ton","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"CO2 1.1.2.1","value":"351","unit":"ton","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 1.4","value":"355.47","unit":"GJ","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 1.4.3","value":"51","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.1","value":"1.5","unit":"FTE","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.1.1","value":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.2","value
raw db output: ":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.3","value":"0","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.4","value":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.7.0","value":"2","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.7.1","value":"1","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.8","value":"0","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}}]
formatted json: {"dataset":[{"vat_number":"-gdpr-","values":{"Capacity":4066,"MaxCapacity":2147483647,"Length":2644}}]}
[{"values":{"label":"CO2 1.1.1.1","value":"405","unit":"ton","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"CO2 1.1.2.1","value":"211","unit":"ton","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 1.4","value":"37.6","unit":"GJ","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 1.4.3","value":"54","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.1","value":"1","unit":"FTE","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.1.1","value":"0","unit":"FTE","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.2","value":"0","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.3","value":"0","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.4","value":"0","unit":"pct","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.7.0","value":"2","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.7.1","value":"1","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"ESG 2.8","value":"0","unit":"","valid_from":"2021-01-01","valid_until":"2021-12-31"}},{"values":{"label":"CO2 1.1.1.1","value":"5630","unit":"ton","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"CO2 1.1.2.1","value":"351","unit":"ton","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 1.4","value":"355.47","unit":"GJ","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 1.4.3","value":"51","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.1","value":"1.5","unit":"FTE","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.1.1","value":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.2","value":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.3","value":"0","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.4","value":"0","unit":"pct","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.7.0","value":"2","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.7.1","value":"1","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}},{"values":{"label":"ESG 2.8","value":"0","unit":"","valid_from":"2020-01-01","valid_until":"2020-12-31"}}]
