/// <summary>
/// Returns the 'version' of this database's schema.
/// </summary>
/// <remarks>
/// During startup, the application needs to obtain this version and compare it
/// to what version it thinks it's compatible with.
/// </remarks>
/// <returns></returns>
public int GetSchemaVersion()
{
try
{
// THIS TRANSACTION MUST REMAIN WRITABLE. SINCE IT IS CALLED FIRST IT WILL ENSURE ALL
// PENDING DATABASE TRANSACTIONS HAVE BEEN ROLLED BACK.
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, false))
{
using (IDbCommand cmd = GetCommand("SELECT VERSION FROM SCHEMA", trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
int result = SqlSafeGetInt(reader, ordinals["VERSION"]);
trx.Commit();
return(result);
}
trx.Commit();
return(0); // return a default value of 0 for version
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException("Could not get Version from Queue Schema. See inner exception for details.", ex);
}
}
} // end-FindBySerialNumber
public void Save(DockingStation ds)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
Save(ds, trx);
trx.Commit();
}
}
/// <summary>
/// Save the passed in Firmware Upgrade Setting.
/// </summary>
/// <param name="firmwareUpgradeSetting"></param>
public void Save(FirmwareUpgradeSetting firmwareUpgradeSetting)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
Save(firmwareUpgradeSetting, trx);
trx.Commit();
}
}
/// <summary>
/// Deletes all EventJournal records for the specified instrument and its components.
/// </summary>
/// <param name="instrumentSerialNumber"></param>
/// <param name="eventCode"></param>
/// <returns>An integer indicating how many journal entries were deleted</returns>
public int DeleteByInstrumentSerialNumber(string instrumentSerialNumber, string eventCode)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
int delCount = DeleteByInstrumentSerialNumber(instrumentSerialNumber, eventCode, trx);
trx.Commit();
return(delCount);
}
}
private bool Update(EventJournal journal)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
bool success = Update(journal, trx);
trx.Commit();
return(success);
}
}
/// <summary>
/// Delete the replaced docking station network settings
/// </summary>
/// <returns></returns>
public bool Delete()
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
Delete(trx);
trx.Commit();
return(true);
}
}
/// <summary>
///
/// </summary>
/// <param name="readOnly">We allow performing the 'find' using a writable transaction.
/// We do allow this because when we're starting up the device, this is the very first
/// query on the database. If there's a sqlite "hot journal" file in existence, when
/// the first query is issued, then sqlite will want to roll back the unclosed transaction.
/// But it's unable to do so if the transaction is read-only.</param>
/// <returns></returns>
public Schema Find(bool readOnly)
{
Schema schema = null;
using (DataAccessTransaction trx = new DataAccessTransaction(readOnly))
{
using (IDbCommand cmd = GetCommand("SELECT * FROM " + TableName, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
schema = new Schema();
schema.Version = SqlSafeGetInt(reader, ordinals["VERSION"]);
schema.AccountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]);
schema.Activated = (SqlSafeGetShort(reader, ordinals["ACTIVE"]) == 1) ? true : false;
if (schema.Version >= INET_VERSION_INS3017) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added
{
schema.IsManufacturing = (SqlSafeGetShort(reader, ordinals["ISMANUFACTURING"]) == 1) ? true : false;
}
// It's assumed the dates are all stored in UTC. So pass in DateTimeKind.Utc
// to ensure they're not converted to local time when retrieved
schema.CylindersVersion = SqlSafeGetNullableDateTime(reader, ordinals["FACTORYCYLINDERSUTCVERSION"], DateTimeKind.Utc);
schema.SchedulesVersion = SqlSafeGetNullableDateTime(reader, ordinals["SCHEDULESUTCVERSION"], DateTimeKind.Utc);
schema.EventJournalsVersion = SqlSafeGetNullableDateTime(reader, ordinals["EVENTJOURNALSUTCVERSION"], DateTimeKind.Utc);
schema.SettingsVersion = SqlSafeGetNullableDateTime(reader, ordinals["SETTINGSUTCVERSION"], DateTimeKind.Utc);
schema.EquipmentVersion = SqlSafeGetNullableDateTime(reader, ordinals["EQUIPMENTUTCVERSION"], DateTimeKind.Utc);
//Suresh 06-FEB-2012 INS-2622
//Column 'CRITICALTERRORSUTCVERSION' is newly added to database so it won't be available in older version of database.
if (schema.Version >= INET_VERSION_INS2622) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added
{
schema.CriticalErrorsVersion = SqlSafeGetNullableDateTime(reader, ordinals["CRITICALINSTRUMENTERRORSUTCVERSION"], DateTimeKind.Utc);
}
//INS-7715/7282 - To determine the account's Service type, to do checks based on Service accounts
if (schema.Version >= INET_VERSION_INS7715) //The version in which SERVICECODE is added
{
schema.ServiceCode = SqlSafeGetString(reader, ordinals["SERVICECODE"]);
}
if (!trx.ReadOnly)
{
trx.Commit();
}
}
}
}
}
return(schema);
}
/// <summary>
/// Deletes the entire contents of the queue
/// </summary>
public void DeleteQueue()
{
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId))
{
using (IDbCommand cmd = GetCommand("DELETE FROM " + TableName, trx))
{
cmd.ExecuteNonQuery();
trx.Commit();
}
}
}
public bool Delete(PersistedQueueData persistedQueueData)
{
bool success = false;
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId))
{
success = Delete(persistedQueueData, trx);
trx.Commit();
}
return(success);
}
public bool Delete(long id)
{
if (id == DataAccess.NULL_ID_FLAG)
{
return(false);
}
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId))
{
bool deleted = Delete(id, trx);
trx.Commit();
return(deleted);
}
}
public bool UpdateServiceCode(string serviceCode)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
using (IDbCommand cmd = GetCommand("UPDATE " + TableName + " SET SERVICECODE = @SERVICECODE", trx))
{
cmd.Parameters.Add(GetDataParameter("@SERVICECODE", serviceCode == string.Empty ? Convert.DBNull : serviceCode));
int rows = cmd.ExecuteNonQuery();
trx.Commit();
return(rows > 0);
}
}
}
public bool Save(PersistedQueueData persistedQueueData)
{
if (persistedQueueData == null)
{
throw new DataAccessException("queueData is null");
}
// If this is a new item to be queued, it shouldn't already have an ID.
if (persistedQueueData.Id != DataAccess.NULL_ID_FLAG)
{
throw new DataAccessException("queueData.Id = " + persistedQueueData.Id);
}
string sql = string.Format("INSERT INTO {0} ( TIMESTAMPUTC, LABEL, ACCOUNTNUM, TYPE, BODY ) VALUES ( @TIMESTAMPUTC, @LABEL, @ACCOUNTNUM, @TYPE, @BODY ); SELECT last_insert_rowid() AS ID", TableName);
IDbCommand cmd = null;
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId))
{
try
{
cmd = GetCommand(sql, trx);
cmd.Parameters.Add(GetDataParameter("@TIMESTAMPUTC", DateTime.UtcNow));
cmd.Parameters.Add(GetDataParameter("@LABEL", persistedQueueData.Label));
cmd.Parameters.Add(GetDataParameter("@TYPE", persistedQueueData.Type));
cmd.Parameters.Add(GetDataParameter("@ACCOUNTNUM", persistedQueueData.InetAccountNum));
cmd.Parameters.Add(GetDataParameter("@BODY", persistedQueueData.SerializedWebServiceParameter));
persistedQueueData.Id = (long)cmd.ExecuteScalar(); // "ID" is treated as an autoincrement field.
SaveProperties(persistedQueueData, trx);
trx.Commit();
return(true);
}
catch (Exception ex)
{
if ((ex is SQLiteException) && (((SQLiteException)ex).ErrorCode == SQLiteErrorCode.Constraint))
{
return(false); // assume we have a 'duplicate' error.
}
throw new DataAccessException(string.Format("Queue Data: \"{0}\", SQL: {1}", persistedQueueData, sql), ex);
}
}
}
public bool UpdateIsManufacturing(bool isManufacturing)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
// There should never be more than one record in this database, but if there are, just select the highest.
using (IDbCommand cmd = GetCommand("UPDATE " + TableName + " SET ISMANUFACTURING = @ISMANUFACTURING", trx))
{
cmd.Parameters.Add(GetDataParameter("@ISMANUFACTURING", isManufacturing ? 1 : 0));
int rows = cmd.ExecuteNonQuery();
trx.Commit();
return(rows > 0);
}
}
}
internal bool UpdateAccount(string accountNum, bool active, bool isManufacturing, string serviceCode)
{
using (DataAccessTransaction trx = new DataAccessTransaction())
{
// There should never be more than one record in this database, but if there are, just select the highest.
using (IDbCommand cmd = GetCommand("UPDATE " + TableName + " SET ACCOUNTNUM = @ACCOUNTNUM, ACTIVE = @ACTIVE, ISMANUFACTURING = @ISMANUFACTURING, SERVICECODE = @SERVICECODE", trx))
{
cmd.Parameters.Add(GetDataParameter("@ACCOUNTNUM", accountNum == string.Empty ? Convert.DBNull : accountNum));
cmd.Parameters.Add(GetDataParameter("@ACTIVE", active ? 1 : 0));
cmd.Parameters.Add(GetDataParameter("@ISMANUFACTURING", isManufacturing ? 1 : 0));
cmd.Parameters.Add(GetDataParameter("@SERVICECODE", serviceCode == string.Empty ? Convert.DBNull : serviceCode));
int rows = cmd.ExecuteNonQuery();
trx.Commit();
return(rows > 0);
}
}
}