void IDBTraits.ReplaceStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody newBody) { string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY"); var position = FindProcedureBodyPosition(package, procedure); StringBuilder packageBuilder = new StringBuilder(); packageBuilder.AppendLine("create or replace"); for (int i = 0; i < position.header; i++) { packageBuilder.Append(package[i]); } packageBuilder.Append(FormatStoredProcedure(procedure, newBody)); for (int i = position.end + 1; i < package.Length; i++) { packageBuilder.AppendFormat(package[i]); } /*Console.WriteLine(); * Console.WriteLine("===NEW PACKAGE==="); * Console.WriteLine(packageBuilder.ToString()); * Console.WriteLine("===END PACKAGE===");*/ using (DbCommand command = commandCreator()) { command.CommandText = packageBuilder.ToString(); command.ExecuteNonQuery(); } }
void IDBTraits.RemoveStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure) { { string[] packageSpec = GetPackage(commandCreator, procedure.packageName, "PACKAGE"); int position = FindProcedureHeader(packageSpec, procedure); StringBuilder packageSpecBuilder = new StringBuilder(); packageSpecBuilder.AppendLine("create or replace"); for (int i = 0; i < position; i++) { packageSpecBuilder.Append(packageSpec[i]); } for (int i = position + 1; i < packageSpec.Length; i++) { packageSpecBuilder.Append(packageSpec[i]); } using (DbCommand command = commandCreator()) { command.CommandText = packageSpecBuilder.ToString(); /*Console.WriteLine(); * Console.WriteLine("=====PACKAGE SPEC====="); * Console.WriteLine(command.CommandText); * Console.WriteLine("=====END PACKAGE SPEC=====");*/ command.ExecuteNonQuery(); } } { string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY"); var position = FindProcedureBodyPosition(package, procedure); StringBuilder packageBuilder = new StringBuilder(); packageBuilder.AppendLine("create or replace"); for (int i = 0; i < position.header; i++) { packageBuilder.Append(package[i]); } for (int i = position.end + 1; i < package.Length; i++) { packageBuilder.Append(package[i]); } using (DbCommand command = commandCreator()) { command.CommandText = packageBuilder.ToString(); /*Console.WriteLine(); * Console.WriteLine("=====PACKAGE BODY====="); * Console.WriteLine(command.CommandText); * Console.WriteLine("=====END PACKAGE BODY=====");*/ command.ExecuteNonQuery(); } } }
private static string FormatStoredProcedure(StoredProcedureReference procedure, StoredProcedureBody body) { StringBuilder result = new StringBuilder(); result.Append(FormatStoredProcedureHeader(procedure)); result.AppendLine(); result.AppendLine("is"); if (body.declarations.Trim() != "") { result.AppendLine(body.declarations.TrimEnd().Trim('\r', '\n')); } result.AppendLine("begin"); if (body.body.Trim() != "") { result.AppendLine(body.body.TrimEnd().Trim('\r', '\n')); } result.AppendFormat("end {0};", procedure.procedureName); result.AppendLine(); return(result.ToString()); }
private static int FindProcedureHeader(string[] package, StoredProcedureReference procedure) { for (int i = 0; i < package.Length; i++) { var line = package[i].Trim().TrimEnd(';'); if (PROCEDURE_BODY_HEADER_REGEX.IsMatch(line)) { var match = PROCEDURE_BODY_HEADER_REGEX.Match(line); string name = match.Groups["name"].Value; string[] parameters = match.Groups["params"].Value.Split(','); if (name == procedure.procedureName) { if (parameters.Length != procedure.parameters.Length) { throw new ApplicationException(String.Format("Parameters number mismatch for procedure {0}: expected {1}, got {2}", procedure.procedureName, procedure.parameters.Length, parameters.Length)); } for (int j = 0; j < parameters.Length; j++) { string[] parts = parameters[j].Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (parts[0] != procedure.parameters[j].name) { throw new ApplicationException(String.Format("Parameter {1} name mismatch for procedure {0}: expected {2}, got {3}", j, procedure.procedureName, procedure.parameters[j].name, parts[0])); } if (parts[1].ToLower() != DIRECTIONS[procedure.parameters[j].direction]) { throw new ApplicationException(String.Format("Parameter {1} direction mismatch for procedure {0}: expected {2}, got {3}", procedure.procedureName, procedure.parameters[j].name, DIRECTIONS[procedure.parameters[j].direction], parts[1])); } if (parts[2] != procedure.parameters[j].type) { throw new ApplicationException(String.Format("Parameter {1} type mismatch for procedure {0}: expected {2}, got {3}", j, procedure.procedureName, procedure.parameters[j].type, parts[2])); } } return(i); } } } throw new ProcedureNotFoundException(String.Format("Procedure {0} header not found", procedure.procedureName)); }
void IDBTraits.CreateStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody body) { throw new NotImplementedException(); }
private static string FormatStoredProcedureHeader(StoredProcedureReference procedure) { throw new NotImplementedException(); }
StoredProcedureBody IDBTraits.GetStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure) { throw new NotImplementedException(); }
void IDBTraits.CreateStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody body) { string packageEnd = string.Format("end {0};", procedure.packageName); string packageEndMarker = packageEnd.ToLower(); { string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY"); try { var position = FindProcedureBodyPosition(package, procedure); throw new ApplicationException(String.Format("Procedure {0} body is already declared", procedure.procedureName)); } catch (ProcedureNotFoundException) { } StringBuilder packageBuilder = new StringBuilder(); packageBuilder.AppendLine("create or replace"); for (int i = 0; i < package.Length; i++) { if (package[i].Trim().ToLower() == packageEndMarker) { if (package.Length > i + 1) { throw new ApplicationException("Unexpected package end"); } break; } packageBuilder.Append(package[i]); } packageBuilder.Append(FormatStoredProcedure(procedure, body)); packageBuilder.AppendLine(packageEnd); using (DbCommand command = commandCreator()) { command.CommandText = packageBuilder.ToString(); /*Console.WriteLine(); * Console.WriteLine("=====PACKAGE BODY====="); * Console.WriteLine(command.CommandText); * Console.WriteLine("=====END PACKAGE BODY=====");*/ command.ExecuteNonQuery(); } } { string[] packageSpec = GetPackage(commandCreator, procedure.packageName, "PACKAGE"); try { var position = FindProcedureHeader(packageSpec, procedure); throw new ApplicationException(String.Format("Procedure {0} is already declared", procedure.procedureName)); } catch (ProcedureNotFoundException) { } StringBuilder packageSpecBuilder = new StringBuilder(); packageSpecBuilder.AppendLine("create or replace"); for (int i = 0; i < packageSpec.Length; i++) { if (packageSpec[i].Trim().ToLower() == packageEndMarker) { if (packageSpec.Length > i + 1) { throw new ApplicationException("Unexpected package end"); } break; } packageSpecBuilder.Append(packageSpec[i]); } packageSpecBuilder.Append(FormatStoredProcedureHeader(procedure)); packageSpecBuilder.AppendLine(";"); packageSpecBuilder.AppendLine(packageEnd); using (DbCommand command = commandCreator()) { command.CommandText = packageSpecBuilder.ToString(); /*Console.WriteLine(); * Console.WriteLine("=====PACKAGE SPEC====="); * Console.WriteLine(command.CommandText); * Console.WriteLine("=====END PACKAGE SPEC=====");*/ command.ExecuteNonQuery(); } } }
private static string FormatStoredProcedureHeader(StoredProcedureReference procedure) { return(string.Format("procedure {0}({1})", procedure.procedureName, String.Join(", ", (from param in procedure.parameters select String.Format("{0} {1} {2}", param.name, DIRECTIONS[param.direction], param.type)).ToArray()))); }
StoredProcedureBody IDBTraits.GetStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure) { string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY"); var position = FindProcedureBodyPosition(package, procedure); StringBuilder declarationsBuilder = new StringBuilder(); for (int i = position.header + 2; i < position.begin; i++) { declarationsBuilder.Append(package[i]); } StringBuilder bodyBuilder = new StringBuilder(); for (int i = position.begin + 1; i < position.end; i++) { bodyBuilder.AppendFormat(package[i]); } return(new StoredProcedureBody(declarationsBuilder.ToString(), bodyBuilder.ToString())); }
private static ProcedureBodyPosition FindProcedureBodyPosition(string[] package, StoredProcedureReference procedure) { int header = FindProcedureHeader(package, procedure); int?begin = null; int?end = null; if (package.Length <= header + 2) { throw new ApplicationException("Unexpected package end"); } if (package[header + 1].Trim().ToLower() != "is") { throw new ApplicationException(String.Format("Incorrect line follows procedure {0} header: '{1}'", procedure.procedureName, package[header + 1].Trim())); } for (int i = header + 2; i < package.Length; i++) { if (package[i].Trim().ToLower() == "begin") { begin = i; break; } } if (!begin.HasValue) { throw new ApplicationException(String.Format("Unable to find body begin for procedure {0}", procedure.procedureName)); } if (package.Length <= begin + 1) { throw new ApplicationException("Unexpected package end"); } for (int i = begin.Value + 1; i < package.Length; i++) { string line = package[i].Trim(); if (PROCEDURE_BODY_FOOTER_REGEX.IsMatch(line)) { var match = PROCEDURE_BODY_FOOTER_REGEX.Match(line); var name = match.Groups["name"].Value; if (name != procedure.procedureName) { throw new ApplicationException(String.Format("Wrong procedure footer: expected {0}, got {1}", procedure.procedureName, name)); } end = i; break; } } if (!end.HasValue) { throw new ApplicationException("Procedure footer not found"); } return(new ProcedureBodyPosition { header = header, begin = begin.Value, end = end.Value, }); }
public void CreateStoredProcedure(StoredProcedureReference procedure, StoredProcedureBody body) { this.traits.CreateStoredProcedure(this.CreateTextCommand, procedure, body); }
public void RemoveStoredProcedure(StoredProcedureReference procedure) { this.traits.RemoveStoredProcedure(this.CreateTextCommand, procedure); }
public void ReplaceStoredProcedureBody(StoredProcedureReference procedure, StoredProcedureBody newBody) { this.traits.ReplaceStoredProcedureBody(this.CreateTextCommand, procedure, newBody); }
//SELECT LINE, TEXT FROM ALL_SOURCE WHERE TYPE = 'PACKAGE BODY' AND NAME = '' AND OWNER = (SELECT USER FROM DUAL) for update public StoredProcedureBody GetStoredProcedureBody(StoredProcedureReference procedure) { return(this.traits.GetStoredProcedureBody(this.CreateTextCommand, procedure)); }