public List<Ingredient> GetNonMedIngredientByLicenceNumber(int licenceNumber, string lang) { var items = new List<Ingredient>(); string commandText = "SELECT DISTINCT I.SUBMISSION_ID, I.MATRIX_ID, I.MATRIX_TYPE_CODE, "; if (lang.Equals("fr")) { commandText += "I.INGREDIENT_NAME_OTHER as I.INGREDIENT_NAME "; } else { commandText += "I.INGREDIENT_NAME "; } commandText += "FROM NHPPLQ_OWNER.INGREDIENT_ONLINE I, NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE L WHERE L.SUBMISSION_ID = I.SUBMISSION_ID AND I.MATRIX_TYPE_CODE = 3 AND L.LICENCE_NUMBER = " + licenceNumber; commandText += " ORDER BY I.INGREDIENT_NAME ASC"; 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 Ingredient(); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.ingredient_name = dr["INGREDIENT_NAME"] == DBNull.Value ? string.Empty : dr["INGREDIENT_NAME"].ToString().Trim(); item.matrix_id = dr["MATRIX_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_ID"]); item.matrix_type_code = dr["MATRIX_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_TYPE_CODE"]); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetNonMedIngredientByLicenceNumber()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public Ingredient Get(int id, string lang) { _ingredient = dbConnection.GetIngredientById(id, lang); return _ingredient; }
public Ingredient GetIngredientById(int id, string lang) { var ingredient = new Ingredient(); string commandText = "SELECT SUBMISSION_ID, MATRIX_ID, MATRIX_TYPE_CODE, "; if (lang.Equals("fr")) { commandText += "INGREDIENT_NAME_OTHER as INGREDIENT_NAME "; } else { commandText += "INGREDIENT_NAME "; } commandText += "FROM NHPPLQ_OWNER.INGREDIENT_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 Ingredient(); item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]); item.ingredient_name = dr["INGREDIENT_NAME"] == DBNull.Value ? string.Empty : dr["INGREDIENT_NAME"].ToString().Trim(); item.matrix_id = dr["MATRIX_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_ID"]); item.matrix_type_code = dr["MATRIX_TYPE_CODE"] == DBNull.Value ? 0 : Convert.ToInt32(dr["MATRIX_TYPE_CODE"]); item.quantity_list = GetAllIngredientQuantityByMatrixId(item.matrix_id, lang); ingredient = item; } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetIngredientById()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return ingredient; }