This project is read-only.
1. Quick Start Guide
2. Useful Tips for Schema projects
3. Technical Overview

Relationship-Mapping Properties
Use the Relationship-Mapping tab (described in 1. Quick Start Guide) to generate code to model all the relationships in your schema, taking care to accurately identify the correct foreign keys involved. Note that several different standard patterns are used, depending if one or both entities use caching.

Once you have these relationships implemented, you can build on them to implement more complex properties, depending on your application/business logic requirements. For example, you might need to get data by navigating across several child relationships, or implement recursive behaviour with respect to parent relationships, such as CategoryFullName.
  Public ReadOnly Property CategoryNameFull(Optional ByVal delimiter As String = "::") As String
      If IsNothing(Parent) Then Return CategoryName
      Return String.Concat(Me.Parent.CategoryNameFull, delimiter, Me.CategoryName)
    End Get
  End Property

Databinding Tweaks
For databinding to datagrids, you might want to add extra properties to account for the fact that controls do not easily support the indirection (fullstop) operator, e.g. "Category.CategoryName" doesnt work (because it contains a fullstop), but you can add a custom readonly property called "ParentCategoryName" which internally returns the same expression, and which can be used in late-binding. Alternatively, one can avoid the use of late binding by using dynamic usercontrols instead of late-binding, as this offers other advantages such as performance and compiler checks.

Bulk Database Activity
Generated list classes all include bulk methods such as SaveAll and DeleteAll, which in turn use low-level methods for transactional bulk operations (e.g. CDataSrc.BulkSave). For best performance you should explicitly clear the cache for all tables involved (if those tables use caching), immediately before the bulk save/delete, to avoid having to maintain the cache during the bulk operation. You may wish to inspect the implementation of these bulk methods for examples of how to use ADODB transactions.

Xml Data Columns
If a column stores xml data, make the corresponding property protected, and instead expose an instance of the class that models the root node of that schema, using a private member and public read-only property. The member will only be instantiated when the property is first hit (lazy-loading), and the save method will check if it has been instantiated, and if so, should serialize this back to the xml string and store it in the protected column property, to be included in the update statement as normal. This provides a seamless integration of relational and xml data from the perspective of the application layer.
 'Xml presented using high-level objects (Note: Save method should serialise this object back to the string colum, if property has been hit)
  Private m_sideImages As XmlImages.CImageSet
  Public ReadOnly Property SideImages() As CImageSet
      If IsNothing(m_sideImages) Then
        m_sideImages = New CImageSet(Me.CategorySideImageName) 'Uses a dom object and custom classes to parse & present the xml
      End If
      Return m_sideImages
    End Get
  End Property

BLOB columns (large objects)
If a column stores a large amount of data, such as a file upload, it is generally more efficient to treat this column separately from the others, by not loading it automatically. Firstly, the SelectColumns property should be overridden to list all columns except the BLOB column (instead of *), so that blob data is not pulled from the database by default. Secondly, the load methods (from datareader/dataset) should be modified by commenting out the line that sets the member variable (also, move these methods from the auto-generated partial class to the customisable one). Finally, add some custom methods to Select and Update just that column, using AbstractionLayer helper classes to assist with the

Encrypted Columns
There are some helpful methods to assist with data encryption, including one pair to very quickly encrypt/data data using an XOR (bitwise) encryption, and another using the triple-des algorithm. Both features obtain their keys from standard configuration settings, and both offer some additional overloads for an alternative key or provider, plus overloads for string, byte-array, and stream-based interfaces. See AbstractionLayer.CBinary.Encrypt and other similar methods. It is recommended that you have a pair of properties that differ only by a suffix, with one being the encrypted/decrypted version of the other, and one exactly reflecting the actual column. If the encryption is one-way (e.g. password hashing the SchemaMembership.CUser), the one property will be readonly and the other will be writeonly.

To use these in the data model, there are 2 general approaches. The first is to decrypt the data when it is read into the business object, and encrypt it at the point where the parameters are assembled for an insert or update. The disadvantage is that decrypted data is stored in memory, which is less secure, so the other (suggested) approach is to have a second read/write property that presents/accepts a decrypted version of the data, and performs the encrypt/decrypt in the get/set before passing it to the original property. For example, if you had a column and corresponding property called “CreditCardNumberEncrypted”, then you would add an additional property (in the customization side of the partial class) called “CreditCardNumberDecrypted”, which does the conversion on-demand.

