CODEWITHSUNDEEP
c#jsonsql-serverstring
Tim Foster
Tim Foster

Reputation: 149

Long json strings truncated when returned via .net API

I have a SQL Server Stored Proc that returns json using FOR JSON. If I call the proc in SQL it returns correctly and the output can be validated with something like jsonlint. However, the proc is called from a .net API layer, and when the json is long-ish, it seems to truncate the string it gets from the proc and thus has invalid json. If I reduce the amount of data returned, I get a valid json string through the API, but if it goes over a certain number of characters, it's truncated.

This result from the proc is fine:

{"APIResult":[{"ID":200,"Status_Message":"Success","User_Message":"Success","OperatingCountries":[{"Country_Name":"Mexico","ISO_Alpha2":"MX","ISO_Alpha3":"MEX","UN_Code":"484","Legal_Age":18,"Default_Language":"es"},{"Country_Name":"United States of America","ISO_Alpha2":"US","ISO_Alpha3":"USA","UN_Code":"840","Legal_Age":21,"Default_Language":"en-us"}]}]}

But this result, is not:

{"APIResult":[{"ID":200,"Status_Message":"Success","User_Message":"Success","OperatingCountries":[{"Country_Name":"Afghanistan","ISO_Alpha2":"AF","ISO_Alpha3":"AFG","UN_Code":"004","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Angola","ISO_Alpha2":"AO","ISO_Alpha3":"AGO","UN_Code":"024","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Albania","ISO_Alpha2":"AL","ISO_Alpha3":"ALB","UN_Code":"008","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Andorra","ISO_Alpha2":"AD","ISO_Alpha3":"AND","UN_Code":"020","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Argentina","ISO_Alpha2":"AR","ISO_Alpha3":"ARG","UN_Code":"032","Legal_Age":18,"Default_Language":"es"},{"Country_Name":"Armenia","ISO_Alpha2":"AM","ISO_Alpha3":"ARM","UN_Code":"051","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"American Samoa","ISO_Alpha2":"AS","ISO_Alpha3":"ASM","UN_Code":"016","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Antarctica","ISO_Alpha2":"AQ","ISO_Alpha3":"ATA","UN_Code":"010","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Antigua and Barbuda","ISO_Alpha2":"AG","ISO_Alpha3":"ATG","UN_Code":"028","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Australia","ISO_Alpha2":"AU","ISO_Alpha3":"AUS","UN_Code":"036","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Austria","ISO_Alpha2":"AT","ISO_Alpha3":"AUT","UN_Code":"040","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Azerbaijan","ISO_Alpha2":"AZ","ISO_Alpha3":"AZE","UN_Code":"031","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Belgium","ISO_Alpha2":"BE","ISO_Alpha3":"BEL","UN_Code":"056","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bangladesh","ISO_Alpha2":"BD","ISO_Alpha3":"BGD","UN_Code":"050","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bahrain","ISO_Alpha2":"BH","ISO_Alpha3":"BHR","UN_Code":"048","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bahamas","ISO_Alpha2":"BS","ISO_Alpha3":"BHS","UN_Code":"044","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bosnia and Herzegovina","ISO_Alpha2":"BA","ISO_Alpha3":"BIH","UN_Code":"070","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bermuda","ISO_Alpha2":"BM","ISO_Alpha3":"BMU","UN_Code":"060","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bolivia","ISO_Alpha2":"BO","ISO_Alpha3":"BOL","UN_Code":"068","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Barbados","ISO_Alpha2":"BB","ISO_Alpha3":"BRB","UN_Code":"052","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bhutan","ISO_Alpha2":"BT","ISO_Alpha3":"BTN","UN_Code":"064","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Bouvet Island","ISO_Alpha2":"BV","ISO_Alpha3":"BVT","UN_Code":"074","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Botswana","ISO_Alpha2":"BW","ISO_Alpha3":"BWA","UN_Code":"072","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Algeria","ISO_Alpha2":"DZ","ISO_Alpha3":"DZA","UN_Code":"012","Legal_Age":18,"Default_Language":"en-gb"},{"Country_Name":"Mexico","ISO_Alpha2":"MX","ISO_Alpha3":"MEX","UN_Code":"484","Legal_Age":18,"Default_Language":"es"},{"Country_Name":"United States of  America","ISO_Alpha2":"US","ISO_Alpha3":"USA","UN_Code":"840","Legal_Age":21,"Default_Language":"en-us"}]}]}

This is the code in the API project:

cmd2 = new SqlCommand("GetOperatingCountries");
cmd2.Connection = conn2;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandTimeout = 1000;

SqlParameter param = new SqlParameter();
param.ParameterName = "DefaultLanguage";
if (DefaultLanguage.ToUpper() == "NULL")
{ param.Value = DBNull.Value; }
else { param.Value = DefaultLanguage; }
param.SqlDbType = SqlDbType.VarChar;
param.IsNullable = true;
cmd2.Parameters.Add(param);

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd2);
da.Fill(ds);
message = ds.Tables[0].Rows[0][0].ToString();

var response = Request.CreateResponse(HttpStatusCode.OK);
response.Content = new StringContent(message, Encoding.UTF8, "application/json");
return ResponseMessage(response);

The first example produces nice json in postman, but the second just returns the unformatted truncated string.

Can someone explain what I am doing wrong please?

Upvotes: 0

Views: 3132

Answers (2)

kobik
kobik

Reputation: 21252

The returned JSON might be returned in multiple rows for lengthy results.

See Format Query Results as JSON with FOR JSON (section "Output of the FOR JSON clause") and Use FOR JSON output in a C# client app

var queryWithForJson = "SELECT ... FOR JSON";
var conn = new SqlConnection("<connection string>");
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append("[]");
}
else
{
    while (reader.Read())
    {
        jsonResult.Append(reader.GetValue(0).ToString());
    }
}

Upvotes: 7

UsmanMirza
UsmanMirza

Reputation: 276

Try to set max request Lnegth or maxAllowedContentLength

 <httpRuntime targetFramework="4.5" maxRequestLength="65536" />

<security>
  <requestFiltering>
    <requestLimits maxAllowedContentLength="52428800" />
  </requestFiltering>
</security>

Upvotes: 0

Related Questions