Receiving json string database MS SQL database, formatting problem

Question

I run a C# app, which gets json from MS SQL 2016 using a stored procedure.

string t = da.ExecuteScalar().ToString();

I get strangely formatted json:

"{\"Komponent\":\"00066X96A\",\"Opis\":\"Part2 II F P\\/L S!\\\"31\\\"\",\"Jednos\":\"szt\",\"Enabled\":true,\"Powierzchnia\":0.0070,\" ... SQLcommand added escaped char.

My stored procedure generates clear json from Management Studio, but the C# app adds strange characters.

In C# I execute scalar:

SqlConnection  conn2 = new SqlConnection(builder2.ConnectionString);
conn2.Open();
SqlCommand da = new SqlCommand("[dbo].[R1079]", conn2 );
da.CommandType = CommandType.StoredProcedure;
string t = da.ExecuteScalar().ToString();

This also happens when sent as response in MVC app, not only debugging mode:

public JsonResult Test(int id)
    {
        SqlConnectionStringBuilder builder2 = new qlConnectionStringBuilder();
        builder2.ConnectionString= "Data Source=cz1-dbs\\BER;Initial Catalog=BER;Integrated Security=True;Application Name=Rapor.exe";


        SqlConnection  conn2 = new SqlConnection(builder2.ConnectionString);

        conn2.Open();

        SqlCommand da = new SqlCommand("[dbo].[R1079]", conn2 );

        da.CommandType = CommandType.StoredProcedure;
        string t = da.ExecuteScalar().ToString();

        return Json(t, "application/json", System.Text.Encoding.UTF8, JsonRequestBehavior.AllowGet);

    }

These "strange characters" are almost certainly not actually there; they're escapes added by the Visual Studio debugger when it visualizes the value (as if you had written string t = "{\"Komponent\":\"00066X96A\",\"Opis\":\"Part2 II F P\\/L S!\\\"31\\\"\",\"Jednos\":\"szt\",\"Enabled\":true,\"Powierzchnia\":0.0070,\"";). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 10, 2019 at 12:04
I want tell also, that it is send as response in mvc app not only debugging mode. I edited my post. And I tested on Postman: localhost:54047/home/test/554 and get plenty of escape char. — user3768730
– user3768730, Commented Jul 10, 2019 at 12:10

Fabian Claasen · Accepted Answer · 2019-07-10 12:18:09Z

1

You make a string from the result with the ToString method. The 'weird' characters in your string are escaped characters.

You can serialize and deserialize json with the NuGet package Newtonsoft.Json. For example:

JsonConvert.SerializeObject(someobject);

This will generate a json string from the object you pass in.

JsonConvert.DeserializeObject<SomeObject>(jsonString);

This will generate an object of Type SomeObject from the jsonString you pass in.

Hope this helps you!

answered Jul 10, 2019 at 12:18

Fabian Claasen

3043 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3768730 Over a year ago

Thank you! but after adding NewtonSoft I get much more escape char in Postman response: "\"{\\\"Komponent\\\":\\\"000662A\\\",\\\"Opis\\\":\\\"SZ KROTSZA II F P\\\\/L S!\\\\\\\"31\\\\\\\"\\\",\\\"Jednostka\\\":\\\"szt\\\",\\\"Enabled\\\":true,\\\"Powierzchnia\\\":0.0070,\\\"Czas_ciecia\\\":12.0000,\\\"Typ_opakowania\\\":\\\"B3szary \\\",\\\"Ilosc_w_opakowaniu\\\":2000,\\\"Czas_rozrywania\\\":0.0000,\\\"Cecha1\\\":\\\"\\\",\

Fabian Claasen Over a year ago

Did you use the deserialize or the serialize? If you used the serialize it will try to make a json of it again and add more escape char's.

Collectives™ on Stack Overflow

Receiving json string database MS SQL database, formatting problem

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related