public static ReplicatorUsingCDC GetInstance(CDCReplicatorOptions options, string sourceCon, string partitionCon) { _instance.Value._sourceCon = sourceCon; _instance.Value._partitionCon = partitionCon; _instance.Value._options = options; _instance.Value._sqlServerDriver = new SqlServerDriver(_instance.Value._sourceCon); return(_instance.Value); }
public async Task UpgradeAsync(string DbSchema, CDCReplicatorOptions option) { Console.WriteLine("Installing version " + _currentVersion + "..."); await this.IsAgentRunning(); string[] filenames = _assembly.GetManifestResourceNames(); string versionScriptPath = null; if (filenames.Contains($"xAudit.CDC.Scripts.Versions.{_currentVersion}.sql")) { versionScriptPath = $"xAudit.CDC.Scripts.Versions.{_currentVersion}.sql"; } else { var regex = new Regex(@"\d+\.\d+\.\d+"); var allVersions = filenames.Where(x => x.StartsWith("xAudit.CDC.Scripts.Versions")).Select(x => (Version)regex.Match(x).Value).ToArray(); Version previousVersion = _currentVersion.FindImmediatePrevious(allVersions); versionScriptPath = $"xAudit.CDC.Scripts.Versions.{previousVersion}.sql"; Console.WriteLine("script not found for current version. Instead executing script from previous version " + previousVersion); } using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }, TransactionScopeAsyncFlowOption.Enabled)) { //execute cleanup query. Delete all SP, UDF etc belongs to current version StringBuilder query = new StringBuilder(); using (Stream stream = _assembly.GetManifestResourceStream("xAudit.CDC.Scripts.cleanup.sql")) { using (StreamReader reader = new StreamReader(stream)) { query.Append(await reader.ReadToEndAsync()); } } query = query.Replace("xAudit", DbSchema); await _sqlServerDriver.ExecuteTextAsync(query.ToString(), null); //execute current version scripts. Create all SP, UDF etc belongs to current version query = query.Clear(); using (Stream stream = _assembly.GetManifestResourceStream(versionScriptPath)) { using (StreamReader reader = new StreamReader(stream)) { query.Append(await reader.ReadToEndAsync()); } } await _sqlServerDriver.ExecuteScriptAsync(query.ToString()); await this.AddVersion(option, DbSchema); transaction.Complete(); } }
private async Task AddVersion(CDCReplicatorOptions option, string dbSchemaName) { DbParameter[] parameters = new SqlParameter[] { new SqlParameter("@VERSION", this._currentVersion.ToString()), new SqlParameter("@MACHINE", Environment.MachineName), new SqlParameter("@MAJOR", this._currentVersion.Major), new SqlParameter("@MINOR", this._currentVersion.Minor), new SqlParameter("@PATCH", this._currentVersion.Patch), new SqlParameter("@PROCESSID", Process.GetCurrentProcess().Id), new SqlParameter("@TOTALTABLES", option.Tables == null?(object)DBNull.Value:option.Tables.Count), new SqlParameter("@TRACKSCHEMACHANGES", option.TrackSchemaChanges), new SqlParameter("@INSTANCENAME", option.InstanceName), }; await _sqlServerDriver.ExecuteNonQueryAsync(dbSchemaName + ".INSERT_NEW_VERSION", parameters); Console.WriteLine("version " + this._currentVersion + " installed"); }
/// <summary> /// Will try to enable cdc on the given tables if not already enabled /// </summary> /// <param name="tables">The list of tables under appropriate schema names</param> /// <returns>Return the list of tables which got failed to be enabled for cdc</returns> private async Task <AuditTableCollection> CheckAndApplyOnTables(string dbSchemaName, CDCReplicatorOptions option) { var ds = await _sqlServerDriver.GetDataSetAsync(dbSchemaName + ".GET_TRACKED_TABLES@2", null); if (ds == null) { return(option.Tables); } HashSet <string> changedTables = new HashSet <string>(); HashSet <string> activeTables = new HashSet <string>(); HashSet <string> inputTables = AuditTableCollectionHelper.ToHashSet(option.Tables); if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { Console.WriteLine("\nThe following active tables have changed since the last run"); Console.WriteLine("---------------------------------------------------------"); foreach (DataRow row in ds.Tables[0].Rows) { var schema = Convert.ToString(row["SCHEMA"]); var table = Convert.ToString(row["TABLE"]); var tableName = schema + "." + table; if (option.TrackSchemaChanges) { changedTables.Add(tableName); } log(tableName, Convert.ToString(row["COLUMN"]), Convert.ToString(row["CHANGE"])); } if (!option.TrackSchemaChanges) { Console.WriteLine("Warning! Tracking schema changes are disabled for this run"); } } if (ds.Tables[1] != null) { foreach (DataRow row in ds.Tables[1].Rows) { var schema = Convert.ToString(row["SOURCE_SCHEMA"]); var table = Convert.ToString(row["SOURCE_TABLE"]); var tableName = schema + "." + table; activeTables.Add(tableName); } } this.SegregateTables(inputTables, activeTables, changedTables, out HashSet <string> recreate, out HashSet <string> disable, out HashSet <string> enable); await this.Enable(enable, option.InstanceName, option.ForceMerge); await this.Disable(disable, option.InstanceName); await this.Reenable(recreate, option.InstanceName, option.ForceMerge); return(option.Tables); //local functions void log(string instance, string column, string change) { switch (change) { case "-": Console.ForegroundColor = ConsoleColor.Red; break; case "+": Console.ForegroundColor = ConsoleColor.Green; break; default: Console.ForegroundColor = ConsoleColor.Yellow; break; } ; Console.Write($"[{change}]"); Console.ForegroundColor = ConsoleColor.Gray; Console.WriteLine($"{instance}--> {column}"); Console.ResetColor(); } }