public List<Licence> GetAllLicence(string status, string licenceName) { var items = new List<Licence>(); string commandText = "SELECT DISTINCT L.* FROM PUB_ACS.PAS_LICENCE L"; if((!string.IsNullOrEmpty(status)) || (!string.IsNullOrEmpty(licenceName))) { commandText += " WHERE"; } if (status.Equals("active")) { commandText += " L.END_DATE IS NULL"; if (!string.IsNullOrEmpty(licenceName)) commandText += " AND"; } if (!string.IsNullOrEmpty(licenceName)) { commandText += " UPPER(L.LICENCE_NAME) LIKE '%" + licenceName.ToUpper().Trim() + "%'"; } using (OracleConnection con = new OracleConnection(MdallDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new Licence(); item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]); item.licence_status = dr["LICENCE_STATUS"] == DBNull.Value ? string.Empty : dr["LICENCE_STATUS"].ToString().Trim(); item.application_id = dr["APPLICATION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPLICATION_ID"]); item.appl_risk_class = dr["APPL_RISK_CLASS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPL_RISK_CLASS"]); item.licence_name = dr["LICENCE_NAME"] == DBNull.Value ? string.Empty : dr["LICENCE_NAME"].ToString().Trim(); item.first_licence_status_dt = dr["FIRST_LICENCE_STATUS_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_STATUS_DT"]); item.last_refresh_dt = dr["LAST_REFRESH_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LAST_REFRESH_DT"]); item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]); item.licence_type_cd = dr["LICENCE_TYPE_CD"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_CD"].ToString().Trim(); item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllLicence()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public Licence Get(int id, string state = "", string lang = "") { _licence = dbConnection.GetLicenceById(id, state, lang); return(_licence); }
public Licence Get(int id, string status = "") { _licence = dbConnection.GetLicenceById(id, status); return _licence; }