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); } } }