public static DataSet Run(ISimpleTaskHost host, DbExportArgs args)
{
var connection = args.FromConnection;
using (var xtool = new DbExportTool(connection.ConnectionString, connection.ProviderName ?? "System.Data.SqlClient"))
{
xtool.ExportMode = args.ExportMode ?? DbExportMode.None;
foreach (var item in args.RelationsToExclude)
{
xtool.ExcludeRelation(item);
}
foreach (var item in args.RelationsToInclude)
{
xtool.IncludeRelation(item);
}
foreach (var item in args.TableFilters)
{
xtool.AddTableFilter(item.TableName, item.WhereCondition);
}
foreach (var item in args.QueryArguments)
{
xtool.QueryArguments[item.Key] = item.Value;
}
foreach (var item in args.Queries)
{
xtool.Export(host, item.TableName, item.WhereCondition);
}
return(xtool.TargetDataSet);
}
}
/// <summary>
/// Exports records from the given table that match the given where condition do dataset.
/// </summary>
/// <param name="host">The host executing this task.</param>
/// <param name="fromTable">Table to export rows from.</param>
/// <param name="where">Where condition to match rows to export.</param>
/// <param name="parameters">Optional parameters of the where clausule (named @p0, @p1, @p2,...).</param>
/// <returns>Supports fluent syntax.</returns>
public DbExportTool Export(ISimpleTaskHost host, string fromTable, string where, object[] parameters = null)
{
var relationsToAdd = new List <DataRelation>();
this.Export(
host,
new ExportAction()
{
Table = DatabaseModel.GetTableOrView(fromTable), Where = where, Parameters = parameters
},
relationsToAdd);
foreach (var relation in relationsToAdd)
{
try
{
TargetDataSet.Relations.Add(relation);
}
catch (Exception ex)
{
host.Error.WriteLine(String.Format("Warning: failed to rebuild relation {0} : {1}", relation.RelationName, ex.Message));
}
}
return(this);
}
public void WriteToDatabase(ISimpleTaskHost host, DbConnection connection, DbCommand command)
{
DataColumn pk = Row.Table.PrimaryKey[0];
//Console.WriteLine(String.Format("UPDATE {0} SET {1} = {2} WHERE {3} = {4}", Row.Table.TableName, Column.ColumnName, SourceRow[SourceColumn], pk.ColumnName, Row[pk]));
command.CommandText = String.Format("UPDATE [{0}] SET [{1}] = @SourceValue WHERE [{2}] = @PkValue", Row.Table.TableName.Replace(".", "].["), Column.ColumnName, pk.ColumnName);
command.Parameters["@SourceValue"].Value = SourceRow[SourceColumn];
command.Parameters["@PkValue"].Value = Row[pk];
Debug.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
public void WriteToDatabase(ISimpleTaskHost host, DbConnection connection, DbCommand command)
{
DataColumn pk = Row.Table.PrimaryKey[0];
//Console.WriteLine(String.Format("UPDATE {0} SET {1} = {2} WHERE {3} = {4}", Row.Table.TableName, Column.ColumnName, SourceRow[SourceColumn], pk.ColumnName, Row[pk]));
command.CommandText = String.Format("UPDATE [{0}] SET [{1}] = @SourceValue WHERE [{2}] = @PkValue", Row.Table.TableName.Replace(".", "].["), Column.ColumnName, pk.ColumnName);
command.Parameters["@SourceValue"].Value = SourceRow[SourceColumn];
command.Parameters["@PkValue"].Value = Row[pk];
Debug.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
internal void WriteToDatabase(ISimpleTaskHost host, DbConnection connection, DbTransaction transaction, Dictionary<string, DbCommand> sqlUpdateCmds)
{
//Console.WriteLine(" {0} ({1})", this.Row.Table.TableName, String.Join(", ", this.Values().Take(1).Select(v => (v ?? "##null").ToString())));
var cmd = (DbCommand)null;
try
{
// Retrieve the (cached) SQL command:
if (!sqlUpdateCmds.TryGetValue(this.Row.Table.TableName, out cmd))
{
#region Create Command
var sb = new StringBuilder();
sb.Append("INSERT INTO [");
sb.Append(this.Row.Table.TableName.Replace(".", "].["));
sb.Append("] (");
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement) continue;
sb.Append("[");
sb.Append(col.ColumnName);
sb.Append("], ");
}
sb.Length -= 2;
sb.Append(") VALUES (");
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement) continue;
sb.Append("@");
sb.Append(col.ColumnName);
sb.Append(", ");
}
sb.Length -= 2;
sb.Append(")");
cmd = connection.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.Transaction = transaction;
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement) continue;
cmd.AddParameter("@" + col.ColumnName, null, ParameterDirection.Input, col.DataType.ToDbType());
}
#endregion
Debug.WriteLine(cmd.CommandText);
sqlUpdateCmds[this.Row.Table.TableName] = cmd;
}
// Set parameter values:
var pindex = 0;
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement) continue;
cmd.Parameters[pindex++].Value = Row[col];
}
// Execute:
cmd.ExecuteNonQuery();
}
catch
{
// Provide details about the failing row:
var sb = new StringBuilder();
sb.AppendLine("Failed to insert following row:");
sb.AppendLine(cmd.CommandText);
foreach (DbParameter param in cmd.Parameters)
{
sb.AppendFormat("- {0} = {1}", param.ParameterName, param.Value);
sb.AppendLine();
}
host.Error.Write(sb.ToString());
// Rethrow:
throw;
}
finally
{ }
// Retrieve identity values:
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
col.ReadOnly = false;
Row[col] = sqlUpdateCmds["IDENTITY"].ExecuteScalar();
break;
}
}
}
public DbImportTool Import(ISimpleTaskHost host, DataSet data, DbConnection connection, DbTransaction transaction)
{
// Collections of actions:
var toImport = new Queue<DependentRow>();
var toImportPerTable = new Dictionary<string, List<DependentRow>>();
var toUpdate = new Queue<DependentUpdate>();
var sqlUpdateCmds = new Dictionary<string, DbCommand>();
sqlUpdateCmds["IDENTITY"] = connection.CreateCommand("SELECT @@IDENTITY", CommandType.Text, transaction);
// Change identity column values to negative to avoid conflicts on destination database:
foreach (DataTable table in data.Tables)
{
foreach (DataColumn col in table.Columns)
{
if (col.AutoIncrement)
{
col.ReadOnly = false;
foreach (DataRow row in table.Rows)
{
row[col] = -(int)row[col];
}
}
}
}
// Read all rows:
foreach (DataTable table in data.Tables)
{
var perTable = toImportPerTable[table.TableName] = new List<DependentRow>();
foreach (DataRow row in table.Rows)
{
var drow = new DependentRow() { Row = row };
toImport.Enqueue(drow);
perTable.Add(drow);
}
}
host.Out.WriteLine("Importing {0} data rows...", toImport.Count);
// Connect row dependencies:
foreach (DataRelation relation in data.Relations)
{
// TODO: Should also include rows with non-AutoIncrement Primary Keys and multi-column Primary Keys.
// Ignore relations where primary key is not an AutoIncrement field:
if (relation.ParentColumns.Count() != 1 || !relation.ParentColumns[0].AutoIncrement) continue;
var parentColumn = relation.ParentColumns[0];
var childColumn = relation.ChildColumns[0];
if (childColumn.AllowDBNull)
{
// If foreign key is nullable, make it null and register a delayed update:
foreach (var parentRow in toImportPerTable[relation.ParentTable.TableName])
{
var parentPkValue = parentRow.Row[parentColumn];
foreach (var childRow in toImportPerTable[relation.ChildTable.TableName].Where(dr => Object.Equals(dr.Row[childColumn], parentPkValue)))
{
toUpdate.Enqueue(new DependentUpdate() { Row = childRow.Row, Column = childColumn, SourceRow = parentRow.Row, SourceColumn = parentColumn });
childRow.Row[childColumn] = DBNull.Value;
}
}
}
else
{
// If foreign key row is not nullable, register dependency:
foreach (var parentRow in toImportPerTable[relation.ParentTable.TableName])
{
var parentPkValue = parentRow.Row[parentColumn];
foreach (var childRow in toImportPerTable[relation.ChildTable.TableName].Where(dr => Object.Equals(dr.Row[childColumn], parentPkValue)))
{
parentRow.AddChild(childRow);
}
}
}
}
var totalActions = (double)(toImport.Count + toUpdate.Count);
// Import rows:
while (toImport.Count > 0)
{
var drow = toImport.Dequeue();
if (drow.HasAllDependenciesResolved)
{
drow.WriteToDatabase(host, connection, transaction, sqlUpdateCmds);
drow.MarkResolved();
}
else
{
//Console.WriteLine("SKIPPED : {0} ({1})", drow.Row.Table.TableName, String.Join(", ", drow.Values().Take(1).Select(v => (v ?? "##null").ToString())));
toImport.Enqueue(drow);
}
host.ReportProgress((totalActions - toImport.Count - toUpdate.Count) / totalActions, drow.Row.Table.TableName);
}
// Dispose commands:
sqlUpdateCmds.Values.ToList().ForEach(cmd => cmd.Dispose());
host.Out.WriteLine("\r\nUpdating {0} data rows...", toUpdate.Count);
// Perform delayed updates:
using(var cmd = connection.CreateCommand())
{
cmd.Transaction = transaction;
cmd.AddParameter("@SourceValue", null);
cmd.AddParameter("@PkValue", null);
while (toUpdate.Count > 0)
{
var dupdate = toUpdate.Dequeue();
dupdate.WriteToDatabase(host, connection, cmd);
host.ReportProgress((totalActions - toImport.Count - toUpdate.Count) / totalActions, dupdate.Row.Table.TableName);
}
}
host.Out.WriteLine();
return this;
}
internal void WriteToDatabase(ISimpleTaskHost host, DbConnection connection, DbTransaction transaction, Dictionary <string, DbCommand> sqlUpdateCmds)
{
//Console.WriteLine(" {0} ({1})", this.Row.Table.TableName, String.Join(", ", this.Values().Take(1).Select(v => (v ?? "##null").ToString())));
var cmd = (DbCommand)null;
try
{
// Retrieve the (cached) SQL command:
if (!sqlUpdateCmds.TryGetValue(this.Row.Table.TableName, out cmd))
{
#region Create Command
var sb = new StringBuilder();
sb.Append("INSERT INTO [");
sb.Append(this.Row.Table.TableName.Replace(".", "].["));
sb.Append("] (");
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
continue;
}
sb.Append("[");
sb.Append(col.ColumnName);
sb.Append("], ");
}
sb.Length -= 2;
sb.Append(") VALUES (");
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
continue;
}
sb.Append("@");
sb.Append(col.ColumnName);
sb.Append(", ");
}
sb.Length -= 2;
sb.Append(")");
cmd = connection.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.Transaction = transaction;
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
continue;
}
cmd.AddParameter("@" + col.ColumnName, null, ParameterDirection.Input, col.DataType.ToDbType());
}
#endregion
Debug.WriteLine(cmd.CommandText);
sqlUpdateCmds[this.Row.Table.TableName] = cmd;
}
// Set parameter values:
var pindex = 0;
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
continue;
}
cmd.Parameters[pindex++].Value = Row[col];
}
// Execute:
cmd.ExecuteNonQuery();
}
catch
{
// Provide details about the failing row:
var sb = new StringBuilder();
sb.AppendLine("Failed to insert following row:");
sb.AppendLine(cmd.CommandText);
foreach (DbParameter param in cmd.Parameters)
{
sb.AppendFormat("- {0} = {1}", param.ParameterName, param.Value);
sb.AppendLine();
}
host.Error.Write(sb.ToString());
// Rethrow:
throw;
}
finally
{ }
// Retrieve identity values:
foreach (DataColumn col in this.Row.Table.Columns)
{
if (col.AutoIncrement)
{
col.ReadOnly = false;
Row[col] = sqlUpdateCmds["IDENTITY"].ExecuteScalar();
break;
}
}
}
public DbImportTool Import(ISimpleTaskHost host, DataSet data, DbConnection connection, DbTransaction transaction)
{
// Collections of actions:
var toImport = new Queue <DependentRow>();
var toImportPerTable = new Dictionary <string, List <DependentRow> >();
var toUpdate = new Queue <DependentUpdate>();
var sqlUpdateCmds = new Dictionary <string, DbCommand>();
sqlUpdateCmds["IDENTITY"] = connection.CreateCommand("SELECT @@IDENTITY", CommandType.Text, transaction);
// Change identity column values to negative to avoid conflicts on destination database:
foreach (DataTable table in data.Tables)
{
foreach (DataColumn col in table.Columns)
{
if (col.AutoIncrement)
{
col.ReadOnly = false;
foreach (DataRow row in table.Rows)
{
row[col] = -(int)row[col];
}
}
}
}
// Read all rows:
foreach (DataTable table in data.Tables)
{
var perTable = toImportPerTable[table.TableName] = new List <DependentRow>();
foreach (DataRow row in table.Rows)
{
var drow = new DependentRow()
{
Row = row
};
toImport.Enqueue(drow);
perTable.Add(drow);
}
}
host.Out.WriteLine("Importing {0} data rows...", toImport.Count);
// Connect row dependencies:
foreach (DataRelation relation in data.Relations)
{
// TODO: Should also include rows with non-AutoIncrement Primary Keys and multi-column Primary Keys.
// Ignore relations where primary key is not an AutoIncrement field:
if (relation.ParentColumns.Count() != 1 || !relation.ParentColumns[0].AutoIncrement)
{
continue;
}
var parentColumn = relation.ParentColumns[0];
var childColumn = relation.ChildColumns[0];
if (childColumn.AllowDBNull)
{
// If foreign key is nullable, make it null and register a delayed update:
foreach (var parentRow in toImportPerTable[relation.ParentTable.TableName])
{
var parentPkValue = parentRow.Row[parentColumn];
foreach (var childRow in toImportPerTable[relation.ChildTable.TableName].Where(dr => Object.Equals(dr.Row[childColumn], parentPkValue)))
{
toUpdate.Enqueue(new DependentUpdate()
{
Row = childRow.Row, Column = childColumn, SourceRow = parentRow.Row, SourceColumn = parentColumn
});
childRow.Row[childColumn] = DBNull.Value;
}
}
}
else
{
// If foreign key row is not nullable, register dependency:
foreach (var parentRow in toImportPerTable[relation.ParentTable.TableName])
{
var parentPkValue = parentRow.Row[parentColumn];
foreach (var childRow in toImportPerTable[relation.ChildTable.TableName].Where(dr => Object.Equals(dr.Row[childColumn], parentPkValue)))
{
parentRow.AddChild(childRow);
}
}
}
}
var totalActions = (double)(toImport.Count + toUpdate.Count);
// Import rows:
while (toImport.Count > 0)
{
var drow = toImport.Dequeue();
if (drow.HasAllDependenciesResolved)
{
drow.WriteToDatabase(host, connection, transaction, sqlUpdateCmds);
drow.MarkResolved();
}
else
{
//Console.WriteLine("SKIPPED : {0} ({1})", drow.Row.Table.TableName, String.Join(", ", drow.Values().Take(1).Select(v => (v ?? "##null").ToString())));
toImport.Enqueue(drow);
}
host.ReportProgress((totalActions - toImport.Count - toUpdate.Count) / totalActions, drow.Row.Table.TableName);
}
// Dispose commands:
sqlUpdateCmds.Values.ToList().ForEach(cmd => cmd.Dispose());
host.Out.WriteLine("\r\nUpdating {0} data rows...", toUpdate.Count);
// Perform delayed updates:
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = transaction;
cmd.AddParameter("@SourceValue", null);
cmd.AddParameter("@PkValue", null);
while (toUpdate.Count > 0)
{
var dupdate = toUpdate.Dequeue();
dupdate.WriteToDatabase(host, connection, cmd);
host.ReportProgress((totalActions - toImport.Count - toUpdate.Count) / totalActions, dupdate.Row.Table.TableName);
}
}
host.Out.WriteLine();
return(this);
}
/// <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...
}
}
}
}
}
}