Avatar billede chrisrj Forsker
15. september 2022 - 13:15 Der er 5 kommentarer og
2 løsninger

Problem med at hive json formatteret data ud af sql server

Hejsa

Jeg 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"}}]
Avatar billede arne_v Ekspert
15. september 2022 - 14:51 #1
Der er flere ting som springer i øjnene.

1)

var jsonResult = new StringBuilder();
...
while (reader.Read())
                        {
                            jsonResult.Append(reader.GetValue(0).ToString());

hvor jeg havde forventet noget a la:

var jsonResult = new StringBuilder();
...
jsonResult.Append("[");
while (reader.Read())
                        {
                            if(jsonResult.Length() > 1) jsonResult.Append(",");
                            jsonResult.Append(reader.GetValue(0).ToString());

}
jsonResult.Append("]");
Avatar billede arne_v Ekspert
15. september 2022 - 14:53 #2
2)

var obj = new
                        {
                            dataset = new[]
                            {
                                new
                                {
                                    vat_number = vat_number,
                                    values = jsonResult
                                }
                            }
                        };
                        var json = JsonSerializer.Serialize(obj);

forstår jeg ikke. jsonResult er allerede JSON så den skal ikke serialiseres igen.

Så mere noget a la:

var json = "{\"vat_number\"=" + vat_number + ",\"values\"=" + jsonResult + "}";
Avatar billede arne_v Ekspert
15. september 2022 - 14:54 #3
Int32 rowsAffected = command.ExecuteNonQuery();

                    Console.WriteLine("RowsAffected: " + rowsAffected);

er forhåbentligt kun noget debug og som forsvinder.
Avatar billede arne_v Ekspert
15. september 2022 - 14:56 #4
Hvis reader.GetValue(0) returnerer en string som jeg formoder så ville jeg erstatte reader.GetValue(0).ToString() med (string)reader[0].
Avatar billede chrisrj Forsker
15. september 2022 - 15:33 #5
Awesome! Du fik, som altid, fundet vejen til min løsning - selvom jeg måtte fedte lidt mere med det for at få alt til at passe. :)

Dette giver det rette output:
var json = "{\"dataset\":{\"vat_number\":\"" + vat_number + "\",\"values\":" + jsonResult + "}}";

Kombineret med lidt smårettelser i SQL'en. :)
Avatar billede arne_v Ekspert
15. september 2022 - 15:48 #6
Ah ja - jeg havde glemt yderste led "dataset".
Avatar billede chrisrj Forsker
15. september 2022 - 15:51 #7
No worries, jeg var heldigvis ikke mere idiot end at jeg selv kunne regne den ud. ;P

Vi takker og bukker endnu engang. :)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
IT-kurser om Microsoft 365, sikkerhed, personlig vækst, udvikling, digital markedsføring, grafisk design, SAP og forretningsanalyse.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester