public static void RemoveTableWithSchema(this ITransformationProvider database, string table)
{
if (TableExistsWithSchema(database, table))
{
database.ExecuteNonQuery(string.Format("DROP TABLE {0}", table));
}
}
public static void ChangeColumnSizeWithSchema(this ITransformationProvider database, string table, ColumnInfo column)
{
if (ColumnExistsWithSchema(database, table, column.Name))
{
database.ExecuteNonQuery(string.Format("ALTER TABLE {0} ALTER COLUMN {1} {2}({3})", table, column.Name, column.Type, column.Size));
}
}
public static void RenameColumnWithSchema(this ITransformationProvider database, string oldtable, string oldcolumn, string newcolumn)
{
if (TableExistsWithSchema(database, oldtable))
{
database.ExecuteNonQuery(string.Format("sp_rename '{0}.{1}', '{2}', 'COLUMN'", oldtable, oldcolumn, newcolumn));
}
}
public static void RenameTableWithSchema(this ITransformationProvider database, string oldtable, string newtable)
{
if (TableExistsWithSchema(database, oldtable))
{
database.ExecuteNonQuery(string.Format("sp_rename '{0}', '{1}'", oldtable, newtable));
}
}
public static void RemoveColumnWithSchema(this ITransformationProvider database, string table, string column)
{
if (!ColumnExistsWithSchema(database, table, column))
{
return;
}
database.DeleteColumnConstraints(table, column);
database.ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP COLUMN {1} ", table, column));
}
public static void RemoveIndex(
this ITransformationProvider transformation,
string tableName,
params string[] columns)
{
var indexName = GetIndexName(tableName, columns);
var sql = GetRemoveIndexSql(tableName, indexName);
transformation.ExecuteNonQuery(sql);
}
public static void SetColumnAsChar4000(
this ITransformationProvider transformationProvider,
string tableName,
string columnName)
{
string sql = string.Format(
@"ALTER TABLE {0} MODIFY ({1} VARCHAR2(4000))",
tableName,
columnName);
transformationProvider.ExecuteNonQuery(sql);
}
/// <summary>
/// Расширяет таблицу SchemaInfo полем AssemblyKey.
/// Использутеся при переходе от старой версии
///
/// Должно быть удалено в будущем
/// </summary>
/// <param name="provider"></param>
public static void Update1To3(ITransformationProvider provider)
{
provider.AddTable(
"SchemaTmp",
new Column("Version", DbType.Int64, ColumnProperty.PrimaryKey),
new Column("AssemblyKey", DbType.String.WithSize(200), ColumnProperty.PrimaryKey, "''"));
string sql = provider.FormatSql(
"INSERT INTO {0:NAME} ({1:NAME}) SELECT {1:NAME} FROM {2:NAME}",
"SchemaTmp", "Version", "SchemaInfo");
provider.ExecuteNonQuery(sql);
provider.RemoveTable("SchemaInfo");
provider.RenameTable("SchemaTmp", "SchemaInfo");
}
/// <summary>
/// Расширяет таблицу SchemaInfo полем AssemblyKey.
/// Использутеся при переходе от старой версии
///
/// Должно быть удалено в будущем
/// </summary>
/// <param name="provider"></param>
public static void Update1To3(ITransformationProvider provider)
{
provider.AddTable(
"SchemaTmp",
new Column("Version", DbType.Int64, ColumnProperty.PrimaryKey),
new Column("AssemblyKey", DbType.String.WithSize(200), ColumnProperty.PrimaryKey, "''"));
string sql = provider.FormatSql(
"INSERT INTO {0:NAME} ({1:NAME}) SELECT {1:NAME} FROM {2:NAME}",
"SchemaTmp", "Version", "SchemaInfo");
provider.ExecuteNonQuery(sql);
provider.RemoveTable("SchemaInfo");
provider.RenameTable("SchemaTmp", "SchemaInfo");
}
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)));
}
/// <summary>
/// Run Script
/// </summary>
/// <param name="p_provider">Database provider</param>
/// <param name="p_scriptType">Script Type</param>
/// <param name="p_scriptName">Name of Script</param>
/// <param name="p_fileName">FileName of Script</param>
/// <param name="p_schemaName">Schema Name - Default as dbo</param>
/// <returns></returns>
private static void RunScript(ITransformationProvider p_provider, ScriptType p_scriptType, string p_scriptName, Type typeHoldingScript, string p_fileName = null, string p_schemaName = "dbo")
{
if (string.IsNullOrEmpty(p_fileName))
{
p_fileName = string.Format("{0}.sql", p_scriptName);
}
// Drop proc/views/trigger if already exists
p_provider.ExecuteNonQuery(string.Format(GetDropStatement(p_scriptType), p_scriptName, p_schemaName));
// Load Script
var assembly = typeHoldingScript.Assembly;
string resourceFullName = string.Format("{0}.Scripts.{1}.{2}.sql", assembly.GetName().Name, p_scriptType, p_fileName);
Console.WriteLine("Resource file name = " + resourceFullName);
Console.WriteLine(resourceFullName);
var stream = assembly.GetManifestResourceStream(resourceFullName);
if (stream == null)
{
throw new InvalidOperationException(string.Format("Cannot load resource: {0}", resourceFullName));
}
var textStreamReader = new StreamReader(stream);
// Execute procedure
// Execute procedure
// 4-26-13 AMB: Changing this so we can set the timeout value
//p_provider.ExecuteNonQuery(textStreamReader.ReadToEnd());
var command = p_provider.GetCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 1800;
command.CommandText = textStreamReader.ReadToEnd();
command.ExecuteNonQuery();
Console.WriteLine(command.CommandText);
}
public static void AddIndex(
this ITransformationProvider transformation,
string tableName,
bool unique,
params string[] columns)
{
if (columns.Length == 0)
{
return;
}
var indexName = GetIndexName(tableName, columns);
string uniqueString = unique ? "UNIQUE" : string.Empty;
string sql = string.Format(
"CREATE {0} INDEX {1} ON {2} ({3})",
uniqueString,
indexName,
tableName,
columns.Join(", ", s => s));
transformation.ExecuteNonQuery(sql);
}
public static void EmptyTable(this ITransformationProvider provider, string tableName)
{
provider.ExecuteNonQuery(string.Format("delete from {0}", tableName));
}
public static void AddVersionColumn(this ITransformationProvider migration, string table)
{
migration.ExecuteNonQuery(string.Format("ALTER TABLE [{0}] ADD [Version] [timestamp] NOT NULL", table));
}
public static int DeleteAllRowsFromTable(this ITransformationProvider migration, string tableName)
{
return(migration.ExecuteNonQuery(string.Format("DELETE FROM {0}", tableName)));
}
/// <summary>
/// Drop Database object
/// </summary>
/// <param name="p_provider">Database provider</param>
/// <param name="p_scriptType">Script type</param>
/// <param name="p_objectName">Name of Database Object</param>
/// <param name="p_schemaName">Name of Schema</param>
public static void DropObject(ITransformationProvider p_provider, ScriptType p_scriptType, string p_objectName, string p_schemaName = "dbo")
{
p_provider.ExecuteNonQuery(string.Format(GetDropStatement(p_scriptType), p_objectName, p_schemaName));
}
/// <summary>
/// Extension method to drop a database schema
/// </summary>
/// <param name="database"></param>
/// <param name="schemaName">Name of the schema to drop</param>
/// <returns></returns>
public static int DropSchema(this ITransformationProvider database, string schemaName)
{
return(database.ExecuteNonQuery(string.Format("DROP SCHEMA {0}", schemaName)));
}
/// <summary>
/// Drop Database object
/// </summary>
/// <param name="p_provider">Database provider</param>
/// <param name="p_scriptType">Script type</param>
/// <param name="p_objectName">Name of Database Object</param>
/// <param name="p_schemaName">Name of Schema</param>
public static void DropObject(ITransformationProvider p_provider, ScriptType p_scriptType, string p_objectName, string p_schemaName = "dbo")
{
p_provider.ExecuteNonQuery(string.Format(GetDropStatement(p_scriptType), p_objectName, p_schemaName));
}
/// <summary>
/// Run Script
/// </summary>
/// <param name="p_provider">Database provider</param>
/// <param name="p_scriptType">Script Type</param>
/// <param name="p_scriptName">Name of Script</param>
/// <param name="p_fileName">FileName of Script</param>
/// <param name="p_schemaName">Schema Name - Default as dbo</param>
/// <returns></returns>
private static void RunScript(ITransformationProvider p_provider, ScriptType p_scriptType, string p_scriptName, Type typeHoldingScript,string p_fileName = null, string p_schemaName = "dbo")
{
if (string.IsNullOrEmpty(p_fileName))
p_fileName = string.Format("{0}.sql", p_scriptName);
// Drop proc/views/trigger if already exists
p_provider.ExecuteNonQuery(string.Format(GetDropStatement(p_scriptType), p_scriptName, p_schemaName));
// Load Script
var assembly = typeHoldingScript.Assembly;
string resourceFullName = string.Format("{0}.Scripts.{1}.{2}.sql", assembly.GetName().Name, p_scriptType, p_fileName);
Console.WriteLine("Resource file name = " + resourceFullName);
Console.WriteLine(resourceFullName);
var stream = assembly.GetManifestResourceStream(resourceFullName);
if (stream == null)
throw new InvalidOperationException(string.Format("Cannot load resource: {0}", resourceFullName));
var textStreamReader = new StreamReader(stream);
// Execute procedure
// Execute procedure
// 4-26-13 AMB: Changing this so we can set the timeout value
//p_provider.ExecuteNonQuery(textStreamReader.ReadToEnd());
var command = p_provider.GetCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 1800;
command.CommandText = textStreamReader.ReadToEnd();
command.ExecuteNonQuery();
Console.WriteLine(command.CommandText);
}
