Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Configuration File Format
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Configuration File > Configuration File Format

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.

A typical OpenAccess ORM App.config or Web.config file consists of a <configSections> element for defining a configuration handler and a <openaccess> section for OpenAccess ORM-specific configuration information. These are described in the sections — The <configSections> Element and Section <openaccess> respectively.

The <configSections> Element

The <configSections> element is the part of a .NET App.config or Web.config file where a user application can specify a definition of a configuration section handler. For reading information from the <openaccess> section, Telerik OpenAccess ORM specifies a config section handler. The type Telerik.OpenAccess.Config.ConfigSectionHandler specifies the class name of the section handler and Telerik.OpenAccess.Config specifies the assembly name. The version number of the Telerik.OpenAccess.Config assembly is always 1.0.0.0.

Copy Code
<configuration>
 
<configSections>
   
<section name="openaccess"
   
type="Telerik.OpenAccess.Config.ConfigSectionHandler, Telerik.OpenAccess.Config,
   
Version=1.0.0.0, Culture=neutral, PublicKeyToken=4a339ffcee0b897b"/>
 
</configSections>
</
configuration>

Section <openaccess>

The <openaccess> section can have four elements or sub-sections.

<connections>
 

Here, the available database connections and their parameters such as database name, user name and password are listed. The <connections> element is discussed in the Connections section below.

<backendconfigurations>
 

Within a backend configuration, you can specify various settings for the database backend such as the isolation level. Refer to the Backend Configurations section for a complete description.

<mappings>
 

A mapping section describes how your persistent classes are mapped to tables and columns in the database. The <mappings> element is discussed in the Mappings section.

<references>
 

OpenAccess ORM needs to know of all assemblies used by the application, which contain persistence capable classes. These assemblies have to be listed in the references section. Additionally, references can be used to locate additional configuration files. Refer to the References section for more information.

Connections

The <connections> section contains one or more <connection> sections. If your application needs to connect to more than one backend you will need multiple <connection> sections in your App.config file.

Here is an example for one connection.

Copy Code
<connections>
 <connection id="DatabaseConnection1">
   <databasename>MyBase</databasename>
   <servername>MyServer</servername>
   <user>openaccess</user>
   <password>openaccess</password>
   <backendconfigurationname>mssqlConfiguration</backendconfigurationname>
   <connectionParams>Application Name=XXX</connectionParams>
 </connection>
</connections>

The name of the connection is specified via the id attribute. A connection consists of the following elements:

databasename
 

Specifies the name of the database.

servername
 

Specifies the name of the server where the database should be created or updated.

It is possible to use the "lpc:" or "tcp:" strings in front of the server name to enforce a transport layer while connecting to a database server (MSSQL). The use of  tcp: in front of the server name will force usage of TCP/IP transport and the use of lpc: in front will force shared memory transport usage.

This works only for MSSQL with the genericADO2 driver, which is the default for .NET 2.0.

user
 

Specifies the SQL user ID.

password
 

Specifies the password for the SQL user ID.

backendconfigurationname
 

Specifies the name of the backend configuration (see the Backend Configurations section).

connectionParams
 

It is possible to specify various Connection parameters, such as Application Name, using the connectionParams element. The following code fragment sets the application name for a particular connection:

Copy Code
   <connectionParams>Application Name=XXX</connectionParams>

A connection can be used in the project properties of the OpenAccess ORM Visual Studio Integration for creating/updating databases or during runtime for opening databases. You can also use the connection ID when getting a Database instance in your application.

C# Copy Code
Database db = Database.Get( "DatabaseConnection1" );
VB .NET Copy Code
Dim db As Database = Database.Get("DatabaseConnection1")

Where "DatabaseConnection1" is a connection defined in the configuration file.

If you do not want to specify the username and password in the App.config file, they can be specified in the source code, as shown below:

C# Copy Code
IObjectScope GetObjectScope(string username, string password)
 {
    
return del.GetObjectScope(username, password);
 }

 

VB .NET Copy Code
Private Function GetObjectScope(ByVal username As String, ByVal password As String) As IObjectScope
  Return del.GetObjectScope(username, password)
End Function

However, it is not permitted to use different username/pwd combinations in subsequent calls.

Integrated security is supported by Telerik OpenAccess ORM 4.0 and above.

Using Oracle TNS Names

For cases where the database backend is Oracle, which needs to be configured with Oracle client's tnsnames.ora file, you can use the useOracleTnsNames=true setting as follows:

Copy Code
<connections>
 
<connection id="DatabaseConnection1">
   
<databasename>TNSNAME_GOES_HERE</databasename>
   
<servername>-</servername>
   
<connectionParams>useOracleTnsNames=true</connectionParams>
   
<user>USERNAME_GOES_HERE</user>
   
<password>PASSWORD_GOES_HERE</password>
   
<backendconfigurationname>oracleConfiguration</backendconfigurationname>
 
</connection>
</
connections>

The - in the <servername> is only there to make it non-empty and it will be ignored.

You can also set <integratedSecurity> to "True" instead of setting the <user> and <password>.

Using the AttachDbFilename setting

If you work with a SQLEXPRESS database server and need to attach a specific database (which is probably located in your Visual Studio solution) you can place the "AttachDbFilename=setting" in the connectionParams element of the connection node:

Copy Code
<connections>
 
<connection id="DatabaseConnection1">
   
<databasename>-</databasename>
   
<servername>.\SQLEXPRESS</servername>
   
<integratedSecurity>True</integratedSecurity>
   
<connectionParams>AttachDbFilename=c:\Projects\ConsoleApplication1\Database1.mdf;</connectionParams>
   
<backendconfigurationname>mssqlConfiguration</backendconfigurationname>
 
</connection>
</
connections>

Instead of specifying the full path name to your database file in the project directory you can also use the |DataDirectory| logical directory:

