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