Example #1
0
        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);
            }
        }
Example #2
0
        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());
                }
            }
        }
Example #9
0
 internal SqlSyncItem(SqlSyncTable table, ChangeType changeType, Dictionary <string, object> values) :
     base(table, changeType, values)
 {
 }