(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
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.
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?
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.
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
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