Database Systems Supported by XPO

• Dec 21, 2023
• 8 minutes to read

XPO supports multiple relational database management systems (RDBMS). Whether you want to connect to a particular database or persist your objects in that database, all you need to do is to use the corresponding XPO data store adapter. For your convenience, each XPO data store adapter’s name contains the name of the database system it supports.

All XPO data store adapters are declared in the DevExpress.Xpo.DB namespace of the DevExpress.Xpo.v24.1.dll assembly. The following table lists them along with the corresponding database provider assemblies they require.

The following table lists the supported database engines along with the corresponding XPO data store adapters and database provider assemblies they require.

Note

The database provider assembly versions listed in the last column are the versions that have been tested with the current XPO version. Since XPO loads ADO.NET provider assemblies dynamically, you can use a higher version of the database provider in many cases (for instance, SQL Server 2019 when only SQL Server 2017 is listed in the table). If you encounter any issues with XPO and the latest database provider versions, please contact us.

Database Engine	Version(s)	XPO Data Store Adapter (Connection String Identifier)	Database Provider Assembly
DB2	DB2 9.7.4	DB2ConnectionProvider (XpoProvider=DB2)	IBM.Data.DB2.Core.dll 1.2.2.100 (NuGet)
Firebird	Firebird 1.5 Firebird 2.5.7 Firebird 3.0.2	FirebirdConnectionProvider (XpoProvider=Firebird)	FirebirdSql.Data.Firebird.dll 2.5.1.0 FirebirdSql.Data.FirebirdClient.dll 5.12.1.0 (NuGet)
In-Memory	None	InMemoryDataStore (XpoProvider=InMemoryDataStore)	A built-in in-memory database for testing purposes only (not intended to be used with large data sets)
Microsoft SQL Server	SQL Server 7.0 SQL Server 2000 (with Desktop Engine) SQL Server 2005 (with Express) SQL Server 2008 (with R2 & Express) SQL Server 2012, 2014, 2016, 2017, 2019, 2022 (with Express & LocalDB) SQL Azure™ Database	MSSqlConnectionProvider (XpoProvider=MSSqlServer)	System.Data.SqlClient.dll 4.6.1.4 Microsoft.Data.SqlClient.dll 5.0.0.0 (NuGet)
MySql	MySQL Server 5.0 MySQL Server 5.1 MySQL Server 5.7 MySQL Server 8.0.31	MySqlConnectionProvider (XpoProvider=MySql)	MySql.Data.dll 8.0.12.0 (NuGet) MySql.Data.dll 8.0.31.0 (NuGet) MySqlConnector.dll 2.1.13 (NuGet)
Oracle	Oracle 9i Oracle 10g Oracle 11g Oracle 12c Oracle 18c Oracle 21c	ODPManagedConnectionProvider (XpoProvider=ODPManaged)	Oracle.ManagedDataAccess.Core.dll 3.1.21.1 (NuGet)
PostgreSQL	PostgreSQL 7 PostgreSQL 8 PostgreSQL 9 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 PostgreSQL 15	PostgreSqlConnectionProvider (XpoProvider=Postgres)	Npgsql.dll 3.2.5.0 Npgsql.dll 6.0.7.0 (NuGet)
SAP HANA	SAP HANA 2.00.057	HanaConnectionProvider (XpoProvider=Hana)	Sap.Data.Hana.Core.v2.1.dll 2.12.20 (Client Libraries)
SQLite	SQLite 3	SQLiteConnectionProvider (XpoProvider=SQLite)	System.Data.SQLite.dll 1.0.116.0 (NuGet) Microsoft.Data.SQLite.dll 6.0.10 (NuGet)
VistaDB	VistaDB 6	VistaDB6ConnectionProvider (XpoProvider=VistaDB6)	VistaDB.6.dll 6.4.0.0 (Download)

