private async Task SetupTableForUpdatesOrDeletesOnly(SqlSyncTable table, SqlCommand cmd, CancellationToken cancellationToken = default)
{
var existsTriggerCommand = new Func <string, string>((op) => $@"select COUNT(*) from sys.objects where schema_id=SCHEMA_ID('{table.Schema}') AND type='TR' and name='__{table.NameWithSchemaRaw}_ct-{op}__'");
var createTriggerCommand = new Func <string, string>((op) => $@"CREATE TRIGGER [__{table.NameWithSchemaRaw}_ct-{op}__]
ON {table.NameWithSchema}
AFTER {op}
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [__CORE_SYNC_CT](TBL, OP, PK_{table.PrimaryColumnType}, SRC)
SELECT '{table.NameWithSchema}', '{op[0]}' , {(op == "DELETE" ? "DELETED" : "INSERTED")}{$".[{table.PrimaryColumnName}]"}, CONTEXT_INFO()
FROM {(op == "DELETE" ? "DELETED" : "INSERTED")}
END");
cmd.CommandText = existsTriggerCommand("UPDATE");
if (((int)await cmd.ExecuteScalarAsync(cancellationToken)) == 0)
{
cmd.CommandText = createTriggerCommand("UPDATE");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
cmd.CommandText = existsTriggerCommand("DELETE");
if (((int)await cmd.ExecuteScalarAsync(cancellationToken)) == 0)
{
cmd.CommandText = createTriggerCommand("DELETE");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
}
private async Task DisableChangeTrackingForTable(SqlCommand cmd, SqlSyncTable table, CancellationToken cancellationToken)
{
var existsTriggerCommand = new Func <string, string>((op) => $@"select COUNT(*) from sys.objects where schema_id=SCHEMA_ID('{table.Schema}') AND type='TR' and name='__{table.NameWithSchemaRaw}_ct-{op}__'");
var dropTriggerCommand = new Func <string, string>((op) => $@"DROP TRIGGER [__{table.NameWithSchemaRaw}_ct-{op}__]");
cmd.CommandText = existsTriggerCommand("INSERT");
if (((int)await cmd.ExecuteScalarAsync(cancellationToken)) == 1)
{
cmd.CommandText = dropTriggerCommand("INSERT");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
cmd.CommandText = existsTriggerCommand("UPDATE");
if (((int)await cmd.ExecuteScalarAsync(cancellationToken)) == 1)
{
cmd.CommandText = dropTriggerCommand("UPDATE");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
cmd.CommandText = existsTriggerCommand("DELETE");
if (((int)await cmd.ExecuteScalarAsync(cancellationToken)) == 1)
{
cmd.CommandText = dropTriggerCommand("DELETE");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
}
public static Task EnableChangeTrakingAsync(this SqlConnection connection, SqlSyncTable table)
{
var cmdText = $@"ALTER TABLE {table.NameWithSchema} ENABLE CHANGE_TRACKING";
using (var cmd = new SqlCommand(cmdText, connection))
{
return(cmd.ExecuteNonQueryAsync());
}
}
public static async Task EnableChangeTrakingAsync(this SqlConnection connection, SqlSyncTable table, CancellationToken cancellationToken)
{
var cmdText = $@"ALTER TABLE {table.NameWithSchema} ENABLE CHANGE_TRACKING";
using (var cmd = new SqlCommand(cmdText, connection))
{
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
}
public static async Task <bool> GetIsChangeTrakingEnabledAsync(this SqlConnection connection, SqlSyncTable table, CancellationToken cancellationToken)
{
var cmdText = $@"SELECT COUNT(*)
FROM sys.change_tracking_tables
WHERE object_id = OBJECT_ID('{table.NameWithSchema}')";
using (var cmd = new SqlCommand(cmdText, connection))
{
return((int)await cmd.ExecuteScalarAsync(cancellationToken) == 1);
}
}
public static async Task <(string, SqlDbType)[]> GetTableColumnsAsync(this SqlConnection connection, SqlSyncTable syncTable, CancellationToken cancellationToken)
{
var cmdText = $@"SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '{syncTable.Name}' AND TABLE_SCHEMA = '{syncTable.Schema}'";
using (var cmd = new SqlCommand(cmdText, connection))
{
using (var reader = await cmd.ExecuteReaderAsync(cancellationToken))
{
var listOfColumnNames = new List <(string, SqlDbType)>();
while (await reader.ReadAsync(cancellationToken))
{
listOfColumnNames.Add((reader.GetString(0), TryGetSqlDbTypeFromString(reader.GetString(1))));
}
return(listOfColumnNames.ToArray());
}
}
}
public static async Task <string[]> GetIndexColumnNamesAsync(this SqlConnection connection, SqlSyncTable syncTable, string indexName, CancellationToken cancellationToken)
{
var cmdText = $@"SELECT
COL_NAME(b.object_id,b.column_id) AS Column_Name
FROM
sys.indexes AS a
INNER JOIN
sys.index_columns AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE
a.is_hypothetical = 0 AND
a.object_id = OBJECT_ID('{syncTable.NameWithSchema}') AND
a.name = '{indexName}'" ;
using (var cmd = new SqlCommand(cmdText, connection))
{
using (var reader = await cmd.ExecuteReaderAsync(cancellationToken))
{
var listOfColumnNames = new List <string>();
while (await reader.ReadAsync(cancellationToken))
{
listOfColumnNames.Add(reader.GetString(0));
}
return(listOfColumnNames.ToArray());
}
}
}
public static async Task <string[]> GetClusteredPrimaryKeyIndexesAsync(this SqlConnection connection, SqlSyncTable syncTable, CancellationToken cancellationToken)
{
var cmdText = $@"SELECT
name AS Index_Name
FROM
sys.indexes
WHERE
is_hypothetical = 0 AND
index_id != 0 AND
object_id = OBJECT_ID('{syncTable.NameWithSchema}') AND
type_desc = 'CLUSTERED' AND
is_primary_key = 1;" ;
using (var cmd = new SqlCommand(cmdText, connection))
{
using (var reader = await cmd.ExecuteReaderAsync(cancellationToken))
{
var listOfIndexNames = new List <string>();
while (await reader.ReadAsync(cancellationToken))
{
listOfIndexNames.Add(reader.GetString(0));
}
return(listOfIndexNames.ToArray());
}
}
}
internal SqlSyncItem(SqlSyncTable table, ChangeType changeType, Dictionary <string, object> values) :
base(table, changeType, values)
{
}