1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Technical Overview:
The design utilizes a code generator to rapidly produce 90-100% of the O/R-mapping classes (Business Objects) for database persistence. It generates lightweight classes in VB.Net or C#, which inherit most of their functionality from abstract framework classes. These provide a structured environment for customization, business logic and reusable code, including standardised region names.

The recommended choice of architecture employs parameterized queries as the underlying technology (as opposed to stored procedures or sql strings), in order to dynamically implement the set of trivial database operations known as CRUD methods i.e. atomic-level select/insert/update/delete. CRUD methods support fully functional O/R classes, and typically constitute between 90-100% of stored procedure requirements.

This design brings 4 key benefits, specifically:
1. Cost - Rapid development & maintenance via code-generation and structure.
2. Quality – High level of code consistency, with minimum scope for human error
3. Portability - Keeping the number of stored procedures to a minimum (or nil) greatly improves database portability and maintainability. When changing platform, tables can usually be imported, but stored procedures/functions/views have to be manually rewritten.
4. Concurrency – Dynamic update statements are inherently less likely to produce data conflicts from concurrent user updates. Note: concurrency-proof option is somewhat depreciated, as internal dictionarys are memory intensive.

Parameterised queries maintain equality with stored procedures on 3 other key issues:
1. Performance – The parameters separate the data from the SQL statement, allowing it to be pre-compiled and cached by the database in exactly the same way that a stored procedure is, giving it an identical performance characteristic.
2. Security – This technique is not vulnerable to SQL injection attacks (and errors), because it uses the same driver-specific parameter collections to transmit the data.
3. Transactional – Can perform large batches of CRUD operations within a transaction context to implement a transactional behavior, or just to share an open connection for performance.

Stored procedures:
Sometimes a few additional stored procedures are provided for such things as complex reporting, database-intensive tasks, or rare performance hacks. Theoritically this logic can always be implemented in the .Net layer using CRUD methods, but is occaisonally it is well-suited to a stored procedure implementation.

It is very straightforward to implement the stored procedure calls in the business layer on an ad-hoc basis, using very simple helper methods that take the procedure name and an optional list of named parameters (in the form of a Dictionary object). From within the class, you can use MakeList(...) to create business objects of that type, or use DataSrc.ExecuteDataset(...) for ad-hoc queries. See also DataSrc.ExecuteQuery(...) and DataSrc.Local.ExecuteReader(...) overloads.

Alternatively, at the time the classes are generated, one can select an option that causes the classes to be generated using a different pattern, such that stored procedures are generated for all CRUD methods, and the dynamic query capability is absent. There is no advantage to using this approach, other than to fulfill a possible DBA requirement that all database activity must use stored procedures.

DBNull Equivalent values
Whenever data is accessed using the generated business objects, the troublesome issue of null values in the database is taken care of automatically. Specifically, this occurs when the object is loaded from a DataReader or DataRow, and also when a set of parameters are produced for an insert or update operation. A value has been nominated for each basic data-type in .Net to represent null values, and the framework maps these values to and from a database null. Examples include: DateTime.MinValue; Double.NaN, Integer.MinValue, Decimal.MinValue, Guid.Empty. String and binary data use ‘Nothing’ (null) to represent a database Null. It is possible to use nullable values, as all the same helper methods are there (e.g. GetInt/GetIntNullable), pretty much just need to change the code-gen templates or manually change the generated code for the members, properties, and load-from-dataset/reader methods.

Caching – Default/Optional Behavior
By default, the class generator creates classes that include a useful caching mechanism. This is quite useful for most situations, except where:
1. The table data is currently (or potentially) too large to be stored in application memory
2. The table data is primarily write-only e.g. an error log.
3. A number of independent applications update the same database, with no common layer such as a webservice to co-ordinate or clear the cache e.g. a web-farm deployment scenario.

If a class employs caching for a particular table, then the associated class will expose a static property called “Cache”, returning a custom collection. In such a case, any type of select-where query on that table is best implemented as a custom index on the custom collection returned by the cache. Such an index is always supplied for the primary-key column, (typically called “GetById”) returning either a single object or null, depending on the value(s) supplied. In such a case, if you wanted to look up a single record, use:
	Dim example As CExample = CExample.Cache.GetById(exampleId)