Copy Code
   <connectionParams>AttachDbFilename=|DataDirectory|\Database1.mdf;</connectionParams>

The effect of using |DataDirectory| is that the OpenAccess ORM schema update build step first applies all the necessary schema changes to the database in the project directory; then the updated database is copied to the working directory for run/debug and |DataDirectory| resolves to the working directory where the copied database resides.

Using the Charset setting

The Charset setting is used to specify a character set required by you. For e.g., in case of the Firebird Server, OpenAccess ORM by default creates databases using the UTF8 character set; this character set can be changed by using the connectionParams element as shown below:

Copy Code
   <connectionParams>Charset=XXX</connectionParams>

"XXX" is the character set that is required and this character set should be known to the specified database.

Connecting to two Databases with different schemas

If your application needs to connect to more than one database, you will need multiple <connection> sections, in its App.config file as shown below:

Copy Code
<connections>
 
<connection id="DB2Connection">
   
<reference assemblyname="db2" />
   
<databasename>DB2</databasename>
   
<servername>localhost</servername>
   
<integratedSecurity>True</integratedSecurity>
   
<backendconfigurationname>mssqlConfiguration</backendconfigurationname>
 
</connection>
 
<connection id="DB1Connection">
   
<reference assemblyname="db1" />
   
<databasename>DB1</databasename>
   
<servername>localhost</servername>
   
<integratedSecurity>True</integratedSecurity>
   
<backendconfigurationname>mssqlConfiguration</backendconfigurationname>
 
</connection>
</
connections>

Each connection section provides a database connection to a specific database, i.e., DB1 or DB2 in our example, which is then used to enhance the specified assemblies, db1 or db2.

As shown in the example above, the first <connection> section defines one set of references for the db2 specific dlls, and the second <connection> section defines another set of references for the db1 specific dlls. Therefore, in this case the metadata information of the persistent classes will be generated out of only the specified assemblies, i.e., db1 assemblies will be used if we need to connect and gain access to DB1 objects. For e.g.:

C# Copy Code
IObjectScope db1Scope = Database.Get("DB1Connection").GetObjectScope();
VB .NET Copy Code
Dim db1Scope As IObjectScope = Database.Get("DB1Connection").GetObjectScope()

In the above case, if we try and add a new object for DB2 using the db1scope, an exception will be thrown:

 

C# Copy Code
db1Scope.Transaction.Begin();
db1Scope.Add(new db2.DB2Class());
db1Scope.Transaction.Commit();
Console.WriteLine(
"Error: DB1 store DB2 object OK");
VB .NET Copy Code
db1Scope.Transaction.Begin()
db1Scope.Add(New db2.DB2Class())
db1Scope.Transaction.Commit()
Console.WriteLine("Error: DB1 store DB2 object OK")

In case of connection dependent reference entries you can point to a complete different set of assemblies for the different databases or to the same if the schema is identical. With this approach you can decide at the assembly level out of which class definitions the database schema should be generated. You can place the shared classes into one assembly and all unique classes into a database specific assembly.

It is not possible to update multiple schemas with the same call. Therefore, the schema update call must be placed on the database specific dll, so the two databases, i.e., DB1 and DB2 in this example, also need to have a <connection> section present in their App.config file. In the Visual Studio the Update Schema property should be set to "true" only once for every database. These settings are used only during design time and not used during runtime.

Integrated Security

Integrated security is supported by Telerik OpenAccess ORM 4.0 and above. The genericADO2 and ODP drivers support integrated security. However, the MySQL, the Advantage Database Server, the SQL Anywhere Server and the Firebird Server backends do not support integrated security. Refer to Low-level Driver for more information about the same.

Integrated Security is specified as shown in the example below:

Copy Code
<connections>
 
<connection id="DatabaseConnection1">
   
<databasename>MyDatabase</databasename>
   
<servername>MyServer</servername>
   
<integratedSecurity>True</integratedSecurity>
   
<backendconfigurationname>mssqlConfiguration</backendconfigurationname>
 
</connection>
</
connections>

It is strongly recommended to specify integrated security explicitly. However, if this is not specified, then it does default to True, if the genericADO2 driver is used against SQL Server 2005 / EXPRESS, otherwise it defaults to False.

Backend Configurations

Backend configuration sets parameters for the given type of backend. The backend configuration dialog enables the user to specify values for various global settings, such as caching or logging levels etc.

The backend configuration dialog writes the various properties within the <backendconfigurations> section of the App.config file.

The <backendconfigurations> element or section contains one or more <backendconfiguration> sub-sections. One backend configuration section contains information about one backend configuration (e.g., one configuration for the MS SQL Server).).

The backendconfiguration section to be used is specified in the connections, using the <backendconfigurationname> element and the backend to be used is specified in the "backend" attribute. Here is an example of a backend configuration:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql">
   
<mappingname>mssqlMapping</mappingname>
   
<ext.db-inheritance>vertical</ext.db-inheritance>
   
<checkModelConsistencyOnCommit>true</checkModelConsistencyOnCommit>
   
<l2CacheEnabled>true</l2CacheEnabled>
   
<l2QueryCacheEnabled>true</l2QueryCacheEnabled>
   
<l2CacheMaxObjects>1200</l2CacheMaxObjects>
   
<logging.logEvents>all</logging.logEvents>
   
<logging.logEventsToSysOut>true</logging.logEventsToSysOut>
 
</backendconfiguration>
</
backendconfigurations>

The id attribute specifies the name of the backend configuration. The backend attribute specifies the backend for the configuration. Currently, OpenAccess ORM supports MS SQL Server 2000 / MSDE, MS SQL Server 2005 / EXPRESS, MySQL 5.0 (version 5.0.19 or higher), Advantage Database Server 8.1, Oracle (version 9.2 or higher), SQL Anywhere Server (OpenAccess ORM supports and has been tested on SQL Anywhere Version 10), SQL Azure, Microsoft SQL CE and Firebird (OpenAccess ORM supports only Firebird Server Version 2.0 and it has been tested on the same) – backend="mssql"|"oracle"|"mysql"|"ads"|"sqlanywhere"|"azure"|"sqlce"|"firebird" (Refer to Access to a Relational Database for more information).

