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 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);
}
}
internal SprocResultWriter(DatabaseStoredProcedure storedProcedure, string ns, ClassBuilder classBuilder)
{
_namespace = ns;
_storedProcedure = storedProcedure;
_logic = new SprocLogic(_storedProcedure);
_resultClassName = _logic.ResultClassName;
_cb = classBuilder;
}
public SprocResultWriter(DatabaseStoredProcedure storedProcedure, string ns)
{
_namespace = ns;
_storedProcedure = storedProcedure;
_logic = new SprocLogic(_storedProcedure);
_resultClassName = _logic.ResultClassName;
_cb = new ClassBuilder();
}
public virtual string DropProcedure(DatabaseStoredProcedure procedure)
{
return(string.Format(CultureInfo.InvariantCulture,
"DROP PROCEDURE {0}{1};",
SchemaPrefix(procedure.SchemaOwner),
Escape(procedure.Name))
+ _sqlFormatProvider.RunStatements());
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure)
{
using (var dbConnection = _factory.CreateConnection())
{
dbConnection.ConnectionString = _schema.ConnectionString;
dbConnection.Open();
ExecuteProcedure(procedure, dbConnection);
}
}
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 void BuildProcedure(DatabaseStoredProcedure storedProcedure)
{
try
{
var txt = _migrationGenerator.AddProcedure(storedProcedure);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure)
{
if (_factory == null)
{
_factory = DbProviderFactories.GetFactory(_schema.Provider);
}
using (var dbConnection = _factory.CreateConnection())
{
dbConnection.ConnectionString = _schema.ConnectionString;
dbConnection.Open();
ExecuteProcedure(procedure, dbConnection);
}
}
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 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);
}
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);
}
}
}
public string BuildProcedure(DatabaseStoredProcedure storedProcedure)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendLine(m_migrationGenerator.AddProcedure(storedProcedure));
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
private static void PrepareStoredProcedureNames(DatabaseStoredProcedure sproc)
{
if (string.IsNullOrEmpty(sproc.NetName))
{
sproc.NetName = NameFixer.ToPascalCase(sproc.Name);
}
foreach (var argument in sproc.Arguments)
{
if (string.IsNullOrEmpty(argument.NetName))
{
argument.NetName = NameFixer.ToPascalCase(argument.Name);
}
}
}
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 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);
}
}
}
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);
}
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 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);
}
public static void StoredProcedures(DatabaseSchema schema, DataTable dt)
{
var storedProcedureKeyMap = new StoredProcedureKeyMap(dt);
foreach (DataRow row in dt.Rows)
{
string name = row[storedProcedureKeyMap.Key].ToString();
string schemaOwner = row[storedProcedureKeyMap.OwnerKey].ToString();
if (storedProcedureKeyMap.IsDb2)
{
//ignore db2 system sprocs
if (IsDb2SystemSchema(schemaOwner))
{
continue;
}
}
bool isFunction = IsFunction(storedProcedureKeyMap.RoutineTypeKey, row);
string package = null;
if (storedProcedureKeyMap.PackageKey != null)
{
package = row[storedProcedureKeyMap.PackageKey].ToString();
if (string.IsNullOrEmpty(package))
{
package = null; //so we can match easily
}
}
//check if already loaded (so can call this function multiple times)
DatabaseStoredProcedure sproc = FindStoredProcedureOrFunction(schema, name, schemaOwner, package);
if (sproc == null)
{
sproc = CreateProcedureOrFunction(schema, isFunction);
sproc.Name = name;
sproc.SchemaOwner = schemaOwner;
sproc.Package = package;
}
if (storedProcedureKeyMap.Sql != null)
{
sproc.Sql = row[storedProcedureKeyMap.Sql].ToString();
}
}
}
public string BuildProcedureCode(DatabaseSchema databaseSchema, DatabaseStoredProcedure databaseStoredProcedure)
{
StringBuilder sb = new StringBuilder();
try
{
if (databaseStoredProcedure.ResultSets.Count == 0)
{
var sprocRunner = new Nikita.Base.DbSchemaReader.Procedures.ResultSetReader(databaseSchema);
sprocRunner.ExecuteProcedure(databaseStoredProcedure);
}
var sprocWriter = new ProcedureWriter(databaseStoredProcedure, "Domain");
sb.Append(sprocWriter.Write());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
public static string GenerateStoredProcedure(DatabaseStoredProcedure proc)
{
if (_procedureTemplate == null)
{
_procedureTemplate = ReadTemplate("storedProcedure");
}
var builder = new StringBuilder(_procedureTemplate);
var sprocRunner = new DatabaseSchemaReader.Procedures.ResultSetReader(proc.DatabaseSchema);
sprocRunner.ExecuteProcedure(proc);
var resultsets = proc.ResultSets;
var propertyString = "";
if (resultsets.Count <= 0)
{
return(builder.Replace("$name$", proc.Name)
.Replace("$properties$", propertyString).ToString());
}
var properties = proc.ResultSets.First().Columns.Select(GenerateAutoPropertySp).ToList();
propertyString = properties.Aggregate((c, n) => c + n);
return(builder.Replace("$name$", proc.Name)
.Replace("$properties$", propertyString).ToString());
}
private static Sp GenerateSp(DatabaseStoredProcedure procedure)
{
return(new Sp {
Name = procedure.Name, Content = TemplateGenarator.GenerateStoredProcedure(procedure), StoredProcedure = procedure
});
}
public override string DropProcedure(DatabaseStoredProcedure procedure)
{
return(null); //doesn't support it
}
public string DropProcedure(DatabaseStoredProcedure procedure)
{
return(_migration.DropProcedure(procedure));
}
public SprocLogic(DatabaseStoredProcedure storedProcedure)
{
_storedProcedure = storedProcedure;
_className = _storedProcedure.NetName ?? (_storedProcedure.NetName = NameFixer.ToPascalCase(_storedProcedure.Name));
}
/// <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 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;
}
