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)
{
}
