private ProductPurpose executeOne(string query, string lang) { ProductPurpose item = null; using (OracleConnection con = new OracleConnection(LnhpdDBConnection)) { try { con.Open(); OracleCommand cmd = new OracleCommand(query, con); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { dr.Read(); item = ProductPurposeFactory(dr, lang); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { if (con.State == ConnectionState.Open) { con.Close(); } } } return(item); }
private ProductPurpose ProductPurposeFactory(OracleDataReader dr, string lang) { var item = new ProductPurpose(); item.text_id = dr["TEXT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["TEXT_ID"]); item.lnhpd_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : idLnhpd(Convert.ToInt32(dr["SUBMISSION_ID"])); if (lang.Equals("fr")) { item.purpose = dr["PURPOSE_F"] == DBNull.Value ? dr["PURPOSE_E"].ToString().Trim() : dr["PURPOSE_F"].ToString().Trim(); } else { item.purpose = dr["PURPOSE_E"] == DBNull.Value ? dr["PURPOSE_F"].ToString().Trim() : dr["PURPOSE_E"].ToString().Trim(); } return(item); }
public ProductPurpose GetProductPurposeById(int id, string lang) { var purpose = new ProductPurpose(); string commandText = "SELECT TEXT_ID, SUBMISSION_ID, "; if (lang.Equals("fr")) { commandText += "PURPOSE_F as PURPOSE_E "; } else { commandText += "PURPOSE_E "; } commandText += "FROM NHPPLQ_OWNER.PRODUCT_PURPOSE_ONLINE WHERE SUBMISSION_ID = " + id; using ( OracleConnection con = new OracleConnection(LnhpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new ProductPurpose(); item.text_id = dr["TEXT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["TEXT_ID"]); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.purpose = dr["PURPOSE_E"] == DBNull.Value ? string.Empty : dr["PURPOSE_E"].ToString().Trim(); purpose = item; } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetProductPurposeById()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return purpose; }
public List<ProductPurpose> GetProductPurposeByLicenceNumber(int licenceNumber, string lang) { var items = new List<ProductPurpose>(); string commandText = "SELECT DISTINCT P.TEXT_ID, P.SUBMISSION_ID, "; if (lang.Equals("fr")) { commandText += "P.PURPOSE_F as P.PURPOSE_E "; } else { commandText += "P.PURPOSE_E "; } commandText += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE L, NHPPLQ_OWNER.PRODUCT_PURPOSE_ONLINE P WHERE L.SUBMISSION_ID = P.SUBMISSION_ID AND L.LICENCE_NUMBER = " + licenceNumber; using ( OracleConnection con = new OracleConnection(LnhpdDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new ProductPurpose(); item.text_id = dr["TEXT_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["TEXT_ID"]); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.purpose = dr["PURPOSE_E"] == DBNull.Value ? string.Empty : dr["PURPOSE_E"].ToString().Trim(); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetProductPurposeByLicenceNumber()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public ProductPurpose Get(int id, string lang) { _purpose = dbConnection.GetProductPurposeById(id, lang); return _purpose; }