Database Objects
Hi-Integrity Systems
Home.NET Download

.NET Download

Source Code

Demonstration Project
April 2016

  • Supports Nullabe<T> data types Commit.
  • Support MySQL UniqueIdentifier is CHAR(38). Commit.
June 2014

  • Supports returning TOP records from ObjectsList(). Closes #92.
  • Support LIMIT command in SQLite via SQLSelect.Top. Closes #93.
  • SQLite text search is case-insensitive so that it is consistent with the other database providers. Closes #94.
  • Added ConstraintBinding convenience constructor for Func<> rather than IConstraint.
March 2014

  • Added support for Android and SQLite via MonoDroid. Closes #91.
  • Support dynamic error messages for contraints. Closes #90.
August 2013

  • Corrected implicit type inference when using foreach. Closes #89.
August 2013

  • Corrected KeyFieldsIsUniqueInCollectionContraint when used with a saved (not reloaded) object. Closes #88.
July 2013

  • Pervasive has full support as per other database systems. Closes #86.
July 2013

  • Correctly onforwards ItemInstance calls up the object hierarchy. Closes #85.
July 2013

  • Corrected serializing an enum value. Closes #84.
  • Corrected validation of milliseconds in Microsoft Access Closes #76.
July 2013

  • Removed custom transaction manager for Microsoft Access and SQL CE databases. Closes #57.
  • Corrects error when using SQLSelect.ctor(string, SQLCondition). Closes #83.
  • Supports early binding for all references which also require other table joins. Closes #81.
  • Added ObjectIsSavedContrains that ensures that an object has been persisted to the database. Closes #79.
October 2012

  • Ensures Microsoft Access and SQL Server CE pass all tests. A few amendments were made due to failed tests.
  • Supports SQLLogicalExpression. Closes #66.
  • SQLConditions is now also expression. Closes #67.
  • Added MONO and MONO_TOUCH compilation constants for release mode. #45.
  • SQLCastExpression works for non-string data types. Closes #68.
  • Time component is written if only milliseconds. Closes #69.
  • Supports nanosecond precision for SQLite time serialization. Closes #71.
  • StringMaxLengthConstraint returns true when string is empty. Closes #72.
  • Microsoft Access SELECT TOP syntax corrected. Closes #73.
  • Added support for SQLTableExists for SQL Server CE. Closes #74.
  • Support non-unicode data types for SQL Server CE. Closes #75.
  • Exception thrown if a date/time containing milliseconds is used with Microsoft Access. Closes #76..
  • Corrected support for milliseconds in date/time values. #76.
  • Reduced memory footprint, objects are disposed early. Closes #65.
July 2012

  • Added support for MonoTouch on iOS. Closes #45.
  • Added support for SQLite database. (Primarily for use on MonoTouch/iOS). Closes #62.
  • Refactored SQL serialization so that new database systems can be easily integrated (Primarily for integrated of SQLite database). Closes #64.
  • Updated collection classes to use generic classes rather than non-generic collection classes. Closes #63.
  • Ported from VB.NET to C#. (Primarily for use on MonoTouch/iOS). Closes #61.