The <mappingname> element refers to a <mapping> element (from the <mappings> section), which describes the mapping of your persistence capable classes to the database tables and columns.

Default values for various mapping options for all persistent capable classes, are also set within the backend configuration section for e.g. setting the inheritance strategy to "vertical" as the default inheritance strategy for all the persistent capable classes. These default values can be overwritten at the class/field level.

Within the backend configuration section, it is also possible to specify ADOType -> SQLType mapping or .NETType -> ADOType mapping globally, across all databases, or specifically for a particular database. Refer to the Type Mapping section for more information.

All Backend configuration properties are case sensitive. As far as possible, please use the Backend Configuration dialog to edit the properties, since in this case OpenAccess ORM will add the appropriate entries to the configuration file.

A Backend configuration section can contain the following elements from the following categories:

Backend settings

This section of the dialog can be used to specify the backend to which your OpenAccess ORM project will connect to. It also specifies the low-level driver to be used to communicate with the database system.

Database backend
 

This is used to specify the backend to which your OpenAccess ORM project will connect to. Currently, OpenAccess ORM supports MS SQL Server 2000 / MSDE, MS SQL Server 2005 / EXPRESS, MySQL 5.0, Advantage Database Server 8.1, Oracle, SQL Anywhere Server, SQL Azure, Microsoft SQL CE and Firebird Server 2.0. Backend appears as an attribute within the <backendconfiguration> element as shown below:

Copy Code
<backendconfiguration id="mssqlConfiguration" driver="genericADO2" backend="mssql" />

The value of the backend attribute can be "mssql" | "mysql" | "oracle" | "ads" | "sqlanywhere" | "azure"|"sqlce"|"firebird" and the value of the drivers can be "genericADO2" | "odp".

Database driver
 

OpenAccess ORM uses a low-level driver for the actual communication with the database system. This can be set to genericADO2 or odp, depending on the type of database, which you need to communicate with (Refer to Low-level Driver for more information).

Caching Settings

OpenAccess ORM has a caching mechanism, the 2nd level cache, which reduces the calls made to the relational server, by conserving data already loaded from the database. Database access is therefore necessary only when the retrieving data is currently not available in the cache. This increases the efficiency and performance of your application.

Caching can be more fine-grained on a per class basis with the cache being enabled by default at the datastore level (Refer to cache-strategy for more information).

Caching for classes as well as for query results can be enabled using this section of the dialog, also the size of the cache can be set here.

2nd Level Cache
 

The <l2CacheEnabled> property enables the 2nd level cache, when its value is set to "true". By default the 2nd level cache is set to "false".

Maximum Objects in Cache
 

The <l2CacheMaxObjects> property controls the maximum number of objects which can be contained in the 2nd level cache. By default its value is set to 10000.

Cache query results
 

The query results are cached, when its value is set to "true". The name of this property is <l2QueryCacheEnabled> and by default it is set to "false". You have to enable the 2nd level cache to cache the query results.

Maximum Queries in Cache
 

The <l2QueryCacheMaxQueries> property controls the maximum number of queries which can be contained in the 2nd level cache. By default its value is set to 1000.

Class Metadata Settings

These settings control the default values for various mapping options for all persistent capable classes. The Class Metadata also defines globally, whether OpenAccess ORM will manage relationships, such as the many-to-many relationships, automatically or not.

These global settings can be overwritten at the class/field level, using the various mapping dialogs.

Cache Strategy
 

The <ext.cache-strategy> property controls how instances of persistence-capable classes are cached. This can be set to "no" to not cache instances, "yes" to cache instances or "all", in which case OpenAccess ORM will read and cache all instances of the class with a single SQL query the first time an instance is required, therefore individual queries for loading referenced instances, will be avoided. It is set by default to "yes" (see also cache-strategy).

The cache-strategy can overwritten as the class level as shown below:

Copy Code
<class name="Item">
   <extension key="cache-strategy" value="no" />
</class>
Discriminator column
 

The <ext.db-class-id> property specifies the value to be used for the discriminator column. Flat mapping requires the addition of a discriminator or indicator column to the table for the base class to identify the type of each row. The default name of the discriminator column is "voa_class". This is optional for vertical mapping. The discriminator column value for each class can be an int or a string.

This can be set to specify any positive 32–bit integer (unique within the hierarchy) or select the classname (Entry:{name}) or the fully qualified classname (Entry: {fullname}) as the class ID or use "no" (Entry:{no}), if you do not wish to use a discriminator. Its default value is a 31–bit hash value (Entry:{hash}) (see also db-class-id).

Do not create table
 

The <ext.db-do-not-create> property turns the table generation for a class or link table on or off (boolean extension) and its default value is set to false (The table will be generated). This is useful when some of the classes in your model map to legacy database tables, which need not be created and need to be left out of the generated schema (see also db-do-not-create-table).

Foreign Key Constraint
 

Sometimes, Relational Servers support referential integrity on columns that allow nulls. However, the generation of a constraint is dependant on some things:.

 

– If the reference is not nullable, then constraint is generated

 

– If the reference is part of a cycle, then the constraint is not generated

However, if the reference is nullable, then whether the constraint is generated or not, depends on the default value of the drivers:

 

– Oracle, SQL Anywhere Server, Advantage Database Server and Firebird: The constraint is generated

 

– MSSQL, SQL Azure, MSSQL CE and MySQL: The constraint is not generated

This default behavior can be overwritten using the <dbNullForeignKey> property:

Copy Code
<dbNullForeignKey>true</dbNullForeignKey>

