DataAccessAdapter Functionality

For the Adapter template group, LLBLGen Pro will generate two Visual Studio projects. This section describes the DataAccessAdapter class which is located in the database specific project and which is the class that performs all database activity for entities, typed lists, typed views and stored procedure calls: the DataAccessAdapter object provides the persistence service to the developer.

For successful usage of the Adapter template group, it's important to understand which functionality is offered by the DataAccessAdapter class and this section shows a brief overview of that functionality. In-depth discussions of various aspects of the functionality can be found in the remainder of the Adapter documentation.

For the full API offered by DataAccessAdapter, please see the LLBLGen Pro runtime framework reference manual (separate download) for the interface IDataAccessAdapter and the class DataAccessAdapterBase.

Functionality

The DataAccessAdapter class is the single class you'll need to interact with the database to fill entities, store changed data, call a stored procedure, start a transaction etc. Below is a brief overview of the various aspects of the DataAccessAdapter class.

Connection strings

The DataAccessAdapter reads the connection string automatically from the *.config file available, however you can also supply a connection string to a constructor overload of DataAccessAdapter. This means that you can target different databases under different users on a per-call basis.

Multi-tenancy support: catalog specific persistence info (SQL Server, MySQL)

Adapter uses catalog specific persistence information. This means that the catalog name is generated into the persistence information. The result of this is that although the connection string might contain a different catalog name, the queries will use the catalog name generated into the persistence information for each field and database object (table/view).

This can be very helpful in the scenario with multiple catalogs per project, but might not be what you want in some situations. You can overwrite the catalog name to be used on a per-call basis by specifying catalog name overwriting information to the DataAccessAdapter's constructor or set some properties later on.

Specifying overwrites in code

Create a new CatalogNameOverwriteHashtable (type provided by the SD.LLBLGen.Pro.ORMSupportclasses assembly). You can specify a CatalogNameUsage value, but this isn't required. You then add key-value pairs, where the key is the catalog name to overwrite and the value is the name to overwrite it with.

If you specify * as key, all catalog names will be set to the name specified as value, if CatalogNameUsage is set to CatalogNameUsage.ForceName. The created CatalogNameOverwriteHashtable is then passable to the DataAccessAdapter constructor or you can set the DataAccessAdapter.CatalogNameOverwrites property to an instance of this special hashtable.

Example. This will overwrite CatalogName with MyCustomer1 in the query executed by the instantiated adapter.

// initializing the overwrite table. 
var overwrites = new CatalogNameOverwriteHashtable();
overwrites.Add("CatalogName", "MyCustomer1");

// ... later in the code I pass it to an adapter instance for usage
// in the query
using(var adapter = new DataAccessAdapter())
{
    adapter.CatalogNameOverwrites = overwrites;
    
    //... further query code.
}

Specifying overwrites in config files.

It's also possible to specify overwrites in the application's .config file. Please see Application configuration through .config files for more details.

Multi-tenancy support: Schema specific persistence info (DB2, Oracle, PostgreSql, SQL Server)

Adapter uses schema specific persistence information. This means that the schema name is generated into the persistence information and that the queries will use the schema name generated into the persistence information for each field and database object (table/view).

This can be very helpful, but might not be what you want in some situations, like when you've defined global synonyms for tables in a particular schema and you want the generated code to target these synonyms in production. Having the ability to change the schema name at runtime can also be helpful if you want to target multiple schemas with the same schema objects.

You can overwrite the schema name to be used on a per-call basis by specifying schema name overwriting information to the DataAccessAdapter's constructor or set some properties later on. This all sounds familiar from the previous catalog name overwriting paragraph, so the schema name overwriting options look the same as for the catalog name ovewriting options.

Specifying overwrites in code

Create a new SchemaNameOverwriteHashtable (type provided by the SD.LLBLGen.Pro.ORMSupportclasses assembly). You can specify a SchemaNameUsage value, but this isn't required. You then add key-value pairs, where the key is the schema name to overwrite and the value is the name to overwrite it with.

If you specify * as key, all schema names will be set to the name specified as value, if SchemaNameUsageSetting is set to ForceName. The created SchemaNameOverwriteHashtable is then passable to the DataAccessAdapter constructor or you can set the DataAccessAdapter.SchemaNameOverwrites property to an instance of this special hashtable.

Example. This will overwrite dbo with MySchema in the query executed by the instantiated adapter.

// initializing the overwrite table. 
var overwrites = new SchemaNameOverwriteHashtable();
overwrites.Add("dbo", "MySchema");

// ... later in the code I pass it to an adapter instance for usage
// in the query
using(var adapter = new DataAccessAdapter())
{
    adapter.SchemaNameOverwrites = overwrites;
    
    //... further query code.
}

Specifying overwrites in config files.

It's also possible to specify overwrites in the application's .config file. Please see Application configuration through .config files for details.

Command timeouts

Sometimes a query can take a long time to complete, for example with data-processing stored procedure calls. With Adapter, you can set the timeout for each query on a per-call basis, using the property DataAccessAdapter.CommandTimeOut. The default is 30 (seconds). Firebird and SqlServer CE don't support command timeouts and a CommandTimeOut value is ignored.

Connection control

It can be useful to open a connection and keep it open for multiple actions and then close it. This can give extra performance, especially in code where multiple database fetches are used in one routine. The property KeepConnectionOpen is used to set this behaviour.

Recursive saves

