public async Task <TM_OPERADOR_EMPRESA> Update(TM_OPERADOR_EMPRESA input) { using (var connection = _connectionFactory.GetConnectionSIROS()) { var dyParam = new OracleDynamicParameters(); dyParam.Add("num_idnominaxemp_ ", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDNOMINAXEMP); dyParam.Add("num_idtipooper_", OracleDbType.Varchar2, ParameterDirection.Input, input.NUM_IDTIPOOPER); dyParam.Add("dte_fecnacimiento_", OracleDbType.Int32, ParameterDirection.Input, input.DTE_FECNACIMIENTO.ToBDSirosDate()); dyParam.Add("str_usuact_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_USUACT); dyParam.Add("num_idsesion_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSESION); dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output); var query = _connectionFactory.GetQueryForSIROS("PKG_EMPRESA.SP_ActualizarOperadorEmp"); var result = await connection.QueryFirstOrDefaultAsync <TM_OPERADOR_EMPRESA>(query, param : dyParam, commandType : CommandType.StoredProcedure); return(result); } }
public async Task <List <TM_SUCURSAL_ES> > GetAllByFilter(int cantidadXPagina, int pagina, string filter) { using (var connection = _connectionFactory.GetConnectionSIROS()) { var splFilter = filter.Split('@'); var dyParam = new OracleDynamicParameters(); dyParam.Add("num_filtrotipo_", OracleDbType.Varchar2, ParameterDirection.Input, splFilter[0]); dyParam.Add("str_filtrovalor_", OracleDbType.Varchar2, ParameterDirection.Input, splFilter[1].ToUpper()); dyParam.Add("num_identidadusuario_", OracleDbType.Int32, ParameterDirection.Input, splFilter[2]); dyParam.Add("num_pagina_", OracleDbType.Varchar2, ParameterDirection.Input, pagina); dyParam.Add("num_registros_", OracleDbType.Varchar2, ParameterDirection.Input, cantidadXPagina); dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output); var query = _connectionFactory.GetQueryForSIROS("PKG_SUCURSAL.SP_GetListaSucursal"); var result = await connection.QueryAsync <TM_SUCURSAL_ES>(query, param : dyParam, commandType : CommandType.StoredProcedure); return(result.AsList()); } }
public async Task <ADMIN.TM_SESION> RegistrarSesion(ADMIN.TM_SESION oItem) { using (var connection = _connectionFactory.GetConnectionSIROS()) { var dyParam = new OracleDynamicParameters(); dyParam.Add("str_usuario_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_USUARIO); dyParam.Add("str_userso_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_USERSO); dyParam.Add("str_numeroip_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_NUMEROIP); dyParam.Add("str_flagcr_", OracleDbType.Varchar2, ParameterDirection.Input, oItem.STR_FLAGCR); dyParam.Add("num_idsesionsso_", OracleDbType.Int32, ParameterDirection.Input, oItem.NUM_IDSESIONSSO); dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output); var query = _connectionFactory.GetQueryForSIROS("PKG_ADMIN.SP_RegistrarSesion"); var result = await connection.QueryFirstOrDefaultAsync <ADMIN.TM_SESION>(query, param : dyParam, commandType : CommandType.StoredProcedure); return(result); } }
public async Task <TM_VEHICULO_RUTA_EMPRESA> Update(TM_VEHICULO_RUTA_EMPRESA input) { using (var connection = _connectionFactory.GetConnectionSIROS()) { var dyParam = new OracleDynamicParameters(); dyParam.Add("num_vehxemp_ ", OracleDbType.Int32, ParameterDirection.Input, input.NUM_VEHXEMP); dyParam.Add("num_idsucursalxes_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSUCURSALXES); dyParam.Add("num_idcombustible_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDCOMBUSTIBLE); dyParam.Add("str_usuact_", OracleDbType.Varchar2, ParameterDirection.Input, input.STR_USUACT); dyParam.Add("num_idsesion_", OracleDbType.Int32, ParameterDirection.Input, input.NUM_IDSESION); dyParam.Add("p_cursor_", OracleDbType.RefCursor, ParameterDirection.Output); var query = _connectionFactory.GetQueryForSIROS("PKG_VEHXEMP.SP_ActualizarVehxEmp"); var result = await connection.QueryFirstOrDefaultAsync <TM_VEHICULO_RUTA_EMPRESA>(query, param : dyParam, commandType : CommandType.StoredProcedure); return(result); } }
/// <summary> /// /// </summary> /// <returns></returns> public object GetEmployeeList() { object result = null; try { var parameters = new OracleDynamicParameters(); parameters.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output); var connection = GetConnection(); if (connection.State == ConnectionState.Closed) { connection.Open(); } if (connection.State == ConnectionState.Open) { result = SqlMapper.Query(connection, "USP_GETEMPLOYEES", parameters, null, true, null, CommandType.StoredProcedure); } } catch (Exception ex) { throw ex; } return(result); }
/// <summary> /// Use this method to return single recordset. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="spname"></param> /// <param name="parameters"></param> /// <param name="timeout"></param> /// <param name="commandType"></param> /// <returns></returns> public IEnumerable <dynamic> QueryDynamic <T>(string spname, object parameters = null, int timeout = 30, CommandType?commandType = null) { try { OracleDynamicParameters dynParams = new OracleDynamicParameters(); dynParams.Add("p_recordset", OracleDbType.RefCursor, ParameterDirection.Output); //dynParams.Add(":id", OracleDbType.Int32, ParameterDirection.Input, value); using (var connection = new OracleConnection(BaseUtility.ConnectionString)) { connection.Open(); return(connection.Query <dynamic>(spname, param: dynParams, commandTimeout: timeout, commandType: commandType)); } } catch (Exception ex) { new Emailer().Publish(new CustomException { ErrorMessage = ex.Message, MethodName = new StackTrace().GetFrame(1).GetMethod().Name, StackTrace = ex.StackTrace, Source = ex.Source }); throw; } }
public List <AgentInformation> GetAgentInfo(string fromDate, string toDate, string options, string accCategory, string accCategorySub) { using (var connection = this.GetConnection()) { var dyParam = new OracleDynamicParameters(); dyParam.Add("FROMDATE", OracleDbType.Varchar2, ParameterDirection.Input, fromDate); dyParam.Add("TODATE", OracleDbType.Varchar2, ParameterDirection.Input, toDate); dyParam.Add("OPTIONS", OracleDbType.Varchar2, ParameterDirection.Input, options); dyParam.Add("ACCCATEGORY", OracleDbType.Varchar2, ParameterDirection.Input, accCategory); dyParam.Add("V_ACC_SUB_CAT", OracleDbType.Varchar2, ParameterDirection.Input, accCategorySub == "All" ? null : accCategorySub); dyParam.Add("CUR_DATA", OracleDbType.RefCursor, ParameterDirection.Output); List <AgentInformation> result = SqlMapper.Query <AgentInformation>(connection, dbUser + "RPT_AGENT_INFORMATION", param: dyParam, commandType: CommandType.StoredProcedure).ToList(); this.CloseConnection(connection); this.CloseConnection(connection); return(result); } }
public void ProcedureInsertIntoUserTable(UserTable user) { first_name = user.FIRST_NAME; last_name = user.LAST_NAME; user_name = user.USER_NAME; email = user.EMAIL; password = user.PASSWORD; user_type = user.USER_TYPE; var paramIn = new OracleDynamicParameters(); paramIn.Add("vpFirstName", dbType: OracleDbType.Varchar2, value: first_name, direction: ParameterDirection.Input); paramIn.Add("vpLastName", dbType: OracleDbType.Varchar2, value: last_name, direction: ParameterDirection.Input); paramIn.Add("vpUserName", dbType: OracleDbType.Varchar2, value: user_name, direction: ParameterDirection.Input); paramIn.Add("vpEmail", dbType: OracleDbType.Varchar2, value: email, direction: ParameterDirection.Input); paramIn.Add("vpPassword", dbType: OracleDbType.Varchar2, value: password, direction: ParameterDirection.Input); paramIn.Add("npUserType", dbType: OracleDbType.Int32, value: user_type, direction: ParameterDirection.Input); conn.Query <UserTable>("INSITE_DEMO.INSERT_ANALITYCS_DATA.INSERT_INTO_USER_TABLE", paramIn, commandType: CommandType.StoredProcedure); }
public bool Create() { try { var p = new OracleDynamicParameters(); p.Add("Rut", this.Rut); p.Add("DigitoV", this.DigitoV); p.Add("Nombre", this.Nombre); p.Add("APaterno", this.APaterno); p.Add("AMaterno", this.AMaterno); p.Add("Apoderado_Id", this.Apoderado.Id); p.Add("Curso", this.Curso.Id); Db.Execute(Procs.Alumno_Crear, p, commandType: CommandType.StoredProcedure); logger.Info("Alumno N°{0} creado correctamente", Rut); return(true); } catch (Exception ex) { logger.Error(ex.Message); return(false); } }
internal static OracleDynamicParameters GetSelectRuleSetsOnQueryParams(Guid?ruleSetGuid, long?departmetnID, string searchTerm, bool?getActive, bool getHospitalLevel, long hospitalId) { int?getActiveValue = null; if (getActive != null) { getActiveValue = ((bool)getActive ? 1 : 0); } int getHospitalLevelValue = (getHospitalLevel ? 1 : 0); OracleDynamicParameters dynamicParameters = new OracleDynamicParameters(); dynamicParameters.Add("p_departmentId", departmetnID, OracleMappingType.Long, ParameterDirection.Input); dynamicParameters.Add("p_hospitalID", hospitalId, OracleMappingType.Long, ParameterDirection.Input); dynamicParameters.Add("p_rulesetGUID ", GuidConvert.ToRaw(ruleSetGuid), OracleMappingType.Raw, ParameterDirection.Input); dynamicParameters.Add("p_rulesetName", searchTerm, OracleMappingType.Varchar2, ParameterDirection.Input, 0, true, 9, 0, String.Empty, DataRowVersion.Current); dynamicParameters.Add("p_isActive", getActiveValue, OracleMappingType.Int16, ParameterDirection.Input); dynamicParameters.Add("p_hospitalOnly", getHospitalLevelValue, OracleMappingType.Int16, ParameterDirection.Input); dynamicParameters.Add("l_ruleSet ", null, OracleMappingType.RefCursor, ParameterDirection.ReturnValue); return(dynamicParameters); }
public IEnumerable <ObuhvatSektora> GetObuhvatSektora(string sifraSektora, int mjesec, int godina, string sifraPodsektora) { if (sifraPodsektora == "-1") { sifraPodsektora = null; } if (sifraSektora == "-1") { sifraSektora = null; } ISqlGenerator <ObuhvatSektora> sqlGenerator = new SqlGenerator <ObuhvatSektora>(); try { // Create repository instance ObuhvatSektoraRepository repository = new ObuhvatSektoraRepository(SqlCon, sqlGenerator); OracleDynamicParameters param = new OracleDynamicParameters(); param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_zup", null, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_grp", "zup", OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output); //Call repository method var items = repository.OracleFunctionCursor("hko.pck_obuhvat.sektor", param); return(items); } catch (Exception ex) { throw; } }
public async static Task <IDTO> AddUpdateMode(Bank bank) { string SPName = ""; string message = ""; OracleDynamicParameters oracleParams = new OracleDynamicParameters(); ComplateOperation <int> complate = new ComplateOperation <int>(); if (bank.ID.HasValue) { oracleParams.Add(BankSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)bank.ID ?? DBNull.Value); SPName = BankSPName.SP_UPADTE_BANCK; message = "Updated Successfully"; } else { oracleParams.Add(BankSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output); SPName = BankSPName.SP_INSERT_BANCK; message = "Inserted Successfully"; } oracleParams.Add(BankSpParams.PARAMETER_CURRENCY_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.CurrencyCode ?? DBNull.Value, 100); oracleParams.Add(BankSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Name ?? DBNull.Value, 500); oracleParams.Add(BankSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Name2 ?? DBNull.Value, 500); oracleParams.Add(BankSpParams.PARAMETER_PHONE_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.PhoneCode ?? DBNull.Value, 50); oracleParams.Add(BankSpParams.PARAMETER_PHONE, OracleDbType.Varchar2, ParameterDirection.Input, (object)bank.Phone ?? DBNull.Value, 50); if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1) { complate.message = message; } else { complate.message = "Operation Failed"; } return(complate); }
public async static Task <IDTO> AddUpdateMode(Report report) { string SPName = ""; string message = ""; OracleDynamicParameters oracleParams = new OracleDynamicParameters(); ComplateOperation <int> complate = new ComplateOperation <int>(); if (report.ID.HasValue) { oracleParams.Add(ReportSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)report.ID ?? DBNull.Value); SPName = ReportSPName.SP_UPADTE_Report; message = "Updated Successfully"; } else { oracleParams.Add(ReportSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output); SPName = ReportSPName.SP_INSERT_Report; message = "Inserted Successfully"; } oracleParams.Add(ReportSpParams.PARAMETER_CODE, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Code ?? DBNull.Value, 30); oracleParams.Add(ReportSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Name ?? DBNull.Value, 500); oracleParams.Add(ReportSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)report.Name2 ?? DBNull.Value, 500); oracleParams.Add(ReportSpParams.PARAMETER_ORDER_BY, OracleDbType.Int64, ParameterDirection.Input, (object)report.Order ?? DBNull.Value); oracleParams.Add(ReportSpParams.PARAMETER_GROUPID, OracleDbType.Int64, ParameterDirection.Input, (object)report.ReportGroupID ?? DBNull.Value); if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1) { complate.message = message; } else { complate.message = "Operation Failed"; } return(complate); }
public IEnumerable <ZaposlenostUdjelDTO> GetNezaposlenostUdjeliRodovi(string sifraSektora, int mjesec, int godina, string sifraPodsektora, string zupanijaID) { if (sifraPodsektora == "-1") { sifraPodsektora = null; } if (sifraSektora == "-1") { sifraSektora = null; } if (zupanijaID == "-1") { zupanijaID = null; } ISqlGenerator <ZaposlenostUdjel> sqlGenerator = new SqlGenerator <ZaposlenostUdjel>(); try { // Create repository instance ZaposlenostUdjelRepository repository = new ZaposlenostUdjelRepository(SqlCon, sqlGenerator); OracleDynamicParameters param = new OracleDynamicParameters(); param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_zup", zupanijaID, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output); //Call repository method var items = repository.OracleFunctionCursor("hko.pck_sektori.nezaposleni_udjel", param); var response = ZaposelnostUdjelHandler.MapNezaposlenostUdjelToDto(items); return(response); } catch (Exception ex) { throw; } }
public ZaposlenostStopa GetZaposlenostStopa(string sifraSektora, int mjesec, int godina, string sifraPodsektora, string zupanijaID) { if (sifraPodsektora == "-1") { sifraPodsektora = null; } if (sifraSektora == "-1") { sifraSektora = null; } if (zupanijaID == "-1") { zupanijaID = null; } ISqlGenerator <ZaposlenostStopa> sqlGenerator = new SqlGenerator <ZaposlenostStopa>(); try { // Create repository instance ZaposlenostStopaRepository repository = new ZaposlenostStopaRepository(SqlCon, sqlGenerator); OracleDynamicParameters param = new OracleDynamicParameters(); param.Add("p_godina", godina, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_mjesec", mjesec, OracleDbType.Decimal, ParameterDirection.Input); param.Add("p_sifra_sektora", sifraSektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_sifra_podsektora", sifraPodsektora, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("p_zup", zupanijaID, OracleDbType.Varchar2, ParameterDirection.Input, 32000); param.Add("outcur ", null, OracleDbType.RefCursor, ParameterDirection.Output); //Call repository method var items = repository.OracleFunctionCursor("hko.pck_sektori.nezaposleni_stopa", param).First(); //ZaposlenostStopa response = ZaposlenostStopa.MapZaposlenostBrojToDto(items); return(items); } catch (Exception ex) { throw; } }
public async static Task <IDTO> AddUpdateMode(Department department) { string SPName = ""; string message = ""; OracleDynamicParameters oracleParams = new OracleDynamicParameters(); ComplateOperation <int> complate = new ComplateOperation <int>(); if (department.ID.HasValue) { oracleParams.Add(DepartmentSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ID ?? DBNull.Value); SPName = DepartmentSPName.SP_UPADTE_COMPANY_DEPARTMENT; message = "Updated Successfully"; } else { oracleParams.Add(DepartmentSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output); SPName = DepartmentSPName.SP_INSERT_COMPANY_DEPARTMENT; message = "Inserted Successfully"; } oracleParams.Add(DepartmentSpParams.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Name ?? DBNull.Value, 500); oracleParams.Add(DepartmentSpParams.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Name2 ?? DBNull.Value, 500); oracleParams.Add(DepartmentSpParams.PARAMETER_ADDRESS, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Address ?? DBNull.Value, 30); oracleParams.Add(DepartmentSpParams.PARAMETER_EMAIL, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.Email ?? DBNull.Value, 30); oracleParams.Add(DepartmentSpParams.PARAMETER_COMPANY_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.CompanyID ?? DBNull.Value); if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1) { complate.message = message; } else { complate.message = "Operation Failed"; } return(complate); }
public async static Task <IDTO> AddUsersToGroup(UserGroup userGroup) { string SPName = ""; string message = ""; ComplateOperation <int> complate = new ComplateOperation <int>(); for (int i = 0; i < userGroup.UserIDs.Length; i++) { OracleDynamicParameters oracleParams = new OracleDynamicParameters(); if (userGroup.ID.HasValue) { oracleParams.Add(UserGroupSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.ID ?? DBNull.Value); SPName = UserGroupSpName.SP_UPADTE_USER_GROUP; message = "Updated Successfully"; } else { oracleParams.Add(UserGroupSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output); SPName = UserGroupSpName.SP_INSER_USER_GROUP; message = "Inserted Successfully"; } oracleParams.Add(UserGroupSpParams.PARAMETER_USER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.UserIDs[i] ?? DBNull.Value); oracleParams.Add(UserGroupSpParams.PARAMETER_USERNAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)userGroup.UserNames[i] ?? DBNull.Value, 30); oracleParams.Add(UserGroupSpParams.PARAMETER_LOCK_USER_CAT, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.UserRelationID ?? DBNull.Value); oracleParams.Add(UserGroupSpParams.PARAMETER_REF_ID, OracleDbType.Int64, ParameterDirection.Input, (object)userGroup.RefrenceID ?? DBNull.Value); if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1) { complate.message = message; } else { complate.message = "Operation Failed"; } } return(complate); }
public async Task <ManagerViewModel> UpdateManagerAsync(int id, Manager manager) { try { var param = new OracleDynamicParameters(); param.Add(name: "MNG_ID", value: id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input); param.Add(name: "MNG_ROLE_ID", value: manager.Role_Id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input); param.Add(name: "MNG_FIRSTNAME", value: manager.FirstName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "MNG_LASTNAME", value: manager.LastName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "MNG_EMAIL", value: manager.Email, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "MNG_PASSWORD_HASH", value: manager.Password_Hash, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "MNG_CURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output); var query = "ESHOP_MANAGER_API.SP_MANAGER_UPDATEMANAGER"; var conn = GetOpenConnection(); var result = (await SqlMapper.QueryAsync <ManagerViewModel>(conn, query, param: param, commandType: CommandType.StoredProcedure)).FirstOrDefault(); return(result); } catch (Exception ex) { throw ex; } }
public string ExecuteAgentReplace(string newMobileNo, string exCluster, string newCluster, AgentPhoneCode item) { try { using (var connection = this.GetConnection()) { var parameter = new OracleDynamicParameters(); parameter.Add("V_NEW_DISTRIBUTOR_NO", OracleDbType.Varchar2, ParameterDirection.Input, newMobileNo); parameter.Add("V_EX_CLUSTER", OracleDbType.Varchar2, ParameterDirection.Input, exCluster); parameter.Add("V_NEW_CLUSTER", OracleDbType.Varchar2, ParameterDirection.Input, newCluster); parameter.Add("V_AGENT_PHONE", OracleDbType.Varchar2, ParameterDirection.Input, item.AgentPhone); parameter.Add("MSGID", OracleDbType.Varchar2, ParameterDirection.Input, "999999999"); parameter.Add("V_FLAG", OracleDbType.Double, ParameterDirection.Output); parameter.Add("OUTMSG", OracleDbType.Varchar2, ParameterDirection.Output, null, 32767); SqlMapper.Query(connection, dbUser + "SP_Execute_Agent_Replace", param: parameter, commandType: CommandType.StoredProcedure); this.CloseConnection(connection); string flag = parameter.oracleParameters[5].Value != null ? parameter.oracleParameters[5].Value.ToString() : null; string successOrErrorMsg = null; if (flag == "0") { successOrErrorMsg = parameter.oracleParameters[6].Value != null ? parameter.oracleParameters[6].Value.ToString() : null; } else { successOrErrorMsg = flag; } return(successOrErrorMsg); } } catch (Exception ex) { throw ex; } }
public bool Update() { try { var p = new OracleDynamicParameters(); p.Add("Id", this.Id); p.Add("Curso", this.Curso.Id); p.Add("Nombre", this.Nombre); p.Add("Descripcion", this.Descripcion); p.Add("Fecha_Viaje", this.Fecha_Viaje); p.Add("Valor", this.Valor); Db.Execute(Procs.Contrato_Actualizar, p, commandType: CommandType.StoredProcedure); logger.Info("Contrato N°{0} actualizado correctamente", Id); return(true); } catch (Exception ex) { logger.Error(ex.Message); return(false); } }
public bool Update() { try { var p = new OracleDynamicParameters(); p.Add("Id", this.Id); p.Add("ContratoID", this.Contrato.Id); p.Add("Tipo_Seguro", this.Tipo_Seguro.Id); p.Add("Valor", this.Valor); p.Add("SeguroID", this.Seguro); p.Add("Total_Dias", this.Total_Dias); Db.Execute(Procs.Seguro_Asociado_Actualizar, p, commandType: CommandType.StoredProcedure); logger.Info("Seguro asociado N°{0} actualizado correctamente", Id); return(true); } catch (Exception ex) { logger.Error(ex.Message); return(false); } }
public async Task <IList <IDepositModel> > GetSomeDeposits(string branch, string openDate, string codeSubject, string fileName, string currency, string accounttype) { IList <DepositModel> deposit = null; try { OracleDynamicParameters parameters = new OracleDynamicParameters(); parameters.Add("p_branch", OracleDbType.Varchar2, ParameterDirection.Input, "H"); parameters.Add("p_opendate", OracleDbType.Varchar2, ParameterDirection.Input, openDate); parameters.Add("p_codesubject", OracleDbType.Varchar2, ParameterDirection.Input, "H"); parameters.Add("p_currency", OracleDbType.Varchar2, ParameterDirection.Input, "H"); parameters.Add("p_deposittype", OracleDbType.Varchar2, ParameterDirection.Input, "H"); parameters.Add("RC1", OracleDbType.RefCursor, ParameterDirection.Output); deposit = await _dataAccess.LoadData <DepositModel, dynamic>("RPT_SEL_DEPOSITSV2_SP", parameters, "OracleConnectionString"); } catch (Exception e) { Utils.NLogMessage(this.GetType(), $" {e.Message} - {e.InnerException}", Utils.NLogType.Error); } return(deposit.ToList <IDepositModel>()); }
public async Task <OrderResponse> SaveOrder(Order order) { using (var connection = this._connectionFactory.GetConnection) { OrderResponse response = new OrderResponse(); if (order.IsDirty) { OracleDynamicParameters parameters = new OracleDynamicParameters(); parameters.Add("BuyerId", order.BuyerId, dbType: OracleDbType.Int64, direction: ParameterDirection.Input); parameters.Add("OrderStatusCode", order.OrderStatusCode, dbType: OracleDbType.Int16, direction: ParameterDirection.Input); parameters.Add("ShippingAddress", order.ShippingAddress, dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input); parameters.Add("OrderId", order.OrderId, dbType: OracleDbType.Int64, direction: ParameterDirection.InputOutput); parameters.Add("EffectiveDate", order.EffectiveDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input); parameters.Add("ExpiryDate", order.ExpiryDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input); await connection.QueryAsync <string>(CommonConstants.SaveOrder, param : parameters, commandType : CommandType.StoredProcedure); response.OrderId = Convert.ToInt32(parameters.Get("OrderId")); response.Message = CommonConstants.SuccessMessage; } else { response.OrderId = order.OrderId; response.Message = CommonConstants.SuccessMessage; } order.ItemsList.ForEach(e => { e.OrderId = order.OrderId; e.StatusCode = order.OrderStatusCode; e.EffectiveDate = order.EffectiveDate; }); SaveItems(order.ItemsList.Where(item => item.IsDirty).ToList(), connection); return(response); } }
public async static Task <IDTO> AddUpdateMode(GroupRelation groupRelation) { string SPName = ""; string message = ""; ComplateOperation <int> complate = new ComplateOperation <int>(); for (int i = 0; i < groupRelation.RefrenceIDs.Length; i++) { OracleDynamicParameters oracleParams = new OracleDynamicParameters(); if (groupRelation.ID.HasValue) { oracleParams.Add(GroupRelationSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.ID ?? DBNull.Value); SPName = GroupRelationSpName.SP_UPADTE_GROUP_RELATION; message = "Updated Successfully"; } else { oracleParams.Add(GroupRelationSpParams.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Output); SPName = GroupRelationSpName.SP_INSERT_GROUP_RELATION; message = "Inserted Successfully"; } oracleParams.Add(GroupRelationSpParams.PARAMETER_ST_GRP_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.GroupID ?? DBNull.Value); oracleParams.Add(GroupRelationSpParams.PARAMETER_GRPNAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)groupRelation.GroupName ?? DBNull.Value, 1000); oracleParams.Add(GroupRelationSpParams.PARAMETER_LOCK_GRP_CAT, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.LockUpGroupCat ?? DBNull.Value); oracleParams.Add(GroupRelationSpParams.PARAMETER_REF_ID, OracleDbType.Int64, ParameterDirection.Input, (object)groupRelation.RefrenceIDs[i] ?? DBNull.Value); if (await NonQueryExecuter.ExecuteNonQueryAsync(SPName, oracleParams) == -1) { complate.message = message; } else { complate.message = "Operation Failed"; } } return(complate); }
public List <PalabraClaveCantidadBE> BuscarPalabrasClavesCantidad(string busqueda, int registros, int pagina, string columna, string orden, OracleConnection db) { List <PalabraClaveCantidadBE> lista = new List <PalabraClaveCantidadBE>(); try { string sp = $"{Package.Mantenimiento}USP_SEL_LISTA_BUSQ_PALABRA_C"; var p = new OracleDynamicParameters(); p.Add("PI_BUSCAR", busqueda); p.Add("PI_REGISTROS", registros); p.Add("PI_PAGINA", pagina); p.Add("PI_COLUMNA", columna); p.Add("PI_ORDEN", orden); p.Add("PO_REF", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output); lista = db.Query <PalabraClaveCantidadBE>(sp, p, commandType: CommandType.StoredProcedure).ToList(); } catch (Exception ex) { Log.Error(ex); } return(lista); }
public async Task <int> UpdateCompanyDepartment([FromBody] Department department) { var dyParam = new OracleDynamicParameters(); dyParam.Add(Params.PARAMETER_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ID ?? DBNull.Value); dyParam.Add(Params.PARAMETER_NAME, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.NAME ?? DBNull.Value, 500); dyParam.Add(Params.PARAMETER_NAME2, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.NAME2 ?? DBNull.Value, 500); dyParam.Add(Params.PARAMETER_ADDRESS, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.ADDRESS ?? DBNull.Value, 30); dyParam.Add(Params.PARAMETER_EMAIL, OracleDbType.Varchar2, ParameterDirection.Input, (object)department.EMAIL ?? DBNull.Value, 30); dyParam.Add(Params.PARAMETER_COMPANY_ID, OracleDbType.Int64, ParameterDirection.Input, (object)department.ST_COM_ID ?? DBNull.Value, 30); int result = await _UpdateRepository.UpdateObjectAsync(SPName.SP_UPADTE_COMPANY_DEPARTMENT, dyParam); if (result == -1) { return(Convert.ToInt32(HttpStatusCode.OK)); } else { return(Convert.ToInt32(HttpStatusCode.NotModified)); } }
public bool Create() { try { var p = new OracleDynamicParameters(); p.Add("c1", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output); p.Add("Curso", this.Curso.Id); p.Add("Nombre", this.Nombre); p.Add("Descripcion", this.Descripcion); p.Add("Fecha_Viaje", this.Fecha_Viaje); p.Add("Total", this.Valor); var retorno = Db.QuerySingle <Contrato_Api>(Procs.Contrato_Crear, param: p, commandType: CommandType.StoredProcedure); this.Id = retorno.Id; logger.Info("Contrato N°{0} creado correctamente", Id); return(true); } catch (Exception ex) { logger.Error(ex.Message); return(false); } }
public bool GuardarDatosArchivo(ExcelBE obj, OracleConnection db) { bool seGuardo = false; try { string sp = $"{Package.Excel}USP_PRC_GUARDAR_EXCEL"; var p = new OracleDynamicParameters(); //p.Add("PI_ID_EXCEL", entidad.ID_EXCEL); p.Add("PI_NOMBRE", obj.NOMBRE); p.Add("PI_ANIO", obj.ANIO); p.Add("PI_MES", obj.MES); p.Add("PI_ID_TIPO_EXCEL", obj.ID_TIPO_EXCEL); p.Add("PI_UPD_USUARIO", obj.UPD_USUARIO); p.Add("PO_ROWAFFECTED", dbType: OracleDbType.Int32, direction: ParameterDirection.Output); db.Execute(sp, p, commandType: CommandType.StoredProcedure); int filasAfectadas = (int)p.Get <dynamic>("PO_ROWAFFECTED").Value; seGuardo = filasAfectadas > 0; } catch (Exception ex) { Log.Error(ex); } return(seGuardo); }
public List <ExcelBE> ListarExcels(int tipoexcel, int registros, int pagina, string columna, string orden, OracleConnection db) { List <ExcelBE> lista = new List <ExcelBE>(); try { string sp = $"{Package.Excel}USP_SEL_LISTA_EXCEL"; var p = new OracleDynamicParameters(); p.Add("PI_TIPO_EXCEL", tipoexcel); p.Add("PI_REGISTROS", registros); p.Add("PI_PAGINA", pagina); p.Add("PI_COLUMNA", columna); p.Add("PI_ORDEN", orden); p.Add("PO_REF", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output); lista = db.Query <ExcelBE>(sp, p, commandType: CommandType.StoredProcedure).ToList(); } catch (Exception ex) { Log.Error(ex); } return(lista); }
public async Task <CustomerViewModel> UpdateCustomerAsync(int id, Customer customer) { try { var param = new OracleDynamicParameters(); param.Add(name: "CTM_ID", value: id, dbType: OracleMappingType.Int32, direction: ParameterDirection.Input); param.Add(name: "CTM_FIRSTNAME", value: customer.FirstName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "CTM_LASTNAME", value: customer.LastName, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "CTM_EMAIL", value: customer.Email, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "CTM_PASSWORD_HASH", value: customer.Password_Hash, dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input); param.Add(name: "CTM_CURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output); var query = CUSTOMER_STORED_PACKAGE + ".SP_CUSTOMER_UPDATECUSTOMER"; var conn = GetOpenConnection(); var result = (await SqlMapper.QueryAsync <CustomerViewModel>(conn, query, param: param, commandType: CommandType.StoredProcedure)).FirstOrDefault(); return(result); } catch (Exception ex) { throw ex; } }