Setting the value of this parameter to "true" will overwrite the driver setting. Please be careful while using this setting, as it might break your application.

Inheritance Strategy
 

The <ext.db-inheritance> property specifies the default inheritance strategy for persistence capable classes. The inheritance strategy can either be flat (default value), vertical or horizontal (see also db-inheritance).

Managed many-to-many
 

If the <managedManyToMany> property is set to true, then OpenAccess ORM manages the many-to-many relationships for managed instances automatically. Its default value is "false".

Managed one-to-many
 

If the <managedOneToMany> property is set to true, then OpenAccess ORM manages the one-to-many relationships for managed instances automatically. Its default value is "false".

OIDs in default fetch group
 

When the <ext.oids-in-default-fetch-group> property is set to true then the OIDs (primary keys) of referenced instances are retrieved along with the default fetch group fields. Its default value is "true" (see also oids-in-default-fetch-group).

Optimistic locking mode
 

The <ext.db-optimistic-locking> property specifies the default optimistic locking mode. The optimistic locking options can be: none, version, timestamp and changed. By default the locking mode is set to "version" (refer to db-optimistic-locking for more information).

Connection Settings

A connection pool is a set of database connections that are re-used by clients. Setting up a connection pool improves performance by eliminating client connection times and it is also the preferred method of handling database connections since it provides a more robust handling of error conditions and lost connections than direct manipulation of the connection objects.

The default values for various connection pool settings for your OpenAccess ORM application can be set using this section of the dialog.

Active connection timeout (secs)
 

The <conTimeout> property specifies the number of seconds after which, active connections that have been inactive or busy are closed. A db.pool.contimeout event is logged if this happens. Its default value is set at 120 seconds (see also Active Connection Timeout).

Block when full
 

If the <blockWhenFull> property is set to true (default value), then threads attempting to obtain a connection will wait, until one is available. A db.pool.full.event event is logged in either case. If it is set to false, an exception will be thrown.

Connect retry count
 

If OpenAccess ORM throws an exception for a connect() call the operation will be retried for the set number of times (0 for infinite, -1 for no retries). When the retries are exhausted the exception will propagate to the application. The name of this property is <retryCount> and its default value is set at 30.

Connect retry interval (ms)
 

The <retryIntervalMs> property sets the number of milliseconds the pool will sleep between retries for the connect() exceptions. Its default value is set at 1000 milliseconds, i.e, by default a connection will try for one second before giving up.

Connection init SQL
 

The <initSql> property specifies an SQL statement to be executed on each newly created connection directly after connecting.

You can set one or more statements by adding <initSql>, <initSql1>, <initSql2>, .. You have to use this order, i.e., the list has to start with <initSql> and missing entries are not allowed.

If the <initSql> statements ends with "; commit" we will execute a transaction commit after executing the statement. If the statement starts with "exec" we will call a stored procedure. For MS SQL Server you can use "exec" only on procedures having no parameters, otherwise you will have to specify the stored procedure call without exec.

The <initSql> property can be used for application role restrictions. It can also be used in a generic way to provide a connection with a customer specific setup. For e.g., the default schema to be used can be specified in the <initSql> property.

Connection validation SQL
 

The <validateSql> property specifies the SQL query to be executed when a connection is validated. This must return at least one row. If nothing is specified then OpenAccess ORM will supply a suitable query (see also Connection Testing).

Max connection age
 

The <maxConAge> controls the maximum number of times a connection can be returned to the connection pool before it is closed to free accumulated resources. (see also Maximum Connection Age).

Pool max active
 

The <maxActive> property sets the maximum number of connections to create for the connection pool. If you set this to 10 then OpenAccess ORM will not open more than 10 connections to the database. You need to use the event log to monitor your production system to decide how many connections you need. This depends on the transaction mode (optimistic or pessimistic), the length of your transactions and the number of users. Its default value is set at 10.

Pool max idle
 

The <maxIdle> property sets the maximum number of idle connections to be kept in the pool. Setting this to a number lower than the number set in "Pool max active" will cause the pool to shrink during idle periods. Its default value is set at 10.

Pool min idle
 

New connections are created by a background thread if the number of idle connections in the pool is less than this setting. This improves response time when the load on the server is increasing. The name of this property is <minIdle> and its default value is set at 2.

Pool reserved
 

The <reserved> property sets the number of connections to reserve for primary key generation. This prevents deadlocks on commit. Its default value is set at 1.

Pool test interval (secs)
 

The <testInterval> property sets the number of seconds between idle connection test runs and active connection timeout checks. Its default value is set at 120 seconds.

PreparedStatement cache size limit
 

The <psCacheMax> property sets the maximum number of PreparedStatements to cache per connection. If left blank this defaults to a value reasonable for the database in use (currently only limited on Oracle to 30). Use 0 for unlimited PreparedStatements. Its default value is set at 0.

Test connection before use
 

If the <testOnAlloc> property is set to true, then each connection is validated before leaving the pool. This may have a serious negative impact on performance, so this property is set by default to false.

Test connection on exception
 

If the <testOnException> property is set to true then connections involved in an exception thrown by OpenAccess ORM are validated before being returned to the pool. By default it is set to true.

Test connection on release
 

If the <testOnRelease> property is set to true then each connection is validated before being returned to the pool. This may have a serious negative impact on performance, so this property is set by default to false.

Test connection when idle
 

If the <testWhenIdle> is set to true then idle connections are tested periodically by a background thread. Connections that fail the validation process are discarded. By default it is set to true.

Wait for connection on startup
 

If the <waitForConOnStartup> property is set to true then the OpenAccess ORM will wait until it can successfully connect to the database before starting. If this is set to false (default value) it will fail to start. This is useful in production to avoid timing issues with database startup and application deployment.

Lock Timeout
 

The <lockTimeout> property sets the time after which lock requests will be timed out. This can be set as shown below:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql">
   
<mappingname>mssqlMapping</mappingname>
   
