0

I need to export data from an Advantage DB, in C# .NET Standard, and write the results in JSON format to file. However, since the amount of data that gets returned can be large, I need to avoid pulling in the entire result set into memory at any given time.

This is my current solution, which ignores the memory constraint.

AdsConnection conn = new AdsConnection(payload.connectionString);
AdsCommand command;
AdsDataReader reader;

try {
  conn.Open();

  command = conn.CreateCommand();
  command.CommandText = (string) payload.query;
  reader = command.ExecuteReader();

  var data = new DataTable();
  data.Load(reader);

  return JsonConvert.SerializeObject(data, Formatting.None, new TrimmingConverter());
} catch (Exception e) {
  return e;
} finally {
  if (conn != null) {
      conn.Close();
  }
}

Loading the reader into a DataTable was the best solution I could find to easily create a representation of the data that could be serialized by Newtonsoft, but data.Load(reader) this is almost certainly pulling in the entire result set into memory.

It seems I can stream the serialization using something along the lines of the following code

AdsConnection conn.Open();
AdsCommand command = conn.CreateCommand();
command.CommandText = (string) payload.query;
AdsDataReader reader = command.ExecuteReader();

DataTable data = new DataTable();
data.Load(reader);

using (FileStream fs = File.Open(@"c:\db_export.json", FileMode.CreateNew))
using (StreamWriter sw = new StreamWriter(fs))
using (JsonWriter jw = new JsonTextWriter(sw))
{ 
  JsonSerializer serializer = new JsonSerializer();
  serializer.Serialize(jw, data);
}

However, I can't seem to find a way around loading the result set entirely into a DataTable first. Is there any way I can stream the result set of the DataReader directly to JsonSerializer?

Display name
  • 1,109
  • 1
  • 15
  • 31
  • 2
    Use the reader directly instead of loading the entire dataset into a DataTable. You'll have to write the elements out one by one, using JsonWriter's methods like `WriteStartObject`, `WriteEndObject` – Panagiotis Kanavos Mar 11 '20 at 14:05
  • As @PanagiotisKanavos said, try a loop `while(reader.Read()){/*Do your stuff*/}` – B. Lec Mar 11 '20 at 14:09
  • You may be able to use the JsonSerializer inside the loop to write individual objects, but you'd still have to explicitly emit the array start/end elements – Panagiotis Kanavos Mar 11 '20 at 14:10
  • Does [JSON.net serialize directly from oledbconnection](https://stackoverflow.com/q/33835729/3744182) answer your question? `DataReaderConverter` from [this answer](https://stackoverflow.com/a/33837306/3744182) works for any `IDataReader`. – dbc Mar 11 '20 at 14:57

0 Answers0