private static void WriteDeleteView(SchemaMigrationContext context, ProcedureDefinition procedure, StreamWriter output)
{
if ( context.FromDatabaseType == DatabaseType.SqlServer)
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.SqlServer).Sql(\"DROP PROCEDURE [{0}].[{1}]\");", procedure.SchemaName, procedure.Name);
else
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Sql(\"DROP PROCEDURE {1}\");", context.FromDatabaseType, procedure.Name);
}
/// <summary>
/// Writes the Migration Up() and Down()
/// </summary>
/// <param name="context">The context that controls how the column should be generated</param>
/// <param name="procedure">the procedure to generate the migration for</param>
/// <param name="migration">The migration index to apply</param>
/// <param name="output">The output stream to append the C# code to</param>
private void WriteToStream(SchemaMigrationContext context, ProcedureDefinition procedure, int migration, StreamWriter output)
{
WriteMigration(output, context, migration
, () => context.MigrationProcedureClassNamer(migration, procedure)
, () => WriteView(context, procedure, output)
, () => WriteDeleteView(context, procedure, output));
}
private void WriteView(SchemaMigrationContext context, ProcedureDefinition procedure, StreamWriter output)
{
var scriptsDirectory = Path.Combine(context.WorkingDirectory, context.ScriptsDirectory);
var scriptFile = Path.Combine(scriptsDirectory, string.Format("CreateProcedure{0}_{1}.sql", procedure.Name, context.FromDatabaseType));
if ( !File.Exists(scriptFile))
{
if (!Directory.Exists(scriptsDirectory))
Directory.CreateDirectory(scriptsDirectory);
File.WriteAllText(scriptFile, procedure.Sql);
}
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Script(@\"{1}\");", context.FromDatabaseType, Path.Combine(context.ScriptsDirectory, Path.GetFileName(scriptFile)));
}
public IList<ProcedureDefinition> ReadProcedures()
{
var procedures = new List<ProcedureDefinition>();
// NOTE: Query for SQL 2005 for greater ... would need to be refactored to support SQL Server 2000
var procedureQuery = Processor.Read("SELECT schema_Name(so.schema_id) as SchemaName, o.name, c.text FROM syscomments c, sysobjects o, sys.objects so WHERE c.id = o.id AND xtype = 'P' AND so.object_id = o.id ORDER BY colid");
foreach ( DataRow dr in procedureQuery.Tables[0].Rows )
{
var matches = (from t in procedures
where t.Name == dr["name"].ToString()
select t).ToList();
ProcedureDefinition procedureDef = null;
if (matches.Count > 0) procedureDef = matches[0];
// create the procedure if not found
if (procedureDef == null)
{
procedureDef = new ProcedureDefinition()
{
SchemaName = dr["SchemaName"].ToString(),
Name = dr["Name"].ToString(),
Sql = dr["Text"].ToString()
};
procedures.Add(procedureDef);
}
else
{
procedureDef.Sql += dr["Text"].ToString();
}
}
return procedures;
}
