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(); }
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... } } } } } }