/// <summary>
/// Initializes the test context with necessary objects for each test class.
/// Should be called from test class constructor.
/// </summary>
/// <example>
/// public class MyTests : DatabaseTestsBase
/// {
/// public MyTests(ITestOutputHelper outputHelper, SqlDatabaseFixture testFixture)
/// {
/// this.InitializeTestContext(outputHelper, testFixture);
/// }
/// // Here goes tests
/// }
/// </example>
/// <param name="helper">The XUit output helper (like Console.Write).</param>
/// <param name="fixture">The database tests fixture.</param>
protected void InitializeTestContext(ITestOutputHelper helper, SqlDatabaseFixture fixture)
{
this.TestFixture = fixture;
this.TestFixture.SqlConnection = this.GetSqlConnectionString();
this.TestFixture.InstantiateDatabaseObjects(helper);
if (!this.TestFixture.IsDatabasePrepared)
{
this.PrepareDatabase();
this.TestFixture.IsDatabasePrepared = true;
}
}
/// <summary>
/// Compares the database column descriptions with data contract properties
/// and reports any misalignment (missing columns, missing properties, incompatible types used, null-ability for both).
/// </summary>
/// <typeparam name="T">Type of data contract (DB POCO class).</typeparam>
/// <param name="fixture">The database tests fixture for getting database object columns metadata.</param>
/// <param name="tableName">Name of the table. Default (if not used) = data contract name.</param>
/// <param name="exceptPocoProperties">The exceptions to be used for data contract properties (extra properties, which should not be considered). When not specified - uses all found properties.</param>
/// <param name="exceptDatabaseFields">The exceptions to database column names (extra fields not used to map data to data contract). When not specified - uses all found columns in database object.</param>
public static async Task <List <string> > CompareDatabaseWithContract <T>(
SqlDatabaseFixture fixture,
string tableName = null,
HashSet <string> exceptPocoProperties = null,
HashSet <string> exceptDatabaseFields = null)
{
tableName ??= typeof(T).Name;
exceptPocoProperties ??= new HashSet <string>();
exceptDatabaseFields ??= new HashSet <string>();
var validationResults = new List <string>();
List <PocoPropertyMetadata> pocoProperties = GetPocoObjectProperties <T>();
var databaseFields = (await fixture.Db.QueryAsync(new DatabaseObjectColumnsMetadataQuery(tableName))).ToList();
// Validating that all DTO POCO properties has counterpart in database object fields
foreach (PocoPropertyMetadata pocoProperty in pocoProperties)
{
if (exceptPocoProperties.Contains(pocoProperty.Name))
{
continue;
}
DatabaseObjectColumnMetadata?dbField = databaseFields.FirstOrDefault(dbf =>
dbf.ColumnName.Equals(pocoProperty.Name, StringComparison.OrdinalIgnoreCase));
if (dbField == null)
{
validationResults.Add($"DB column with name \"{pocoProperty.Name}\" was not found (but property exists). If property is extra (non-database loadable), then use \"exceptPocoProperties\" parameter to exclude it from test.");
continue;
}
if (pocoProperty.TypeName.Equals("string", StringComparison.OrdinalIgnoreCase) && !(dbField.DataType.Contains("CHAR") || dbField.DataType.Contains("XML")))
{
validationResults.Add($"Data contract property \"{pocoProperty.Name}\" (STRING) has non-matching type \"{dbField.DataType}\" in database column.");
continue;
}
if (!new HashSet <string> {
"BYTE[]", "STRING"
}.Contains(pocoProperty.TypeName.ToUpper(CultureInfo.InvariantCulture)) && pocoProperty.IsNullable != dbField.IsNullable && pocoProperty.IsNullable && !dbField.HasDefaultValue)
{
validationResults.Add($"Data contract property \"{pocoProperty.Name}\" ({pocoProperty.TypeName}{(pocoProperty.IsNullable ? "?" : string.Empty)}) has non-matching NULL-ability in database field \"{dbField.DataType} {(dbField.IsNullable ? "NULL" : "NOT NULL")}\".");
}
}
// Validating that all Database Fields has counterpart in POCO properties
foreach (DatabaseObjectColumnMetadata dbField in databaseFields)
{
if (exceptDatabaseFields.Contains(dbField.ColumnName))
{
continue;
}
PocoPropertyMetadata?pocoProperty = pocoProperties.FirstOrDefault(pp =>
pp.Name.Equals(dbField.ColumnName, StringComparison.OrdinalIgnoreCase));
if (pocoProperty == null)
{
validationResults.Add($"Data contract property with name \"{dbField.ColumnName}\" was not found (but DB column exists). If DB field is not loadable, then use \"exceptDatabaseFields\" parameter to exclude it from test.");
continue;
}
if (dbField.DataType.Contains("CHAR") && pocoProperty.TypeName.ToUpper(CultureInfo.InvariantCulture) != "STRING")
{
validationResults.Add($"Database column \"{dbField.ColumnName}\" ({dbField.DataType}) has non-matching type \"{pocoProperty.TypeName}\" in data contract property.");
continue;
}
if (!new HashSet <string> {
"BYTE[]", "STRING"
}.Contains(pocoProperty.TypeName.ToUpper(CultureInfo.InvariantCulture)) && !dbField.DataType.ToUpper(CultureInfo.InvariantCulture).Contains("XML") && pocoProperty.IsNullable != dbField.IsNullable && pocoProperty.IsNullable && !dbField.HasDefaultValue)
{
validationResults.Add($"Database column \"{dbField.ColumnName}\" ({dbField.DataType} {(dbField.IsNullable ? "NULL" : "NOT NULL")}) has non-matching NULL-ability in data contract property ({pocoProperty.TypeName}{(pocoProperty.IsNullable ? "?" : string.Empty)}).");
}
}
return(validationResults);
}