<lockTimeout>555</lockTimeout>
 
</backendconfiguration>
</
backendconfigurations>

The above example will set the lock timeout for the connections to the server to 555 milliseconds. This is the time span the server will get on a per-connection basis and after which the server will try to resolve surmised blockades with a "lock not granted" error.

Use this property with care! The default time set for a lock timeout is 5000 milliseconds, which is generally enough for granting locks on the server. Setting this property to –1 leads to infinite blocking.

In case of firebird, lock timeout values cannot be set on a transaction, since when the lockTimeout property is specified in the app.config file as "0", then the NO_WAIT Firebird transaction option is used.

Database Settings

The "database" settings configure parameters for a single instance of a particular type of backend.

Check model consistency on commit (SLOW)
 

The <checkModelConsistencyOnCommit> property controls whether the model is checked for consistency, i.e. it checks the consistency of the one-to-many and many-to-many relationships when a transaction is committed. This is not enabled by default as the check is expensive (refer to Model Consistency Checking for more information).

MultiThreading
 

The <option.Multithreaded> property supports multiple threads accessing one ObjectScope, i.e., one Objectscope may be used from more than one thread at the same time. This can be set as shown below:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql">
   
<mappingname>mssqlMapping</mappingname>
   
<option.Multithreaded>true</option.Multithreaded>
 
</backendconfiguration>
</
backendconfigurations>

The <option.Multithreaded> property generates object scopes which are completely synchronized.

By default the <option.Multithreaded> property is set to false for performance reasons.

HighLow Key Generator Settings

A key generator gives a possibility of generating identity field values automatically, i.e. providing primary keys. OpenAccess ORM currently supports key generation using a lookup table (the HIGHLOW or sequence block algorithm) and using identity or auto increment columns (AUTOINC key generator). The HIGHLOW key generator is the default and it uses a last_used table to generate the id for a new instance (Refer to Primary Key for more information).

This section of the dialog allows you to configure the HighLow key generator for all classes and set its default properties.

Create table
 

The <ext.db-key-generator.createTable> property indicates whether the table is to be created or not. Its default value is true.

Grab size
 

The <ext.db-key-generator.grabSize> property specifies the number of instances that can be created per update of the table. Values greater than one improves performance but might generate "holes" in the sequence. Its default value is 10.

Key column length
 

The <ext.db-key-generator.keyColumnLength> property specifies the length of the primary key column. Its default value is 64 and if your table names are not unique in the first 64 characters you should increase this value.

Key column name
 

The <ext.db-key-generator.keyColumnName> property specifies the name of the primary key column. Its default value is table_name.

Primary-Key constraint name
 

The <ext.db-key-generator.pkConstraint> property specifies the name of the primary key constraint for the table.

Start
 

The <ext.db-key-generator.start> property specifies the value to be used as the primary key when the table has no row. Otherwise, max(id) + 1 is used to generate the primary key value. Its default value is 0.

Table name
 

The <ext.db-key-generator.tableName> property specifies the name of the table. Its default value is voa_keygen.

Value column name
 

The <ext.db-key-generator.valueColumnName> property specifies the name of the "last used number" column. Its default value is last_used_id.

Logging Settings

This section of the dialog allows you to configure the event logging properties, such as specifying the log file name and controlling the amount of logging information to be generated, etc.

Logging events does have an impact on performance.

Log file name
 

The <logDownloader.filename> property specifies the file name for the log output.

Log level
 

The <logging.logEvents> property specifies the amount of logging information to be generated. The event logging levels for the events to log property are as follows:

none (default value)

errors

normal

verbose

all

Refer to Configuring Logging for more information about the event logging levels.

Write events to text file
 

The <logDownloader.eventText> property specifies whether the logging information is written to a text file or not. Default value is false.

Write Log Output to Console
 

The <logging.logEventsToSysOut> property specifies whether the logging information is printed to the console or not. Default value is false.

Event Tracing
 

The <logging.logEventsToTrace> property specifies whether events are traced using the .NET Logging Framework. If enabled, the log messages are sent to the System.Diagnostics.Trace class (refer to Using the System.Diagnostics.Trace framework with OpenAccess ORM for more information). Default value is false.

Type Mapping

This section of the dialog allows you to specify the CLR Type to ADO .NET Type mapping or an ADO .NET Type to SQLType mapping for the selected database. It can also be used to specify other type mapping information such as length, scale, null etc.

ADO Type Mapping
 

Using this dialog it is possible to specify an ADOType —> SQLType mapping for the selected database. The name of the selected database appears as a caption (Microsoft SQL Server 2000 / MSDE) in the ADO Type Mapping dialog, as shown in the image below.

This dialog can be opened by clicking on the ADO Type Mapping property in the Backend Configuration menu. A screen-shot of the ADO Type Mapping dialog is given below:

The ADOType —> SQLType mapping can be specified clicking on the entries appearing below the SQL Type, which provides you with a drop-down list of SQLTypes, that are supported by the selected database (driver). From this drop-down list you can choose the required mapping, i.e., which SQLType needs to be mapped to which ADOType.

Using this dialog, it is also possible to specify the length, scale and whether null values are allowed for a particular type.

Once all the necessary changes are made, you will need to click on the "OK" button to commit the changes, clicking on the "Cancel" button will not save any changes.

Whenever the user makes any changes to the type mappings, changes are made to the XML Metadata in the App.Config file.

The following shows an example of the XML metadata, of a BIT(ADOType) —> INT(SQLType) mapping. The length has also been set to "20", its scale set to "10" and null values not allowed for this type, for a MSSQL database:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql" driver="genericADO2">
   
<typeMapping>
      
<ADOType>BIT</ADOType>
      
<SQLType>INT</SQLType>
      
<Length>20</Length>
      
<Scale>10</Scale>
      
<Nulls>false</Nulls>
   
</typeMapping>
 
