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); } } }