EntitySpaces Multi-Provider Mode

Multi-Provider mode lets you run the same binary code against multiple back-end databases. For example, the EntitySpaces Demo runs against Northwind for MS SQL Server, MS Access, MySQL, and VistaDB. Our NUnit Test Suite also takes advantage of this. It does take some up-front planning. The database schema must match, and that may mean some compromises. Northwind is well suited to this, but not all schema are.

Using multi-provider mode is not necessary if both databases use the same provider, e.g., you have a Test database and Production database, both using MS SQL Server. Do not check "Target Multiple Databases" on the "Advanced Options" tab of the Generated Master template. In that case, all you need to do is change your config entry. You could even have multiple config entries and switch them at runtime. "Target Multiple Databases" is only used when the back-end requires a different EntitySpaces provider, and only in combination with the MetadataMap template.

You would not use multi-provider mode if you need to hit two different databases, with different schema, and need to consolidate the results for presentation. You would do a full generation for each, have a config entry for each, distribute both providers, and do your connection switching at runtime.

The loosely coupled EntitySpaces Providers let you swap out back-end databases by regenerating, changing the config, and distributing a different Provider assembly. If you have a situation that demands running the same application against different databases, but not at the same time, then use the information below to help determine if multi-provider mode is the answer. You may decide it would be better to fully generate BusinessObjects for each in separate projects, and distribute separate solutions, compiled against each.

Matching Schema

• The table and column names must match.
  • For MySQL's all lower-case table names, you can use MyGeneration to Alias the offending table names.
• Datatypes need to match. Implicit conversion in .NET makes this fairly forgiving.
  • Northwind's Products Discontinued column is defined as a bit in MS SQL Server, True/False in MS Access, and TINYINT(1) in MySQL. All can be treated as a bool by .NET.
  • All the supported databases have some form of auto-incrementing (identity) primary key. EntitySpaces handles this seamlessly.
• Some datatypes are unique to a particular database, or map differently, and will need to be avoided.
  • The timestamp column maps to a byte[] in MS SQL Server, a large integer in VistaDB, and a DateTime in MySQL. Consequently, EntitySpaces cannot handle the built-in concurrency checking for SQL Server or VistaDB.
  • Not all databases have a Guid datatype. A Guid primary key with a default of newid() cannot be used, if one of the databases does not support it.

You'll have to experiment with other datatypes to see how well they behave. Using multi-provider mode may mean reducing to the lowest common denominator amongst the datatypes offered.

Multi-Provider Mode Walk-through

We'll go through the steps used to create the EntitySpaces 2007 Demo.

Generated Master template

• Pick one of the databases. We chose MS SQL Server.
• Set your MyGeneration Default Settings to connect to it.
• Run the Generated Master Template.
• Make all Basic and Advanced selections as you normally would.
• Check "Target Multiple Databases" on the "Advanced Options" tab.
• Click "OK" to generate.

This generates the standard classes. The only difference is that the MultiProviderMode property in each Metadata class will now return true. This helps EntitySpaces coerce database columns to match the generated properties. The mapName for any class created using the Generated Master template is always "esDefault".

MetadataMap template

The template is found under the EntitySpaces C# or VB "Generated" folder in MyGeneration's Template Browser. For each of the other databases (MS Access, MySQL, and VistaDB):

• Set your MyGeneration Default Settings to connect to it.
• Run the MetadataMap template.
• Enter a mapName ("esAccess", "esMySQL", or "esVistaDB", respectively).
• Select the same Tables/Views.
• Click "OK" to generate.

This generates an additional file for each table/view. It is a partial class on the matching generated Metadata class. It maps the database specific stored procedure names and column datatypes using the mapName you provided.

Configuration

The mapName for any classes generated using the Generated Master template is always "esDefault". You provided the mapName for each of the other databases. For each connection defined in the config, you indicate which mapName to use with the "providerMetadataKey" attribute.

Switching Connections in Code

Config file method. The Demo uses an App.config to set up the EntitySpaces connections. When using a config file, the default connection is set in the file, and cannot be changed in code. Each time you instantiate a collection or entity, you should specify the connection name to use.

In the Demo, we provided a menu option to set the connectionName. That connection is used for all examples until it is changed again from the menu.

Alternate configless method. Not demonstrated in the Demo is the EntitySpaces configless technique, where the EntitySpaces configuration information is removed from the config file, and set in code. This has an additional advantage in that you can change the default connection in code. Setting the connnectionName for each instantiated collection or entity becomes unnecessary. You would just need to change the default connection in the menu click event.

With the latest release of EntitySpaces, you can have the best of both worlds. Use the configless technique to gain the advantage of setting the default connection. And, use the "AppSettings:" prefix in the connectionString to reference a standard connection string in your config file.

Database Specific Syntax

Using multi-provider mode does not preclude you from using database specific syntax in your Custom classes. You can use a switch/case that checks which provider is currently being accessed. In the Demo's Employees Custom class, we did this to demonstrate ExecuteNonQuery. The same binary code is running against all databases, and yet, MySQL specific syntax is used to create the SQL text sent in the method when required. Notice below that esParameters is also provider independent. No database specific parameter prefixes (like '@' for SQL Server or '?' for MySQL) are needed when naming your parameters. The correct prefixes will be added. In the raw SQL in the case statements, database specific syntax must be used, including column delimiters, parameter prefixes, etc.