Database Engine	Version(s)	XPO Data Store Adapter (Connection String Identifier)	Database Provider Assembly
Advantage	Advantage Database Server v9.1 Advantage Database Server v10.1 Advantage Database Server v11.1	AdvantageConnectionProvider (XpoProvider=Advantage)	Advantage.Data.Provider.dll 9.10.2.0 Advantage.Data.Provider.dll 10.10.0.49 Advantage.Data.Provider.dll 11.10.2.24
ASA	SQL Anywhere 16, SQL Anywhere 17	AsaConnectionProvider (XpoProvider=Asa)	iAnywhere.Data.SQLAnywhere.dll 16.0.0.13242 Sap.Data.SQLAnywhere.v4.0 17.0.0.13584 (NuGet) Sap.Data.SQLAnywhere.v4.5 17.0.0.13584 (NuGet)
ASE	Sybase Adaptive Server 12 Sybase Adaptive Server 15.5 Sybase Adaptive Server 16	AseConnectionProvider (XpoProvider=Ase)	Sybase.Data.AseClient.dll 1.15.50.0 Sybase.AdoNet2.AseClient 16.0.0.2 Sybase.AdoNet35.AseClient 16.0.0.2 Sybase.AdoNet4.AseClient 16.0.0.2
DB2	DB2 9.7.4	DB2ConnectionProvider (XpoProvider=DB2)	IBM.Data.DB2.dll 9.7.4.4 (NuGet)
Firebird	Firebird 1.5 Firebird 2.5.7 Firebird 3.0.2	FirebirdConnectionProvider (XpoProvider=Firebird)	FirebirdSql.Data.Firebird.dll 2.5.1.0 FirebirdSql.Data.FirebirdClient.dll 5.12.1.0 (NuGet)
In-Memory	None	InMemoryDataStore (XpoProvider=InMemoryDataStore)	A built-in in-memory database for testing purposes only (not intended to be used with large data sets)
Microsoft Access	Microsoft Jet	AccessConnectionProvider (XpoProvider=MSAccess)	System.Data.dll
Microsoft SQL Server	SQL Server 7.0 SQL Server 2000 (with Desktop Engine) SQL Server 2005 (with Express) SQL Server 2008 (with R2 & Express) SQL Server 2012, 2014, 2016, 2017, 2019, 2022 (with Express & LocalDB) SQL Azure™ Database	MSSqlConnectionProvider (XpoProvider=MSSqlServer)	System.Data.dll Microsoft.Data.SqlClient.dll 5.0.0 (NuGet)
Microsoft SQL Server Compact	SQL Server 2005 Mobile SQL Server 2005 Compact SQL Server Compact 3.5 SQL Server Compact 4.0	MSSqlCEConnectionProvider (XpoProvider=MSSqlServerCE)	System.Data.SqlServerCe.dll 3.5.0 System.Data.SqlServerCe.dll 4.0.8482.1
MySql	MySQL Server 5.0 MySQL Server 5.1 MySQL Server 5.7 MySQL Server 8.0.31	MySqlConnectionProvider (XpoProvider=MySql)	MySql.Data.dll 8.0.12 (NuGet) MySql.Data.dll 8.0.31 (NuGet) MySqlConnector.dll 2.1.4 (NuGet)
Oracle	Oracle 9i Oracle 10g Oracle 11g Oracle 12c Oracle 18c Oracle 21c	OracleConnectionProvider (XpoProvider=Oracle) ODPConnectionProvider (XpoProvider=ODP) ODPManagedConnectionProvider (XpoProvider=ODPManaged)	System.Data.OracleClient.dll 2.0.0.0 Oracle.DataAccess.dll 4.122.1.0 Oracle.ManagedDataAccess.dll 4.122.21.1 (NuGet)
Pervasive	Pervasive PSQL 9 Pervasive PSQL 10 Pervasive PSQL 11	PervasiveSqlConnectionProvider (XpoProvider=Pervasive)	Pervasive.Data.SqlClient.dll 2.10.0.15 Pervasive.Data.SqlClient.dll 3.5.0.1811
PostgreSQL	PostgreSQL 7 PostgreSQL 8 PostgreSQL 9 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 PostgreSQL 15	PostgreSqlConnectionProvider (XpoProvider=Postgres)	Npgsql.dll 2.2.7.0 (NuGet) Npgsql.dll 3.2.7.0 (NuGet) Npgsql.dll 4.1.9.0 (NuGet) Npgsql.dll 5.0.7.0 (NuGet) Npgsql.dll 6.0.7.0 (NuGet)
SAP HANA	SAP HANA 2.00.057	HanaConnectionProvider (XpoProvider=Hana)	Sap.Data.Hana.v4.5.dll 2.12.20 (Client Libraries)
SQLite	SQLite 3	SQLiteConnectionProvider (XpoProvider=SQLite)	System.Data.SQLite.dll 1.0.88.0 (NuGet)
VistaDB	VistaDB 4 VistaDB 5 VistaDB 6	VistaDBConnectionProvider (XpoProvider=VistaDB) VistaDB5ConnectionProvider (XpoProvider=VistaDB5) VistaDB6ConnectionProvider (XpoProvider=VistaDB6)	VistaDB.4.dll 4.0.0.0 VistaDB.5.NET40.dll 5.2.0.0 VistaDB.6.dll 6.4.0.0 (Download)

