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