public static void ClearConstraints(List <ConstraintDefinition> ConstraintList)
{
string schemaName = "dbo";
// delete all foreign constraints in all databases
using (var db = new ADODatabaseContext("TEST"))
{
//_databaseList
foreach (var database in _databaseList)
{
var constraints = ConstraintList.Where(x => x.DatabaseName == database).ToList();
foreach (var constraint in constraints)
{
string constraintName = "fk_" + constraint.FkTable + "_" + constraint.PkTable;
if (!String.IsNullOrEmpty(constraint.SchemaName))
{
schemaName = constraint.SchemaName;
}
string 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())
{
string fkTableName = reader["TableName"].ToString();
string constraintName = reader["ConstraintName"].ToString();
string schemaName = reader["SchemaName"].ToString();
using (var dbExec = new ADODatabaseContext("TEST", database))
{
string query = "ALTER TABLE " + database + "." + schemaName + "." + fkTableName + " DROP CONSTRAINT " + constraintName;
dbExec.ExecuteNonQuery(query);
}
}
}
}
}
public static int TotalRecords(string tableName, string database)
{
int results = 0;
using (var db = new ADODatabaseContext("", database))
{
string query = "SELECT COUNT(*) AS total FROM " + tableName;
using (var reader = db.ReadQuery(query))
{
while (reader.Read())
{
results = reader["total"].ToInt();
break;
}
}
}
return(results);
}
private string ReadTableIdentityFieldName()
{
string 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))
{
string 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()
});
}
}
}
}
// truncate all tables in the databases setup
public static void TruncateData()
{
List <string> 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())
{
string tableName = reader["table_name"].ToString();
string 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);
}
}
}
