예제 #1
0
        /// --------------------------------------------------------------------------------
        /// <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();
        }
예제 #2
0
        /// --------------------------------------------------------------------------------
        /// <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();
        }
예제 #3
0
        /// --------------------------------------------------------------------------------
        /// <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);
        }
예제 #4
0
        /// --------------------------------------------------------------------------------
        /// <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>
        /// 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);
        }
        private bool LockRecordExists(string strTableName, IDatabaseObject objObject, SQL.SQLCondition objAdditionalCondition = null)
        {
            SQL.SQLSelect objSelect = new SQL.SQLSelect();

            objSelect.Fields.Add(string.Empty, SQL.AggregateFunction.Count);
            objSelect.Tables.Add(pstrLockTableName);
            objSelect.Where.Add("TableName", SQL.ComparisonOperator.EqualTo, strTableName);
            objSelect.Where.Add("RecordID", SQL.ComparisonOperator.EqualTo, objObject.DistinctValue.ToString());
            if (objAdditionalCondition != null)
                objSelect.Where.Add(objAdditionalCondition);

            using (ConnectionScope connection = new ConnectionScope(pobjDatabase))
            {
                using (IDataReader objReader = connection.Execute(objSelect))
                {
                    objReader.Read();
                    return System.Convert.ToInt32(objReader[0]) != 0;
                }
            }
        }
예제 #7
0
        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 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");
                }
            }
        }
예제 #9
0
        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);
            }
        }
예제 #10
0
        /// --------------------------------------------------------------------------------
        /// <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;
                }
            }
        }
예제 #11
0
        /// --------------------------------------------------------------------------------
        /// <summary>
        /// Returns a collection of objects that match the specified search criteria.
        /// This function utilises any subsets, ordering or table joins specified in the
        /// collection. To add a set of conditions to the objSearchCriteria object with
        /// higher precendance use the "Add(SQLConditions)" overloaded function as this will
        /// wrap the conditions within parentheses.
        /// </summary>
        ///
        /// <param name="objCollection">
        /// The collection to search within.
        /// </param>
        ///
        /// <param name="objSearchCriteria">
        /// The criteria to search for within the collection. To add a set of conditions with
        /// with higher precendance use the "Add(SQLConditions)" overloaded function as this
        /// will wrap the conditions within parentheses.
        /// </param>
        ///
        /// <returns><see cref="Collections.IList" />	(System.Collections.IList)</returns>
        ///
        /// <remarks>
        /// The following wildcard characters are used when using the LIKE operator (extract
        /// from Microsoft Transact-SQL Reference)
        ///
        ///
        /// <font size="1">
        /// <table width="659" border="1" cellspacing="1" cellpadding="4">
        ///   <tr>
        ///     <th width="16%" height="20">Wildcard character</th>
        ///     <th width="22%">Description</th>
        ///     <th width="62%">Example</th>
        ///   </tr>
        ///   <tr>
        ///     <td>%</td>
        ///     <td>Any string of zero or more characters.</td>
        ///     <td>WHERE title LIKE '%computer%' finds all book titles with the word
        ///         'computer' anywhere in the book title.</td>
        ///   </tr>
        ///   <tr>
        ///     <td>_ (underscore)</td>
        ///     <td>Any single character.</td>
        ///     <td>WHERE au_fname LIKE '_ean' finds all four-letter first names that end
        ///       with ean (Dean, Sean, and so on).</td>
        ///   </tr>
        /// </table>
        /// </font>
        /// </remarks>
        ///
        /// <example>
        /// <code>
        /// Public Function Search(ByVal objSearchCriteria As Object, ByVal eType As SearchType) As IList
        ///
        ///     Dim objConditions As SQL.SQLConditions = New SQL.SQLConditions
        ///
        ///     Select Case eType
        ///         Case SearchType.DescriptionPrefix
        ///             objConditions.Add("ProductName", SQL.ComparisonOperator.Like, objSearchCriteria &amp; "%")
        ///         Case SearchType.Description
        ///             objConditions.Add("ProductName", SQL.ComparisonOperator.Like, "%" &amp; objSearchCriteria &amp; "%")
        ///     End Select
        ///
        ///     Return objDatabase.ObjectsSearch(objGlobalProductsInstance, objConditions)
        ///
        /// End Function
        /// </code>
        /// </example>
        /// --------------------------------------------------------------------------------
        ///
        public IList ObjectsSearch(IDatabaseObjects objCollection, SQL.SQLConditions objSearchCriteria)
        {
            var objSelect = new SQL.SQLSelect();
            ArrayList objResults = new ArrayList();

            if (objSearchCriteria.IsEmpty)
                throw new ArgumentException("Search criteria is empty");

            SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
            objSelect.Tables.Joins = objCollection.TableJoins(objPrimaryTable, objSelect.Tables);
            objSelect.OrderBy = objCollection.OrderBy();
            objSelect.Where = objCollection.Subset();

            if (objSearchCriteria != null)
            {
                if (objSelect.Where == null)
                    objSelect.Where = new SQL.SQLConditions();

                objSelect.Where.Add(objSearchCriteria);
            }

            using (ConnectionScope objConnection = new ConnectionScope(this))
            {
                using (IDataReader objReader = objConnection.Execute(objSelect))
                {
                    while (objReader.Read())
                        objResults.Add(ObjectFromDataReader(objCollection, objReader));

                    return objResults;
                }
            }
        }