May 2012

  • Removed custom TransactionManager. Closes #57.
    All transactions now fall through to MSDTC when used with TransactionScope. Use the LocalTransactionScope to use local transactions and bypass MSDTC. IMPORTANT: This is a breaking change as of release Connections are now NOT implicitly opened when any way. Database.ConnectionController.Execute* function is called within a TransactionScope. Instead a ConnectionScope must also be used to call the Execute* statement. Previously, the connection would be implicitly opened if any Database.ConnectionController.Execute* functions was called within a TransactionScope using statement. Normal calls to DatabaseObject.Save or Database.ObjectSave are not affected, so only minor code changes (if any) should be required.
  • Added support for LocalTransactionScope object (like TransactionScope except no MSDTC). Closes #58.
  • Added support SQLExpression operator overloading. Closes #52.
    Provides the powerful ability to generate SQL expressions for arithmetic operations and string concatenation using regular .NET code. Any arithmetic operator or string concatenation can be applied to an SQLExpression and the appropriate operator is translated into the appropriate SQL command. This significantly simplifies the .NET code and also simplifies the process of generating the SQL code, because normal .NET operators can be utilised. For example: "New SQLFieldExpression("Field") Mod 10" translates to SQL "([Field] % 10)". The expression can be used in an SQLSelect, SQLInsert, SQLDelete or SQLUpdate class (amongst others).
  • Improved error message when a FieldMapping cannot be set or read. Closes #54.
  • Corrected MySQL boolean fields with used with FieldMappingAttribute. Closes #60.
  • Corrected SQLViewExists for MySQL. Closes #59.
  • Added support for MySQL SELECT LIMIT command via the SQLSelect.Top property. Closes #53.
  • MySQLDatabase class now supports latest MySQL 5.1 driver. Closes #55.
  • Added support for MySQL change BIT data type. Closes #56.
April 2012

  • Added support for concatenating string expressions. Closes #51.
April 2012

  • Added support for early binding object references. Closes #41.
  • Marked all *UsingAttributes* classes as Obsolete. Closes #46.
  • Specifying the ItemInstanceAttribute and overriding ItemInstance_ for DatabaseObjects.Generic.DatabaseObjects* types is optional. Closes #40.
  • Added support for multiple TableJoinAttribute definitions. Closes #44.
  • Integrated IDatabaseObjects.DistinctFieldAutoAssignment functionality. Closes #47.
  • ItemInstanceAttribute type is validated. Closes #48.
  • Improved error message when KeyFieldAttribute omitted or KeyFieldname not overridden. Closes #49.
  • Added support for aggregate function expressions. Closes #50.
  • Added support for millisecond component when writing date/time. Closes #43.
  • Corrected table join conditions for IS NULL expression. Closes #42.
  • Does not utilise IDatabaseObjcts.TableJoins when counting objects. Closes #39.
  • Added support for GetDateFunction for MySQL. Closes #26.
March 2012

  • Added and implemented ConnectionScope construct throughout library. Closes #10.
  • Added RootContainerObject concept. Closes #8.
  • Added EmailAddressConstraint for email address validation. Closes #16.
  • Added support for specifying the database name for table in a SELECT statement. Closes #36.
  • Corrected multiple users locking the same object. Closes #34.
  • Indicated that SQLSelectTableJoinConditions.Add(string, ComparisonOperator, string) is obsolete. Closes #20.
  • Refactored SQLSelectTables.SQL property so that the table joinsSQL code is not generated twice unnecessarily. Closes #21.
  • Refactored SQLLeftExpression and renamed to SQLLeftFunctionExpression. Closes #30.
  • Disabled parentheses for SQLFunctionExpression when no parameters. Closes #35.
  • ItemInstance for DatabaseObjectsMultipleSubclassUsingAttributes correctly fails. Closes #38.
  • Type specified by ItemInstance now correctly instantiates when using a DatabaseObjects.DatabaseObjects parameter. Closes #37.
  • Using InvalidOperationException instead of MethodAccessException for runtime / development errors. Closes #19.
  • Refactored code regarding SQLSelectTableBase.GetPrefix. Closes #17.
March 2012
  • Provide access to the parent SQLSelect object from the SQLSelect.Tables.Joins object. Closes #12.
  • Added * expression for indicating to select all fields from a table. Closes #11.
  • Added constraint KeyFieldIsUniqueInCollectionConstraint that ensures that a key field is unique in a collection. Closes #13.
  • Added Constraints.Constraint that accepts a predicate as the constraint. Closes #14.
  • Added a RegExConstraint for validating constraints using regular expressions. Closes #15.
  • Disabled table joins for Database.ObjectExists function. Closes #27.
  • Disabled table joins for Database.ObjectExistsByDistinctValue function. Closes #28.
  • Added support for creating computed columns. Closes #22.
  • Added support for SQL RIGHT function. Closes #31.
  • Added support for SQL CASE statements. Closes #23.
  • SQLConditionExpression inherits from SQLExpression. Closes #33.
  • Added support for SQL length string function. Closes #25.
  • Added ability to determine if a database view exists. Closes #5.
  • Added ability to drop database views. Closes #4.
  • Added ability to create database views. Closes #3.
  • Added support for conditions precedence in table join conditions. Closes #18.
  • Added support for running TSQL or non database-agnostic SQL functions. Closes #2.
  • Improved creating multiple table joins. Closes #6. Closes #7.
  • Added ability to perform bitwise operations in SQL. Closes #1.
  • Added in support for Unit testing in the DatabaseObjects.UnitTests project / repository.
  • Minor update to the bundled constraint descriptions to improve clarify in error messages.
  • Added new constraint Constraints.DateIsTodayOrFutureForNewObjectConstraint.
