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.v23.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.

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());
}

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:

• 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:

  • 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.