예제 #12
0
        /// --------------------------------------------------------------------------------
        /// <summary>
        /// Returns an IList object containing the first n of the collection's associated child
        /// objects. This function is useful when loading a set of objects for a subset or
        /// for use with the IEnumerable interface.
        /// </summary>
        ///
        /// <param name="objCollection">
        /// The collection which contains the objects to load.
        /// </param>
        ///
        /// <param name="maxRecords">
        /// The maximum number of records to return. 
        /// Zero returns all of the records.
        /// </param>
        ///
        /// <returns><see cref="Collections.IList" />	(System.Collections.IList)</returns>
        ///
        /// <example>
        /// <code>
        /// 'Can be used to provide an enumerator for use with the "For Each" clause
        /// Private Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
        ///
        ///     Return objDatabase.ObjectsList(objGlobalProductsInstance, 1000).GetEnumerator
        ///
        /// End Function
        /// </code>
        /// </example>
        /// --------------------------------------------------------------------------------
        ///
        public IList ObjectsList(IDatabaseObjects objCollection, int maxRecords)
        {
            IList objArrayList = new ArrayList();
            SQL.SQLSelect objSelect = new SQL.SQLSelect();

            SQL.SQLSelectTable objPrimaryTable = objSelect.Tables.Add(objCollection.TableName());
            objSelect.Top = maxRecords;
            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))
                {
                    while (objReader.Read())
                        objArrayList.Add(ObjectFromDataReader(objCollection, objReader));

                    return objArrayList;
                }
            }
        }
예제 #13
0
        /// --------------------------------------------------------------------------------
        /// <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);
        }
예제 #14
0
        /// --------------------------------------------------------------------------------
        /// <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);
                }
            }
        }
예제 #15
0
 public int ExecuteNonQueryWithConnect(SQL.ISQLStatement[] objSQLStatements)
 {
     using (var connectionScope = new ConnectionScope(this))
         return connectionScope.ExecuteNonQuery(new SQL.SQLStatements(objSQLStatements));
 }
예제 #16
0
        /// --------------------------------------------------------------------------------
        /// <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);
                    }
                }
            }
        }
예제 #17
0
 /// --------------------------------------------------------------------------------
 /// <summary>
 /// Executes the SQL statement.
 /// Returns Nothing/null if no record was selected, otherwise the first field from the
 /// returned result.
 /// ConnectionController.Start and
 /// ConnectionController.Finished are automatically called.
 /// </summary>
 /// --------------------------------------------------------------------------------
 public object ExecuteScalarWithConnect(SQL.ISQLStatement objSQLStatement)
 {
     using (var connectionScope = new ConnectionScope(this))
     {
         using (var objDataReader = connectionScope.Execute(objSQLStatement))
         {
             if (objDataReader.Read())
                 return objDataReader[0];
             else
                 return null;
         }
     }
 }
        /// --------------------------------------------------------------------------------
        /// <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);
        }
예제 #19
0
        /// --------------------------------------------------------------------------------
        /// <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;
                }
            }
        }
        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());
                }
            }
        }
예제 #21
0
        /// --------------------------------------------------------------------------------
        /// <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>
        /// 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);
                }
            }
        }
예제 #23
0
        /// --------------------------------------------------------------------------------
        /// <summary>
        /// Returns the number of items in the collection. If the collection's
        /// IDatabaseObjects.Subset has been implemented then this function returns the
        /// number of records within the subset, not the entire table.
        /// Also utilises the table joins so that any filters specified on the subset
        /// can be used.
        /// </summary>
        ///
        /// <param name="objCollection">
        /// The object for which the number of records are returned.
        /// </param>
        ///
        /// <returns><see cref="Int32" />	(System.Int32)</returns>
        ///
        /// <example>
        /// <code>
        /// 'Return the number of items in this collection.
        /// Public ReadOnly Property Count() As Integer
        ///     Get
        ///
        ///         Return objDatabase.ObjectsCount(Me)
        ///
        ///     End Get
        /// End Property
        /// </code>
        /// </example>
        /// --------------------------------------------------------------------------------
        ///
        public int ObjectsCount(IDatabaseObjects objCollection)
        {
            var objSelect = new SQL.SQLSelect();

            objSelect.Where = objCollection.Subset();
            objSelect.Fields.Add(string.Empty, SQL.AggregateFunction.Count);
            objSelect.Tables.Add(objCollection.TableName());

            using (ConnectionScope objConnection = new ConnectionScope(this))
            {
                using (IDataReader objReader = objConnection.Execute(objSelect))
                {
                    objReader.Read();
                    return System.Convert.ToInt32(objReader[0]);
                }
            }
        }