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;
}
