public List<ProductRoute> GetProductRouteByLicenceNumber(int licenceNumber, string lang)
{
var items = new List<ProductRoute>();
var route = new ProductRoute();
string commandText = "SELECT DISTINCT R.SUBMISSION_ID, R.ROUTE_ID, ";
if (lang.Equals("fr"))
{
commandText += "R.ROUTE_TYPE_DESC_F as R.ROUTE_TYPE_DESC ";
}
else {
commandText += "R.ROUTE_TYPE_DESC ";
}
commandText += "FROM NHPPLQ_OWNER.PRODUCT_LICENCE_ONLINE P, NHPPLQ_OWNER.PRODUCT_ROUTE_ONLINE R WHERE P.SUBMISSION_ID = R.SUBMISSION_ID AND P.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 ProductRoute();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.route_id = dr["ROUTE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ROUTE_ID"]);
item.route_type_desc = dr["ROUTE_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["ROUTE_TYPE_DESC"].ToString().Trim();
items.Add(item);
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetProductRouteByLicenceNumber()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return items;
}
public ProductRoute Get(int id, string lang)
{
_route = dbConnection.GetProductRouteById(id, lang);
return _route;
}
public ProductRoute GetProductRouteById(int id, string lang)
{
var route = new ProductRoute();
string commandText = "SELECT SUBMISSION_ID, ROUTE_ID, ";
if (lang.Equals("fr"))
{
commandText += "ROUTE_TYPE_DESC_F as ROUTE_TYPE_DESC ";
}
else {
commandText += "ROUTE_TYPE_DESC ";
}
commandText += "FROM NHPPLQ_OWNER.PRODUCT_ROUTE_ONLINE WHERE ROUTE_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 ProductRoute();
item.submission_id = dr["SUBMISSION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["SUBMISSION_ID"]);
item.route_id = dr["ROUTE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ROUTE_ID"]);
item.route_type_desc = dr["ROUTE_TYPE_DESC"] == DBNull.Value ? string.Empty : dr["ROUTE_TYPE_DESC"].ToString().Trim();
route = item;
}
}
}
}
catch (Exception ex)
{
string errorMessages = string.Format("DbConnection.cs - GetProductRouteById()");
ExceptionHelper.LogException(ex, errorMessages);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
return route;
}
