public async Task <CommandResponse> ExecuteProcedure(ProcedureName procedureName, DbRequestToken token, params DataBaseParameter[] parameters)
{
var command = token.Connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName.GetStringName();
command.Transaction = token.Transaction;
AddParamsToCommand(command, parameters);
var reader = await command.ExecuteReaderAsync();
var resp = new CommandResponse();
for (int i = 0; i < reader.FieldCount; i++)
{
resp.ColumnNames.Add(reader.GetName(i));
}
while (reader.Read())
{
var currentVals = new List <object>(reader.FieldCount);
for (var i = 0; i < reader.FieldCount; ++i)
{
currentVals.Add(reader[i]);
}
}
return(resp);
}
protected override void AppendTo(SqlStringBuilder builder)
{
var orReplace = ReplaceIfExists ? "OR REPLACE" : "";
builder.AppendFormat("CREATE {0}PROCEDURE ", orReplace);
ProcedureName.AppendTo(builder);
builder.Append("(");
if (Parameters != null && Parameters.Length > 0)
{
for (int i = 0; i < Parameters.Length; i++)
{
Parameters[i].AppendTo(builder);
if (i < Parameters.Length - 1)
{
builder.Append(", ");
}
}
}
builder.Append(")");
builder.AppendLine(" IS");
builder.Indent();
Body.AppendTo(builder);
builder.DeIndent();
}
public int AddCharacter(int accId, string nickname, string race, string className, char sex)
{
dbConnection.OpenConnection();
OracleCommand cmd = dbConnection.connection.CreateCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName.BuildProcedureName(PackageType.NORMAL, ProcedureName.add_character);
cmd.BindByName = true;
cmd.Parameters.Add("Return_Value", OracleDbType.Int16, ParameterDirection.ReturnValue);
cmd.Parameters.Add("acc_id", accId);
cmd.Parameters.Add("nick", nickname);
cmd.Parameters.Add("rasa", race);
cmd.Parameters.Add("klasa", className);
cmd.Parameters.Add("plec", sex);
cmd.ExecuteNonQuery();
return(Convert.ToInt16(cmd.Parameters["Return_value"].Value.ToString()));
}
catch (OracleException ex)
{
Console.WriteLine("Cannot run procedure!");
Console.WriteLine("Exception message: " + ex.Message);
Console.WriteLine("Exception source: " + ex.Source);
return(-1);
}
}
public override void WriteTo(IndentedTextWriter writer)
{
var initialIndent = writer.Indent;
writer.Write("CREATE PROCEDURE ");
ProcedureName.WriteTo(writer);
// parameters
if (Parameters.Any())
{
writer.Write(" (");
writer.Indent++;
Parameters.WriteCommaSeparatedMultilineTo(writer);
writer.Indent--;
writer.Write(')');
}
writer.WriteLine();
writer.Indent++;
try
{
// definition
writer.Write("AS ");
Definition.WriteTo(writer);
}
finally
{
writer.Indent--;
}
writer.WriteLine();
}
public async Task <T> ExecProcedureNonQuery <T>(ProcedureName procedureName, DbRequestToken token, params ProcedureParameter[] parameters) where T : struct
{
var dbParams = await GetDbParameters(procedureName, token, parameters);
var result = await dbMediator.ExecuteProcedureNonQuery(procedureName, token, dbParams);
return((T)Convert.ChangeType(result, typeof(T)));
}
public async Task <IEnumerable <T> > ExecProcedure <T>(ProcedureName procedureName, DbRequestToken token, params ProcedureParameter[] parameters) where T : new()
{
var dbParams = await GetDbParameters(procedureName, token, parameters);
var result = await dbMediator.ExecuteProcedure(procedureName, token, dbParams);
return(result.MapToType <T>());
}
public IEnumerable <ResultSet> Execute(IDictionary <string, object> suppliedParameters)
{
// TODO: PostgreSql supports stored procedure overloading. This does not.
var procedure = DatabaseSchema.Get(Adapter.ConnectionProvider, Adapter.ProviderHelper).FindProcedure(ProcedureName);
if (procedure == null)
{
throw new UnresolvableObjectException(ProcedureName.ToString());
}
var executeReader = procedure.Parameters.Where(p => p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Output ||
p.Direction == ParameterDirection.ReturnValue).Count() > 0;
using (var conn = Adapter.ConnectionProvider.CreateConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = procedure.QualifiedName;
cmd.CommandType = CommandType.StoredProcedure;
AddCommandParameters(procedure, cmd, suppliedParameters);
try
{
var result = Enumerable.Empty <ResultSet>();
cmd.WriteTrace();
if (executeReader)
{
using (var rdr = cmd.ExecuteReader())
{
var rdrAdvanced = RetrieveReturnValue(procedure, rdr, suppliedParameters);
rdrAdvanced = RetrieveOutputParameterValues(procedure, rdr, suppliedParameters, rdrAdvanced);
if (!rdrAdvanced)
{
result = rdr.ToMultipleDictionaries();
}
}
}
else
{
cmd.ExecuteNonQuery();
}
return(result);
}
catch (DbException ex)
{
throw new AdoAdapterException(ex.Message, cmd);
}
}
}
}
private async Task <DataBaseParameter[]> GetDbParameters(ProcedureName procedureName, DbRequestToken token, params ProcedureParameter[] parameters)
{
var result = new DataBaseParameter[parameters.Length];
for (var i = 0; i < parameters.Length; ++i)
{
result[i] = await GetDbParameter(procedureName, token, parameters[i]);
}
return(result);
}
public async Task <int> ExecuteProcedureNonQuery(ProcedureName procedureName, DbRequestToken token, params DataBaseParameter[] parameters)
{
var command = token.Connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName.GetStringName();
command.Transaction = token.Transaction;
AddParamsToCommand(command, parameters);
return(await command.ExecuteNonQueryAsync());
}
/// <summary>
/// Gets the name of the procedure.
/// </summary>
/// <param name="dbName">Name of the db.</param>
/// <param name="extraction">The extraction.</param>
/// <param name="nameFormat">The name format.</param>
/// <returns></returns>
public ProcedureName GetProcedureName(string dbName, WordsExtraction extraction, NameFormat nameFormat)
{
var words = GetLanguageWords(nameFormat.Culture);
var procedureName = new ProcedureName {
DbName = dbName
};
procedureName.NameWords = ExtractWords(words, dbName, extraction);
procedureName.MethodName = Format(words, procedureName.NameWords, nameFormat.Case, Singularization.DontChange);
return(procedureName);
}
protected override void AppendTo(SqlStringBuilder builder)
{
builder.Append("CREATE ");
if (ReplaceIfExists)
{
builder.Append("OR REPLACE ");
}
builder.Append("TRIGGER ");
TriggerName.AppendTo(builder);
builder.AppendFormat(" {0} {1} ", EventTime.ToString().ToUpperInvariant(), EventType.AsDebugString());
builder.Append("ON ");
TableName.AppendTo(builder);
builder.Append(" ");
builder.Append("FOR EACH ROW ");
if (Status != TriggerStatus.Unknown)
{
if (Status == TriggerStatus.Disabled)
{
builder.Append("DISABLE ");
}
else if (Status == TriggerStatus.Enabled)
{
builder.Append("ENABLE ");
}
}
builder.Append("CALL ");
ProcedureName.AppendTo(builder);
builder.Append("(");
if (ProcedureArguments != null &&
ProcedureArguments.Length > 0)
{
for (int i = 0; i < ProcedureArguments.Length; i++)
{
ProcedureArguments[i].AppendTo(builder);
if (i < ProcedureArguments.Length - 1)
{
builder.Append(", ");
}
}
}
builder.Append(")");
}
/// <summary>
/// Validates this procedure
/// </summary>
/// <param name="path">The path to this object as a string</param>
/// <param name="messages">the validation messages to date, these may be added to within this method</param>
public void Validate(string path, List <ValidationMessage> messages)
{
var vb = new ValidationBuilder(path, messages);
if (vb.ArgumentRequiredCheck("Procedure Name", ProcedureName))
{
if (ProcedureName != null)
{
ProcedureName.ValidateMandatory(vb.Path + "ProcedureName", vb.Messages);
}
}
}
public override int GetHashCode()
{
unchecked
{
var hashCode = base.GetHashCode();
hashCode = (hashCode * 397) ^ SourceId.GetHashCode();
if (ProcedureName != null)
{
hashCode = (hashCode * 397) ^ ProcedureName.GetHashCode();
}
return(hashCode);
}
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (ProcedureType == ProcedureProcedureType.LEGACY)
{
throw new NotSupportedException("Legacy type stored procedures are not supported.");
}
var command = new PSqlCommand();
command.Append($"CREATE OR ALTER PROCEDURE {ProcedureName.AsSqlIndentifier()}");
if (ProcedureInputs > 0)
{
var inputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.IN)
.OrderBy(o => o.ParameterNumber);
command.Append(" (");
command.AppendLine();
command.Append(CreateParametersDefinitions(inputs, sourceMetadata, targetMetadata, context));
command.Append(")");
}
command.AppendLine();
if (ProcedureOutputs > 0)
{
var outputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.OUT)
.OrderBy(o => o.ParameterNumber);
command.Append("RETURNS (");
command.AppendLine();
command.Append(CreateParametersDefinitions(outputs, sourceMetadata, targetMetadata, context));
command.Append(")");
command.AppendLine();
}
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
if (ProcedureType == ProcedureProcedureType.SELECTABLE)
{
command.Append($" SUSPEND{SqlHelper.Terminator}");
command.AppendLine();
}
command.Append("END");
}
else
{
command.Append(ProcedureSource);
}
yield return(command);
}
public override int GetHashCode()
{
unchecked
{
var hashCode = base.GetHashCode();
hashCode = (hashCode * 397) ^ (SourceId.GetHashCode());
if (ProcedureName != null)
{
hashCode = (hashCode * 397) ^ (ProcedureName.GetHashCode());
}
if (ExecuteActionString != null)
{
hashCode = (hashCode * 397) ^ (ExecuteActionString.GetHashCode());
}
return(hashCode);
}
}
private async Task <DataBaseParameter> GetDbParameter(ProcedureName procedureName, DbRequestToken token, ProcedureParameter parameter)
{
var dbParam = new DataBaseParameter(parameter);
if (parameter.DbType.HasValue)
{
return(dbParam);
}
var procNameParam = new ProcedureParameter("procedureName", procedureName.GetStringName())
{
DbType = SqlDbType.NVarChar
};
var res = (await ExecProcedure <ProcedureParameterDescription>(ProcedureName.spInternalGetProcedureParameters, token, procNameParam)).Single();
dbParam.DbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), res.ToString());
return(dbParam);
}
public int GeneratePosition()
{
dbConnection.OpenConnection();
OracleCommand cmd = dbConnection.connection.CreateCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName.BuildProcedureName(PackageType.NORMAL, ProcedureName.generate_position);
cmd.BindByName = true;
cmd.Parameters.Add("Return_Value", OracleDbType.Int16, ParameterDirection.ReturnValue);
cmd.ExecuteNonQuery();
return(Convert.ToInt16(cmd.Parameters["Return_value"].Value.ToString()));
}
catch (OracleException ex)
{
Console.WriteLine("Cannot run procedure!");
Console.WriteLine("Exception message: " + ex.Message);
Console.WriteLine("Exception source: " + ex.Source);
return(-1);
}
}
public bool CheckIsAccountExists(int id)
{
dbConnection.OpenConnection();
OracleCommand cmd = dbConnection.connection.CreateCommand();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName.BuildProcedureName(PackageType.NORMAL, ProcedureName.check_is_account_exists);
cmd.BindByName = true;
cmd.Parameters.Add("Return_Value", OracleDbType.Int16, ParameterDirection.ReturnValue);
cmd.Parameters.Add("acc_id", id);
cmd.ExecuteNonQuery();
return(Convert.ToBoolean(Convert.ToInt16(cmd.Parameters["Return_value"].Value.ToString())));
}
catch (OracleException ex)
{
Console.WriteLine("Cannot run procedure!");
Console.WriteLine("Exception message: " + ex.Message);
Console.WriteLine("Exception source: " + ex.Source);
return(false);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="LocalVariable"/> class.
/// </summary>
/// <param name="name">The name of the variable.</param>
/// <param name="proc">The procedure in which the local variable is instantiated.</param>
public LocalVariable(string name, MethodDefinition proc)
{
PvName = name;
ProcName = new ProcedureName(proc);
}
protected override IEnumerable <Command> OnDrop(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
yield return(new Command()
.Append($"DROP PROCEDURE {ProcedureName.AsSqlIndentifier()}"));
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (ProcedureType == ProcedureProcedureType.Legacy)
{
throw new NotSupportedException("Legacy type stored procedures are not supported.");
}
var command = SqlHelper.IsValidExternalEngine(this) ? new Command() : new PSqlCommand();
command.Append($"CREATE OR ALTER PROCEDURE {ProcedureName.AsSqlIndentifier()}");
if (ProcedureInputs > 0)
{
var inputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.In)
.OrderBy(o => o.ParameterNumber);
command.Append(" (");
command.AppendLine();
command.Append(CreateParametersDefinitions(inputs, sourceMetadata, targetMetadata, context));
command.Append(")");
}
command.AppendLine();
if (ProcedureOutputs > 0)
{
var outputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.Out)
.OrderBy(o => o.ParameterNumber);
command.Append("RETURNS (");
command.AppendLine();
command.Append(CreateParametersDefinitions(outputs, sourceMetadata, targetMetadata, context));
command.Append(")");
command.AppendLine();
}
if (SqlHelper.IsValidExternalEngine(this))
{
if (context.EmptyBodiesEnabled)
{
yield break;
}
command.Append($"EXTERNAL NAME '{SqlHelper.DoubleSingleQuotes(EntryPoint)}'");
command.AppendLine();
command.Append($"ENGINE {EngineName.AsSqlIndentifier()}");
}
else
{
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
if (ProcedureType == ProcedureProcedureType.Selectable)
{
command.Append($" SUSPEND{SqlHelper.Terminator}");
command.AppendLine();
}
command.Append("END");
}
else
{
command.Append(ProcedureSource);
}
}
yield return(command);
}
Cmd = new SqlCommand(ProcedureName, Con)
{
CommandType = CommandType.StoredProcedure
};
}
/// <summary>
/// Gets the name of the procedure.
/// </summary>
/// <param name="dbName">Name of the db.</param>
/// <param name="extraction">The extraction.</param>
/// <param name="nameFormat">The name format.</param>
/// <returns></returns>
public ProcedureName GetProcedureName(string dbName, WordsExtraction extraction, NameFormat nameFormat)
{
var words = GetLanguageWords(nameFormat.Culture);
var procedureName = new ProcedureName { DbName = dbName };
procedureName.NameWords = ExtractWords(words, dbName, extraction);
procedureName.MethodName = Format(words, procedureName.NameWords, nameFormat.Case, Singularization.DontChange);
return procedureName;
}
public async Task ExecuteProcedureNoResult(ProcedureName procedureName, DbRequestToken token, params ProcedureParameter[] parameters)
{
var dbParams = await GetDbParameters(procedureName, token, parameters);
await dbMediator.ExecuteProcedureNonQuery(procedureName, token, dbParams);
}
public static string GetStringName(this ProcedureName procedureName)
{
return($"[dbo].[{procedureName}]");
}
