CODEWITHSUNDEEP
c#jsonasp.net-web-api
vba
vba

Reputation: 584

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

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

Upvotes: 2

Views: 3761

Answers (4)

vba
vba

Reputation: 584

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.

Upvotes: 2

Chris
Chris

Reputation: 377

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?

Upvotes: 0

Zeeshan
Zeeshan

Reputation: 506

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

Upvotes: 0

risteb
risteb

Reputation: 21

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

Upvotes: 1

Related Questions