instead of:
	Dim example As New CExample(exampleId)

In order to enforce consistent behavior, only one of the two options described above will be possible. In other words, either the cache property will not exist, or the constructor that takes a primary key value will not exist, depending on whether or not the class employs caching. This is not mandatory, but it helps ensure that the cache will be used if it exists.

At the time the class is generated, you can nominate other columns of interest (such as a foreign key column, status column, or boolean-valued column) that could be used for additional indices, and the class generator will automatically write some code to implement that custom index. These types of index (on a FK) return a list (as opposed to the single instance for a unique/PK index), which may be empty, depending on the value of the foreign key etc supplied. This index can then be used instead of executing a select-where query against a database, and is substantially more efficeint e.g.
	Dim exampleList As CExampleList = CExample.Cache.GetByStatusId(statusId)
instead of:
	Dim exampleList As CExampleList = New CExample().SelectByStatus(statusId)

As with the primary key index, only one of these two options will be provided, depending on whether caching is employed, and only the latter case involves database activity. Both types of index provide an effective space-for-time tradeoff. They both only require a single pass through the entire collection to initialize the index (the first time the index is used), and thereafter provide virtually instantaneous query results by looking up the results from a private hash-table. The “Cache” property stores the main collection, which is created using a single called to the database (select-all statement). Whenever a cache is employed, you will only ever need to do a select-all operation on the actual table, and therefore you typically wont need to implement any indices on that table within the database itself, since the code only ever uses the in-memory, .Net indices.

Note that you can cascade these properties to implement complex combinations of filters e.g.

Generalized Architecture of Framework (.Net Projects & Libraries)

Core Abstract Classes (Framework Level)
The auto-generated classes that make up the business layer (blue) will typically inherit from just one of the 3 main classes shown in yellow below. The lowest one (CBaseSmart) is the default choice for a common framework, but sometimes the stripped down version above it (CBaseDynamic) is used if memory efficiency is an issue. The third choice (CBaseSp) is only employed if stored procedures are a required, i.e. the use of dynamic queries are explicitly prohibited by your database administrator.

Variations on Abstract Classes (by nature of the Primary Keys)
The choice between the 3 main bases classes described above (yellow) establishes the basic framework, but this choice is then multiplied by about 6 independent variations, according to the nature of the primary key, and how many columns it spans (for the specific table that the business object seeks to model. Specifically, the main types are:
1. Single-column primary key, with values (sequence) generated by the database. (This is by far the most common case).
2. Single-column primary key, user supplies the value e.g. 4-letter codes.
3. 2-column primary key, with no other columns to update
4. 2-column primary key, plus some other columns exist in the table
5. 3-column primary key, with no other columns to update
6. 3-column primary key, plus some other columns exist in the table
The first 2 variations inherit directly from the selected base class. They both have constructors that accept a same single-value i.e. the primary key, for selecting and updating a single record. The only difference is that the former has a read-only property to represent the primary key value (since only the database can set its value on insert), whereas the latter has a read-write property (since the user must set it before insert). If the class inherits from CBaseDynamic, there is one extra boolean property that controls whether the PK column is included in the insert statements that are generated.

The other main variations require 2 or 3 parameters be supplied to the constructor in order to be able to select a single record, according to the number of columns that constitute the primary key. These are referred to as many-to-many (M2M) and 3-way tables respectively (also known as Associative tables). In theory there is no limit to the number of columns that a PK can span, but 2 is a minority, 3 is very rare, and 4 has not been encountered to date (can code up another base class if ever required).

Additionally, if these multi-column primary keys are the only columns in the table, then there will never be a need for an update operation (only insert), and there is never a need to select a single record based on the primary key either. This can be modelled with a slightly different base class, having less constructors and a slightly different implementation of the Save method, which only does an insert.

1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Last edited May 26, 2010 at 1:18 PM by jeremyconnell, version 19


No comments yet.