New ConnectionType support and Improvements to selecting Statements

In the past, there has been a lot of confusion about how Data Abstract picks the appropriate statement for a data table, if more than one connection is defined in a schema, and a data table provides different statements for different connections. A few new properties have been added to ease this:

  • A ConnectionType property has been added to the connections. This property is user-editable, and arbitrary values can be used to logically group connections that use similar statements, whether based in SQL dialect, database structure or any other factor together. When initially defining connections in Schema Modeler, this value will be pre-filled based on the driver or aux-driver - connections to SQL Server (whether through ADO SDAC or MSSQL.NET would default to, say, "MSSQL", while connections to Oracle would default to "ORACLE").
  • A matching ConnectionType property has also been added to the statements, specifying (optionally) which type of connections the statement is meant to apply to.
  • Finally, a boolean Default property has also been added to the statements, marking a single statement per data table as the default.

At runtime, the Data Abstract Service will use the following logic to locate the appropriate statement to retrieve a data table:

  • Check for a statement matching the active connection's ConnectionName (as was done in version 4.0 and below).
  • Check for a statement matching the new ConnectionType of the active connection.
  • Check for a statement marked as Default.
  • Check for a statement matching the "default" connection (which was the second and last step in version 4.0 and below).
  • If no statement is defined at all, use AutoSQL, as described above, to generate the appropriate SELECT code on the fly.

It is worth noting that AutoSQL can be applied in two different scenarios, either if step 1 through 4 found a statement that was configured to use AutoSQL (in other words, might provide a column mapping, but no SQL code), or if, as described in step 5, no statement was defined for the data table. In this second case, no column mapping will be applied, and the fields defined in the data table must exactly match with the underlying database.

This system should make it a lot more intuitive to set up schemas with multiple statements and connections, while at the same time providing new flexibility to allow scenarios that were not easy to achieve before. For example, you can now have several groups of connections sharing separate statements, where two or more connections use statements that map to a "legacy" database layout, while others reflect a new database format.

The important thing to realize is, that in the most general case of accessing a single database, you don't need to worry about all of this at all, as Data Abstract will do the right thing for you - but if you do need the flexibility for accessing different database formats or layouts, you have all these options at your disposal.