</backendconfiguration>
</
backendconfigurations>
CLR Type Mapping
 

Using this dialog it is possible to specify a CLR Type to an ADO .NET Type, then the ADO .NET Type can be mapped to its corresponding SQLType for the selected database. The name of the selected database appears as a caption (Microsoft SQL Server 2000 / MSDE) in the CLR Type Mapping dialog as shown in the image below.

This dialog can be opened by clicking on the CLR Type Mapping property in the Backend Configuration menu. A screen-shot of the CLR Type Mapping dialog is given below:

The CLR —> ADOType mapping can be specified clicking on the entries appearing below the ADOType, which provides you with a drop-down list of ADOTypes, that are supported by the selected database (driver). From this drop-down list you can choose the required mapping, i.e., which CLR Type needs to be mapped to which ADO .NET Type. The ADOType —> SQLType mapping can also be specified in the same way, by choosing the required SQLType from the drop-down list, of supported SQLTypes for the selected database.

Using this dialog, it is also possible to specify the length, scale and whether null values are allowed for a particular type.

Once all the necessary changes are made, you will need to click on the "OK" button to commit the changes, clicking on the "Cancel" button will not save any changes.

Whenever the user makes any changes to the type mappings, changes are made to the XML Metadata in the App.Config file.

The following shows an example of the XML metadata, of a System.String(CLRType) —> CLOB(ADOTYPE) mapping and further the CLOB(ADOTYPE) is mapped to BIGINT(SQLTYPE) for a MSSQL database:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql" driver="genericADO2">
   
<typeMapping>
      
<CLRType>System.String</CLRType>
      
<ADOType>CLOB</ADOType>
      
<SQLType>BIGINT</SQLType>
   
</typeMapping>
 
</backendconfiguration>
</
backendconfigurations>

Whenever you use the ADO Type Mapping dialog, and choose a particular ADOType —> SQLType mapping, this gets applied to all the cases of this mapping, i.e. all the CLRTypes, which are mapped to that particular ADOType also get mapped to the chosen SQLType.

For example, if you have chosen an INTEGER(ADOType)—> BIGINT(SQLType) mapping in the ADO Type Mapping dialog, this mapping will hold true for all the cases of the CLRType mapping as well, i.e., System.Nullable<System.Int32>, System.Nullable<System.UInt16>, System.Int32 and System.UInt16, will all map to BIGINT(SQLType).

Therefore, if you need only, for e.g., System.Int32 to map to BIGINT(SQLType), you should use the CLR Type Mapping dialog and only map System.Int32 to BIGINT(SQLType). In this case, the other three cases of the CLRType will yet map to INT(SQLType), which is its default value.

If the user makes any changes to the default type mapping values, that particular value appears in red. The user can choose "default" from the drop-down list to revert to the default value for a particular type. Reverting to the "default" value for a particular type will remove its corresponding entries from the XML metadata.

Specifying properties for different databases

When there are more than one backends specified for your application, it is possible for you to have only one mapping section, which applies to all the backends.

For e.g.:

Copy Code
<mappings current="mssqlMapping">
 
<mapping id="MssqlMapping>
   
<namespace name="BusinessObjects">
     
<class name="Customer">
         
<field name="name">
           
<extension key="db-column">
             
<extension key="db-column-name" value="f_nme" />
           
</extension>
           
<!-- other mapping information for persistent class "Customer" - ->
         </field>
     <class name="Customer">
   </namespace>
 </mapping>
</mappings>

One or more properties for a column can be mapped using the "db-column"" extension (refer to db-column for more information). In the above example, the column name is set to "f_name" for all databases.

It is also possible to specify separate mappings for different databases, by using an optional "value"" attribute, within the "db-column" extension. The "value"" attribute specifies the type of database that the mapping applies to. If this is not specified then the mapping applies to all databases. Therefore, you will only need only one "db-column" extension unless you require different mappings for different databases.

For example, if we have a string field "name" for which we need the to specify the column length for all databases to be 250, except for mssql, which needs to be set to 200, this mapping can be specified as under:

Copy Code
<field name="name">
 
<extension key="db-column">
   
<extension key="db-length" value="250" />
 
</extension>
 
<extension key="db-column" value="mssql">
   
<extension key="db-length" value="200" />
 
</extension>
</
field>

For "mssql", the length has been explicitly set to 200.

However, please note the following:

If you want, to specify the type of the column to be "CHAR" for all databases, including mssql, this mapping will need to be specified as under, using the above example:

Copy Code
<field name="name">
 
<extension key="db-column">
   
<extension key="db-type" value="CHAR" />
   
<extension key="db-length" value="250" />
 
</extension>
 
<extension key="db-column" value="mssql">
   
<extension key="db-type" value="CHAR" />
   
<extension key="db-length" value="200" />
 
</extension>
</
field>

As you can see from the above example, the child extension db-type has to explicitly be set for mssql, even though it is same as that used for the other databases. This is due to the fact that entire block for the db-column extension, including its child extensions, are picked up for a particular database, and the default values apply if nothing is specified within the block. In the above example, in case of the mssql database, if the db-type was not explicitly set to "CHAR" , the default value of "VARCHAR" would apply to the "name" field.

Restricting Database Access

In order to restrict database access, it is possible to use an "Application Role" for MS SQL Server. This can be used to restrict database access much finer than with a simple login.

For using an application role, an SQL String needs to be passed when a connection to the server is created. This can be accomplished with <initsql> tag, as shown in the following configuration file entry:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql" driver="genericADO2">
   
<mappingname>mssqlMapping</mappingname>
   
<initsql>sp_setapprole 'abc','super'</initsql>
 
</backendconfiguration>
</
backendconfigurations>

The data of the <initsql> tag is sent to the server when a connection to the backend is made (refer to Connection init SQL for more information).

