/// -------------------------------------------------------------------------------- /// <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 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 & "%") /// Case SearchType.Description /// objConditions.Add("ProductName", SQL.ComparisonOperator.Like, "%" & objSearchCriteria & "%") /// 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; } } }
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 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; } } }