/// -------------------------------------------------------------------------------- /// <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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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; } } }
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; } } }
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); } }
private static SQL.SQLFieldValues FieldValuesFromDataReader(IDatabaseObjects objCollection, IDataReader objReader) { string strFieldName; string strTablePrefix; SQL.SQLFieldValues objFieldValues; //check that the distinct field name exists if (!FieldExists(objReader, objCollection.DistinctFieldName())) throw new Exceptions.DatabaseObjectsException(System.Convert.ToString(((object) objCollection).GetType().Name + ".DistinctFieldName '" + objCollection.DistinctFieldName() + "' is invalid")); objFieldValues = new SQL.SQLFieldValues(); strTablePrefix = objCollection.TableName() + "."; //Copy the recordset values into the SQL.SQLFieldValues object for (int intIndex = 0; intIndex < objReader.FieldCount; intIndex++) { //If the recordset has been loaded with a join then it may be prefixed with //the table name - this is the case with Microsoft Access //If so remove the table name if the table prefix is the same as objCollection.TableName //All of the other joined fields with tablename prefixes on the fields will remain. This is ok considering //most of the time an inner join has been performed where the field names are equal in the 2 joined tables strFieldName = objReader.GetName(intIndex); if (strFieldName.IndexOf(strTablePrefix) == 0) objFieldValues.Add(strFieldName.Substring(strTablePrefix.Length), objReader[intIndex]); else objFieldValues.Add(strFieldName, objReader[intIndex]); } return objFieldValues; }
/// -------------------------------------------------------------------------------- /// <summary> /// Sets the field value for 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 modify. /// </param> /// <param name="objItem"> /// The object which represents the database record to be set. Specifically, /// the object's distinct field name is used to determine which record to modify. /// </param> /// <param name="strFieldName"> /// The name of the database field that is to be set. /// </param> /// <param name="objNewValue"> /// The new value that the database field it to be set to. /// If Nothing/null then the field is set to NULL. /// </param> /// <exception cref="Exceptions.ObjectDoesNotExistException">If the object has not already been saved</exception> /// -------------------------------------------------------------------------------- public void ObjectSetFieldValue(IDatabaseObjects objCollection, IDatabaseObject objItem, string strFieldName, object objNewValue) { if (!objItem.IsSaved) throw new Exceptions.ObjectDoesNotExistException(objItem); SQL.SQLUpdate objUpdate = new SQL.SQLUpdate(); objUpdate.TableName = objCollection.TableName(); objUpdate.Fields.Add(strFieldName, objNewValue); objUpdate.Where.Add(objCollection.DistinctFieldName(), SQL.ComparisonOperator.EqualTo, objItem.DistinctValue); var objSubset = objCollection.Subset(); if (objSubset != null && !objSubset.IsEmpty) objUpdate.Where.Add(objSubset); using (ConnectionScope objConnection = new ConnectionScope(this)) objConnection.ExecuteNonQuery(objUpdate); }
public ObjectAlreadyLockedException(IDatabaseObjects objCollection, IDatabaseObject objObject) : base(objObject.GetType().Name + "." + objCollection.DistinctFieldName() + " " + objObject.DistinctValue.ToString() + " is already locked") { }