/// <summary>
/// Schreiben der Daten in die Zieltabelle mittel Reader
/// </summary>
/// <param name="reader"></param>
public void WriteToServer(IDataReader reader)
{
SchemaTable = Connection.GetSchema("Columns", new string[] { string.Empty, string.Empty, DestinationTableName });
SchemaTable.DefaultView.Sort = "COLUMN_NAME";
// Tabelle der Zielnamen
ParameterSet.Clear();
if (reader.FieldCount != SchemaTable.Rows.Count)
{
throw new InvalidOperationException($"Columns of reader differs from table {DestinationTableName}");
}
// Mapping Ziel-Felder => Reader-Felder
int[] mapIndex = new int[reader.FieldCount];
Dictionary <string, int> mapNames = new Dictionary <string, int>();
for (int i = 0; i < reader.FieldCount; i++)
{
mapNames[reader.GetName(i)] = i;
}
InsertCommand = Connection.CreateCommand();
foreach (DataRow row in SchemaTable.Rows) // Reihenfolge der Zieltablle
{
string name = $"{row["COLUMN_NAME"]}".Trim();
if (!mapNames.TryGetValue(name, out int pos))
{
throw new InvalidOperationException($"Column {name} not found in table {DestinationTableName}");
}
mapIndex[Convert.ToInt32(row["ORDINAL_POSITION"])] = pos; // Spaltenmapping
string columncast = "BLOB SUB_TYPE 0"; // unbekannt
InputParameter parameter = new InputParameter();
parameter.DbType = DbType.Object;
switch ($"{row["COLUMN_DATA_TYPE"]}".ToUpper())
{
case "DATE":
columncast = "date";
parameter.DbType = DbType.DateTime;
break;
case "CHAR":
columncast = $"char({(int)row["COLUMN_SIZE"]})";
parameter.DbType = DbType.String;
break;
case "VARCHAR":
columncast = $"varchar({(int)row["COLUMN_SIZE"]})";
if ($"{row["CHARACTER_SET_NAME"]}".Trim() == "NONE")
{
columncast += " CHARACTER SET NONE";
}
parameter.DbType = DbType.String;
break;
case "DOUBLE PRECISION":
columncast = "double precision";
parameter.DbType = DbType.Double;
break;
case "BIGINT":
columncast = "bigint";
parameter.DbType = DbType.Int64;
break;
case "INTEGER":
columncast = "integer";
parameter.DbType = DbType.Int32;
break;
case "DECIMAL":
columncast = $"decimal({(int)row["NUMERIC_PRECISION"]},{(int)row["NUMERIC_SCALE"]})";
parameter.DbType = DbType.Decimal;
break;
case "BLOB SUB_TYPE 1":
columncast = "BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8";
parameter.DbType = DbType.String;
break;
}
parameter.CastValue = $"{columncast}=?";
ParameterSet.Add(parameter);
}
int numRows = CreateInsertCommand(int.MaxValue);
long currentRows = 0;
int insertRows = 0;
int numFields = reader.FieldCount;
InsertCommand.Transaction = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
List <object[]> rowValues = new List <object[]>(numRows);
try
{
object[] values = new object[numFields];
while (reader.Read())
{
reader.GetValues(values);
object[] mapValues = new object[numFields];
for (int i = 0; i < numFields; i++)
{
mapValues[i] = values[mapIndex[i]];
}
rowValues.Add(mapValues);
currentRows++; // Anzahl insgesamt
if (currentRows % NotifyAfter == 0)
{
FbRowsCopiedEventArgs args = new FbRowsCopiedEventArgs(currentRows);
FbRowsCopied?.Invoke(this, args);
if (args.Abort)
{
throw new OperationCanceledException("Operation canceled!");
}
}
insertRows++; // eingefügte Zeilen
if (rowValues.Count == numRows)
{
ExecuteInsert(rowValues);
rowValues.Clear(); // wieder freigeben
if (insertRows >= BatchSize)
{
insertRows = 0;
InsertCommand.Transaction.Commit();
InsertCommand.Transaction = Connection.BeginTransaction();
}
}
}
if (rowValues.Count > 0)
{
CreateInsertCommand(rowValues.Count);
ExecuteInsert(rowValues);
}
InsertCommand.Transaction.Commit();
}
catch (Exception exBulk)
{
InsertCommand.Transaction.Rollback();
throw;
}
}