/// --------------------------------------------------------------------------------
/// <summary>
/// Initializes the database and collection class that the object is contained within.
/// </summary>
/// <param name="objDatabase">
/// The database that the collection is associated with.
/// </param>
/// <param name="objCollection">
/// The collection that contains the referenced object. The class' MyBase.Object
/// function is called to load the object.
/// </param>
/// --------------------------------------------------------------------------------
public ObjectReference(Database objDatabase, IDatabaseObjects objCollection)
{
if (objDatabase == null)
throw new ArgumentNullException("Database has not been set");
else if (objCollection == null)
throw new ArgumentNullException("Collection has not been set");
pobjDatabase = objDatabase;
pobjCollection = objCollection;
}
public ConsumerService(ILogger <ConsumerService> logger, ConsumerConfiguration consumerConfigurations, LogFormat logFormat, RabbitServer rabbitServer, IConfiguration iConfig, IDatabaseObjects databaseObjects)
{
_consumerConfigurations = consumerConfigurations;
_logger = logger;
_iConfig = iConfig;
_logFormat = logFormat;
_rabbitServer = rabbitServer;
_instances = new Dictionary <IParser, string>();
_databaseObjects = databaseObjects;
var usedDevices = _databaseObjects._usedDevice.Get();
foreach (var item in usedDevices)
{
var targetType = Type.GetType("MLConsumer.DeviceAndParserServices." + item.DeviceClassName);
_instances.Add(Activator.CreateInstance((targetType), _databaseObjects._errorLogService, _iConfig) as IParser, item.DeviceBrand);
}
}
/// <summary>
/// Returns whether the object is locked by the current user. Specifically, the user that was specified
/// in the constructor.
/// </summary>
public bool IsLockedByCurrentUser(IDatabaseObjects objCollection, IDatabaseObject objObject)
{
return this.LockRecordExists(objCollection.TableName(), objObject, new SQL.SQLCondition("UserID", SQL.ComparisonOperator.EqualTo, pstrCurrentUserID));
}
/// --------------------------------------------------------------------------------
/// <summary>
/// UnLocks this object. Throws an exception if the object is not locked by the current
/// user or the object has not been saved.
/// </summary>
/// --------------------------------------------------------------------------------
public void UnLock(IDatabaseObjects objCollection, IDatabaseObject objObject)
{
//If the table is locked by someone else
if (!this.IsLockedByCurrentUser(objCollection, objObject))
throw new MethodAccessException("Object already locked");
else if (!objObject.IsSaved)
throw new MethodAccessException("Object is not saved and cannot be unlocked");
SQL.SQLDelete objDelete = new SQL.SQLDelete();
objDelete.TableName = pstrLockTableName;
objDelete.Where.Add("TableName", SQL.ComparisonOperator.EqualTo, objCollection.TableName());
objDelete.Where.Add("RecordID", SQL.ComparisonOperator.EqualTo, objObject.DistinctValue.ToString());
objDelete.Where.Add("UserID", SQL.ComparisonOperator.EqualTo, pstrCurrentUserID);
using (ConnectionScope connection = new ConnectionScope(pobjDatabase))
connection.ExecuteNonQuery(objDelete);
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Extracts the fields to save to the database from the objItem.SaveFields function.
/// The fields are then written to the database using either an SQL INSERT or UPDATE
/// depending on whether the object has already been saved. If the collection has
/// implemented IDatabaseObjects.KeyFieldName then objItem's key is also validated to
/// ensure it is not null and unique within the collection. If objCollection has
/// implemented IDatabaseObjects.Subset then objItem should exist within objCollection.
/// If not, a duplicate key error may occur if the obItem's key is being used in
/// another subset in the same table. If a record is being amended
/// (IDatabaseObject.IsSaved returns true) then the function will "AND" the collection's
/// IDatabaseObjects.Subset conditions and the objItem's IDatabaseObject.DistinctValue
/// value to create the WHERE clause in the UPDATE statement. Therefore, the
/// combination of the IDatabaseObjects.Subset and IDatabaseObject.DistinctValue
/// conditions MUST identify only one record in the table. Otherwise multiple records
/// will be updated with the same data. If data is only inserted and not amended
/// (usually a rare occurance) then this requirement is unnecessary.
/// </summary>
///
/// <param name="objCollection">
/// The collection which contains or will contain the object to save.
/// </param>
///
/// <param name="objItem">
/// The object to save to the database. The values saved to the database are extracted from the
/// SQLFieldValues object returned from IDatabaseObject.SaveFields.
/// </param>
///
/// <example> Saves a product object (Me) to the database.
/// <code>
/// Public Sub Save()
///
/// objDatabase.ObjectSave(NorthwindDB.Products, Me)
///
/// End Sub
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public void ObjectSave(IDatabaseObjects objCollection, IDatabaseObject objItem)
{
SQL.SQLFieldValues objFieldValues;
var objNewGUID = Guid.Empty;
var autoAssignment = MergeDistinctFieldAutoAssignmentAndDistinctFieldAutoIncrements(objCollection);
objFieldValues = objItem.SaveFields();
if (objFieldValues == null)
throw new Exceptions.DatabaseObjectsException(objItem.GetType().Name + " IDatabaseObject.SaveFields not implemented");
//Add the distinct field value if it hasn't been added via the SaveFields sub
if (!objFieldValues.Exists(objCollection.DistinctFieldName()))
{
if (autoAssignment == SQL.FieldValueAutoAssignmentType.None)
objFieldValues.Add(objCollection.DistinctFieldName(), objItem.DistinctValue);
else if (autoAssignment == SQL.FieldValueAutoAssignmentType.NewUniqueIdentifier)
{
//For a new object, with a GUID that should be automatically assigned
//Create a new GUID for the distinct field so that it saved for the INSERT
if (!objItem.IsSaved)
{
objNewGUID = System.Guid.NewGuid();
objFieldValues.Add(objCollection.DistinctFieldName(), objNewGUID);
}
}
}
#if !DEBUG
ItemKeyEnsureValid(objCollection, objItem, objFieldValues);
#endif
using (ConnectionScope objConnection = new ConnectionScope(this))
{
if (objItem.IsSaved)
{
var objUpdate = new SQL.SQLUpdate();
objUpdate.TableName = objCollection.TableName();
objUpdate.Fields.Add(objFieldValues);
objUpdate.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objItem.DistinctValue);
var objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
{
objUpdate.Where.Add(objSubset);
}
if (objConnection.ExecuteNonQuery(objUpdate) != 1)
throw new Exceptions.RecordDoesNotExistException(objCollection, objItem);
}
else
{
var objInsert = new SQL.SQLInsert();
objInsert.TableName = objCollection.TableName();
objInsert.Fields = objFieldValues;
objConnection.ExecuteNonQuery(objInsert);
if (autoAssignment == SQL.FieldValueAutoAssignmentType.NewUniqueIdentifier)
objItem.DistinctValue = objNewGUID;
else if (autoAssignment == SQL.FieldValueAutoAssignmentType.AutoIncrement)
objItem.DistinctValue = objConnection.ExecuteScalar(new SQL.SQLAutoIncrementValue());
object objRollbackDistinctValue = objItem.DistinctValue;
objItem.IsSaved = true;
if (Transaction.Current != null)
{
Transaction.Current.EnlistVolatile(new TransactionExecuteActionOnRollback(() => objItem.IsSaved = false), EnlistmentOptions.None);
Transaction.Current.EnlistVolatile(new TransactionExecuteActionOnRollback(() => objItem.DistinctValue = objRollbackDistinctValue), EnlistmentOptions.None);
}
}
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Locks the database record associated with this object by selecting and locking
/// the row in the database. Supported in Microsoft SQLServer, Pervasive and MySQL.
/// The record lock is released when the transaction is committed or rolled back.
/// Throws an exception if not in transaction mode.
/// Returns the field values from the record that has been locked.
/// </summary>
/// --------------------------------------------------------------------------------
public SQL.SQLFieldValues ObjectLockRecord(IDatabaseObjects objCollection, IDatabaseObject objItem)
{
SQL.SQLSelect objSelect = new SQL.SQLSelect();
SQL.SQLConditions objSubset;
objSelect.PerformLocking = true;
objSelect.Tables.Add(objCollection.TableName());
objSelect.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objItem.DistinctValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objSelect.Where.Add(objSubset);
using (var objReader = this.Transactions.Execute(objSelect))
{
if (objReader.Read())
return FieldValuesFromDataReader(objCollection, objReader);
else
throw new Exceptions.ObjectDoesNotExistException(objCollection, objItem.DistinctValue);
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Gets and returns the field value from the database record associated with the
/// object and collection.
/// </summary>
/// <param name="objCollection">
/// The collection that the object exists within.
/// The function utilises the collection's subset and tablename to determine which
/// table and record to read.
/// Returns DBNull.Value if the field is NULL.
/// </param>
/// <param name="objItem">
/// The object which represents the database record to be read. Specifically,
/// the object's distinct field name is used to determine which record to read.
/// </param>
/// <param name="strFieldName">
/// The name of the database field that is to be read.
/// </param>
/// <exception cref="Exceptions.ObjectDoesNotExistException">If the object has not already been saved.</exception>
/// --------------------------------------------------------------------------------
public object ObjectGetFieldValue(IDatabaseObjects objCollection, IDatabaseObject objItem, string strFieldName)
{
if (!objItem.IsSaved)
throw new Exceptions.ObjectDoesNotExistException(objItem);
SQL.SQLSelect objSelect = new SQL.SQLSelect();
SQL.SQLConditions objSubset;
objSelect.Fields.Add(strFieldName);
objSelect.Tables.Add(objCollection.TableName());
objSelect.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objItem.DistinctValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objSelect.Where.Add(objSubset);
using (ConnectionScope objConnection = new ConnectionScope(this))
{
using (IDataReader objReader = objConnection.Execute(objSelect))
{
if (objReader.Read())
return objReader[0];
else
throw new Exceptions.ObjectDoesNotExistException(objCollection, objItem.DistinctValue);
}
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns whether an object exists for the specified distinct value in the collection.
/// </summary>
///
/// <param name="objCollection">
/// The collection that is searched for the distinct value.
/// </param>
///
/// <param name="objDistinctValue">
/// The value to search for in the collection. This is the value of the field defined
/// by the collection's IDatabaseObjects.DistinctFieldName function.
/// </param>
/// --------------------------------------------------------------------------------
///
public bool ObjectExistsByDistinctValue(IDatabaseObjects objCollection, object objDistinctValue)
{
SQL.SQLSelect objSelect = new SQL.SQLSelect();
SQL.SQLConditions objSubset;
objSelect.Tables.Add(objCollection.TableName());
objSelect.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objDistinctValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objSelect.Where.Add(objSubset);
using (ConnectionScope objConnection = new ConnectionScope(this))
using (IDataReader objReader = objConnection.Execute(objSelect))
return objReader.Read();
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Creates and initializes an object from the current record of a DataRow object.
/// </summary>
///
/// <param name="objCollection">
/// The collection associated with the IDataReader object.
/// </param>
///
/// <param name="objRow">
/// The data to be copied into a new IDatabaseObject object.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
/// --------------------------------------------------------------------------------
///
public static IDatabaseObject ObjectFromDataRow(IDatabaseObjects objCollection, System.Data.DataRow objRow)
{
System.Data.DataTable objTable = objRow.Table.Clone();
objTable.Rows.Add(objRow);
return ObjectFromFieldValues(objCollection, FieldValuesFromDataReader(objCollection, objTable.CreateDataReader()));
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Creates and initializes an object from the current record of an IDataReader object.
/// </summary>
///
/// <param name="objCollection">
/// The collection associated with the IDataReader object.
/// </param>
///
/// <param name="objReader">
/// The data to be copied into a new IDatabaseObject object.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
/// --------------------------------------------------------------------------------
///
public static IDatabaseObject ObjectFromDataReader(IDatabaseObjects objCollection, IDataReader objReader)
{
return ObjectFromFieldValues(objCollection, FieldValuesFromDataReader(objCollection, objReader));
}
private IDictionary ObjectsDictionaryBase(IDatabaseObjects objCollection, bool bKeyIsDistinctField = false)
{
//Returns an IDictionary with the key being either the DistinctField or KeyField
IDictionary objDictionary = new Hashtable();
SQL.SQLSelect objSelect = new SQL.SQLSelect();
string strKeyField;
SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
objSelect.Tables.Joins = objCollection.TableJoins(objPrimaryTable, objSelect.Tables);
objSelect.Where = objCollection.Subset();
objSelect.OrderBy = objCollection.OrderBy();
using (ConnectionScope objConnection = new ConnectionScope(this))
{
using (IDataReader objReader = objConnection.Execute(objSelect))
{
if (bKeyIsDistinctField)
strKeyField = objCollection.DistinctFieldName();
else
strKeyField = objCollection.KeyFieldName();
while (objReader.Read())
objDictionary.Add(objReader[strKeyField], ObjectFromDataReader(objCollection, objReader));
return objDictionary;
}
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns the database fields for an object from the collection using a distinct value
/// (see IDatabaseObjects.DistinctFieldName).
/// Returns Nothing/null if the distinct value does not exist.
/// </summary>
/// --------------------------------------------------------------------------------
///
private SQL.SQLFieldValues ObjectFieldValuesIfExists(IDatabaseObjects objCollection, object objDistinctValue)
{
SQL.SQLSelect objSelect = new SQL.SQLSelect();
SQL.SQLConditions objSubset;
SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
objSelect.Tables.Joins = objCollection.TableJoins(objPrimaryTable, objSelect.Tables);
objSelect.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objDistinctValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objSelect.Where.Add(objSubset);
using (ConnectionScope objConnection = new ConnectionScope(this))
{
using (IDataReader objReader = objConnection.Execute(objSelect))
{
if (objReader.Read())
return FieldValuesFromDataReader(objCollection, objReader);
else
return null;
}
}
}
/// <summary>
/// Merges the obsolete function DistinctFieldAutoIncrements with the new DistinctFieldAutoAssignment function.
/// </summary>
private SQL.FieldValueAutoAssignmentType MergeDistinctFieldAutoAssignmentAndDistinctFieldAutoIncrements(IDatabaseObjects collection)
{
#pragma warning disable 0618
if (collection.DistinctFieldAutoIncrements())
#pragma warning restore 0618
return SQL.FieldValueAutoAssignmentType.AutoIncrement;
else
return collection.DistinctFieldAutoAssignment();
}
private object ItemKeyFieldValue(IDatabaseObjects objCollection, IDatabaseObject objItem, SQL.SQLFieldValues objFieldValues)
{
//On the rare occurance that the KeyField is the same as the DistinctField
//then the key value may not have been set in the Save and therefore be
//available in the objFieldValues collection. In which case the
//key has to be extracted from the objItem.DistinctField.
object objKeyFieldValue;
if (string.Compare(objCollection.DistinctFieldName(), objCollection.KeyFieldName(), true) == 0)
objKeyFieldValue = objItem.DistinctValue;
else
objKeyFieldValue = objFieldValues[objCollection.KeyFieldName()].Value;
return objKeyFieldValue;
}
private void ItemKeyEnsureValid(IDatabaseObjects objCollection, IDatabaseObject objItem, SQL.SQLFieldValues objFieldValues)
{
SQL.SQLSelect objSelect;
object objKeyFieldValue;
SQL.SQLConditions objSubset;
//If the key field is set and the key field is specified in the object
if (objCollection.KeyFieldName() != string.Empty && objFieldValues.Exists(objCollection.KeyFieldName()))
{
objKeyFieldValue = ItemKeyFieldValue(objCollection, objItem, objFieldValues);
if (objKeyFieldValue is string)
{
if (String.IsNullOrEmpty((string)objKeyFieldValue))
throw new Exceptions.DatabaseObjectsException(objItem.GetType().Name + " " + objCollection.KeyFieldName() + " field is Null");
}
objSelect = new SQL.SQLSelect();
objSelect.Tables.Add(objCollection.TableName());
objSelect.Fields.Add(objCollection.KeyFieldName());
objSelect.Where.Add(objCollection.KeyFieldName(), SQL.ComparisonOperator.EqualTo, objKeyFieldValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objSelect.Where.Add(objSubset);
if (objItem.IsSaved)
objSelect.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.NotEqualTo, objItem.DistinctValue);
using (ConnectionScope objConnection = new ConnectionScope(this))
using (IDataReader objReader = objConnection.Execute(objSelect))
if (objReader.Read())
throw new Exceptions.ObjectAlreadyExistsException(objItem, objKeyFieldValue);
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns whether the key exists within the collection. If the collection's
/// IDatabaseObjects.Subset has been set then only the subset is searched not the
/// entire table.
/// </summary>
///
/// <param name="objCollection">
/// The collection to search within.
/// </param>
///
/// <param name="objKey">
/// The key value to search by.
/// </param>
///
/// <returns><see cref="Boolean" /> (System.Boolean)</returns>
///
/// <example>
/// <code>
/// Public Function Exists(ByVal strProductCode As String) As Boolean
///
/// Return objDatabase.ObjectExists(Me, strProductCode)
///
/// End Function
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public bool ObjectExists(IDatabaseObjects objCollection, object objKey)
{
var objSelect = new SQL.SQLSelect();
string keyFieldName = objCollection.KeyFieldName();
EnsureKeyFieldNameIsSet(keyFieldName, objCollection);
objSelect.Tables.Add(objCollection.TableName());
//.Fields.Add objCollection.DistinctFieldName
objSelect.Where.Add(keyFieldName, SQL.ComparisonOperator.EqualTo, objKey);
var objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
{
objSelect.Where.Add(objSubset);
}
using (ConnectionScope objConnection = new ConnectionScope(this))
using (IDataReader objReader = objConnection.Execute(objSelect))
return objReader.Read();
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Creates and initializes an object from the values contained in an SQLFieldValues object.
/// This function is generally used from within an IDatabaseObject.Load function when
/// the IDatabaseObjects.TableJoins function has been implemented.
/// </summary>
///
/// <param name="objCollection">
/// The collection associated with the field values.
/// </param>
///
/// <param name="objFieldValues">
/// The data container from which to load a new object.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
/// --------------------------------------------------------------------------------
public static IDatabaseObject ObjectFromFieldValues(IDatabaseObjects objCollection, SQL.SQLFieldValues objFieldValues)
{
IDatabaseObject objItem;
if (objCollection is IDatabaseObjectsMultipleSubclass)
objItem = ((IDatabaseObjectsMultipleSubclass) objCollection).ItemInstanceForSubclass(objFieldValues);
else
objItem = objCollection.ItemInstance();
ObjectLoad(objCollection, objItem, objFieldValues);
return objItem;
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns the database fields for an object from the collection using a distinct value
/// (see IDatabaseObjects.DistinctFieldName). If the collection has implemented the
/// IDatabaseObjects.Subset function then the objDistinctValue need only be unique
/// within the collection's subset, not the entire database table.
/// This is typically used to interogate the database fields before loading the
/// object with a call to ObjectFromFieldValues.
/// This function is rarely used and generally the Object function suffices.
/// </summary>
///
/// <param name="objCollection">
/// The collection that contains the object.
/// </param>
///
/// <param name="objDistinctValue">
/// The value that uniquely identifies the object within the collection. This is the value
/// of the field defined by the collection's IDatabaseObjects.DistinctFieldName function.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
/// --------------------------------------------------------------------------------
///
public SQL.SQLFieldValues ObjectFieldValues(IDatabaseObjects objCollection, object objDistinctValue)
{
SQL.SQLFieldValues objFieldValues = ObjectFieldValuesIfExists(objCollection, objDistinctValue);
if (objFieldValues == null)
throw new Exceptions.ObjectDoesNotExistException(objCollection, objDistinctValue);
return objFieldValues;
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Initializes an existing object with values from a set of database fields.
/// Specifically, sets the IDatbaseObject.IsSaved property to true,
/// sets the IDatbaseObject.DistinctValue using the provided data and
/// calls IDatbaseObject.LoadFields().
/// </summary>
///
/// <param name="objCollection">
/// The collection associated with the field values. This is not used
/// to create an object - but to set the distinct field for the object using the
/// IDatabaseObjects.DistinctFieldName property.
/// </param>
///
/// <param name="objFieldValues">
/// The data container that contains the data to be copied into the object.
/// </param>
/// --------------------------------------------------------------------------------
public static void ObjectLoad(IDatabaseObjects objCollection, IDatabaseObject objItem, SQL.SQLFieldValues objFieldValues)
{
if (objFieldValues == null)
throw new ArgumentNullException();
objItem.IsSaved = true;
objItem.DistinctValue = objFieldValues[objCollection.DistinctFieldName()].Value;
objItem.LoadFields(objFieldValues);
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns an instance of an object from the collection using a distinct value (see
/// IDatabaseObjects.DistinctFieldName). If the collection has implemented the
/// IDatabaseObjects.Subset function then the objDistinctValue need only be unique
/// within the collection's subset, not the entire database table.
/// Returns Nothing/null if the distinct value does not exist in the database.
/// This feature is what differentiates Database.Object() from Database.ObjectIfExists().
/// </summary>
///
/// <param name="objCollection">
/// The collection that contains the object.
/// </param>
///
/// <param name="objDistinctValue">
/// The value that uniquely identifies the object within the collection. This is the value
/// of the field defined by the collection's IDatabaseObjects.DistinctFieldName function.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
///
/// <example> Loads a product using a product ID of 123
/// <code>
/// objProduct = objDatabase.Object(NorthwindDB.Products, 123)
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public IDatabaseObject ObjectIfExists(IDatabaseObjects objCollection, object objDistinctValue)
{
SQL.SQLFieldValues objFieldValues = this.ObjectFieldValuesIfExists(objCollection, objDistinctValue);
if (objFieldValues == null)
return null;
else
return ObjectFromFieldValues(objCollection, objFieldValues);
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Initializes an existing object with values from a set of database fields.
/// Specifically, sets the IDatbaseObject.IsSaved property to true,
/// sets the IDatbaseObject.DistinctValue using the provided data and
/// calls IDatbaseObject.LoadFields().
/// </summary>
///
/// <param name="objCollection">
/// The collection associated with the field values. This is not used
/// to create an object - but to set the distinct field for the object using the
/// IDatabaseObjects.DistinctFieldName property.
/// </param>
///
/// <param name="objData">
/// The data container that contains the data to be copied into the object.
/// </param>
/// --------------------------------------------------------------------------------
public static void ObjectLoad(IDatabaseObjects objCollection, IDatabaseObject objItem, IDataReader objData)
{
ObjectLoad(objCollection, objItem, FieldValuesFromDataReader(objCollection, objData));
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns an array of IDatabaseObject objects contained within this collection.
/// </summary>
/// --------------------------------------------------------------------------------
public IDatabaseObject[] ObjectsArray(IDatabaseObjects objCollection)
{
IList objList = this.ObjectsList(objCollection);
IDatabaseObject[] objArray = new IDatabaseObject[objList.Count - 1 + 1];
objList.CopyTo(objArray, 0);
return objArray;
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns an instance of an object from the collection using a distinct value (see
/// IDatabaseObjects.DistinctFieldName). If the collection has implemented the
/// IDatabaseObjects.Subset function then the objDistinctValue need only be unique
/// within the collection's subset, not the entire database table.
/// </summary>
///
/// <param name="objCollection">
/// The collection that contains the object.
/// </param>
///
/// <param name="objDistinctValue">
/// The value that uniquely identifies the object within the collection. This is the value
/// of the field defined by the collection's IDatabaseObjects.DistinctFieldName function.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
///
/// <example> Loads a product using a product ID of 123
/// <code>
/// objProduct = objDatabase.Object(NorthwindDB.Products, 123)
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public IDatabaseObject Object(IDatabaseObjects objCollection, object objDistinctValue)
{
return ObjectFromFieldValues(objCollection, this.ObjectFieldValues(objCollection, objDistinctValue));
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns an object from the collection using a unique key value.
/// The key must be unique within the collection. If the collection's
/// IDatabaseObjects.Subset has been implemented then the key need only be unique
/// within the subset specified, not the entire database table.
/// </summary>
///
/// <param name="objCollection">
/// The collection which contains the object.
/// </param>
///
/// <param name="objKey">
/// The key that identifies the object with the collection. The key is the value of
/// the field defined by the collection's IDatabaseObjects.KeyFieldName.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
///
/// <example>
/// <code>
/// Default Public ReadOnly Property Item(ByVal strProductCode As String) As Product
/// Get
///
/// Return objDatabase.ObjectByKey(Me, strProductCode)
///
/// End Get
/// End Property
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public IDatabaseObject ObjectByKey(IDatabaseObjects objCollection, object objKey)
{
IDatabaseObject objObject = ObjectByKeyIfExists(objCollection, objKey);
if (objObject == null)
throw new Exceptions.ObjectDoesNotExistException(objCollection, objKey);
return objObject;
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns an object from the collection using a unique key value.
/// The key must be unique within the collection. If the collection's
/// IDatabaseObjects.Subset has been implemented then the key need only be unique
/// within the subset specified, not the entire database table.
/// Returns Nothing/null if the object does exist with the specified key.
/// This feature is what differentiates Database.ObjectByKey() from Database.ObjectByKeyExists().
/// </summary>
///
/// <param name="objCollection">
/// The collection which contains the object.
/// </param>
///
/// <param name="objKey">
/// The key that identifies the object with the collection. The key is the value of
/// the field defined by the collection's IDatabaseObjects.KeyFieldName.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
///
/// <example>
/// <code>
/// Default Public ReadOnly Property Item(ByVal strProductCode As String) As Product
/// Get
///
/// Return objDatabase.ObjectByKey(Me, strProductCode)
///
/// End Get
/// End Property
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public IDatabaseObject ObjectByKeyIfExists(IDatabaseObjects objCollection, object objKey)
{
var objSelect = new SQL.SQLSelect();
string keyFieldName = objCollection.KeyFieldName();
EnsureKeyFieldNameIsSet(keyFieldName, objCollection);
SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
objSelect.Tables.Joins = objCollection.TableJoins(objPrimaryTable, objSelect.Tables);
objSelect.Where.Add(keyFieldName, SQL.ComparisonOperator.EqualTo, objKey);
var objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
{
objSelect.Where.Add(objSubset);
}
using (ConnectionScope objConnection = new ConnectionScope(this))
{
using (IDataReader objReader = objConnection.Execute(objSelect))
{
if (objReader.Read())
return ObjectFromDataReader(objCollection, objReader);
else
return null;
}
}
}
/// <summary>
/// Returns the user ID that has the object locked.
/// Throws an exception if the object is not locked.
/// </summary>
public string LockedByUserID(IDatabaseObjects objCollection, IDatabaseObject objObject)
{
SQL.SQLSelect objSelect = new SQL.SQLSelect();
objSelect.Fields.Add("UserID");
objSelect.Tables.Add(pstrLockTableName);
objSelect.Where.Add("TableName", SQL.ComparisonOperator.EqualTo, objCollection.TableName());
objSelect.Where.Add("RecordID", SQL.ComparisonOperator.EqualTo, objObject.DistinctValue.ToString());
using (ConnectionScope connection = new ConnectionScope(pobjDatabase))
{
using (IDataReader objReader = connection.Execute(objSelect))
{
if (objReader.Read())
return objReader[0].ToString();
else
throw new Exceptions.DatabaseObjectsException("Object is not locked");
}
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Returns the last object in the collection respectively
/// filtered and sorted by the collection's IDatabaseObjects.Subset and
/// IDatabaseObjects.OrderBy values. It differs from ObjectByOrdinal in that it only
/// loads the first record from the database table not the entire table.
/// </summary>
///
/// <param name="objCollection">
/// The collection which contains the object.
/// </param>
///
/// <returns><see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)</returns>
///
/// <example>
/// <code>
/// 'Ideal for loading default objects
/// Dim objDefaultSupplier As Supplier = objDatabase.ObjectByOrdinalFirst(objGlobalSuppliersInstance)
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public IDatabaseObject ObjectByOrdinalLast(IDatabaseObjects objCollection)
{
var objSelect = new SQL.SQLSelect();
//only select the first row of the recordset
objSelect.Top = 1;
SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
objSelect.Tables.Joins = objCollection.TableJoins(objPrimaryTable, objSelect.Tables);
objSelect.Where = objCollection.Subset();
SQL.SQLSelectOrderByFields objOrderBy = objCollection.OrderBy();
if (objOrderBy != null)
{
objOrderBy.OrderingReverseAll();
objSelect.OrderBy = objOrderBy;
}
using (ConnectionScope objConnection = new ConnectionScope(this))
{
using (IDataReader objReader = objConnection.Execute(objSelect))
{
if (objReader.Read())
return ObjectFromDataReader(objCollection, objReader);
else
throw new Exceptions.ObjectDoesNotExistException(objCollection, "TOP 1 with reversed ordering");
}
}
}
/// <summary>
/// Returns whether the object is locked.
/// </summary>
public bool IsLocked(IDatabaseObjects objCollection, IDatabaseObject objObject)
{
return this.LockRecordExists(objCollection.TableName(), objObject);
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Deletes an object's database record. If the collection's IDatabaseObjects.Subset
/// has been implemented then the object must exist within the subset, otherwise the
/// object will not be deleted. If the object has not been saved to the database the
/// function will exit without executing an SQL DELETE command. After deleting the
/// database record the object is set to Nothing. The calling function should receive
/// the object ByRef for this to have any affect. Setting the object to Nothing
/// minimises the possibility of the deleted object being used in code after
/// ObjectDelete has been called.
/// </summary>
///
/// <param name="objCollection">
/// The collection that contains the object to delete. If the item does not exist
/// within the collection then the object will not be deleted.
/// </param>
///
/// <param name="objItem">
/// The object to delete. The calling function should receive this object ByRef
/// as the object is set to Nothing after deletion.
/// Reference Type: <see cref="IDatabaseObject" /> (DatabaseObjects.IDatabaseObject)
/// </param>
///
/// <example>
/// <code>
/// Public Sub Delete(ByRef objProduct As Product)
///
/// objDatabase.ObjectDelete(Me, objProduct)
/// 'objProduct will now be Nothing
///
/// End Sub
/// </code>
/// </example>
/// --------------------------------------------------------------------------------
///
public void ObjectDelete(IDatabaseObjects objCollection, ref IDatabaseObject objItem)
{
if (objItem.IsSaved)
{
SQL.SQLDelete objDelete = new SQL.SQLDelete();
SQL.SQLConditions objSubset;
objDelete.TableName = objCollection.TableName();
objDelete.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objItem.DistinctValue);
objSubset = objCollection.Subset();
if (objSubset != null && !objSubset.IsEmpty)
objDelete.Where.Add(objSubset);
using (ConnectionScope objConnection = new ConnectionScope(this))
objConnection.ExecuteNonQuery(objDelete);
objItem.IsSaved = false;
if (Transaction.Current != null)
{
IDatabaseObject objItemCopy = objItem;
Transaction.Current.EnlistVolatile(new TransactionExecuteActionOnRollback(() => objItemCopy.IsSaved = true), EnlistmentOptions.None);
}
}
//The function that calls ObjectDelete objItem MUST be ByRef for this to have any effect
objItem = null;
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Locks an object.
/// Throws an exception if the object is already locked.
/// Throws an exception if the object is not been saved.
/// Because it is possible that between calling IsLocked and calling Lock another
/// user may have locked the object. Therefore, it is recommended calling Lock and then
/// trapping the Exceptions.ObjectAlreadyExistsException to determine whether the object is already locked.
/// </summary>
/// <exception cref="Exceptions.DatabaseObjectsException">Thrown if the object has not been saved.</exception>
/// <exception cref="Exceptions.ObjectAlreadyExistsException">Thrown if the object has already been locked.</exception>
/// --------------------------------------------------------------------------------
public void Lock(IDatabaseObjects objCollection, IDatabaseObject objObject)
{
if (!objObject.IsSaved)
throw new Exceptions.DatabaseObjectsException("Object is not saved and cannot be locked");
SQL.SQLInsert objInsert = new SQL.SQLInsert();
objInsert.TableName = pstrLockTableName;
objInsert.Fields.Add("TableName", objCollection.TableName());
objInsert.Fields.Add("RecordID", objObject.DistinctValue.ToString());
objInsert.Fields.Add("UserID", pstrCurrentUserID);
//If another user/connection has managed to add a record to the database just before
//this connection has a DatabaseObjectsException will be thrown because duplicate keys will
//be added to the table.
using (ConnectionScope connection = new ConnectionScope(pobjDatabase))
{
try
{
connection.ExecuteNonQuery(objInsert);
}
catch (Exceptions.DatabaseObjectsException)
{
throw new Exceptions.ObjectAlreadyLockedException(objCollection, objObject);
}
}
}
/// <summary>
/// Throwns an exception if the key field name is "".
/// </summary>
private void EnsureKeyFieldNameIsSet(string keyFieldName, IDatabaseObjects collection)
{
if (String.IsNullOrEmpty(keyFieldName))
throw new InvalidOperationException("The KeyFieldAttribute has not been specified or the KeyFieldName function overridden for " + collection.GetType().FullName);
}
