/// <summary>
/// Effective implementation of the Export.
/// </summary>
private void Export(ISimpleTaskHost host, ExportAction initialAction, List <DataRelation> relationsToAdd)
{
// Create action queue and seed with initialAction:
var actionQueue = new ExportActionsQueue();
actionQueue.Enqueue(initialAction);
// Run over all actions in queue:
while (actionQueue.Count > 0)
{
// Get next action:
var action = actionQueue.Dequeue();
// Host handling:
if (host.IsAbortRequested)
{
host.Aborting(); return;
}
host.Out.WriteLine(" FROM {0} WHERE {1} [{2}]", action.Table, action.Where, (action.Parameters == null) ? "" : String.Join(", ", action.Parameters));
// Retrieve or Create table definition on dataset:
var dsTableName = action.Table.SchemaDotName;
var dsTable = (DataTable)null;
if (TargetDataSet.Tables.Contains(dsTableName))
{
// Retrieve table:
dsTable = TargetDataSet.Tables[dsTableName];
}
else
{
// Create table:
Connection.FillDataSet(TargetDataSet, dsTableName, "SELECT * FROM " + action.Table.ToString() + " WHERE (1=0)", MissingSchemaAction.AddWithKey);
dsTable = TargetDataSet.Tables[dsTableName];
// Create relational constraints to tables already existing, in one direction:
foreach (var relation in action.Table.GetFromRelations())
{
if (TargetDataSet.Tables.Contains(relation.ForeignTable.SchemaDotName))
{
var parentCols = new List <DataColumn>();
foreach (var col in relation.PrimaryColumns)
{
parentCols.Add(dsTable.Columns[col.Name]);
}
var childTable = TargetDataSet.Tables[relation.ForeignTable.SchemaDotName];
var childCols = new List <DataColumn>();
foreach (var col in relation.ForeignColumns)
{
childCols.Add(childTable.Columns[col.Name]);
}
relationsToAdd.Add(new DataRelation(relation.SchemaDotName, parentCols.ToArray(), childCols.ToArray()));
}
}
// and in the other direction:
foreach (var relation in action.Table.GetToRelations())
{
if (TargetDataSet.Tables.Contains(relation.PrimaryTable.SchemaDotName))
{
var parentTable = TargetDataSet.Tables[relation.PrimaryTable.SchemaDotName];
if (dsTable == parentTable)
{
continue;
}
var parentCols = new List <DataColumn>();
foreach (var col in relation.PrimaryColumns)
{
parentCols.Add(parentTable.Columns[col.Name]);
}
var childCols = new List <DataColumn>();
foreach (var col in relation.ForeignColumns)
{
childCols.Add(dsTable.Columns[col.Name]);
}
relationsToAdd.Add(new DataRelation(relation.SchemaDotName, parentCols.ToArray(), childCols.ToArray()));
}
}
}
// Construct SQL query for export:
var sql = "SELECT * FROM " + action.Table.ToString() + " WHERE (" + action.Where + ")";
// Append filters (if any):
foreach (var filter in this.TableFilters.Where(f => f.Key == action.Table))
{
sql += " AND (" + filter.Value + ")";
}
using (var cmd = Connection.CreateCommand(sql))
{
// Add system query parameters (if any):
if (action.Parameters != null && action.Parameters.Length > 0)
{
for (int i = 0; i < action.Parameters.Length; i++)
{
cmd.AddParameter("@p" + i, action.Parameters[i]);
}
}
// Add user query parameters (if any) (only on root queries, where action.Parameters = null):
if (action.Parameters == null)
{
foreach (var item in this.QueryArguments)
{
if (!sql.Contains(item.Key))
{
continue; // Skip arguments that do not appear in sql.
}
cmd.AddParameter(item.Key, item.Value);
}
}
// Execute the query:
using (var reader = cmd.ExecuteReader())
{
// Construct a map of columns:
var map = (DataReaderMap)null;
map = new DataReaderMap(reader);
// Iterate over all returned rows:
while (reader.Read())
{
// Retrieve row values:
var values = new object[reader.FieldCount];
reader.GetValues(values);
try
{
// Add row to dataset:
var dataRow = TargetDataSet.Tables[dsTableName].Rows.Add(values);
// Enqueue related child rows for export (except if excluded):
foreach (var rel in action.Table.GetFromRelations())
{
if ((this.ExportMode == DbExportMode.None) && (!this.RelationsToInclude.Contains(rel)))
{
continue;
}
if (this.RelationsToExclude.Contains(rel))
{
continue;
}
var vs = new object[rel.ForeignColumns.Count];
var wh = "(" + String.Join(") AND (", Enumerate.FromTo(0, rel.ForeignColumns.Count - 1).Select(i => "[" + rel.ForeignColumns[i].Name + "] = @p" + i).ToArray()) + ")";
for (int i = 0; i < rel.ForeignColumns.Count; i++)
{
vs[i] = values[map.ColumnIndexes[rel.PrimaryColumns[i].Name]];
}
actionQueue.Enqueue(new ExportAction()
{
Table = rel.ForeignTable, Where = wh, Parameters = vs
});
}
// Enqueue related parent rows for export (if in Full mode or relation explicitely included):
foreach (var rel in action.Table.GetToRelations())
{
if ((this.ExportMode != DbExportMode.Full) && (!this.RelationsToInclude.Contains(rel)))
{
continue;
}
if (this.RelationsToExclude.Contains(rel))
{
continue;
}
var vs = new object[rel.PrimaryColumns.Count];
var wh = "(" + String.Join(") AND (", Enumerate.FromTo(0, rel.PrimaryColumns.Count - 1).Select(i => "[" + rel.PrimaryColumns[i].Name + "] = @p" + i).ToArray()) + ")";
for (int i = 0; i < rel.PrimaryColumns.Count; i++)
{
vs[i] = values[map.ColumnIndexes[rel.ForeignColumns[i].Name]];
}
actionQueue.Enqueue(new ExportAction()
{
Table = rel.PrimaryTable, Where = wh, Parameters = vs
});
}
}
catch (System.Data.ConstraintException)
{
// Ignore primary key violation: record was already present...
}
}
}
}
}
}