private TableSchema(
string tableName,
Column[] columns,
TableVersion sourceVersion,
TableVersion targetVersion,
string targetStatePath,
int? batchSize
)
{
var buffername = tableName.Replace("[", "").Replace("]", "");
TableName = tableName;
SyncNewOrUpdatedTableName = $"sync.[{buffername}_{Guid.NewGuid()}]";
SyncDeletedTableName = $"sync.[{buffername}_{Guid.NewGuid()}]";
Columns = columns;
SourceVersion = sourceVersion;
TargetVersion = targetVersion;
CreateNewOrUpdatedSyncTableStatement = this.GetCreateNewOrUpdatedSyncTableStatement();
CreateDeletedSyncTableStatement = this.GetCreateDeletedSyncTableStatement();
SourceNewOrUpdatedSelectStatment = this.GetNewOrUpdatedAtSourceSelectStatment();
SourceDeletedSelectStatement = this.GetDeletedAtSourceSelectStatement();
MergeNewOrUpdateStatement = this.GetNewOrUpdatedMergeStatement();
DeleteStatement = this.GetDeleteStatement();
DropNewOrUpdatedTableStatment = SyncNewOrUpdatedTableName.GetDropStatment();
DropDeletedTableStatment = SyncDeletedTableName.GetDropStatment();
TargetStatePath = targetStatePath;
BatchSize = batchSize ?? 1000;
}
private TableSchema(
string tableName,
Column[] columns,
TableVersion sourceVersion,
TableVersion targetVersion,
string targetStatePath,
int? batchSize
)
{
TableName = tableName;
SyncTableName = string.Format(
"sync.[{0}_{1}]",
tableName,
Guid.NewGuid()
);
Columns = columns;
SourceVersion = sourceVersion;
TargetVersion = targetVersion;
CreateSyncTableStatement = this.GetCreateSyncTableStatement();
SourceSelectStatment = this.GetSourceSelectStatment();
MergeStatement = this.GetMergeStatement();
DropStatment = this.GetDropStatment();
TargetStatePath = targetStatePath;
BatchSize = batchSize ?? 1000;
}
public static string GetTableVersionStatement(string tableName, Column[] columns)
{
return string.Format(
@"
SELECT '{0}' AS TableName,
SYS_CHANGE_VERSION AS CurrentVersion,
CHANGE_TRACKING_MIN_VALID_VERSION(
OBJECT_ID('{0}')
) AS MinValidVersion,
GETUTCDATE() AS QueriedDateTime
FROM CHANGETABLE(VERSION {0}, ({1}), ({1})) as t",
tableName,
string.Join(
",",
columns
.Where(column => column.IsPrimary)
.Select(column => column.QuoteName)
)
);
}
public static TableVersion GetSourceVersion(this SqlConnection conn, string tableName, Column[] columns)
{
var tableVersionStatement = SqlStatmentExtenstions.GetTableVersionStatement(tableName, columns);
var result = conn.Query<TableVersion>(
tableVersionStatement
).FirstOrDefault();
return result;
}
public static TableVersion GetSourceVersion(this SqlConnection conn, string tableName, bool globalChangeTracking, Column[] columns)
{
var tableVersionStatement = SqlStatmentExtenstions.GetTableVersionStatement(tableName, globalChangeTracking, columns);
var result = conn.Query<TableVersion>(
tableVersionStatement,
commandTimeout: 5000
).FirstOrDefault();
return result;
}