private static void WriteProcedure(DatabaseStoredProcedure procedure, ProcedureWriter procWriter)
{
foreach (var argument in procedure.Arguments)
{
if (argument.Out)
{
//we don't deal with INOUT parameters.
procWriter.AddOutputParameter(argument.Name, argument.DatabaseDataType);
continue;
}
//an IN sproc
procWriter.AddParameter(argument.Name, argument.DatabaseDataType);
}
procWriter.BeginProcedure();
var sql = procedure.Sql.Trim()
//standardize to windows line endings
.Replace("\r\n","\n").Replace("\r","\n").Replace("\n","\r\n");
//remove the BEGIN and END as the procWriter writes these
if (sql.StartsWith("BEGIN", StringComparison.OrdinalIgnoreCase))
{
sql = sql.Substring(5);
}
if (sql.EndsWith("END", StringComparison.OrdinalIgnoreCase))
{
sql = sql.Substring(0, sql.Length - 3).Trim();
}
procWriter.AddSql(sql);
}
public SprocWriter(DatabaseStoredProcedure storedProcedure, string ns)
{
_namespace = ns;
_storedProcedure = storedProcedure;
_logic = new SprocLogic(_storedProcedure);
_sprocResultType = _logic.ResultType;
_cb = new ClassBuilder();
}
internal SprocResultWriter(DatabaseStoredProcedure storedProcedure, string ns, ClassBuilder classBuilder)
{
_namespace = ns;
_storedProcedure = storedProcedure;
_logic = new SprocLogic(_storedProcedure);
_resultClassName = _logic.ResultClassName;
_cb = classBuilder;
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure)
{
var executionName = procedure.Name;
if (!string.IsNullOrEmpty(procedure.Package))
executionName = procedure.Package + "." + procedure.Name;
//for Oracle, sprocs with REF CURSORs indicate it returns something.
if (_isOracle && !procedure.Arguments.Any(a => a.DatabaseDataType == "REF CURSOR"))
return;
using (var resultSet = new DataSet { Locale = CultureInfo.InvariantCulture })
{
using (DbConnection connection = _factory.CreateConnection())
{
connection.ConnectionString = _schema.ConnectionString;
using (var command = _factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = executionName;
command.CommandTimeout = 5;
command.CommandType = CommandType.StoredProcedure;
AddParameters(procedure, command);
connection.Open();
using (DbTransaction tx = connection.BeginTransaction())
{
command.Transaction = tx;
using (DbDataAdapter adapter = _factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
try
{
adapter.FillSchema(resultSet, SchemaType.Source);
}
catch (DbException exception)
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
catch (Exception exception) //for exceptions that don't derive from DbException
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
}
tx.Rollback();
}
}
}
UpdateProcedure(procedure, resultSet);
}
}
private static void PrepareStoredProcedureNames(DatabaseStoredProcedure sproc, INamer namer)
{
if (string.IsNullOrEmpty(sproc.NetName))
sproc.NetName = namer.Name(sproc);
foreach (var argument in sproc.Arguments)
{
if (string.IsNullOrEmpty(argument.NetName))
argument.NetName = namer.Name(argument);
}
}
private void CreateResult(ResultType resultType, DatabaseStoredProcedure storedProcedure, string script)
{
var result = new CompareResult
{
SchemaObjectType = SchemaObjectType.StoredProcedure,
ResultType = resultType,
Name = storedProcedure.Name,
SchemaOwner = storedProcedure.SchemaOwner,
Script = script
};
_results.Add(result);
}
public override string AddProcedure(DatabaseStoredProcedure procedure)
{
if (string.IsNullOrEmpty(procedure.Sql))
{
//the procedure.Sql contains the BEGIN to END statements, not the CREATE PROCEDURE and arguments.
//for now, just comment
return "-- add procedure " + procedure.Name;
}
var name = procedure.Name;
var procWriter = new ProcedureWriter(name, null);
WriteProcedure(procedure, procWriter);
return procWriter.End();
}
private void WriteSproc(DatabaseStoredProcedure sproc)
{
if (sproc == null) return;
using (_cb.BeginNest("private static DbConnection CreateConnection()"))
{
_cb.AppendLine(@"const string connectionString = @""" + _schema.ConnectionString + "\";");
_cb.AppendLine(@"const string provider = @""" + _schema.Provider + "\";");
_cb.AppendLine("var factory = DbProviderFactories.GetFactory(provider);");
_cb.AppendLine("var connection = factory.CreateConnection();");
_cb.AppendLine("connection.ConnectionString = connectionString;");
_cb.AppendLine("return connection;");
}
_cb.AppendLine("[TestMethod]");
using (_cb.BeginNest("public void Test" + sproc.NetName + "()"))
{
using (_cb.BeginNest("using (var connection = CreateConnection())"))
{
_cb.AppendLine("var cmd = new " + sproc.NetName + "(connection);");
var list = new List<string>();
foreach (var arg in sproc.Arguments)
{
if (arg.DataType == null)
{
list.Add("null");
}
else if (arg.DataType.IsString)
{
list.Add("a");
}
else if (arg.DataType.IsNumeric)
{
list.Add("1");
}
else if (arg.DataType.IsDateTime)
{
list.Add("DateTime.Now");
}
}
_cb.AppendLine("var result = cmd.Execute(" + string.Join(", ", list.ToArray()) + ");");
_cb.AppendLine("//test whether it worked!");
}
}
}
private static void UpdateArgumentDataTypes(IDictionary <string, DataType> dataTypes, DatabaseStoredProcedure sproc)
{
foreach (DatabaseArgument arg in sproc.Arguments)
{
arg.DataType = FindDataType(dataTypes, arg.DatabaseDataType);
}
}
public virtual string DropProcedure(DatabaseStoredProcedure procedure)
{
return string.Format(CultureInfo.InvariantCulture,
"DROP PROCEDURE {0}{1};",
SchemaPrefix(procedure.SchemaOwner),
Escape(procedure.Name))
+ _sqlFormatProvider.RunStatements();
}
public virtual string AddProcedure(DatabaseStoredProcedure procedure)
{
//CREATE PROCEDURE cannot be combined with other statements in a batch, so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
var sql = procedure.Sql;
if (string.IsNullOrEmpty(sql))
{
//without the sql, we can't do anything
return "-- add procedure " + procedure.Name;
}
if (sql.TrimStart().StartsWith("PROCEDURE ", StringComparison.OrdinalIgnoreCase))
{
return "CREATE " + sql + _sqlFormatProvider.RunStatements();
}
//helpfully, SqlServer includes the create statement
//MySQL doesn't, so this will need to be overridden
return sql + _sqlFormatProvider.RunStatements();
}
private static void UpdateArgumentDataTypes(IDictionary<string, DataType> dataTypes, DatabaseStoredProcedure sproc)
{
foreach (DatabaseArgument arg in sproc.Arguments)
{
arg.DataType = FindDataType(dataTypes, arg.DatabaseDataType);
}
}
public string AddProcedure(DatabaseStoredProcedure procedure)
{
return _migration.AddProcedure(procedure);
}
public void BuildProcedure(DatabaseStoredProcedure storedProcedure)
{
try
{
var txt = _migrationGenerator.AddProcedure(storedProcedure);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
private static void AddParameters(DatabaseStoredProcedure procedure, DbCommand command)
{
foreach (var argument in procedure.Arguments)
{
if (argument.Ordinal == 0 && !argument.In && string.Equals(argument.Name, "RETURN_VALUE", StringComparison.OrdinalIgnoreCase))
continue;
var parameter = command.CreateParameter();
AddParameter(parameter, argument);
command.Parameters.Add(parameter);
}
}
private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, bool isFunction)
{
DatabaseStoredProcedure sproc;
if (isFunction)
{
//functions are just a type of stored procedure
DatabaseFunction fun = new DatabaseFunction();
databaseSchema.Functions.Add(fun);
sproc = fun;
}
else
{
sproc = new DatabaseStoredProcedure();
databaseSchema.StoredProcedures.Add(sproc);
}
return sproc;
}
private static void WriteStoredProcedure(string procedures, string directoryPath, string @namespace, DatabaseStoredProcedure sproc, ProjectWriter pw)
{
//if no .net classname, don't process
if (string.IsNullOrEmpty(sproc.NetName)) return;
var sw = new SprocWriter(sproc, @namespace);
var txt = sw.Write();
var fileName = sproc.NetName + ".cs";
var path = Path.Combine(directoryPath, fileName);
File.WriteAllText(path, txt);
pw.AddClass(procedures + @"\" + fileName);
if (sw.RequiresOracleReference)
{
if (sw.RequiresDevartOracleReference)
pw.AddDevartOracleReference();
else
pw.AddOracleReference();
}
if (sw.HasResultClass)
{
var rs = new SprocResultWriter(sproc, @namespace);
txt = rs.Write();
fileName = rs.ClassName + ".cs";
path = Path.Combine(directoryPath, fileName);
File.WriteAllText(path, txt);
pw.AddClass(procedures + @"\" + fileName);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ProcedureWriter"/> class.
/// </summary>
/// <param name="storedProcedure">The stored procedure.</param>
/// <param name="ns">The namespace.</param>
public ProcedureWriter(DatabaseStoredProcedure storedProcedure, string ns)
{
_namespace = ns;
_storedProcedure = storedProcedure;
}
public void ProcedureTest()
{
var schema = PrepareModel();
var procedure = new DatabaseStoredProcedure { Name = "SelectCategory" };
var argument = new DatabaseArgument
{
Name = "p1",
DatabaseDataType = "VARCHAR",
Length = 10,
DataType = DataTypeConverter.FindDataType("VARCHAR", schema.DataTypes, SqlType.SqlServer, null),
In = true,
};
procedure.Arguments.Add(argument);
var rs = new DatabaseResultSet();
var resultColumn = new DatabaseColumn { Name = "Output", DbDataType = "VARCHAR" };
DataTypeConverter.AddDataType(resultColumn);
rs.Columns.Add(resultColumn);
procedure.ResultSets.Add(rs);
schema.StoredProcedures.Add(procedure);
var directory = TestHelper.CreateDirectory("MySprocTest");
const string @namespace = "MySprocTest";
var settings = new CodeWriterSettings
{
Namespace = @namespace,
CodeTarget = CodeTarget.Poco,
WriteStoredProcedures = true
};
var target = new CodeWriter(schema, settings);
target.Execute(directory);
var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
if (procedures == null)
Assert.Fail("Could not find Procedures subdirectory");
var files = procedures.GetFiles("*.cs");
var products = files.FirstOrDefault(f => f.Name == "SelectCategory.cs");
Assert.IsNotNull(products, "Should have written SelectCategory class for SelectCategory procedure");
var category = files.FirstOrDefault(f => f.Name == "SelectCategoryResult.cs");
Assert.IsNotNull(category, "Should have written SelectCategoryResult class to the result of the sproc");
}
public SprocLogic(DatabaseStoredProcedure storedProcedure)
{
_storedProcedure = storedProcedure;
_className = _storedProcedure.NetName ?? (_storedProcedure.NetName = NameFixer.ToPascalCase(_storedProcedure.Name));
}
public override string DropProcedure(DatabaseStoredProcedure procedure)
{
return null; //doesn't support it
}
public void BuildProcedureCode(DatabaseSchema databaseSchema, DatabaseStoredProcedure databaseStoredProcedure)
{
try
{
//grab the data
if (databaseStoredProcedure.ResultSets.Count == 0)
{
//Delete sprocs won't have resultsets, so will get called multiple times
var sprocRunner = new DatabaseSchemaReader.Procedures.ResultSetReader(databaseSchema);
sprocRunner.ExecuteProcedure(databaseStoredProcedure);
}
//write it
var sprocWriter = new ProcedureWriter(databaseStoredProcedure, "Domain");
var txt = sprocWriter.Write();
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Clipboard.SetText("//sorry, not available - " + exception.Message,
TextDataFormat.UnicodeText);
Debug.WriteLine(exception.Message);
}
}
private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, List<DatabaseArgument> args)
{
//if it's ordinal 0 and no name, it's a function not a sproc
DatabaseStoredProcedure sproc;
if (args.Find(delegate(DatabaseArgument arg) { return arg.Ordinal == 0 && string.IsNullOrEmpty(arg.Name); }) != null)
{
//functions are just a type of stored procedure
DatabaseFunction fun = new DatabaseFunction();
databaseSchema.Functions.Add(fun);
sproc = fun;
}
else
{
sproc = new DatabaseStoredProcedure();
databaseSchema.StoredProcedures.Add(sproc);
}
return sproc;
}
private static void UpdateProcedure(DatabaseStoredProcedure procedure, DataSet resultSet)
{
foreach (DataTable table in resultSet.Tables)
{
var result = new DatabaseResultSet();
procedure.ResultSets.Add(result);
foreach (DataColumn column in table.Columns)
{
var dbColumn = new DatabaseColumn();
dbColumn.Name = column.ColumnName;
dbColumn.DbDataType = column.DataType.Name;
dbColumn.Length = column.MaxLength;
dbColumn.Nullable = column.AllowDBNull;
result.Columns.Add(dbColumn);
}
}
}
