Database Systems Supported by XPO
- 6 minutes to read
XPO supports multiple database systems. 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.
Note
XPO implements MS Access, MS SQL Server, and MS SQL Azure database providers and incorporates the corresponding data store adapters (see AccessConnectionProvider and MSSqlConnectionProvider, respectively). With an InMemoryDataStore (not intended to be used with large data sets), XPO allows you to create an in-memory data store and manipulate objects within it.
All XPO data store adapters are declared in the DevExpress.Xpo.DB namespace of the DevExpress.Xpo.v20.2.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 provider.
Database Engine | Version(s) | XPO Data Store Adapter | Database Provider Assembly |
---|---|---|---|
DB2 | DB2 9.7.4 | DB2ConnectionProvider | IBM.Data.DB2.Core.dll 1.2.2.100 (NuGet) |
Firebird | Firebird 1.5, Firebird 2.5.7, Firebird 3.0.2 | FirebirdSql.Data.Firebird.dll 2.5.1.0 FirebirdSql.Data.FirebirdClient.dll 5.12.1.0 (NuGet) | |
MSSqlServer | Microsoft SqlServer 7.0, Microsoft SqlServer 2000, MS SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005, SQL Server 2005 Express Edition, SQL Server 2008, SQL Azure™ Database, SQL Server 2008 R2, SQL Server 2008 R2 Express, SQL Server 2012, SQL Server 2012 Express (including LocalDB), SQL Server 2014, SQL Server 2014 Express (including LocalDB), SQL Server 2016, SQL Server 2016 Express (including LocalDB), SQL Server 2017 | System.Data.SqlClient.dll 4.4.0 Microsoft.Data.SqlClient.dll 1.1.0 (NuGet) | |
MySql | MySQL Server 5.0, MySQL Server 5.1, MySQL Server 5.7, MySQL Server 8.0.12 | MySqlConnectionProvider | MySql.Data.dll 8.0.12.0 (NuGet) |
Oracle | Oracle 9i, Oracle 10g, Oracle 11g, Oracle 12c, Oracle 18c | ODPManagedConnectionProvider | Oracle.ManagedDataAccess.Core.dll 2.18.3 (NuGet) |
PostgreSQL | PostgreSQL 7, PostgreSQL 8, PostgreSQL 9, PostgreSQL 10, PostgreSQL 11, PostgreSQL 12 | PostgreSqlConnectionProvider | Npgsql.dll 3.2.5.0 Npgsql.dll 4.0.3.0 (NuGet) |
SQLite | SQLite 3 | SQLiteConnectionProvider | System.Data.SQLite.dll 1.0.88.0 (NuGet) Microsoft.Data.SQLite.dll 3.1.2 (NuGet) |
VistaDB | VistaDB 6 | VistaDB6ConnectionProvider | VistaDB.6.dll 6.0.4.0 (Download) |
Database Engine | Version(s) | XPO Data Store Adapter | Database Provider Assembly |
---|---|---|---|
Advantage | Advantage Database Server v9.1 Advantage Database Server v10.1 Advantage Database Server v11.1 | AdvantageConnectionProvider | 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 | 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 | 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 | IBM.Data.DB2.dll 9.7.4.4 (NuGet) |
Firebird | Firebird 1.5 Firebird 2.5.7 Firebird 3.0.2 | FirebirdSql.Data.Firebird.dll 2.5.1.0 FirebirdSql.Data.FirebirdClient.dll 5.12.1.0 (NuGet) | |
MSAccess | Microsoft Jet | System.Data.dll | |
MSSqlServer | Microsoft SqlServer 7.0 Microsoft SqlServer 2000 MS SQL Server 2000 Desktop Engine (MSDE 2000) Microsoft SQL Server 2005 SQL Server 2005 Express Edition SQL Server 2008 SQL Azure™ Database SQL Server 2008 R2 SQL Server 2008 R2 Express SQL Server 2012 SQL Server 2012 Express (including LocalDB) SQL Server 2014 SQL Server 2014 Express (including LocalDB) SQL Server 2016 SQL Server 2016 Express (including LocalDB) SQL Server 2017 | System.Data.dll Microsoft.Data.SqlClient.dll 1.1.0 (NuGet) | |
MSSqlServerCE | Microsoft SqlServer 2005 Mobile SQL Server 2005 Compact Edition (Microsoft SqlServer 2005 Everywhere Edition CTP) SQL Server Compact 3.5 SQL Server Compact 4.0 | MSSqlCEConnectionProvider | 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.12 | MySqlConnectionProvider | MySql.Data.dll 6.9.9 (NuGet) |
Oracle | Oracle 9i Oracle 10g Oracle 11g Oracle 12c Oracle 18c | OracleConnectionProvider ODPConnectionProvider ODPManagedConnectionProvider | System.Data.OracleClient.dll 2.0.0.0 Oracle.DataAccess.dll 4.122.1.0 Oracle.ManagedDataAccess.dll 4.122.1.0 (NuGet) |
Pervasive | Pervasive PSQL 9 Pervasive PSQL 10 Pervasive PSQL 11 | PervasiveSqlConnectionProvider | 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 | PostgreSqlConnectionProvider | Npgsql.dll 2.2.7.0 (NuGet) Npgsql.dll 3.2.7.0 (NuGet) Npgsql.dll 4.1.19.0 (NuGet) Npgsql.dll 5.0.7.0 (NuGet) |
SQLite | SQLite 3 | SQLiteConnectionProvider | System.Data.SQLite.dll 1.0.88.0 (NuGet) |
VistaDB | VistaDB 4 VistaDB 5 VistaDB 6 | VistaDBConnectionProvider VistaDB5ConnectionProvider VistaDB6ConnectionProvider | VistaDB.4.dll 4.0.0.0 VistaDB.5.NET40.dll 5.2.0.0 VistaDB.6.dll 6.0.4.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, this can be done either by specifying the connection settings of the XpoDefault (via the XpoDefault.ConnectionString or XpoDefault.DataLayer properties) and using the Session.DefaultSession or creating your Session object, or by assigning the Session.ConnectionString or Session.Connection properties of a session. Refer to How to: Connect to a Data Store for complete information on how to connect to a data store.
Example
To connect to the MySql database, add the MySql.Data assembly to your project’s References. Then you can use the following code to create a connection to the database.
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());
}
How To Create a Correct Connection String
Built-in connection providers have the public GetConnectionString
method. This method returns a connection string that contains the XpoProvider
parameter. Use this method if you can create a connection string in code.
You can specify an XPO provider type in a connection string using the XpoProvider parameter: XpoProvider=Provider name;...
The list below contains the XpoProvider
parameter values for all the supported connection providers:
AccessConnectionProvider:
MSAccess
XpoProvider=MSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
DataSetDataStore:
XmlDataSet
XpoProvider=XmlDataSet;Data Source=C:\mydatabase.xml;Read Only=false
InMemoryDataStore:
InMemoryDataStore
XpoProvider=InMemoryDataStore;Data Source=C:\mydatabase.xml;Read Only=false
MSSqlConnectionProvider:
MSSqlServer
XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true
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
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
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