Audit Trail
A schema project can extend one of the base classes (such as CBaseDynamic), adding a thin layer of extra functionality such as an Audit trail. An example of this is given in SchemaAudit, which introduces a couple of mustoverride methods to load & serialise the objects, and it also overrides the save & delete logic to record a snapshot of the data before it was deleted or modified, along with some identifiying information. The audit trail provided is very easy to use - simply reference SchemaAudit, and when generating classes, don't untick the checkbox called audit-trail (except for logging tables etc). Beware that audit trails can get very big over time, so may need to consider exporting and compressing (or deleting) old audit trail information.

Inheritance in the data model
Occasionally there is a need to map a table to an abstract class, having several derived classes. Typically, an integer-typed column known as a differentiator is used to control which concrete class should be instantiated when the records are read out of a database.

For example, one website project I had had a table called "tblSpots", consisting of a SpotId (primary key), a SpotType (differentiator), and a SpotText (long-text) columns. The SpotType would determine how to interpret the data in SpotText, which might be: text/html; a filename (image uploader); or xml data from a particular schema. When the records were loaded from the database, different classes were instantiated according to the value of the differentiator, all having the same abstract base class. In each case, the SpotText column was scoped as a protected property, and the data instead exposed using a meaningful alias property of type string, such as "FileName" or "Text", and in the case of xml data this was exposed as a class representing the root node of the schema (auto-generated with the XSD class generator).

Another example I have encountered was a table that implemented a tree-structure with a self-referencing parentId column, and also had a differentiator column (i.e. FundId, FundName, FundType). There were a number of different fund types, with business rules controlling which types could parent which other types. These business rules were too complex to easily enforce with a normal database constraint, and so these constraints were instead implemented by the different classes that modelled each fund type, all of which shared the same common abstact base class, but also modelled the properties that were unique to that fund type, such as various strongly-typed collections of child funds.

To implement inheritance, the following approach is recommended:
1. Auto-generate the class from the table as normal
2. Mark the class as abstract
3. Mark the differentiator column as protected, read-only, and must-override (abstract). Discard the member variable that was used to store this information.
4. Create at least one class to inherit from the abstract base class.
5. Modify the two factory methods in the base class that are responsible for instantiating objects from a datareader and a dataset. These methods now need to "peek" at the datasource to read the value of the differentiator column, and use that information in a switch statement to decide which concrete class to instantiate. The datasource is then supplied to the constructor as normal, and a specific object returned.
6. If required, a collection class can be created for each derived class, e.g. to enforce type-based constraints on parent/child relationships.

Winforms & WebServices Efficiency
When deployed within an intranet environment, a win-forms application can usually be configured to connect directly to the database, and this will typically give better performance that connecting via a web-service. However, for extranet or remote deployments, a web-service interface is the only option. Fortunately this scenario can easily be configured – simply set the driver to be “webservice”, and the connection string to be the URL where the web-service is deployed.

The problem with this scenario is that even on a fast connection, there can be a second or so latency between requests, so if your application is doing a lot of small calls to the database, it can be very slow. To optimize for this environment, it is firstly advised to use the caching option, so data is stored, indexed, and retrieved locally, once the cache is initialized. Secondly, it is advisable to initialize the various data caches simultaneously, using a single call to the web-server, while the application is starting up.

To do this, you need to create a data-transport class to store the data that will be sent i.e. one collection class for each cached table. This is best defined in the schema project, since that project is already present on both the client and the server, allowing it to be serialized/de-serialized correctly. You can then add an application-specific web-service to transfer the binary data. The server side will use a method in the Framework project called CBinary.CompressToBytesAndZip(), which serializes the data and compresses it, returning a byte array to send. The client side will use the companion method CBinary.DecompressFromBytesAndUnzip(), and cast it back to the original type, which is the transport class described above. Finally, the members of this class are used to directly initialize the client-side cache for each applicable table. Note: See also CBinary.Pack and CBinary.Unpack, for encrypted versions of the same thing.

Similarly, for bulk save/delete operations, you should either use the SaveAll/DeleteAll methods generated with the list classes, or use the low-level equivalent methods (CDataSrc.BulkSave and CDataSrc.BulkDelete) if there is a mixture of classes involved.

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

Last edited May 25, 2010 at 4:02 PM by jeremyconnell, version 24


No comments yet.