How to return JSON result from C# Web API that gets JSON result from SQL Server Stored Procedure?

Question

I have a Stored Procedure in SQL Server that returns the result as a JSON

From the Web API, I call the Stored Procedure and store the results in an SQLDataReader.

I now have to return the JSON result.

SqlDataReader reader = cmd.ExecuteReader()

I have to convert the JSON string that the Stored Procedure returned in the reader as a JSON response to the API request

I tried this

 using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dataTable = new DataTable();
                    dataTable.Load(reader);
                    return JsonConvert.SerializeObject(dataTable);
                }

The result I get is

"[{\"JSON_F52E2B61-18A1-11d1-B105-00805F49916B\":\"[{\\\"ID\\\":2,\\\"StateCode\\\":42,\\\"CarrierID\\\":1,\\\"Code\\\":\\\"BI\\\",\\\"Type\\\":6,\\\"Name\\\":\\\"Bodily Injury\\\",\\\"Description\\\":\\\"Bodily Injury\\\",\\\"Priority\\\":2,\\\"IsActive\\\":true,\\\"EffectiveDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"CreatedBy\\\":\\\"Admin\\\",\\\"CreatedDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"ModifiedBy\\\":\\\"Admin\\\",\\\"ModifiedDate\\\":\\\"2016-11-03T00:00:00\\\"},{\\\"ID\\\":3,\\\"StateCode\\\":42,\\\"CarrierID\\\":1,\\\"Code\\\":\\\"PD\\\",\\\"Type\\\":6,\\\"Name\\\":\\\"Property Damage\\\",\\\"Description\\\":\\\"Property Damage\\\",\\\"Priority\\\":3,\\\"IsActive\\\":true,\\\"EffectiveDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"CreatedBy\\\":\\\"Admin\\\",\\\"CreatedDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"ModifiedBy\\\":\\\"Admin\\\",\\\"ModifiedDate\\\":\\\"2016-11-03T00:00:00\\\"},{\\\"ID\\\":4,\\\"StateCode\\\":42,\\\"CarrierID\\\":1,\\\"Code\\\":\\\"PIP\\\",\\\"Type\\\":6,\\\"Name\\\":\\\"Personal Injury Protection\\\",\\\"Description\\\":\\\"Personal Injury Protection\\\",\\\"Priority\\\":4,\\\"IsActive\\\":true,\\\"EffectiveDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"CreatedBy\\\":\\\"Admin\\\",\\\"CreatedDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"ModifiedBy\\\":\\\"Admin\\\",\\\"ModifiedDate\\\":\\\"2016-11-03T00:00:00\\\"},{\\\"ID\\\":5,\\\"StateCode\\\":42,\\\"CarrierID\\\":1,\\\"Code\\\":\\\"APIP\\\",\\\"Type\\\":6,\\\"Name\\\":\\\"Additional PIP\\\",\\\"Description\\\":\\\"Additional PIP\\\",\\\"Priority\\\":5,\\\"IsActive\\\":true,\\\"EffectiveDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"CreatedBy\\\":\\\"Admin\\\",\\\"CreatedDate\\\":\\\"2016-11-03T00:00:00\\\",\\\"ModifiedBy\\\":\\\"Admin\\\",\\\"ModifiedDate\\\":\\\"2016-1

What have you tried so far?

Crowcoder
– Crowcoder

2020-02-06 13:17:30 +00:00
Commented Feb 6, 2020 at 13:17 — Crowcoder
– Crowcoder, Commented Feb 6, 2020 at 13:17
I updated the question with the method I tried @Crowcoder

vba
– vba

2020-02-06 13:23:21 +00:00
Commented Feb 6, 2020 at 13:23 — vba
– vba, Commented Feb 6, 2020 at 13:23

vba · Accepted Answer · 2020-02-07 09:34:50Z

2

I found this solution worked for me,

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        jsonResult.Append("");
                    }
                    else
                    {
                        while (reader.Read())
                        {
                            jsonResult.Append(reader.GetValue(0).ToString());
                        }
                    }
                    var response = this.Request.CreateResponse(HttpStatusCode.OK);
                    response.Content = new StringContent(jsonResult.ToString(), Encoding.UTF8, "application/json");
                    return response;
                }

Did not require any JSON Serialize or Deserialize. Plain and simple! Thanks for the help guys.

edited Feb 7, 2020 at 9:34

answered Feb 7, 2020 at 9:22

vba

5949 silver badges21 bronze badges

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

Comments

risteb · Accepted Answer · 2020-02-06 13:29:14Z

1

If your method has a return type ActionResult you can always do return Json(result, JsonRequestBehavior.AllowGet);

answered Feb 6, 2020 at 13:29

risteb

211 silver badge2 bronze badges

1 Comment

vba Over a year ago

'''ActionResult''' is a MVC type return type, I am looking for a Web API type return that handles JSON data. Also the '''Json(result',JsonRequestBehavior.AllowGet) );''' doesn't return a JSON data that the request can handle

Zeeshan · Accepted Answer · 2020-02-06 14:26:56Z

0

The JSON string you are getting from Stored Procedure can be Deserialize into object using Newtonsoft.Json like

    var obj = JsonConvert.DeserializeObject<Object>(jsonFromSp)

then return that object from API

edited Feb 6, 2020 at 14:26

answered Feb 6, 2020 at 14:14

Zeeshan

4962 gold badges5 silver badges21 bronze badges

1 Comment

vba Over a year ago

Thank you @ZeeCode. I am trying to get data from hundreds of tables and return it in the API, meaning I have to create so many objects. Is there a better way?

Chris · Accepted Answer · 2020-02-06 14:31:59Z

0

Have you tried converting your DataTable into a List of a custom object where the object's properties are the column names? Then you should be able to pass the JSON List?

answered Feb 6, 2020 at 14:31

Chris

3871 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to return JSON result from C# Web API that gets JSON result from SQL Server Stored Procedure?

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related