/// <summary>
/// Clear all the constraints in the database.
/// </summary>
/// <param name="pConstraintList">This is the list of available constraints in the code created by the Unit Test Database Creator utility</param>
public static void ClearConstraints(List <ConstraintDefinition> pConstraintList)
{
var schemaName = "dbo";
// delete all foreign constraints in all databases
using (var db = new ADODatabaseContext("TEST"))
{
//_databaseList
foreach (var database in _databaseList)
{
var constraints = pConstraintList.Where(x => x.DatabaseName == database).ToList();
foreach (var constraint in constraints)
{
var constraintName = "fk_" + constraint.FkTable + "_" + constraint.PkTable;
if (!string.IsNullOrEmpty(constraint.SchemaName))
{
schemaName = constraint.SchemaName;
}
var query = "SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='" + constraintName + "' AND CONSTRAINT_SCHEMA='" + schemaName + "'";
using (var reader = db.ReadQuery(query))
{
while (reader.Read())
{
query = "ALTER TABLE " + constraint.DatabaseName + ".." + constraint.FkTable + " DROP CONSTRAINT " + constraintName;
db.ExecuteNonQuery(query);
break;
}
}
}
}
}
}
/// <summary>
/// clear all constraints in database
/// </summary>
private static void ClearAllConstraints(string database)
{
using (var db = new ADODatabaseContext("TEST", database))
{
using (var reader = db.ReadQuery(@"
SELECT
OBJECT_NAME(OBJECT_ID) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM
sys.objects
WHERE
type_desc LIKE '%CONSTRAINT' AND
OBJECT_NAME(OBJECT_ID) LIKE'fk_%'" ))
{
while (reader.Read())
{
var foreignKeyTableName = reader["TableName"].ToString();
var constraintName = reader["ConstraintName"].ToString();
var schemaName = reader["SchemaName"].ToString();
using (var dbExec = new ADODatabaseContext("TEST", database))
{
var query = "ALTER TABLE [" + database + "].[" + schemaName + "].[" + foreignKeyTableName + "] DROP CONSTRAINT " + constraintName;
dbExec.ExecuteNonQuery(query);
}
}
}
}
}
private string ReadTableIdentityFieldName()
{
var query = @"
SELECT * FROM [" + _databaseName + @"].sys.identity_columns AS a
INNER JOIN [" + _databaseName + @"].sys.objects AS b ON a.object_id=b.object_id
WHERE
LOWER(b.name)='" + _tableName + @"' AND
type='U'" ;
using (var db = new ADODatabaseContext(_connectionString))
{
var reader = db.ReadQuery(query);
while (reader.Read())
{
return(reader["name"].ToString());
}
}
return("");
}
private void BuildFieldsForTable()
{
Fields.Clear();
using (var db = new ADODatabaseContext(_connectionString))
{
var columnQuery = "SELECT * FROM [" + _databaseName + "].INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + _tableName + "'";
using (var reader = db.ReadQuery(columnQuery))
{
while (reader.Read())
{
Fields.Add(new FieldDefinition
{
Name = reader["COLUMN_NAME"].ToString(),
Type = reader["DATA_TYPE"].ToString()
});
}
}
}
}
/// <summary>
/// Returns the total records in a table specified. This can be used to verify how many records are created in your unit test.
/// </summary>
/// <param name="tableName">Table to check</param>
/// <param name="database">Database that table resides in</param>
/// <param name="schema">Optional schema name. If not specified, then it will be set to "dbo"</param>
/// <returns></returns>
public static int TotalRecords(string tableName, string database, string schema = "dbo")
{
var results = 0;
// make sure the schema name doesn't already contain a "."
schema = schema.Replace(".", "");
using (var db = new ADODatabaseContext("TEST", database))
{
var query = $"SELECT COUNT(*) AS total FROM [{schema}].[{tableName}]";
using (var reader = db.ReadQuery(query))
{
while (reader.Read())
{
results = int.Parse(reader["total"].ToString());
break;
}
}
}
return(results);
}
/// <summary>
/// Truncate all tables in the databases setup.
/// This method will remove all constraints before truncating tables. You must
/// re-apply your constraints for each test, if you need them.
/// </summary>
public static void TruncateData()
{
var tableList = new List <string>();
using (var db = new ADODatabaseContext("TEST"))
{
//_databaseList
foreach (var database in _databaseList)
{
ClearAllConstraints(database);
// generate a table list
using (var reader = db.ReadQuery(@"
SELECT *
FROM [" + database + @"].INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME" ))
{
while (reader.Read())
{
var tableName = reader["table_name"].ToString();
var schemaName = reader["TABLE_SCHEMA"].ToString();
tableList.Add($"[{database}].[{schemaName}].[{tableName}]");
}
}
}
}
using (var db = new ADODatabaseContext("TEST"))
{
foreach (var item in tableList)
{
db.ExecuteNonQuery(@"TRUNCATE TABLE " + item);
}
}
}