3.5.2 Nov.2011
  • Added DatabaseObjects.Constraints.StringMaxLengthConstraint.
  • Added the ability to clone a ConstraintBinding so that values passed as parameters can be validated. Private _name As String Private nameIsSetConstraint As New ConstraintBinding(Of String)( _ Function() Return Me._name, New StringIsSetConstraint()) Public Property Name As String Set(ByVal value As String) nameIsSetConstraint.Clone(value).EnsureConstraintSatisfied() Me._name = value End Set End Property
  • Added support for serialization and deserialization of the DatabaseObjectException class.
  • Added support for constraints which are business logic / rules that can be applied to a particular field or property on an object and which is shared between the business objects and the user interface. The constraints allow for the consolidation of the property validation rules into the business objects that the user interface can utilise. See frmOrders for an example in the Northwind demonstration project. The example below shows how two constraints can be created for a class property. Making the constraint publicly accessible allows the user interface logic to utilise the constraint without having to duplicate the logic. Custom constraints can be written ny inheriting from IConstraint. <DatabaseObjects.FieldMapping("OrderDate")> _ Private _TheDate As Date = Date.MinValue Public ReadOnly DateIsSet As ConstraintBinding(Of Date) Public ReadOnly DateIsValid As ConstraintBinding(Of Date) Public Sub New() DateIsSet = New ConstraintBinding(Of Date) _ (Function() TheDate, New DateIsSetConstraint()) 'If this is a new object - ensures that the order date 'is today or a future date DateIsValid = New ConstraintBinding(Of Date) _ (Function() TheDate, New DateIsTodayOrFutureForNewObjectConstraint(Me)) End Sub Public Property TheDate As Date Set(value As Date) 'Throws an exception if the value does not satisfy the constraint Me.DateIsSet.Clone(value).EnsureConstraintSatisfied() 'Throws an exception if the value does not satisfy the constraint Me.DateIsValid.Clone(value).EnsureConstraintSatisfied() Me._TheDate = value End Set Get Return Me._TheDate End Get End Property Public Shadows Sub Save() 'Throws an exception if the date has not been set DateIsSet.EnsureConstraintSatisfied() 'Throws an exception if the date is not in a valid range DateIsValid.EnsureConstraintSatisfied() MyBase.Save() End Sub
  • Specifying the item class (which inherits from DatabaseObject or implements IDatabaseObject) can be specified using the ItemInstance attribute. This avoids having to override the ItemInstance function to indicate the associated class instance. The class instance MUST have an empty constructor or a constructor with one argument of type DatabaseObjects.DatabaseObjects.
  • Corrected an issue where the table prefix would not be applied to a field name.
  • Added improved support for transactions when using TransactionScope. In particular, all objects that have the DistinctField and IsSaved values changed by the library are rolled back to the original values before the transaction if the transaction is rolled back. Essentially, the all object state is restored to the state prior to the transaction beginning. The local database transactions functions remain the same, but now the state of the database and the in-memory objects will be in sync.
  • Removed the Database.ObjectByOrdinal function because it is redundant. The IEnumerable interface in conjunction with the Database.ObjectList() function should be used for enumerating through a list rather than the Database.ObjectByOrdinal function.
  • SQL expressions (expressions, functions) can be specified in the GROUP BY clause.
  • Added support for expressions in the SQLSelect fields list. i.e. SELECT (COUNT(*) / 100) AS CountPercentage FROM ... Added function SQLSelect.Fields.Add(SQLExpression) that allows a calculated field to be selected.
  • Added convenience function DatabaseObjects.ObjectSave(item As IDatabaseObject) and Generic.DatabaseObjects.ObjectSave(item As T).
  • Refactored code that loads and saves fields marked with the FieldMapping and FieldMappingObjectHook attributes into the DatabaseObjectUsingAttributesHelper module / static class.
  • Added support for the HyperSQL syntax and the Database class now accepts a IDbConnection as well as a connection string.
  • The database connection object is re-used rather than a new one created. Made appropriate adjustments to Database.ConnectionController.
  • Corrected an incorrect error thrown when calling SQLTableFields.Add().
  • Added support for table scheme names.
  • Added in support for SELECT GETDATE() using the SQLSelectGetDate class to return the database's date time.
  • Added in support for HAVING clause for SELECT statements.
  • All solutions compiled against .NET framework 3.5 and upgraded to Visual Studio 2010.
  • Added the ability to create an MicrosoftSQLServerDatabase object by parsing an existing connection string and extracting the data source, database and any credentials. This is useful because the OLEDBServices=-3 option will automatically be set when parsed, so that the TransactionScope works correctly.
  • Modified the DatabaseObject equality operaters so that INT IDENTITY fields which are returned as decimal data types still compare correctly with integer data types.
  • Modified transactions controller so that inner transactions are not supressed. This allows the TransactionScopeOption passed to the TransactionScope constructor to function as expected, rather than inner transactions always being suppressed.
  • Modified SQLStatements so that it can be instantiated with an empty constructor and statements added via the Add function.
  • Modified the DatabaseObject equality operaters so that INT IDENTITY fields which are returned as decimal data types still compare correctly with integer data types.
  • Modified transactions controller so that inner transactions are not supressed. This allows the TransactionScopeOption passed to the TransactionScope constructor to function as expected, rather than inner transactions always being suppressed.
  • Modified SQLStatements so that it can be instantiated with an empty constructor and statements added via the Add function.
  • Added a DatabaseObject.Delete() function which allows inheriting classes to delete the database record associated with the object.
  • Transactions are automatically implied and started when calling Execute(), ExecuteNonQuery() or ExecuteScalar() from within a TransactionScope using statement.
  • Added DatabaseObjects.SQL.SQLCastExpression, which allows fields to be cast to different types when selecting a field.
  • Added SQLGetDateFunctionExpression which can be used for inserting or updating a field with the date/time on the database server.
  • Added Database.ConnectionController.ExecuteScalar and ExecuteScalarWithConnect which returns the first field from the first record from a SELECT statement.
  • DatabaseObjects.SQL.SQLInsert statements now support expressions in the fields that are inserted.
  • Added a new SELECT statement (DatabaseObjects.SQL.SQLSelectExpression), which can be used to execute and return the value from an SQL function. Currently, it is used with SQLGetDateFunctionExpression to return the date on the database server.
  • Corrected a problem introduced in 2.18.3 regarding the Database.ObjectsCount function.
