How to stream DataRows from IDataReader using Bin2DataStreamer.WriteDataReader for generic SQL

(How) can Bin2DataStreamer be used standalone to process and stream data from an IDataReader

I want to process DB SQL select data on the fly and stream it in chunks (of several rows)

The Bin2DataStreamer looks to have what I need except it requires a SchemaDataTable.

Below is the code I would like to write.
Can this be done? How do I create the necessary schema?
I have found no samples and learned little from Bin2DataStreamer.

private void doSerializeDataRows(System.Data.Common.DbDataReader reader)
{
  System.IO.Stream dataStream = new RemObjects.SDK.Types.Binary();
  RemObjects.DataAbstract.Bin2DataStreamer streamer = new RemObjects.DataAbstract.Bin2DataStreamer(dataStream, RemObjects.DataAbstract.StreamerInitialization.Write);
  RemObjects.DataAbstract.Schema.SchemaDataTable schema = null;
  RemObjects.DataAbstract.DataForAppend dataForAppend = null;
  System.Data.DataTable dataTable = reader.GetSchemaTable(); // How can I create a RemObjects.DataAbstract.Schema.SchemaDataTable from this System.Data.DataTable?
  //streamer.BeginWriteDataReader()
  while (reader.Read())
  {
    streamer.WriteDataReader(reader, schema);
    // Calc CRC for row, ...
    // if enough data : sendRowsToTarget(dataStream);
  } // while
  streamer.EndWriteDataReader(dataForAppend);
  streamer.FinalizeStreamer();
  // send remaining rows to target
  //sendRowsToTarget(dataStream);
}

Note: I also looked at remoteDataAdapter.FillWithDASql(this.reportTable, DASQL, null); but this appears to do a bit too much

FYI: db server, databasename and tables are all dynamic. My plan was to mainly use the RemObjects API and only use DataAbstract for the serialization

Hello

So you need to execute an arbitrary SQL and to send results to the caller? DataService already has a method for this (disabled by default for security reasons). What you need to do is

      using reader := command.ExecuteReader() do begin
        dataStreamer.InitializeStreamer(commandResult, StreamerInitialization.Write);
        try
          dataStreamer.WriteDataReader(reader, Schema.DeriveDataReaderSchema(reader, /* table name here /*), maxRecords, includeSchema);
        finally
          dataStreamer.FinalizeStreamer();
        end;
      end;

This is Oxygene code (not C#), still you should see the idea and method names.

Please note that Bin2 data stream is not completed until the FinalizeStreamer method is called, so it should not be sent to the client before it is finalized.

Regards

1 Like

Thanks for the info Anton.

Part of my goal is to process each datarow on the fly (while reading) and streaming.
e.g. the “// Calc CRC for row, …” from my code.
For that I need to process/write row by row.
Can this be done with WriteDataReader? Should I set maxrecords to 1 and call it multiple times?

By the way: I would also create multiple streamers for an entire select.
So only certain chunks of data can be sent over the internet if required

The main and only purpose of DataStreamer is to fetch data from database and to serialize them as fast as possible. Processing data on the fly is just out of scope of the data streamer.

What is your goal? Ie what do you need to achieve? There could be more efficient ways to go than using Bin2 Data Streamer for the purpose it was not designed for.

For example you could fetch data from IDataReader and then send them to the client as an array of Variant arrays

Or if you really need to read the data sent using DataAbstract DataAdapter you could server-side read several rows into a System.DataTable instance, perform necessary calculations over them and then serialize down that DataTable. Luckily System.DataTable class already exposes a method named CreateDataReader ( DataTable.CreateDataReader Method (System.Data) | Microsoft Docs ) that allows to easily put that DataTable into a data streamer.

Hi Anton,

I have finished my code but I get an error while recreating the DataTable on the RO server side.

On the client side I have:
readDataTable.Clear();
int nrread = localdbcon.FillTable(startrecord, chunksize,readDataTable);
if (roTableSchema == null)
{
roTableSchema = RemObjects.DataAbstract.Schema.Schema.DeriveDataTableSchema(readDataTable);
fragmentDataTable = readDataTable.Clone();
}

    var tableReader = fragmentDataTable.CreateDataReader();
    RemObjects.DataAbstract.Bin2DataStreamer streamer=new RemObjects.DataAbstract.Bin2DataStreamer();
    var stream = new RemObjects.SDK.Types.Binary();
    streamer.InitializeStreamer(stream, RemObjects.DataAbstract.StreamerInitialization.Write);
    streamer.WriteDataReader(fragmentDataTable, roTableSchema);
    streamer.FinalizeStreamer();        

NOW stream gets sent to RO SERVER

On the server side I have:

 string srcTableName = "VerkoopLog";
  RemObjects.DataAbstract.Bin2DataStreamer streamer = new RemObjects.DataAbstract.Bin2DataStreamer();
  var stream = new System.IO.MemoryStream(data);
  var dataTable = new System.Data.DataTable();
  streamer.ReadDataTable(stream, srcTableName, dataTable,true,true);

This gives me exception:
RemObjects.DataAbstract.DAException: 'Cannot find schema for table in stream received from server: VerkoopLog

The stream content looks good.

afbeelding

Why do I get this error?
The DeriveDataTableSchema schema data looks good to me.

TIA,
Frederic

I think I might have found a solution: With
streamer.WriteDataReader(tableReader, roTableSchema,-1,true);
I get further

Yes. Otherwise the resulting stream does not contain the table Schema definition, so the client-side streamer does not know how to properly deserialize the incoming data