internal override void PopulateForeignKeys(DatabaseObjects.Database database, DbCommand cmd, string schemaName, bool includeSystemSchemas, BackgroundWorker worker) { if (worker != null) { worker.ReportProgress(0, $"Populating foreign keys for {database.DatabaseName}..."); } foreach (var tbl in database.Schemas.First().Tables) { cmd.CommandText = $"pragma foreign_key_list({tbl.TableName})"; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { var foreignKey = new ForeignKey(database); foreignKey.ForeignKeyName = "fk_" + tbl.TableName + "_" + rdr["from"].ToString() + "_" + rdr["to"].ToString(); foreignKey.ParentTable = tbl.Schema.Tables.First(t => t.TableName == rdr["table"].ToString()); foreignKey.ChildTable = tbl; foreignKey.ChildTable.ForeignKeys.Add(foreignKey); foreignKey.Columns.Add(new ForeignKeyColumn() { ParentColumn = foreignKey.ParentTable.Columns.First(c => c.ColumnName == rdr["to"].ToString()), ChildColumn = foreignKey.ChildTable.Columns.First(c => c.ColumnName == rdr["from"].ToString()) }); } } } } }
internal override void PopulateKeyConstraints(DatabaseObjects.Database database, DbCommand cmd, bool includeSystemSchemas, BackgroundWorker worker) { if (worker != null) { worker.ReportProgress(0, $"Populating key constraints for {database.DatabaseName}..."); } foreach (var tbl in database.Schemas.SelectMany(s => s.Tables)) { cmd.CommandText = "pragma table_info(" + tbl.TableName + ")"; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { if ((long)rdr["pk"] == 1) { var constraint = new KeyConstraint(database); constraint.ConstraintName = tbl + "_pkey"; constraint.IsPrimaryKey = true; constraint.Parent = tbl; tbl.KeyConstraints.Add(constraint); var col = new IndexColumn(); col.ColumnName = rdr["name"].ToString(); constraint.Columns.Add(col); } } } rdr.Close(); } } }
public static DatabaseObjectSynchronizationBase GetSynchronization(DatabaseObjects.Database targetDatabase, object forObject) { var genericType = typeof(DatabaseObjectSynchronizationBase <>).MakeGenericType(forObject.GetType()); var type = (from t in typeof(DatabaseObjectSynchronizationBase).Assembly.GetTypes() where t.IsSubclassOf(genericType) select t).First(); return(Activator.CreateInstance(type, targetDatabase, forObject) as DatabaseObjectSynchronizationBase); }
public WorkspaceWithSourceBase(DatabaseObjectBase sourceObject, DatabaseObjects.Database targetDatabase, DatabaseObjectBase targetObject, bool ignoreCase, bool forData, bool condensed) : base(targetDatabase, targetObject) { SourceObject = sourceObject; if (!forData) { populateDifferences(ignoreCase, condensed); } }
internal override void PopulateColumns(DatabaseObjects.Database database, DbCommand cmd, string schemaName, bool includeSystemSchemas, BackgroundWorker worker) { if (worker != null) { worker.ReportProgress(0, $"Populating columns for {database.DatabaseName}..."); } foreach (var tbl in database.Schemas.First().Tables) { cmd.CommandText = $"pragma table_info({tbl.TableName})"; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { var col = new Column(database); col.ColumnName = rdr["name"].ToString(); var colType = rdr["type"].ToString(); var m = Regex.Match(colType, "(.*nvarchar)\\((\\d*)\\)", RegexOptions.IgnoreCase); if (m.Success) { colType = m.Groups[1].Value; col.CharacterMaximumLength = Convert.ToInt16(m.Groups[2].Value); } m = Regex.Match(colType, "(.*numeric)\\((\\d*),(\\d*)\\)", RegexOptions.IgnoreCase); if (m.Success) { colType = m.Groups[1].Value; col.NumericPrecision = Convert.ToInt16(m.Groups[2].Value); col.NumericScale = Convert.ToInt16(m.Groups[3].Value); } if (string.IsNullOrEmpty(colType)) { colType = "text"; } col.ColumnType = ColumnTypes.First(t => t.TypeName.ToLower() == colType.ToLower()); col.IsNullable = rdr["notnull"].ToString() == "0"; var def = rdr["dflt_value"]; if (def != DBNull.Value) { col.ColumnDefault = def.ToString(); } col.IsIdentity = rdr["pk"].ToString() == "1"; col.Parent = tbl; tbl.Columns.Add(col); } } rdr.Close(); } } }
public virtual List <View> GetSchemaViews(DbConnection connection, DatabaseObjects.Database database) { // if (_schemaViews != null) return _schemaViews; _schemaViews = new List <View>(); if (string.IsNullOrEmpty(this.SchemaViewSQL)) { return(_schemaViews); } using (var cmd = connection.CreateCommand()) { _schemaViews = populateObjects <View>(database, cmd, string.Format(this.SchemaViewSQL, database.DatabaseName), string.Empty, true, string.Empty, string.Empty, null); } return(_schemaViews); }
public static List <SynchronizationItem> GetExtendedProperties(DatabaseObjects.Database targetDatabase, DatabaseObjectWithExtendedProperties sourceObject, DatabaseObjectWithExtendedProperties targetObject) { // TODO:? if (sourceObject.Database.DataSource.GetType().FullName != targetDatabase.DataSource.GetType().FullName) { return(new List <SynchronizationItem>()); } var items = new List <SynchronizationItem>(); var skips = new List <string>(); if (targetObject != null) { foreach (var toProperty in targetObject.ExtendedProperties) { var fromProp = sourceObject.ExtendedProperties.FirstOrDefault(p => p.PropName == toProperty.PropName); if (fromProp == null) { items.AddRange(new ExtendedPropertySynchronization(targetObject.Database, toProperty).GetDropItems(sourceObject)); } else { var toItems = new ExtendedPropertySynchronization(targetObject.Database, fromProp).GetSynchronizationItems(toProperty, true, false); if (toItems.Any()) { items.AddRange(toItems); } skips.Add(toProperty.PropName); } } } foreach (var fromProperty in sourceObject.ExtendedProperties) { if (skips.Contains(fromProperty.PropName)) { continue; } items.AddRange(new ExtendedPropertySynchronization(targetDatabase, fromProperty).GetCreateItems()); } return(items); }
public void SetUp() { var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "TestDatabase.db3"); if (File.Exists(databasePath)) File.Delete(databasePath); SqliteConnection.CreateFile(databasePath); this.database = new DatabaseObjects.Database(new SqliteConnection("Data Source=" + databasePath), DatabaseObjects.Database.ConnectionType.SQLite); var createTable = new DatabaseObjects.SQL.SQLCreateTable(); createTable.Name = "T1"; createTable.Fields.Add("Name", DatabaseObjects.SQL.DataType.UnicodeVariableCharacter, 100); using (var connection = new DatabaseObjects.ConnectionScope(this.database)) connection.ExecuteNonQuery(createTable); }
public void SetUp() { var databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "TestDatabase.db3"); if (File.Exists(databasePath)) { File.Delete(databasePath); } SqliteConnection.CreateFile(databasePath); this.database = new DatabaseObjects.Database(new SqliteConnection("Data Source=" + databasePath), DatabaseObjects.Database.ConnectionType.SQLite); var createTable = new DatabaseObjects.SQL.SQLCreateTable(); createTable.Name = "T1"; createTable.Fields.Add("Name", DatabaseObjects.SQL.DataType.UnicodeVariableCharacter, 100); using (var connection = new DatabaseObjects.ConnectionScope(this.database)) connection.ExecuteNonQuery(createTable); }
public void PopulateSchemas(DbConnection connection, DatabaseObjects.Database database) { database.Schemas = new List <Schema>(); if (string.IsNullOrEmpty(this.SchemaSQL)) { database.Schemas.Add(new Schema(database) { SchemaName = "" }); } else { var conn = connection ?? OpenConnection(database.DatabaseName); using (var cmd = conn.CreateCommand()) { populateObjects <Schema>(database, cmd, string.Format(this.SchemaSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, null); } if (connection == null) { conn.Dispose(); conn = null; } } }
public KeyConstraintSynchronization(DatabaseObjects.Database targetDatabase, KeyConstraint constraint) : base(targetDatabase, constraint) { }
private List <TDatabaseObject> populateObjects <TDatabaseObject>(DatabaseObjects.Database database, DbCommand cmd, string query, string forSchema, bool includeSystemSchemas, string additionalPreWhere, string additionalPostWhere, BackgroundWorker worker) where TDatabaseObject : DatabaseObjectBase { if (string.IsNullOrEmpty(query)) { return(new List <TDatabaseObject>()); } if (worker != null) { if (worker.CancellationPending) { return(null); } worker.ReportProgress(0, $"Populating {typeof(TDatabaseObject).Name.CamelCaseToSpaced()}s for {database.DatabaseName}..."); } var objs = new List <TDatabaseObject>(); query += additionalPreWhere; if (!string.IsNullOrEmpty(forSchema)) { query = $@"select * from ( {query} ) z where {GetConvertedObjectName("SchemaName")} = '{forSchema}'" ; } else if (!includeSystemSchemas && SystemSchemaNames.Count > 0) { query = $@"select * from ( {query} ) z where {GetConvertedObjectName("SchemaName")} is null or {GetConvertedObjectName("SchemaName")} not in ({string.Join(", ", SystemSchemaNames.Select(s => "'" + s + "'").ToArray())})" ; } query += additionalPostWhere; cmd.CommandText = query; cmd.CommandTimeout = 60000; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { var obj = rdr.ToObject <TDatabaseObject>(database); objs.Add(obj); var values = new Dictionary <string, object>(); for (int i = 0; i < rdr.FieldCount; i++) { var col = rdr.GetName(i); values.Add(col, rdr[col]); } obj.RawValues = values; if (worker != null) { if (worker.CancellationPending) { rdr.Close(); return(null); } worker.ReportProgress(0, $"Populating {typeof(TDatabaseObject).Name.CamelCaseToSpaced()}s for {database.DatabaseName} - {obj.ProgressDisplay}..."); } } } rdr.Close(); } foreach (var obj in objs) { obj.setObjectProperties(cmd.Connection, obj.RawValues); } return(objs); }
public ViewSynchronization(DatabaseObjects.Database targetDatabase, View view) : base(targetDatabase, view) { }
public SchemaSynchronization(DatabaseObjects.Database targetDatabase, Schema schema) : base(targetDatabase, schema) { }
internal virtual void PopulateIndexes(DatabaseObjects.Database database, DbCommand cmd, bool includeSystemSchemas, BackgroundWorker worker) { populateObjects <Index>(database, cmd, string.Format(this.IndexSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); }
internal virtual void PopulateColumns(DatabaseObjects.Database database, DbCommand cmd, string forSchema, bool includeSystemSchemas, BackgroundWorker worker) { populateObjects <Column>(database, cmd, string.Format(this.ColumnSQL, database.DatabaseName), forSchema, false, string.Empty, string.Empty, worker); }
public TableSynchronization(DatabaseObjects.Database targetDatabase, Table table) : base(targetDatabase, table) { }
internal override void PopulateIndexes(DatabaseObjects.Database database, DbCommand cmd, bool includeSystemSchemas, BackgroundWorker worker) { if (worker != null) { worker.ReportProgress(0, "Populating indexes for " + database.DatabaseName + "..."); } foreach (var tbl in database.Schemas.First().Tables) { cmd.CommandText = $"pragma index_list({tbl.TableName})"; DataTable dt = new DataTable(); using (var rdr = cmd.ExecuteReader()) { dt.Load(rdr); } foreach (DataRow dr in dt.Rows) { var index = tbl.Indexes.FirstOrDefault(i => i.IndexName == dr["name"].ToString() && i.Table.TableName == tbl.TableName); if (index == null) { index = new Index(database) { IndexName = dr["name"].ToString(), // TODO IndexType = "NONCLUSTERED", IsUnique = dr["unique"].ToString() == "1", Table = tbl }; index.IndexColumns = new List <IndexColumn>(); index.Table.Indexes.Add(index); } cmd.CommandText = $"pragma index_info({index.IndexName})"; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { index.IndexColumns.Add(new IndexColumn() { ColumnName = rdr["name"].ToString(), Ordinal = Convert.ToInt16(rdr["seqno"]) }); } } } } foreach (var index in tbl.Indexes) { cmd.CommandText = $"select * from sqlite_master where type = 'index' and name = '{index.IndexName}' and tbl_name = '{index.Table.TableName}'"; using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { index.Definition = rdr["sql"].ToString(); } } } } } }
public SequenceSynchronization(DatabaseObjects.Database targetDatabase, Sequence sequence) : base(targetDatabase, sequence) { }
public DefaultConstraintSynchronization(DatabaseObjects.Database targetDatabase, DefaultConstraint constraint) : base(targetDatabase, constraint) { }
public DatabaseWorkspace(DatabaseObjectBase sourceObject, DatabaseObjects.Database targetDatabase, DatabaseObjectBase targetObject, bool ignoreCase, bool condensed) : base(sourceObject, targetDatabase, targetObject, ignoreCase, false, condensed) { }
public void PopulateTablesAndColumns(DatabaseObjects.Database database, BackgroundWorker worker) { if (database == null) { database = CurrentDatabase; } database.Schemas = new List <Schema>(); var fks = new List <ForeignKey>(); using (var conn = OpenConnection(database.DatabaseName)) { using (var cmd = conn.CreateCommand()) { if (string.IsNullOrEmpty(this.SchemaSQL)) { database.Schemas.Add(new Schema(database) { SchemaName = "" }); } else { populateObjects <Schema>(database, cmd, string.Format(this.SchemaSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); } cmd.CommandText = string.Format(this.CombinedSQL, database.DatabaseName); using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { while (rdr.Read()) { var rawTbl = rdr.ToObject <Table>(database); var schema = database.Schemas.First(s => s.SchemaName == rawTbl.SchemaName); var tbl = schema.Tables.FirstOrDefault(t => t.TableName == rawTbl.TableName); if (tbl == null) { tbl = rawTbl; if (worker != null) { worker.ReportProgress(0, $"Populating Table {tbl.TableName} for {database.DatabaseName}..."); } tbl.Schema = schema; schema.Tables.Add(tbl); } var col = rdr.ToObject <Column>(database); if (!tbl.Columns.Any(c => c.ColumnName == col.ColumnName)) { col.Schema = schema; // col.ColumnType = database.DataSource.GetColumnType(rdr["DataType"].ToString(), col.ColumnDefault); col.Parent = tbl; tbl.Columns.Add(col); } if (rdr["ForeignKeyName"] != DBNull.Value) { var fk = rdr.ToObject <ForeignKey>(database); // TODO: WHY??? if (fk.ParentTableSchema == "tmp" || fk.ChildTableSchema == "tmp") { continue; } fk.Schema = schema; // tbl.ForeignKeys.Add(fk); fks.Add(fk); } //var values = new Dictionary<string, object>(); //for (int i = 0; i < rdr.FieldCount; i++) //{ // var col = rdr.GetName(i); // values.Add(col, rdr[col]); //} //obj.RawValues = values; //if (worker != null) worker.ReportProgress(0, $"Populating {typeof(TDatabaseObject).Name.CamelCaseToSpaced()}s for {database.DatabaseName} - {obj.ProgressDisplay}..."); } } rdr.Close(); } } // PopulateTables(conn, database.Schemas.ToArray(), false); foreach (var fk in fks) { fk.ParentTable = database.Schemas.Find(s => s.SchemaName == fk.ParentTableSchema).Tables.Find(t => t.TableName == fk.ParentTableName); fk.ChildTable = database.Schemas.Find(s => s.SchemaName == fk.ChildTableSchema).Tables.Find(t => t.TableName == fk.ChildTableName); fk.Columns.Add(new ForeignKeyColumn() { ChildColumn = fk.ChildTable.Columns.First(c => c.ColumnName == fk.ChildColumnName), ParentColumn = fk.ParentTable.Columns.First(c => c.ColumnName == fk.ParentColumnName) }); var curr = fk.ChildTable.ForeignKeys.FirstOrDefault(x => x.ForeignKeyName == fk.ForeignKeyName); if (curr != null) { curr.Columns.AddRange(fk.Columns); } else { fk.ChildTable.ForeignKeys.Add(fk); } } } }
public TriggerSynchronization(DatabaseObjects.Database targetDatabase, Trigger trigger) : base(targetDatabase, trigger) { }
public void PopulateChildren(DatabaseObjects.Database database, bool condensed, BackgroundWorker worker) { if (database == null) { database = CurrentDatabase; } database.ExtendedProperties = new List <ExtendedProperty>(); database.Schemas = new List <Schema>(); database.ServerLogins = new List <ServerLogin>(); database.Principals = new List <DatabasePrincipal>(); database.Permissions = new List <Permission>(); database.Credentials = new List <Credential>(); database.Extensions = new List <Extension>(); using (var conn = OpenConnection(database.DatabaseName)) { using (var cmd = conn.CreateCommand()) { populateObjects <ExtendedProperty>(database, cmd, string.Format(this.ExtendedPropertySQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); populateObjects <DatabasePrincipal>(database, cmd, string.Format(this.DatabasePrincipalSQL, database.DatabaseName), string.Empty, true, string.Empty, string.Empty, worker); foreach (var dp in database.Principals) { if (dp.OwningPrincipalID > 0) { dp.Owner = database.Principals.First(p => p.PrincipalID == dp.OwningPrincipalID); dp.Owner.Ownings.Add(dp); } } if (string.IsNullOrEmpty(this.SchemaSQL)) { database.Schemas.Add(new Schema(database) { SchemaName = "" }); } else { populateObjects <Schema>(database, cmd, string.Format(this.SchemaSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); } populateObjects <RoutineSynonym>(database, cmd, string.Format(this.RoutineSynonymSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); populateObjects <View>(database, cmd, string.Format(this.ViewSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); if (!condensed) { populateObjects <ServerLogin>(database, cmd, string.Format(this.ServerLoginSQL, database.DatabaseName), string.Empty, true, string.Empty, string.Empty, worker); populateObjects <Permission>(database, cmd, string.Format(this.PermissionSQL, database.DatabaseName), string.Empty, true, string.Empty, string.Empty, worker); populateObjects <Credential>(database, cmd, string.Format(this.CredentialSQL, database.DatabaseName), string.Empty, true, string.Empty, string.Empty, worker); } populateObjects <Table>(database, cmd, string.Format(this.TableSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); PopulateColumns(database, cmd, string.Empty, false, worker); PopulateForeignKeys(database, cmd, string.Empty, false, worker); PopulateKeyConstraints(database, cmd, false, worker); PopulateIndexes(database, cmd, false, worker); populateObjects <DefaultConstraint>(database, cmd, string.Format(this.DefaultConstraintSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); populateObjects <Trigger>(database, cmd, string.Format(this.TriggerSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); populateObjects <Sequence>(database, cmd, string.Format(this.SequenceSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); populateObjects <Extension>(database, cmd, string.Format(this.ExtensionSQL, database.DatabaseName), string.Empty, false, string.Empty, string.Empty, worker); } conn.Close(); } }
public WorkspaceBase(DatabaseObjects.Database targetDatabase, DatabaseObjectBase targetObject) { TargetDatabase = targetDatabase; TargetObject = targetObject; SynchronizationItems = new List <SynchronizationItem>(); }
internal virtual void PopulateForeignKeys(DatabaseObjects.Database database, DbCommand cmd, string schemaName, bool includeSystemSchemas, BackgroundWorker worker) { populateObjects <ForeignKey>(database, cmd, string.Format(this.ForeignKeySQL, database.DatabaseName), schemaName, false, string.Empty, string.Empty, worker); }
public DatabaseObjectSynchronizationBase(DatabaseObjects.Database targetDb, DatabaseObjectBase obj) { TargetDatabase = targetDb; DatabaseObject = obj; }
public ForeignKeySynchronization(DatabaseObjects.Database targetDatabase, ForeignKey foreignKey) : base(targetDatabase, foreignKey) { }
public bool Transfer(List <TableWorkspace> workspaces, DbTransaction trans, DatabaseObjects.Database fromDatabase) { string tableName = string.Empty; using (var cmd = trans.Connection.CreateCommand()) { cmd.Transaction = trans; cmd.CommandTimeout = 60000; var sort = !workspaces.All(ws => ws.RemoveAddKeys); long i = 0; var selected = workspaces.Where(t => t.SelectTableForData).ToList(); var datas = sort ? getSortedWorkspaces(selected) : selected; var counts = datas.Count(); foreach (var table in datas) { i++; tableName = table.TargetTable.ToString(); if (table.RemoveAddIndexes) { _worker.ReportProgress((int)(100 * i / counts), "Removing indexes for " + tableName); table.TargetTable.RemoveIndexes(cmd); } } i = 0; using (var conn = fromDatabase.OpenConnection()) { using (var cmdSrc = conn.CreateCommand()) { cmdSrc.CommandTimeout = 60000; foreach (var table in datas) { i++; _worker.ReportProgress((int)(100 * i / counts), string.Format("Copying {1} of {2}: {0}", table.SourceTable.GetObjectNameWithSchema(table.TargetDatabase.DataSource), i, counts)); long rowCount = 0; var commonCols = table.SourceTable.Columns.Where(c => string.IsNullOrEmpty(c.Formula)).Select(c => c.ColumnName).Intersect(table.TargetTable.Columns.Select(c => c.ColumnName)); cmdSrc.CommandText = string.Format("select count(*) from {0} {1}", table.SourceTable.GetObjectNameWithSchema(table.SourceTable.Database.DataSource), table.WhereClause); rowCount = Convert.ToInt64(cmdSrc.ExecuteScalar()); cmdSrc.CommandText = string.Format("select {2} from {0} {1}", table.SourceTable.GetObjectNameWithSchema(table.SourceTable.Database.DataSource), table.WhereClause, string.Join(", ", commonCols.Select(c => table.TargetObject.Database.DataSource.GetConvertedObjectName(c)))); using (var rdr = cmdSrc.ExecuteReader()) { if (trans.Connection is SqlConnection) { using (var copy = new SqlBulkCopy((SqlConnection)trans.Connection, (table.KeepIdentity ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.CheckConstraints, (SqlTransaction)trans)) { foreach (var col in table.SourceTable.Columns) { if (!string.IsNullOrEmpty(col.Formula)) { continue; } if (!table.TargetTable.Columns.Any(c => c.ColumnName == col.ColumnName)) { continue; } copy.ColumnMappings.Add(col.ObjectName, col.ObjectName); } copy.BulkCopyTimeout = 600; copy.BatchSize = table.TransferBatchSize == 0 ? TableWorkspace.DEFAULT_BATCH_SIZE : table.TransferBatchSize; copy.NotifyAfter = 500; copy.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e) { if (_worker.CancellationPending) { e.Abort = true; return; } _worker.ReportProgress((int)(100 * i / counts), string.Format("Copying {4} of {5}: [{0}].[{1}] {2} of {3}", table.SourceTable.Schema.SchemaName, table.SourceTable.TableName, e.RowsCopied, rowCount, i, counts)); }; copy.DestinationTableName = string.Format("[{0}].[{1}]", table.TargetTable.Schema.SchemaName, table.TargetTable.TableName); copy.WriteToServer(rdr); } } else { if (rdr.HasRows) { List <string> columns = new List <string>(); for (int j = 0; j < rdr.FieldCount; j++) { columns.Add(rdr.GetName(j)); } var batchSize = table.TransferBatchSize == 0 ? TableWorkspace.DEFAULT_BATCH_SIZE : table.TransferBatchSize; var insertQry = $@"insert into {table.TargetTable.GetObjectNameWithSchema(table.TargetDatabase.DataSource)} ({string.Join(", ", columns.Select(dc => table.TargetObject.Database.DataSource.GetConvertedObjectName(dc)).ToArray())}) values " ; var sb = new StringBuilder(insertQry); bool firstIn = true; long counter = 0; long rowsCopied = 0; DateTime dtStart = DateTime.Now; while (rdr.Read()) { rowsCopied++; if (_worker.CancellationPending) { cmd.Cancel(); break; } sb.AppendLine((firstIn ? "" : ",\r\n") + "(" + string.Join(", ", columns.Select(dc => getInsertValue(rdr, dc)).ToArray()) + ")"); counter++; firstIn = false; if (rowsCopied % 100 == 0) { var totalSeconds = (DateTime.Now - dtStart).TotalSeconds; var recordsPerSecond = rowsCopied / (totalSeconds == 0 ? 1 : totalSeconds); var estimatedSeconds = rowCount / recordsPerSecond; _worker.ReportProgress((int)(100 * i / counts), string.Format("Copying {4} of {5}: {0} {1} of {2} {3}", table.SourceTable.GetObjectNameWithSchema(table.TargetDatabase.DataSource), rowsCopied, rowCount, $"\r\n{Math.Round(totalSeconds / 60, 2)} of {Math.Round(estimatedSeconds / 60, 2)} mins", i, counts)); } if (counter >= batchSize) { cmd.CommandText = sb.ToString(); cmd.ExecuteNonQuery(); sb = new StringBuilder(insertQry); counter = 0; firstIn = true; } } if (!_worker.CancellationPending && !firstIn) { cmd.CommandText = sb.ToString(); cmd.ExecuteNonQuery(); } } } } if (_worker.CancellationPending) { break; } } } conn.Close(); } if (_worker.CancellationPending) { return(false); } i = 0; foreach (var table in datas) { i++; tableName = table.TargetTable.TableName; if (table.RemoveAddIndexes) { _worker.ReportProgress((int)(100 * i / datas.Count()), "Adding indexes for " + tableName); table.TargetTable.AddIndexes(cmd); } } } return(true); }
public DatabasePrincipalSynchronization(DatabaseObjects.Database targetDatabase, DatabasePrincipal principal) : base(targetDatabase, principal) { }
public ExtensionSynchronization(DatabaseObjects.Database targetDatabase, Extension extension) : base(targetDatabase, extension) { }