The LLBLGen Pro system uses specific database drivers per database
vendor, using the 'provider model'. The drivers themselves use the
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 driver using .NET's SqlClient
- Oracle driver using Oracle's own ODP.NET
- Oracle driver using .NET's Oracle client (requires Oracle's OCI to be installed)
- MySql driver using DevArt's MySqlDirect client (the express version is OK)
- IBM DB2 driver using IBM's own DB2 client
- Firebird driver using the Firebird official client
- PostgreSql driver using Npsql
- Microsoft Access 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.
As the LLBLGen Pro designer works with the .NET 2.0+
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.
Each database driver supports a variety of features and these are listed below.
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.
- SQL Server 2000 or higher.
- SQL Server:
Scope_identity()and all SQL Server specific types including
Date, DateTime2, DateTimeOffsetand
SQL Server 2005+: Xml datatype,
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 2012+: All 2008 features as well as sequences.
- SQL Server 2016+: full encrypted database, temporal tables
- Multiple catalogs per project.
- Multiple schemas per catalog.
SQL Server 2016+: History tables of a temporal table pair are filtered out and are not included in schema data.
- 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
- 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
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.
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
It's recommended you use ODP.NET, latest version, for Oracle and not the MS Client based driver, as System.Data.OracleClient is deprecated by Microsoft and won't receive new features.
- Firebird v1.5 or higher.
- All features of Firebird 1.5 and higher, except array types.
- Dialect 3 only.
- PostgreSQL v7.4 or higher.
- All features of PostgreSql 7.4 or higher, including table valued function, except array types.
- All datatypes supported by Npgsql are supported.
- PostgreSQL 10.x+: Identity fields.
- Multiple schemas per catalog
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.
Functions which return a table definition or use
SETOF are included in both the stored procedures and the table-valued functions. Functions which return a
refcursor are ignored as the feature isn't implemented properly in Npgsql (as of v3.2.7). We'll add this feature again if refcursors are properly fetched again in
- MS Access 2000 or higher.
- 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.
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 v7.x or higher.
- 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 v4.1 or higher.
- Tables, identity columns, primary keys, unique constraints
All native MySql types except
ENUM(which will be converted to
- MySql v5.x+: Support for stored procedures
- Multiple catalogs per project
- Index hints for select / fetch queries.
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.