Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Using Multiple Databases with OpenAccess ORM (MSSQL specific)
Programmer's Guide > OpenAccess ORM Classic (Old API) > OpenAccess Application Scenarios > Using Multiple Schemata with OpenAccess ORM > Using Multiple Databases with OpenAccess ORM (MSSQL specific)

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

MSSQL supports the transparent use of multiple databases over one connection. This allows to separate data into different physical databases (even on remote servers), and permits to combine queries over multiple databases for improved scalability and load balancing. It also allows to store data into different databases over one connection without explicit usage of a distributed transaction mechanism. OpenAccess ORM allows to use multiple databases from one object scope at the same time, much in the same way as it allows the usage of multiple database schemata. In order to specify the database, it's name must be prepended to the schema name of the table name extension. When a database is specified, the schema must be specified as well.

Multiple Databases

Assuming you have two persistent classes Person and Address.

Copy Code
<class name="Person">
    <extension key="db-table-name" value="Database1.schema1.TabPers" />
</class>
<class name="Address">
    <extension key="db-table-name" value="Database2.schema1.TabAddr" />
</class>

With the mapping as given in the example, the instances of the Person class would be persisted into the TabPers table of schema schema1 in Database1, and the Address instances would be held in the TabAddr table of schema schema1 in the database Database2. Please note, that referential constraints are not possible between two different database as they can only be enforced by the database system within one database.

In order to support a more flexible naming of databases, the database names given as part of the table specification are treated as logical database names. For all schema migration algorithms and comparisons, the logical database name is used, but whenever OpenAccess ORM generates SQL statements, a mapping from the logical database name to a physical database name is attempted. When the mapping does not contain an entry for the logical name, the logical name is also used as the physical name. The database logical to physical name mapping is specified in the backend configuration node of the application configuration file.

Databases logical to physical mapping

Assuming the example from above we need to specify that Database1 actually has the physical name of "My beloved Persons".

Copy Code
<backendconfiguration id="mssqlConfiguration" backend="mssql">
    <mappingname>mssqlMapping</mappingname>
    <databaseMapping>Database1='My beloved Persons'</databaseMapping>
</backendconfiguration>

When more than one logical database name is to be mapped, additional mapping nodes can be created as databaseMapping1, databaseMapping2, etc by just appending increasing numbers, starting with 1.

Mapping logical database names to physical database names can also be used to map a logical database name to a physical database name on a different MS SQL Server. To use another server, it's name has just to be prepended to the physical database name and be separated by a dot.

Logical Server name mapping

Assuming the example from above, but this time using the addresses from a different server and database name.

Copy Code
<backendconfiguration id="mssqlConfiguration" backend="mssql">
    <mappingname>mssqlMapping</mappingname>
    <databaseMapping>Database1='My beloved Persons'</databaseMapping>
    <databaseMapping1>Database2='AddressServer'.'MyAddresses'</databaseMapping1>
</backendconfiguration>

This configuration would use the table TabPers of schema schema1 of the database "My beloved Persons" of the server the actual connection was made to for storing Person instances, and use table TabAddr of schema schema1 of the database "MyAddresses" on server "AddressServer" to hold the Address instance data.

Please note, that all restrictions of linked servers apply, especially that the name of the remote database server must be resolvable and reachable by the server the connection was made to.

OpenAccess ORM does not create linked databases during schema update, they must exist beforehand.