public List<DeviceIdentifier> GetAllDeviceIdentifier(string deviceIdentifierName, int licenceId, int deviceId) { var items = new List<DeviceIdentifier>(); string commandText = "SELECT DISTINCT * FROM PUB_ACS.PAS_LICENCE_DEV_IDENT"; if ((!string.IsNullOrEmpty(deviceIdentifierName)) || (licenceId > 0) || (deviceId > 0)) commandText += " WHERE"; if (!string.IsNullOrEmpty(deviceIdentifierName)) { commandText += " DEVICE_IDENTIFIER LIKE '%" + deviceIdentifierName.ToUpper().Trim() + "%'"; } if (licenceId > 0) { if (!string.IsNullOrEmpty(deviceIdentifierName)) commandText += " AND"; commandText += " ORIGINAL_LICENCE_NO = " + licenceId; } if (deviceId > 0) { if ((!string.IsNullOrEmpty(deviceIdentifierName)) || (licenceId > 0)) commandText += " AND"; commandText += " DEVICE_ID = " + deviceId; } commandText += " ORDER BY UPPER(DEVICE_IDENTIFIER)"; 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 DeviceIdentifier(); item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]); item.device_id = dr["DEVICE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DEVICE_ID"]); item.identifier_first_issue_dt = dr["FIRST_LICENCE_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_DT"]); item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]); item.device_identifier = dr["DEVICE_IDENTIFIER"] == DBNull.Value ? string.Empty : dr["DEVICE_IDENTIFIER"].ToString().Trim(); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllLicenceDeviceIdentifier()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public DeviceIdentifier GetDeviceIdentifierById(int id) { var deviceIdentifier = new DeviceIdentifier(); string commandText = "SELECT * FROM PUB_ACS.PAS_LICENCE_DEV_IDENT WHERE DEVICE_ID = " + id; 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 DeviceIdentifier(); item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]); item.device_id = dr["DEVICE_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DEVICE_ID"]); item.identifier_first_issue_dt = dr["FIRST_LICENCE_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_DT"]); item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]); item.device_identifier = dr["DEVICE_IDENTIFIER"] == DBNull.Value ? string.Empty : dr["DEVICE_IDENTIFIER"].ToString().Trim(); deviceIdentifier = item; } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetLicenceDeviceIdentifierById()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return deviceIdentifier; }
public DeviceIdentifier Get(int id) { _licenceDeviceIdentifier = dbConnection.GetDeviceIdentifierById(id); return _licenceDeviceIdentifier; }