user4912134 user4912134 - 3 months ago 36
C# Question

Serializing the result set from the Web API

We created a Web API which queries the Oracle Database and returns the result in JSON format. If the data returned is very large it throws an Out of Exception issue. So it was suggested in the other question the issue is because loading that entire result set into memory before serializing it out to the

HttpResponseMessage
.

So created the below classes

[JsonConverter(typeof(OracleDataTableJsonResponseConverter))]
public sealed class OracleDataTableJsonResponse
{
public string ConnectionString { get; private set; }
public string QueryString { get; private set; }
public OracleParameter[] Parameters { get; private set; }

public OracleDataTableJsonResponse(string connStr, string strQuery, OracleParameter[] prms)
{
this.ConnectionString = connStr;
this.QueryString = strQuery;
this.Parameters = prms;
}
}

class OracleDataTableJsonResponseConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return objectType == typeof(OracleDataTableJsonResponse);
}

public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
throw new NotImplementedException("OracleDataTableJsonResponse is only for writing JSON. To read, deserialize into a DataTable");
}

public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
var response = (OracleDataTableJsonResponse)value;

using (var dbconn = new OracleConnection(response.ConnectionString))
{
using (var selectCommand = new OracleCommand(response.QueryString, dbconn))
{
if (response.Parameters != null)
selectCommand.Parameters.AddRange(response.Parameters);

using (var reader = selectCommand.ExecuteReader())
{
writer.WriteDataTable(reader, serializer);
}
}
}
}
}

public static class JsonExtensions
{
public static void WriteDataTable(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
{
if (writer == null || reader == null || serializer == null)
throw new ArgumentNullException();
writer.WriteStartArray();

while (reader.Read())
{
writer.WriteStartObject();

for (int i = 0; i < reader.FieldCount; i++)
{
writer.WritePropertyName(reader.GetName(i));
serializer.Serialize(writer, reader[i]);
}

writer.WriteEndObject();
}

writer.WriteEndArray();
}
}


And the API Controller is

public HttpResponseMessage Getdetails([FromUri] string[] id)
{
var prms = new List<OracleParameter>();
var connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
var inconditions = id.Distinct().ToArray();
var strQuery = @"SELECT
STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,
STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,
Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE,
STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME ,
Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE,
FROM
STCD_PRIO_CATEGORY_DESCR,
WHERE
STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
var sb = new StringBuilder(strQuery);
for (int x = 0; x < inconditions.Length; x++)
{
sb.Append(":p" + x + ",");
var p = new OracleParameter(":p" + x, OracleDbType.NVarchar2);
p.Value = inconditions[x];
prms.Add(p);
}
if (inconditions.Length>0)
sb.Length--;
strQuery = sb.Append(")").ToString();

var returnObject = new { data = new OracleDataTableJsonResponse(connStr, strQuery, prms.ToArray()) };
var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
ContentDispositionHeaderValue contentDisposition = null;
if (ContentDispositionHeaderValue.TryParse("inline; filename=PStudyData.json", out contentDisposition))
{
response.Content.Headers.ContentDisposition = contentDisposition;
}
return response;
}


The issue now is when I call the API it throws the error an exception in exception is in the
class OracleDataTableJsonResponseConverter
in the statement
using( var reader = selectCommand.ExecuteReader())
saying
invalidOperationException Connection must be open for this operation


When I tried to debug I see all the connection strings are being passed correctly but it throws error in the server version like below ,
enter image description here

What could be the issue, I am new to ASP.NET and Oracle connections and I'm kind of stuck here. Any help is greatly appreciated

Answer

You have to explicitly open the connection.

public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
    var response = (OracleDataTableJsonResponse)value;

    using (var dbconn = new OracleConnection(response.ConnectionString))
    {
        dbconn.Open();
        ...
    }
}