Note

XPO does not support tables with multi-column (compound) keys or indexes in ASE databases. To avoid exceptions when connecting to ASE databases containing these tables, use one-column keys or indexes.

To make XPO use your database server, you should do the following:

• Add the assemblies that correspond to the database provider from the table above to your project’s References.
• Connect to a database. Generally, you can do this in the following ways:
  • Specify the connection settings of the XpoDefault (XpoDefault.ConnectionString or XpoDefault.DataLayer properties) and use the Session.DefaultSession or create your Session object.
  • Specify the Session.ConnectionString or Session.Connection properties of a session. Refer to the following topic for complete information on how to connect to a data store: How to: Connect to a Data Store.

Example

To connect to the MySql database, add the MySqlConnector package to your project’s References. Then you can use the following code to create a connection to the database.

C#

using DevExpress.Xpo;
using DevExpress.Xpo.DB;
// Connects to a database
static void Main() {
    string connectionString =
        MySqlConnectionProvider.GetConnectionString("DBSERVER", "user", "pwd", "MyApp");
    IDataLayer dataLayer =
       XpoDefault.GetDataLayer(connectionString, AutoCreateOption.DatabaseAndSchema);
    XpoDefault.DataLayer = dataLayer;
    Session session = new Session();
    Application.Run(new MainForm());
}

VB.NET

Imports DevExpress.Xpo
Imports DevExpress.Xpo.DB
' Connects to a database
Public Sub New()
    MyBase.New()
    Dim connectionString = MySqlConnectionProvider.GetConnectionString("DBSERVER", "user", _
      "pwd", "MyApp")
    Dim dataLayer As IDataLayer = XpoDefault.GetDataLayer(connectionString, _
      AutoCreateOption.DatabaseAndSchema)
    XpoDefault.DataLayer = dataLayer
    Dim session As Session = New Session()
End Sub

How To Create a Correct Connection String

When an application creates an XPO Connection Provider from a connection string, XPO uses the XpoProvider parameter to determine what provider to create.

In applications that read a connection string from the configuration file, use the XpoProvider parameter with the required XPO provider name to specify a database provider type: XpoProvider=Provider name;....

Built-in connection providers have the GetConnectionString method. Use this method when you need to create a connection string with the XpoProvider parameter in code.

The list below contains the XpoProvider parameter values for all supported connection providers:

• AdvantageConnectionProvider: Advantage

  XpoProvider=Advantage;Data Source=\myserver\myvolume\mypat\mydd.add;ServerType=local;User ID=ASSSYS;TrimTrailingSpaces=true

• AsaConnectionProvider: Asa

  XpoProvider=Asa;Uid=MyUsername;PWD=MyPassword;DBF=c:\mydatabase.db;Persist Security Info=true

• AseConnectionProvider: Ase

  XpoProvider=Ase;Port=5000;Data Source=MyAseServer;User ID=MyUserName;Password=MyPassword;Initial Catalog=MyDatabase;Persist Security Info=true

• DB2ConnectionProvider: DB2

  XpoProvider=DB2;Server=MyAddress:MyPortNumber;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Persist Security Info=true

• FirebirdConnectionProvider: Firebird

  XpoProvider=Firebird;DataSource=localhost;User=SYSDBA;Password=masterkey;Database=MyDatabase.fdb;ServerType=0;Charset=NONE

• InMemoryDataStore: InMemoryDataStore

  XpoProvider=InMemoryDataStore;Data Source=C:\mydatabase.xml;Read Only=false

• AccessConnectionProvider: MSAccess

  MS Access 2007 or later

  XpoProvider=MSAccess;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.accdb;Jet OLEDB:Database Password=;

  Previous MS Access versions

  XpoProvider=MSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

• MSSqlConnectionProvider: MSSqlServer

  XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true