In the above example, the stored procedure "sp_setapprole" is invoked with two parameters ("abc" being the application name, and "super" the password). The "sp_setapprole" is a Microsoft-predefined stored procedure and given above is just an example of what could be done with the <initsql> tag.

Using the integrated HIGHLOW generator with a user defined table

OpenAccess ORM allows you to use a user defined table as source for the HIGHLOW key generator keys (refer to db-key-generator for more information about HIGHLOW key generators). For this you need to change the default properties for the HIGHLOW key generator. This can be accomplished by defining entries, in the configuration file as shown below:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql">
   
<mappingname>mssqlMapping</mappingname>
   
<ext.db-key-generator.tableName>my_unique_key</ext.db-key-generator.tableName>
   
<ext.db-key-generator.keyColumnName>tbl_name</ext.db-key-generator.keyColumnName>
   
<ext.db-key-generator.valueColumnName>next_hi</ext.db-key-generator.valueColumnName>
   
<ext.db-key-generator.keyColumnLength>100</ext.db-key-generator.keyColumnLength>
   
<ext.db-key-generator.createTable>true</ext.db-key-generator.createTable>
 
</backendconfiguration>
</
backendconfigurations>

The above configuration entries creates a user-defined table, with unique properties for the HIGHLOW Key Generator (refer to Table 13–1 Properties for the HIGHLOW key generator for all the default properties). Such as, the db-key-generator.keyColumnName sets the user-defined name for the table column and the db-key-generator.valueColumnName extension sets the name of the last used number column and so on.

Explicitly setting drivers

You will need to explicitly choose a driver while using .NET 2.0 and SQL Server 2000 or MSDE (Refer to Low-level Driver for more information about the same).

In other cases it is not recommended to do so, unless our support asks to you to do so.

In case of MySQL 5.0, there are some restrictions to be kept in mind, for the driver:

There is a bug while using Decimal instances with the MySQL Server (versions prior to 5.0.19); this is a server side problem. Decimal and BigDecimal types are therefore not usable on those versions.

Out-of-Range DateTime handling is not working. If you specify a DateTime out of the server range a wrong value is stored.

Out-of-Range char handling is not working. If you specify a char out of the server range a wrong value is stored.

Blobs are not fully supported yet.

Generation of indexes via the [Index] attribute is not supported yet.

A driver is explicitly set as shown in the example below:

Copy Code
<backendconfigurations>
 
<backendconfiguration id="mssqlConfiguration" backend="mssql" driver="genericADO2">
   
<mappingname>mssqlMapping</mappingname>
 
</backendconfiguration>
</
backendconfigurations>

Using the System.Diagnostics.Trace framework with OpenAccess ORM

In order to aid developers to trace the execution of their code, the .NET Framework has provided the System.Diagnostics.Trace class. This framework can be used with OpenAccess ORM once it has been configured in the App.config file, within the <backendconfigurations> section, as follows:

Copy Code
<backendconfigurations>
  
<backendconfiguration id="mssqlConfiguration" backend="mssql">
     
<mappingname>mssqlMapping</mappingname>
     
<logging.logEventsToTrace>True</logging.logEventsToTrace>
     
<logging.logEvents>all</logging.logEvents>
     
<logDownloader.eventText>True</logDownloader.eventText>
  
</backendconfiguration>
</
backendconfigurations>

The above logging settings will configure the <backendconfiguration>, in such a way that all the events are traced using the System.Diagnostics.Trace class. You can then receive these events with the provided standard trace listeners. There is no need to write your own TraceListener, you just need to configure the pre-existing one to log all events to a file. This again can be done within the App.config:

Copy Code
<?xml version="1.0"?>
<
configuration>
  
<configSections>....</configSections>
  
<system.diagnostics>
     
<trace autoflush="true" indentsize="4">
       
<listeners>
         
<remove name="Default" />
         
<add name="myListener"  type="System.Diagnostics.TextWriterTraceListener"
         
initializeData="c:\myListener.log" />
       
</listeners>
     
</trace>
  
</system.diagnostics>
  
<openaccess>....</openaccess>
</
configuration>

This will generate a text log file, in the C drive, with the name myListener.log; for more information about the configuration, please refer to the .NET framework documentation.

Alternatively, you can implement your own TraceListener class like this:

Copy Code
class TracerImpl : System.Diagnostics.TraceListener
{
  
internal TracerImpl() : base("OpenAccess")  { }

  
public override void WriteLine(object o, string category)
  {
     OpenAccess.Diagnostics.ITraceEvent e = o
as OpenAccess.Diagnostics.ITraceEvent;
     
if (e != null && ShouldTrace(e))
        WriteLine(e.ToString());
  }

  
private bool ShouldTrace(OpenAccess.Diagnostics.ITraceEvent e)
  {
        
// perform custom filtering here on properties of e
        
return true;
  }
}
VB .NET Copy Code
Friend Class TracerImpl
 Inherits System.Diagnostics.TraceListener
   Friend Sub New()
    MyBase.New("OpenAccess")
   End Sub
   Public Overrides Overloads Sub WriteLine(ByVal o As Object, ByVal category As String)
   Dim e As OpenAccess.Diagnostics.ITraceEvent = TryCast(o, OpenAccess.Diagnostics.ITraceEvent)
   If e IsNot Nothing AndAlso ShouldTrace(e) Then
   WriteLine(e.ToString())
   End If
   End Sub
   Private Function ShouldTrace(ByVal e As OpenAccess.Diagnostics.ITraceEvent) As Boolean
   ' perform custom filtering here on properties of e
   Return True
   End Function
End Class

An instance of this type can then be created and made available with:

C# Copy Code
  System.Diagnostics.Trace.Listeners.Add(new TracerImpl());
VB .NET Copy Code
System.Diagnostics.Trace.Listeners.Add(New TracerImpl())

