77b65bea-e766-4e3c-a1ed-c4c2534b6019trueD0001-SQL-11.sdt.localjazz dev master Prodtrue<ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dllAmazon.Lambda.S3EventsAWSSDK.CoreAWSSDK.S3AmazonAmazon.Auth.AccessControlPolicyAmazon.Auth.AccessControlPolicy.ActionIdentifiersAmazon.InternalAmazon.Lambda.S3EventsAmazon.MissingTypesAmazon.RuntimeAmazon.Runtime.CredentialManagementAmazon.Runtime.CredentialManagement.InternalAmazon.Runtime.EventStreamsAmazon.Runtime.EventStreams.InternalAmazon.Runtime.InternalAmazon.Runtime.Internal.AuthAmazon.Runtime.Internal.SettingsAmazon.Runtime.Internal.TransformAmazon.Runtime.Internal.UtilAmazon.Runtime.SharedInterfacesAmazon.Runtime.SharedInterfaces.InternalAmazon.S3Amazon.S3.EncryptionAmazon.S3.Encryption.InternalAmazon.S3.InternalAmazon.S3.IOAmazon.S3.ModelAmazon.S3.Model.Internal.MarshallTransformationsAmazon.S3.TransferAmazon.S3.UtilAmazon.UtilAmazon.Util.InternalAmazon.Util.Internal.PlatformServicesNewtonsoft.JsonNewtonsoft.Json.ConvertersNewtonsoft.Json.LinqNewtonsoft.Json.SchemaNewtonsoft.Json.SerializationSystemSystem.DiagnosticsSystem.Diagnostics.TracingSystem.IO.CompressionThirdParty.BouncyCastle.Asn1ThirdParty.BouncyCastle.Asn1.UtilitiesThirdParty.BouncyCastle.MathThirdParty.BouncyCastle.OpenSslThirdParty.BouncyCastle.Utilities.IO.PemThirdParty.Ionic.ZlibThirdParty.Json.LitJsonThirdParty.MD5System.ComponentModel
private static string tableQuery = @"SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
, CAST(CASE WHEN ccu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON tc.TABLE_SCHEMA = c.TABLE_SCHEMA AND tc.TABLE_NAME = c.TABLE_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA AND ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND ccu.COLUMN_NAME = c.COLUMN_NAME
WHERE c.TABLE_SCHEMA in ('cci', 'dss')
AND (CASE c.TABLE_SCHEMA
WHEN 'cci' THEN 1
ELSE
CASE WHEN c.TABLE_NAME IN ('DimCaseType', 'CaseTypeFamily', 'CaseTypeFamilyMapping', 'CaseTypeFamilyMappingAPRDRG', 'CaseTypeFamilyMappingICD10', 'CaseTypeFamilyMappingCPT', 'CaseTypeFamilyVersion')
THEN 1 ELSE 0 END END = 1)
AND c.DATA_TYPE NOT IN ('datetime','uniqueidentifier', 'timestamp')
AND NOT (c.COLUMN_NAME = 'RowID' and CAST(CASE WHEN ccu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END AS BIT) = 1)
AND tc.CONSTRAINT_TYPE = 'Primary Key'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION";
private List OrderByStatements;
private List ColumnsToBeExcluded;
void Main()
{
OrderByStatements = new List
{
new OrderByInformation("cci.InitiativeWorkflowHistory", new [] {"FromStep","ToStep"}),
new OrderByInformation("cci.VariationEncounterSavingInfo", new []{"EncounterRecordNumber","PhysicianID"}),
new OrderByInformation("cci.FactOpportunityMetric", new []{"FiscalYearID","FiscalMonthID"}),
new OrderByInformation("cci.FactOpportunitySavings", new []{"FiscalyYearID","FiscalMonthID"}),
new OrderByInformation("dss.DimCaseType", new []{"Name"})
};
ColumnsToBeExcluded = new List
{
new ColumnsToExclude("cci.SystemSetting", new [] {"IsEncrypted"}),
new ColumnsToExclude("cci.Configuration", new [] {"InitiativeIsSimplifiedWorkflow"})
};
var result = GetTables();
var tableList = result.Select(r => r.SchemaTable);
var sqlStatements = result.Select(r => r.SqlSelect);
// result.Dump(nameof(result));
// tableList.Dump(nameof(tableList));
// sqlStatements.Dump(nameof(sqlStatements));
var connections = new ServerInstance[] {
new ServerInstance("D0001-SQL-11.sdt.local","jazz dev master Prod", tableList),
new ServerInstance("D0001-SQL-11.sdt.local", "jazz tst master prod", tableList)
}.ToList();
DoTheComparisons(connections, sqlStatements);
}
// Define other methods and classes here
#region Gather Table Information
public IList
GetTables()
{
return ExecuteQueryDynamic(tableQuery)
.GroupBy(x => new { x.TABLE_SCHEMA, x.TABLE_NAME }, (g, d) => new Table
{
SchemaName = g.TABLE_SCHEMA,
TableName = g.TABLE_NAME,
Columns = d
.Select(y => new Column
{
ColumnName = y.COLUMN_NAME,
DataType = y.DATA_TYPE,
IsPrimaryKey = y.IsPrimaryKey
}),
OrderBy = OrderByStatements?.SingleOrDefault(obs => obs.TableName == $"{g.TABLE_SCHEMA}.{g.TABLE_NAME}")?.OrderBy,
Exclude = ColumnsToBeExcluded?.SingleOrDefault(ctbe => ctbe.TableName == $"{g.TABLE_SCHEMA}.{g.TABLE_NAME}")?.ColumnNames ?? new List()
}).ToList();
}
public class Table
{
public string SchemaName { get; set; }
public string TableName { get; set; }
public IEnumerable Columns { get; set; }
public IEnumerable OrderBy { get; set; }
public IEnumerable Exclude { get; set; }
internal string SchemaTable => $"{SchemaName}.{TableName}";
public string SqlSelect => $"select\r\n\t{SelectColumns}\r\nfrom {SchemaTable} OBJ{OrderByStatement}";
internal string SelectColumns => !Columns?.Any() ?? true ? "*" : string.Join(",\r\n\t", Columns.Where(c => !Exclude.ToList().Contains(c.ColumnName)).Select(c => c.ColumnName));
internal string OrderByStatement => !OrderBy?.Any() ?? true ? "" : "\r\nOrder By " + string.Join(", ", OrderBy.Select(ob => $"{ob.ColumnName}{(ob.IsDescending ? " DESC" : "")}"));
}
public class Column
{
public string ColumnName { get; set; }
public string DataType { get; set; }
public bool IsPrimaryKey { get; set; }
}
public class OrderBy
{
public string ColumnName { get; set; }
public bool IsDescending { get; set; } = false;
}
public class OrderByInformation
{
public string TableName { get; set; }
public IEnumerable OrderBy { get; set; }
public OrderByInformation(string tableName, IEnumerable columns)
{
TableName = tableName;
OrderBy = columns.Select(c => new OrderBy
{
ColumnName = c.StartsWith("D|") ? c.Substring(2) : c,
IsDescending = c.StartsWith("D|")
});
}
}
public class ColumnsToExclude
{
public string TableName { get; set; }
public IEnumerable ColumnNames { get; set; }
public ColumnsToExclude(string tableName, IEnumerable columns)
{
TableName = tableName;
ColumnNames = columns;
}
}
#endregion
#region Do the comparisons
public void DoTheComparisons(IEnumerable connections, IEnumerable sqlSelects)
{
var sql = string.Join("\r\n", sqlSelects);
var dataLegacy = GetData(connections.First(), sql);
var dataUpdate = GetData(connections.Last(), sql);
foreach (var dt in Enumerable.Range(0, dataLegacy.Tables.Count))
{
var legacy = dataLegacy.Tables[dt].Rows;
var update = dataUpdate.Tables[dt].Rows;
var idxCount = legacy.Count;
var compare = new List();
foreach (var row in Enumerable.Range(0, idxCount))
{
var r = legacy[row];
if (row < update.Count)
{
var u = update[row];
var rJson = JsonConvert.SerializeObject(r.ItemArray);
var uJson = JsonConvert.SerializeObject(u.ItemArray);
if (rJson != uJson)
{
compare.Add(new CompareSet(r, u));
}
}
else
{
compare.Add(new CompareSet(r, null));
}
}
if (compare.Any())
{
compare.Dump(dataLegacy.Tables[dt].TableName, 3);
}
else
{
"No differences found in data".Dump(dataLegacy.Tables[dt].TableName);
}
}
}
public class ServerInstance
{
public string Server { get; set; }
public string Database { get; set; }
public IEnumerable TableNames { get; set; }
public ServerInstance(string server, string database, IEnumerable tableNames)
{
Server = server;
Database = database;
TableNames = tableNames;
}
}
public DataSet GetData(ServerInstance serverInstance, string sql)
{
var server = serverInstance.Server;
var database = serverInstance.Database;
var connStr = $"data source={server};initial catalog='{database}';persist security info=True;Integrated Security=SSPI;";
using (var conn = new SqlConnection(connStr))
{
var dataset = new DataSet();
var adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(dataset);
var tableNames = serverInstance.TableNames;
var idx = 0;
foreach (var tableName in tableNames)
{
dataset.Tables[idx].TableName = tableName;
idx += 1;
}
return dataset;
}
}
public class CompareSet
{
internal IList ColumnNames {get;set;}
internal IList