Database Drivers

The LLBLGen Pro system uses specific database drivers per database vendor, using the 'provider model'. The drivers themselves use the ADO.NET DbProviderFactory system to avoid a hard-linked reference to a specific ADO.NET provider assembly version. The drivers are exclusively used by the designer, the generated code uses the SQL engines of the chosen target framework. LLBLGen Pro ships with the following drivers:

  • Microsoft SQL Server 2000/2005/2008/2008R2/2012/2014/2016 driver using .NET's SqlClient
  • Oracle 9i/10g/11g/12c driver using Oracle's own ODP.NET
  • Oracle 9i/10g/11g/12c driver using .NET's Oracle client (requires Oracle's OCI to be installed)
  • MySql 4.x/5.x driver using DevArt's MySqlDirect client (the express version is OK)
  • IBM DB2 7.x-11.x driver using IBM's own DB2 client
  • Firebird 1.x/2.x driver using the Firebird official client
  • PostgreSql 7.4+/8.x/9.x driver using Npsql
  • Microsoft Access 2000/XP/2003/2007/2010/2013/2016 driver using .NET's OleDb client

A database driver is used to connect to a database server and to retrieve all available schema information for the credentials and connection information specified. This information is then stored in LLBLGen Pro's own format inside the project in Relational Model Data storage containers.

Info

As the LLBLGen Pro designer works with the .NET 2.0+ DbProviderFactory system, it's key that all ADO.NET providers used by you are built for .NET 2.0 and support the DbProviderFactory system.

E.g. for Oracle ODP.NET, older .NET 1.x 9i builds aren't supported as they don't support DbProviderFactory. When in doubt, download the latest ADO.NET provider for your database from the vendor's website.

Supported features per database driver

Each database driver supports a variety of features and these are listed below.

General

Each driver which supports unique constraints will only retrieve unique constraint meta-data, not unique indexes. The drivers consider indexes as part of the data, not the model. Unique indexes are therefore ignored. If you have unique indexes instead of unique constraints in your database, please define them as unique constraints with separate indexes instead. The unique constraints are then picked up by the drivers.

Microsoft SQL Server

  • SQL Server 2000+: Scope_identity() and all SqlServer 2000 specific types.
  • SQL Server2005+: Xml datatype, Varchar(MAX) and Varbinary(MAX), all database constructs, including User Defined Types (UDTs) written in .NET, table valued functions and synonyms for Tables and Views (not for linked servers). No support for XQuery queries.
  • SQL Server 2008+: All SQL Server 2005 features as well as the 4 new types: Date, DateTime2, DateTimeOffset and Time.
  • SQL Server 2012/2014/2016: All 2008 features as well as sequences.
  • SQL Server 2016+: full encrypted database, temporal tables
  • Multiple catalogs per project.
  • Multiple schemas per catalog.
Info

SQL Server 2016+: History tables of a temporal table pair are filtered out and are not included in schema data.

Oracle, using ODP.NET

  • Oracle 9i or higher.
  • Tables, views, sequences, table valued functions and procedures.
  • Synonyms for tables, views and sequences.
  • REF CURSOR output parameters in procedures.
  • All native Oracle types, including *LOB and synonyms for types like the type INT and XMLType
  • Multiple schemas per project
  • Identity fields / default sequences.
  • Managed provider (ODP.NET v12+) and unmanaged ODP.NET. Managed provider is chosen first, if not found, the unmanaged provider is chosen.
  • No support for user defined types.
  • No support for overloaded stored procedures in the same package

Oracle, using Microsoft Oracle Client (System.Data.OracleClient)

For supported features, see Oracle (ODP.NET). Microsoft's Oracle provider requires an Oracle client present on the system, please consult the Microsoft Oracle provider documentation in the .NET reference manual.

Restrictions in the Microsoft Oracle provider: - XMLType is not supported by LLBLGen Pro and the MS Oracle driver, as that type isn't supported by the Microsoft Oracle provider. If you need to use this type, use the ODP.NET version of the Oracle driver. - All NUMBER(x, y) types are seen as System.Decimal. This can be a huge disadvantage. In that case, consider using an ODP.NET based driver

Firebird

  • All features of Firebird 1.5 and higher, except array types.
  • Dialect 3 only.

PostgreSql

  • All features of PostgreSql 7.4 or higher, including table valued function, except array types.
  • All datatypes supported by Npgsql are supported.
  • Multiple schemas per catalog
Info

To make the designer work with the Npgsql ADO.NET provider installed on your system, you have to use the installer provided by the Npgsql project at GitHub. This way, you know the DbProviderFactory is properly installed.

Microsoft Access

  • All features of MS Access 2000, except parameterized stored queries, so no stored procedure calls
  • Database passwords, security files are supported.

Required for Microsoft Access: OleDB driver for Jet v4.0, which is shipped with MDAC 2.5 or higher (a requirement for .NET so these are installed) and a .mdb file in the MS Access 2000 format or higher. For MS Access 2007/2010/2013/2016, an OleDB ACE driver v12 or higher is required.

Info

If you want to run LLBLGen Pro on a 64bit windows system and you want to retrieve Relational Model Data from an MS Access database, you will run into the limitation that Microsoft hasn't shipped a 64bit MS Access JET driver.

To overcome this, please run the LLBLGen Pro designer as a 32bit application. To do so, please use the 32bit enforcer helper executable LLBLGenPro_x86.exe which is located in the LLBLGen Pro installation folder and which will run the designer in 32bit, regardless whether you have a 64bit OS or not.

If you want to use the newer Microsoft Access Database Engine (ACE driver) which is supported on 64bit windows and which can also work with .accdb files (MS Access 2007/2010/2013/2016), please visit this link at the Microsoft download center to obtain the engine to make the LLBLGen Pro MS Access driver work properly on 64bit and with .accdb files.

IBM DB2 UDB

  • IBM DB2 UDB v7.x/8.x/9x/10.x.
  • Tables, views, sequences, identity columns and procedures.
  • All native IBM DB2 UDB types, including *LOB.
  • Multiple schemas per catalog.
  • No support for user defined types.
  • No support for table / view alias definitions inside the schema. A table / view schema alias is a public alias for an existing schema table and defined as such in the schema.
  • No support for iSeries DB2 installations.
  • IBM DB2 10+: Temporal tables for select / fetch queries, using Business Time and System Time predicates.

Required for DB2: IBM DB2 .NET provider, shipped with the latest ClientAccess version, also available through the DB2 personal edition installation, or through the IBM website for DB2 licensees.

MySql

  • MySql v4.x (4.1 or higher with InnoDB preferred) or v5.x
  • Tables, identity columns, primary keys, unique constraints
  • All native MySql types except SET and ENUM (which will be converted to VarChar).
  • Support for stored procedures (v5.x+).
  • Multiple catalogs per project
  • Index hints for select / fetch queries.
Info

MySQL v4.x: No support for database defined foreign keys. Foreign key constraint meta-data defined in the database isn't read by the MySql driver from a v4.x MySql database.

Required for MySql: DevArt's MySqlDirect.NET provider v5.x or higher. The Express version is enough.