When XPO was first released it supported MSSQL Server and MS Access as the main database systems, most of the examples and documentation did not specify how XPO connected to the database, at that time it looks like the connection to the database happened like magic.
There was little to none information about how to connect to a DBRMS that were not MSSQL Server or MS Access
So I decided to post a ticket to developer express asking how can I create the correct connection string for each of the supported database, and that is the origen of this ticket that I use on all my presentations lectures about XPO.
Here is a sample of each of the supported connection strings
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
AseConnectionPrvider: 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
By default, when you start using XPO without specifying a connection string it will connect by to MSAccess.
The XPO connection string is a special type of connection string, it is somehow the same as your regular .net connection string, but it includes the parameter “XpoProvider”, you can learn more about this on my post about the XpoProviders. The need of having the parameter XpoProvider is the connection string is because otherwise, it will be impossible to identify the database engine that you want to use with XPO as you can see on the following screenshot from DevExpress website.
There are 2 special cases that XPO can use a regular connection string
- When you are using an MSSQL Server as your RDBMS, you can just use a regular connection string without the need to specify an XpoProvider.
- When you are using MS Access with OleDb you can also use a just regular connection string
You can see a real-life example when you create an XAF application and you check the app or web config, here is a screenshot of the possible connection strings
Besides the connection string as literal strings, you can also ask the XpoProvider to generate the correct connection string for you on runtime using the static method of “GetConnectionString”. You can see an example of how to use PostgreSqlConnectionProvider to generate the correct connection string, in DevExpress official documentation here. The same goes for all of the XpoProviers, you can information about each provider in the documentation for the DevExpress.Xpo.DB namespace.