private void EnsureTableExists()
{
SQL.SQLTableExists objTableExists = new SQL.SQLTableExists(pstrLockTableName);
using (ConnectionScope connection = new ConnectionScope(pobjDatabase))
{
IDataReader with_1 = connection.Execute(objTableExists);
//If table does not exist
if (!with_1.Read())
{
connection.ExecuteNonQuery(CreateTable());
connection.ExecuteNonQuery(CreateTableIndex());
}
}
}
/// --------------------------------------------------------------------------------
/// <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>
/// 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>
/// Provides a means by which to ensure all locks have been removed for this user
/// in situations where an unexpected exception occurs and/or the user logs out of
/// system.
/// </summary>
/// --------------------------------------------------------------------------------
public void UnlockAll()
{
SQL.SQLDelete objDelete = new SQL.SQLDelete();
objDelete.TableName = pstrLockTableName;
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>
/// 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;
}
public int ExecuteNonQueryWithConnect(SQL.ISQLStatement[] objSQLStatements)
{
using (var connectionScope = new ConnectionScope(this))
return connectionScope.ExecuteNonQuery(new SQL.SQLStatements(objSQLStatements));
}
/// --------------------------------------------------------------------------------
/// <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);
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Deletes all of the objects in the collection. If IDatabaseObjects.Subset
/// has been implemented then only the objects within the subset are deleted, not
/// the table's entire contents.
/// </summary>
///
/// <param name="objCollection">
/// The collection from which all objects are to be deleted.
/// </param>
/// --------------------------------------------------------------------------------
///
public void ObjectsDeleteAll(IDatabaseObjects objCollection)
{
SQL.SQLDelete objDelete = new SQL.SQLDelete();
objDelete.TableName = objCollection.TableName();
objDelete.Where = objCollection.Subset();
using (ConnectionScope objConnection = new ConnectionScope(this))
objConnection.ExecuteNonQuery(objDelete);
}
