private static bool ConstraintExistsWithSchema(this ITransformationProvider database, string constraint, string table)
{
if (!TableExistsWithSchema(database, table))
{
return(false);
}
using (IDataReader reader =
database.ExecuteQuery(string.Format("SELECT TOP 1 * FROM sysobjects WHERE name = '{0}' AND parent_obj = object_id('{1}')", constraint, table)))
{
return(reader.Read());
}
}
public static bool ColumnExistsWithSchema(this ITransformationProvider database, string table, string column)
{
if (!TableExistsWithSchema(database, table))
{
return(false);
}
using (IDataReader reader =
database.ExecuteQuery(string.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}' AND COLUMN_NAME='{1}'", GetTableName(RemoveBrackets(table)), column)))
{
return(reader.Read());
}
}
public static bool TableExistsWithSchema(this ITransformationProvider database, string table)
{
string tableWithoutBrackets = RemoveBrackets(table);
string schemaName = GetSchemaName(tableWithoutBrackets);
string tableName = GetTableName(tableWithoutBrackets);
using (IDataReader reader
= database.ExecuteQuery(string.Format(
"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='{0}' AND TABLE_NAME='{1}'",
schemaName,
tableName)))
{
return(reader.Read());
}
}
// Deletes all constraints linked to a column. Sql Server
// doesn't seems to do this.
public static void DeleteColumnConstraints(this ITransformationProvider database, string table, string column)
{
string sqlContrainte = FindConstraints(table, column);
List <string> constraints = new List <string>();
using (IDataReader reader = database.ExecuteQuery(sqlContrainte))
{
while (reader.Read())
{
constraints.Add(reader.GetString(0));
}
}
// Can't share the connection so two phase modif
foreach (string constraint in constraints)
{
database.RemoveForeignKey(table, constraint);
}
}
public static void RefreshViewsDependantOnTable(this ITransformationProvider database, string tableName)
{
var dependantViewsQuery = string.Format(
"SELECT DISTINCT ss.name + '.' + so.name FROM sys.objects AS so "
+ "INNER JOIN sys.sql_expression_dependencies AS sed ON so.object_id = sed.referencing_id "
+ "JOIN sys.schemas AS ss on ss.schema_id = so.schema_id "
+ "WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('{0}')",
tableName);
var dependantViewsList = new List <string>();
using (IDataReader reader = database.ExecuteQuery(dependantViewsQuery))
{
while (reader.Read())
{
dependantViewsList.Add(reader.GetString(0));
}
}
dependantViewsList.ForEach(view => database.ExecuteNonQuery(string.Format("EXEC sp_refreshview '{0}'", view)));
}
public static IDataReader ExecuteQuery(ITransformationProvider p_provider, string p_sqlStatement)
{
return p_provider.ExecuteQuery(p_sqlStatement);
}
public static IDataReader ExecuteQuery(ITransformationProvider p_provider, string p_sqlStatement)
{
return(p_provider.ExecuteQuery(p_sqlStatement));
}