• MSSqlCEConnectionProvider: MSSqlServerCE

  XpoProvider=MSSqlServerCE;Data Source=MyDatabase.sdf;Password=MyPassword

• MySqlConnectionProvider: MySql

  XpoProvider=MySql;Server=MyServerAddress;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Persist Security Info= true;Charset=utf8

• ODPConnectionProvider: ODP

  XpoProvider=ODP;Data Source=TORCL;User ID=MyUserName;Password=MyPassword

• ODPManagedConnectionProvider: ODPManaged

  XpoProvider=ODPManaged;Data Source=TORCL;User ID=MyUserName;Password=MyPassword

• OracleConnectionProvider: Oracle

  XpoProvider=Oracle;Data Source=TORCL;User ID=MyUserName;Password=MyPassword

• PervasiveSqlConnectionProvider: Pervasive

  XpoProvider=Pervasive;Server=MyServerAddress;UID=MyUserName;PWD=MyPassword;ServerDSN=MyDatabase

• PostgreSqlConnectionProvider: Postgres

  XpoProvider=Postgres;Server=127.0.0.1;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Encoding=UNICODE

• HanaConnectionProvider: Hana

  XpoProvider=Hana;Server=10.0.0.1:39017;UserID=SYSTEM;Password=MyPassword

• SQLiteConnectionProvider: SQLite

  XpoProvider=SQLite;Data Source=filename

• VistaDBConnectionProvider: VistaDB

  XpoProvider=VistaDB;Data Source=C:\mydatabase.vdb4

• VistaDB5ConnectionProvider: VistaDB5

  XpoProvider=VistaDB5;Data Source=C:\mydatabase.vdb5

• VistaDB6ConnectionProvider: VistaDB6

  XpoProvider=VistaDB6;Data Source=C:\mydatabase.vdb6

How to Support Unlisted Databases or Customize Existing XPO Connection Providers

You can implement a custom XPO connection provider to connect XPO to an unsupported database system or to customize XPO connection behavior for a certain RDBMS. However, we do not have documentation and technical support for this advanced task - developers must have experience with the target database system, internal XPO architecture, and Criteria Language to proceed further. You can refer to the XPO source code of existing XPO connection providers that declare the base interfaces and classes such as IDataStore and ConnectionProviderSql. You can find them in the following directories:

• …\DevExpress.Xpo\DevExpress.Xpo\Providers
• \DevExpress.Data\Db

Refer to the following links for more examples:

• Implement a custom XPO connection provider for AdoNetCore.AseClient

• Use inheritance to customize built-in provider behavior:

  • How to reopen a broken connection when using a PostgreSQL database
  • XPO, SQL Server and spatial data revisited
  • Persisting DateTimeOffset with XPO
  • How to create a custom XPO connection provider and then use it in an XAF application

Base ConnectionProviderSql API Considerations

To get familiar with the required XPO connection provider API, review the following file in the example for SAP ASE: AseClientConnectionProvider.cs.

In this example, built-in XPO connection providers rely on native RDBMS vendor drivers for .NET and ADO.NET. XPO is not designed for ODBC, JDBC, and other database-agnostic API to RDBMS. When you implement a custom XPO connection provider, you need to change all methods that have database-specific code. Refer to your RDBMS ADO.NET driver documentation for additional information.

The connection provider uses Reflection to create ADO.NET objects. Private constants (such as AseAssemblyName, AseConnection, AseExceptionName) store names of all assemblies and classes. You can use the same technique if you change these constant values to the names of your RDBMS ADO.NET driver assemblies and classes. The XpoProviderTypeString constant specifies a provider name that identifies your custom provider. This name should not match existing provider names. You can find a complete list of built-in provider names in the following section: How to Create a Correct Connection String.

Override the following:

• GetSqlCreateColumnXXX methods (for example, GetSqlCreateColumnTypeForBoolean, GetSqlCreateColumnTypeForByte, GetSqlCreateColumnTypeForSByte) to specify database-specific type names. XPO uses these methods to generate a SQL script when it creates a new database or updates a database schema.

• FormatXXX methods (for instance, FormatTable, FormatColumn, FormatSelect) to specify database-specific SQL commands to perform corresponding operations.

• CreateDatabase, CreateConnection, CreateParameter, GetParameterName, WrapException, IsConnectionBroken, and similar methods to implement the corresponding functionality. Use your RDBMS ADO.NET driver.