The DataAccessAdapter class supports recursive saves, which is the default. This also works with entity collections. The logic automatically determines the order in which actions need to take place.

For example: - Instantiate a Customer entity, add a new Order object to its Orders collection. Now add OrderDetails objects to the new Order object,. You can simply save the Customer entity and all included new/dirty entities will be saved and any PK-FK relations will be updated/synchronized - Alter the Customer object in the example above, and save the Order object. The Customer object is saved first, then the Order and then the OrderDetails objects with all PK-FK values being synced

This synchronization of FK-PK values is already done at the moment you set a property to a reference of an entity object, for example myOrder.Customer = myCustomer, if the entity (in this case myCustomer) is not new. Synchronization is also performed after a save action, so identity/sequenced columns are also synchronized.

Fetching/deleting/saving entities/typed lists/typed views

The DataAccessAdapter object offers full support for fetching/deleting/saving entities and entity collections and filling typed lists and typed views. It also supports, as SelfServicing, directly updating/deleting of entities in the persistent storage.

Calling stored procedures

Calling stored procedures is fully supported by the DataAccessAdapter object. The DataAccessAdapter object controls the transactions, so you can now call a stored procedure inside an existing transaction.

Transactions

Adapter fully supports ADO.NET transactions and System.Transactions based distributed transactions. You can start a transaction using the DataAccessAdapter class and all actions performed after that are executed inside that transaction. It doesn't matter if you fetch collections, typed lists, delete / save entities or call a stored procedure.

All multi-entity affecting actions like recursive saves and the save of an entity collection, or the deletion of a set of entities, is, as in SelfServicing, always performed inside a transaction: if an existing transaction is present, that transaction is used, otherwise a new transaction is created and used.

Intercepting activity calls

The DataAccessAdapter class has functionality on board to let you perform actions during various stages of a process, for example right before a save action, or when an entity is fetched. These methods start with 'On' and are defined as virtual and are by definition implemented as an empty method (no-op). Please consult the LLBLGen Pro reference manual and inspect the DataAccessAdapterBase class' members for the details about these methods. DataAccessAdapterBase is the base class for every DataAccessAdapter class.

If you want to perform a given action when one of these methods are called, you can override them in the generated DataAccessAdapter class, preferably using the methods discussed in Adding your own code to the generated classes. Please consult the LLBLGen Pro reference manual, available in the LLBLGen Pro installation folder, for details about these methods (DataAccessAdapterBase.On..) when they're called and what is passed in.

ArithAbort flag (SqlServer only)

If an entity is saved into a table which is part of an indexed view, SqlServer requires that SET ARITHABORT ON is specified prior to the actual save action. You can tell LLBLGen Pro to set that option, by calling the method DataAccessAdapter.SetArithAbortFlag(bool) method. After each SQL statement a SET ARITHABORT OFF statement will be executed if the ArithAbort flag is set to true. Setting this flag affects all INSERT statements following the call to SetArithAbortFlag(), until you call that method again.

DQE Compatibility mode (SqlServer only)

The SQL Server DQE is used for all SQL Server versions and it therefore uses a compatibility mode to generate the SQL which is compatible with the used database version. To set the default compatibility mode of the SqlServer DQE in code, you can use the DataAccessAdapter static method SetSqlServerCompatibilityLevel, as shown in the following example which sets the compatibility mode to SqlServer 2012:

DataAccessAdapter.SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012);

The different compatibility modes are:

• SqlServerCompatibilityLevel.SqlServer7 (or the value 0), for SQL Server 7 compatibility
• SqlServerCompatibilityLevel.SqlServer2000 (or the value 1), for SQL Server 2000 compatibility
• SqlServerCompatibilityLevel.SqlServer2005 (or the value 2), default, for SQL Server 2005 or higher compatibility
• SqlServerCompatibilityLevel.SqlServerCE3x (or the value 3), for SQL Server CE Desktop v3.0/3.1 compatibility
• SqlServerCompatibilityLevel.SqlServerCE35 (or the value 4), for SQL Server CE Desktop v3.5 or higher compatibility
• SqlServerCompatibilityLevel.SqlServerCE40 (or the value 5), for SQL Server CE Desktop v4.0 or higher compatibility
• SqlServerCompatibilityLevel.SqlServer2012 (or the value 6), for SQL Server 2012 or higher compatibility.

The default is SqlServer2005, which means 2005 and higher. The integer values have to be used when you're using the .config file parameter. See for more details about that parameter Generated code - Application configuration through .config files.

There's also a per-instance compatibility setting, so you can use one DataAccessAdapter instance with a different compatibility level than another. To do that, set the instance property CompatibilityLevel to one of the SqlServerCompatibilityLevel values. Setting this property will, for that DataAccessAdapter instance, override the default compatibility level.

See Database specific features, SQL Server Specific: compatibility mode,  for details about the SQL specific effects each compatibility mode has.

DQE compatibility mode (Oracle only)

To set the compatibility level for Oracle at runtime, you can use the DataAccessAdapter static method SetOracleCompatibilityLevel as shown in the following example, which sets the compatibility level to Oracle 12c:

DataAccessAdapter.SetOracleCompatibilityLevel(OracleCompatibilityLevel.Oracle12c);

The default is OracleCompatibilityLevel.Oracle9i10g11g. See for more details about the compatibility parameter and its effect the section: Database specific features and Application configuration through .config files