In order to allow processing of only "interesting" events (based on the type of the event or the database, etc. you are interested in). The interface OpenAccess.Diagnostics.ITraceEvent can be used to gain access to various aspects(data) of the events (such as Name, ConnectionId, FetchGroup etc.). This information could be used to implement the ShouldTrace method, as per your requirement (as shown in the example above).

All tracing output for OpenAccess ORM is done using the TraceAdapter class. If you want to use the instance programmatically (rather than by configuring it) you can use the TraceAdapter's listeners collection to add or remove your TraceListener object or you can use the Level property to modify the amount of tracing at runtime. The following sections describe trace switches, which control the initial value of the trace level for OpenAccess ORM and trace sources, which are used to bundle the output of OpenAccess ORM tracing.

Trace Switches

A trace switch controls the initial value of the trace level for OpenAccess. It is used as the initial value for the <logging.logEvents> config entry; this means that you can override this setting specifically for a particular backend, or also turn the tracing off for a particular backend.

You can configure a trace switch by editing the configuration file that corresponds to the name of your application. In this file, you can add or remove a switch, set a switch's value, or clear all the switches previously set by the application. The example below shows the entries in the configuration file for adding a switch with its DisplayName set to "OpenAccess" and the Level set to "1":

Copy Code
<configuration>
  
<system.diagnostics>
     
<switches>
        
<add name="OpenAccess" value="1" />
     
</switches>
  
</system.diagnostics>
</
configuration>

A trace switch can also be configured using the .NET frameworks machine.config, which is quite handy at times, since once it is configured, it is valid for all applications as their default setting.

You can limit the amount of tracing information as per your requirement by setting the Level. You specify the level of detail you want in your tracing output by setting and configuring trace switches to the appropriate trace level. The following table lists the levels of the TraceLevel enumeration and their values:

Enumerated Value (only for .NET 2.0) Integer Value (for .NET 1.1 and .NET 2.0) Type of message displayed
Off 0 None
Error 1 Only error (here equal to Critical) messages
Warning 2 Warning and error messages
Information 3 Informational, warning and error messages
Verbose 4 Verbose, informational, warning and error messages
Note: Values greater than "4" can also be used, in order to fine-tune the level of verbosity.

By default, the switch Level property is set using the value specified in the configuration file. If the TraceSwitch constructor cannot find the initial switch settings in the configuration file, the Level of the new switch defaults to TraceLevel.Off.

Trace Sources

A trace source is used to bundle the output of OpenAccess ORM tracing. Trace sources are a new feature in the .NET Framework version 2.0 that provides an enhanced tracing system. The TraceSource class is used by applications to produce traces that can be associated with the application. In case of .NET 1.1, only System.Diagnostics.Trace can be used.

Trace sources have been introduced so that it is possible to write different trace sources to different files making it more flexible. For e.g., it is possible for a user to inject a trace listener specifically for the OpenAccess ORM trace source; and then, only OpenAccess ORM messages will be obtained.

Trace output from TraceSource can be controlled by editing the configuration file that corresponds to the name of your application. The following example shows the contents of a sample application configuration file:

Copy Code
<configuration>
  
...
  
<system.diagnostics>
     
<sources>
        
<source name="OpenAccess">
           
<listeners>
              
<add type="System.Diagnostics.ConsoleTraceListener"/>
           
</listeners>
        
</source>
     
</sources>
     
<switches>
        
<add name="OpenAccess" value="Verbose" />
     
</switches>
  
</system.diagnostics>
  
...
</configuration>

The TraceSource class is identified by the name of a source, typically the name of the application, in the above example this is set to "OpenAccess". The trace messages coming from a particular component can be initiated by a particular trace source, allowing all messages coming from that component to be easily identified.

Mappings

The <mappings> section contains one or more <mapping> sections. Each mapping section contains information about the mapping of a set of persistence capable classes to the tables and columns in the database. The mapping to be used is specified in the backend configuration using the <mappingname> element. Here is an example of a <mappings> node with a single <mapping> section.

Copy Code
<mappings>
 
<mapping id="mssqlMapping">
   
<namespace name="PersistentClasses">
     
<class name="PersistentClass1">
       
<!—mapping information for PersistentClass1 -->
     
</class>
   
</namespace>
 
</mapping>
</
mappings>

The id attribute specifies the name of the mapping. For a complete description of the mapping element, refer to Mapping.

References

The <references> section contains one or more <reference> elements. The <reference> elements are used to list the assemblies may contain either persistence capable classes which are used by the application or a configuration file (refer to Using Configuration Files for Class Libraries below for information about using additional configuration files). The assemblyname attribute specifies the name of the assembly. This has to be the "short" name. The short name is the value returned by the System.Reflection.AssemblyName.Name property.

Here is an example of a reference node.

Copy Code
<references>
 
<reference assemblyname="PersistentClasses"/>
</
references>
 
 

OpenAccess ORM needs to know of all assemblies used by the application, which contain persistence capable classes. These assemblies have to be listed in the <references> section. If any assemblies with persistence capable classes are not listed you will not be able to use the respective persistence capable classes in your application because they will not be considered to be persistence capable.

Also the order of the references within the references node is important. OpenAccess ORM has a sophisticated default mapping mechanism. If you do not specify the complete mapping information by yourself, OpenAccess ORM is able to calculate a complete mapping on its own using certain naming strategies, etc. In this mapping process, the assemblies with persistence capable classes are processed in the order specified in the references node. If you change this order, the mapping might change and no longer be consistent with an existing database.

The referenced assemblies must also be referenced (directly or recursively) by your application. That is, there needs to be an .assembly extern <assemblyname> statement in the manifest. Therefore, there must be some explicit usage of the referenced assembly. If they are not referenced, the reference cannot be resolved, and a ConfigurationException("The config file reference for assemblyname <assemblyname> could not be resolved") will be thrown. Additionally, references can be used to locate additional configuration files. Refer to Using Configuration Files for Class Libraries below for details.