February 2010
  • Added support for collections that create different subclasses. This has been implemented by the use of the IDatabaseObjectsMultipleSubclass interface and the implementation by classes Generic.DatabaseObjectsMultipleSubclass and Generic.DatabaseObjectsMultipleSubclassUsingAttributes. The use of the this interface implements an alternative ItemInstance function called ItemInstanceForSubclass(SQLFieldValues) which provides the field values for the object so that the appropriate sub-class type can be determined, created and returned.
  • The DatabaseObject.ParentCollection property can now be set - so that the object can be associated with a different parent.
  • DatabaseController.ExecuteNonQuery() and DatabaseController.ExecuteNonQueryWithConnect() now return the number of rows affected by an UPDATE or INSERT command.
  • Added Database.ObjectFromDataRow() so that an IDatabaseObject can be loaded from a System.Data.DataRow object.
  • Added class SQLSelectTableFromSelect so that a SELECT statement can be joined with another SELECT statement, rather than just joining with another table.
  • Added convenience property DatabaseObject.GrandParentDistinctValue.
September 2009
  • Added ObjectIfExists and ObjectByKeyIfExists functions to the Database and all collection objects (DatabaseObjects.DatabaseObjects and Generic.DatabaseObjects*). These functions perform the same function as Object and ObjectByKey except that a DatabaseObjectDoesNotExist exception is not thrown if the distinct value or key value specified is invalid and the object does not exist, intead Nothing/null is returned. This provides a more efficient means of loading an object rather than calling ObjectExists() to determine that the value is valid and then making another call to Object or ObjectByKey, thereby eliminating the need for duplicate SQL select statements. Generally, the ObjectIfExists and ObjectByKeyIfExists function should only be used where performance is paramount, because if misused can easily create unreadable and inconsistent code.
  • Provides support for local transactions, specifically with the use of the TransactionScope construct. This construct implicitly calls the MyBase.ParentDatabase.Transactions.Begin and MyBase.ParentDatabase.Transactions.Commit or MyBase.ParentDatabase.Transactions.Rollback depending on whether TransactionScope.Complete() is called. The TransactionScope handles all of the cleanup and ensuring that all database statements are executed within a database transaction. TransactionScopes calls can be nested, and nested transactions will utilise the ambient/outer transaction. Supported by all OLEDB providers, SQL Server, Microsoft Access and SQL Server CE. MySQL ODBC provider is not currently supported. All of the OLDEB provides have automatic transaction enlistment disabled (OLE DB Services = -3) in the connection strings for the MicrosoftSQLServerDatabase, MicrosoftAccessDatabase and MicrosoftSQLServerCompactEditionDatabase.

    Public Shadows Sub Save() Using objTransactionScope As New TransactionScope Me.Details.Save() MyBase.Save() MyBase.ParentDatabase.Connection.Start() 'Execute other SQL statements... MyBase.ParentDatabase.Connection.Finished() objTransactionScope.Complete() End Using End Sub
  • Added classes for all of the database connection types supported (MicrosoftSQLServerDatabase, MySQLDatabase, MicrosoftSQLCompactEditionDatabase and MicrosoftAccessDatabase) so that the connection string does not need to be specified. An instance of one of these classes can be used instead of the DatabaseObjects.Database class. All classes inherit from DatabaseObjects.Database. For Microsoft Access databases the MicrosoftAccessDatabase class also opens a dummy connection ensure that the connection pool remains alive. Also updated the demonstration program to use the new classes.
  • Added the "Overrides DatabaseObject.Equals(Object)" function for determining object equality.
  • Added the Database.ObjectByOrdinalLast and DatabaseObjects.ObjectByOrdinalByLast to return the last object in the collection.
  • Added support for selecting TOP for SQLCompactEdition databases as this uses the syntax TOP(123) rather than TOP 123.
  • Added support for the UniqueIndentifier data type and added a new property to the DistinctFieldAttribute() class. This property can be used to indicate that the distinct field should be automatically assigned a globally unique identifier. In line with this change, the IDatabaseObjects.DistinctFieldAutoIncrements function has been deprecated and replaced with the IDatabaseObjects.DistinctFieldAutoAssignment property. See the warnings for details on how to modify existing code. For backwards compatability this new function is not required and is by default de-activated using the #if UseAutoAssignment compilation variable. To migrate enable the compilation argument and modify any code as necessary.
  • Displays a more descriptive error message when an error occurs for setting a field marked with the FieldMapping attribute.
  • Support for .NET Framework 1.1 has been removed.
  • Added support for transaction isolation levels. Added overloaded method for Database.Transactions.Begin().
  • Added properties DatabaseObject.GrandParent and DatabaseObject.GrandParentDistinctValue.
  • Added support for SQL Server Compact Edition databases.
  • Modified SQLAutoIncrements to use SCOPE_IDENTITY() rather than @@IDENTITY.
  • Updated DatabaseObjectsLockController.Lock() to throw ObjectAlreadyExistsException rather than the generic DatabaseObjectsException.
  • Corrected an issue with the Generic.DatabaseObjectsVolatileListUsingAttributes, Generic.DatabaseObjectsVolatileUsingAttributes and DatabaseObjectsVolatileUsingAttributes classes so items are loaded correctly when using attributes.
  • Fixed checking of a unique key when saving an object and the object does not contain the unique key to check against.
  • Added a set of new classes that allow the collection classes to be setup with attributes rather than overriding the DatabaseObjects functions. i.e. DistinctFieldName, DistinctFieldAutoIncrements, KeyFieldName, OrderBy, Subset, TableName and TableJoins. To use attributes simply use the appropriate DatabaseObjects super class with the UsingAttributes suffix. i.e. DatabaseObjectsListUsingAttributes and the appropriate attributes. Attributes not specified default to String.Empty or Nothing. Attributes Table() and DistinctField() are mandatory. Attributes KeyField(), OrderBy(), Subset() and TableJoin() can be specified if required. For example:
    <DistinctField("CustomerID", bAutoIncrements:=True)> _ <Table("Customers")> _ <KeyField("CustomerName")> _ <OrderBy("CustomerName")> _ <TableJoin("CustomerPostalAddressID", "Addresses", "AddressID")> _ Public Class Customers Inherits Generic.DatabaseObjectsListUsingAttributes(Of Customer) ..
    If more control is required the functions can still be overridden and modified and the equivalent attribute removed.
  • Fixed '\' check for MySQL - should have returned '\' for non-MySQL databases rather than Nothing. Only affected version 2.15.
  • Any MySQL input that includes a backslash character is now correctly interpreted and not considered an escape character. This also helps protect against SQL injection attacks.
  • Added support for "... WHERE Field NOT IN (SELECT ...)" via the SQLConditions.AddNotInSelect() functions
  • Added support for Pervasive SQL. Thanks to Ryan Rogers for his contribution. See for more details.
  • Added class DatabaseObjects.SQL.SQLInsertFromSelect which allows data to be copied from one table to another.
  • Added event StatementExecuted to the Database.ConnectionController class. The primary purpose is to simplify logging of SQL statements executed.
  • Throws a descriptive exception if the Connection.Start() or Transactions.Begin() functions have not been called before calling Connection.Execute() or Connection.ExecuteNonQuery().
  • Correctly replaces quote characters with \' for MySQL.
  • Modified Database.Transactions.Rollback() so that rollback can be called multiple times and no error will occur.
  • Added SQL expressions that can be used for generating complex arithmetic expressions as part of UPDATE and SELECT statements. Depcrecated and removed SQLUpdate.CopyFields.Add. Modify existing code to call Fields.AddCopy instead of CopyFields.Add.
  • Modified Generic.ObjectReference to allow returning of NULL via DistinctValue if not set.
  • Includes support for setting and loading an individual field for the object's record.
  • LockRecord returns the fields that have been locked.
  • Includes support for a data property for DatabaseObjectsVolatile collections.
  • Fixes SQL.SQLAlterTable for MySQL so that it correctly adds and modifies columns.
  • Exposes custom DatabaseObjects exceptions in the Exceptions namespace.
  • DatabaseObjects has an additional constructor to correctly load and initialize an IDatabaseObject object from a set of database fields.
  • Database provides a Connection class that can be used to open and close connections that the library uses. This ensures that only one connection is ever used and an additional connection is not required. This avoids potential connection deadlock issues.
  • Utilises framework 2.0. Includes additional generic collection classes in the DatabaseObjects.Generic namespace.
  • Includes object equality which has been implemented in DatabaseObject via operator overloading.
  • Version 2 of the library is accessible via the DatabaseObjects2.vbproj project and the compiled dll is in the bin2 folder. DatabaseObjects for Framework 1.1 is still included in the download.
  • Initial Release