public SQLSelectTableFromSelect(SQLSelect objSelect, string strAlias)
{
if (String.IsNullOrEmpty(strAlias))
throw new ArgumentNullException();
else if (objSelect == null)
throw new ArgumentNullException();
pobjSelect = objSelect;
base.Alias = strAlias;
}
public SQLUnion(SQLSelect objSelect1, SQLUnion.Type eType, SQLSelect objSelect2)
{
Add(objSelect1);
AddUnionType(eType);
Add(objSelect2);
}
public SQLUnion(SQLSelect objSelect1, SQLSelect objSelect2)
: this(objSelect1, pceDefaultUnionType, objSelect2)
{
}
public void Add(SQLSelect objSelect)
{
EnsurePreviousTypeExists();
pobjSelectStatements.Add(objSelect);
}
/// --------------------------------------------------------------------------------
/// <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 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;
}
}
}
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;
}
}
}
/// --------------------------------------------------------------------------------
/// <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 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 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;
}
}
}
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 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;
}
}
}
/// --------------------------------------------------------------------------------
/// <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>
/// 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 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();
}
public SQLCreateView(string strViewName, SQLSelect objSelectStatement)
{
this.Name = strViewName;
this.Select = objSelectStatement;
}
/// --------------------------------------------------------------------------------
/// <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]);
}
}
}
