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 List SchemasAndTablesToReview = new List();
private List OrderByStatements = new List();
private List ColumnsToBeExcluded = new List();
private List JsonColumnsToBeParsed = new List();
private static Dictionary WhereClauses = new Dictionary();
public static int TopRows = int.MaxValue;
void Main()
{
SchemasAndTablesToReview.AddRange(new[]
{
new TablesAndColumns("clientdss", new [] { "FactPatientEncounterSummary" })
});
WhereClauses.Add("clientdss.FactPatientEncounterSummary", "OBJ.MSDRGCaseTypeFamilyID <> 0 OR OBJ.APRDRGCaseTypeFamilyID <> 0");
OrderByStatements.AddRange(new[]
{
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("clientdss.FactPatientEncounterSummary", new []{"EncounterRecordNumber"}),
new OrderByInformation("dss.DimCaseType", new []{"CaseTypeFamilyID", "CaseTypeId", "Name"})
});
ColumnsToBeExcluded.AddRange(new[]
{
new ColumnsToExclude("cci.SystemSetting", new [] {"IsEncrypted"}),
new ColumnsToExclude("cci.Configuration", new [] {"InitiativeIsSimplifiedWorkflow"}),
new ColumnsToExclude("clientdss.FactPatientEncounterSummary", new [] {"MikeQAtest1ID", "CustomJesseValidatingAllOthers", "CustomNewCalculatedSystemFieldasdf"})
});
JsonColumnsToBeParsed.AddRange(new[]
{
new JsonColumns("dss.DimCaseType", new [] {"CaseTypeConfigJSON"})
});
var tables = GetTables();
var tableList = tables.Select(r => r.SchemaTable);
var connections = new ServerInstance[] {
new ServerInstance("D0011-SQL-01.sdt.local","jazz tst Southern Illinois 20190918.1", tableList),
new ServerInstance("D0011-SQL-01.sdt.local", "jazz tst Southern Illinois 20200331.1", tableList)
}.ToList();
DoTheComparisons(connections, tables);
}
// Define other methods and classes here
#region Gather Table Information
public string GetTableQuery()
{
var tableSchemas = string.Join(", ", SchemasAndTablesToReview.Select(sattr => $"'{sattr.SchemaName}'"));
var tableList = string.Join("\r\n", SchemasAndTablesToReview.ConvertAll(sattr =>
{
if (sattr.TableNames?.Any(cn => cn == "*") ?? true)
{
return $"WHEN c.TABLE_SCHEMA = '{sattr.SchemaName}' THEN 1";
}
else
{
return $"WHEN c.TABLE_SCHEMA = '{sattr.SchemaName}' AND c.TABLE_NAME IN ({string.Join(", ", sattr.TableNames.Select(cn => $"'{cn}'"))}) THEN 1";
}
}));
return $@"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 ({tableSchemas})
AND (CASE
{tableList}
ELSE 0
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";
}
public IList
GetTables()
{
var tableQuery = GetTableQuery();
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(),
Json = JsonColumnsToBeParsed?.SingleOrDefault(ctbe => ctbe.TableName == $"{g.TABLE_SCHEMA}.{g.TABLE_NAME}")?.ColumnNames ?? new List(),
Where = WhereClauses.ContainsKey($"{g.TABLE_SCHEMA}.{g.TABLE_NAME}") ? $"\r\nwhere {WhereClauses[$"{g.TABLE_SCHEMA}.{g.TABLE_NAME}"]}" : ""
}).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; }
public IEnumerable Json { get; set; }
public string Where { get; set; } = "";
internal string SchemaTable => $"{SchemaName}.{TableName}";
public string SqlSelect => $"select TOP({TopRows}) \r\n\t{SelectColumns}\r\nfrom {SchemaTable} OBJ{Where}{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 TablesAndColumns
{
public string SchemaName { get; set; }
public IEnumerable TableNames { get; set; }
public TablesAndColumns(string schemaName, IEnumerable tables)
{
SchemaName = schemaName;
TableNames = tables;
}
}
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;
}
}
public class JsonColumns
{
public string TableName { get; set; }
public IEnumerable ColumnNames { get; set; }
public JsonColumns(string tableName, IEnumerable columns)
{
TableName = tableName;
ColumnNames = columns;
}
}
#endregion
#region Do the comparisons
public void DoTheComparisons(IEnumerable connections, IEnumerable
tables)
{
foreach (var table in tables)
{
var sql = table.SqlSelect;
var dataLegacy = GetData(connections.First(), sql);
var dataUpdate = GetData(connections.Last(), sql);
var legacy = dataLegacy.Rows;
var update = dataUpdate.Rows;
var idxCount = legacy.Count;
var compare = new List();
var jsonColumns = tables.SingleOrDefault(t => t.SchemaTable == table.TableName)?.Json.ToList();
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)
{
if (jsonColumns == null)
{
compare.Add(new CompareSet(r, u));
}
else
{
compare.Add(new CompareSet(r, u, jsonColumns));
}
}
}
else
{
if (jsonColumns == null)
{
compare.Add(new CompareSet(r, null));
}
else
{
compare.Add(new CompareSet(r, null, jsonColumns));
}
}
}
if (compare.Any())
{
compare.Dump(table.TableName, 3);
}
else
{
"No differences found in data".Dump($"{table.TableName} ({dataLegacy.Rows.Count} rows)");
}
}
}
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 DataTable 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 datatable = new DataTable();
var adapter = new SqlDataAdapter(sql, conn);
try
{
adapter.Fill(datatable);
}
catch (Exception ex)
{
sql.Dump(nameof(sql));
throw;
}
var tableNames = serverInstance.TableNames;
var idx = 0;
foreach (var tableName in tableNames)
{
datatable.TableName = tableName;
idx += 1;
}
return datatable;
}
}
public class CompareSet
{
internal IList ColumnNames { get; set; }
internal IList