Database Systems Supported by XPO
- 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.
Database Engine | Version | XPO Data Store Adapter (Connection String Identifier) | Database Provider Assembly |
---|---|---|---|
DB2 | DB2 9. | DB2Connection (Xpo | IBM. |
Firebird | Firebird 1. Firebird 2. Firebird 3. |
(Xpo | Firebird Firebird |
In-Memory | None | In (Xpo | 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. 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 & Local SQL Azure™ Database |
(Xpo | System. Microsoft. |
My | My My My My | My (Xpo | My My My |
Oracle | Oracle 9i Oracle 10g Oracle 11g Oracle 12c Oracle 18c Oracle 21c | ODPManaged (Xpo | Oracle. |
Postgre | Postgre Postgre Postgre Postgre Postgre Postgre Postgre | Postgre (Xpo | Npgsql. Npgsql. |
SAP HANA | SAP HANA 2. | Hana (Xpo | Sap. |
SQLite | SQLite 3 | SQLite (Xpo | System. Microsoft. |
Vista | Vista | Vista (Xpo | Vista |
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.
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
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:
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
MSSqlConnectionProvider:
MSSqlServer
XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true
MySqlConnectionProvider:
MySql
XpoProvider=MySql;Server=MyServerAddress;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Persist Security Info= true;Charset=utf8
ODPManagedConnectionProvider:
ODPManaged
XpoProvider=ODPManaged;Data Source=TORCL;User ID=MyUserName;Password=MyPassword
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
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:
#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.