Current filter:
                                You should refresh the page.
                                  • I am using XpoDefault.GetDataLayer method to initialize the Data Access Layer in my application. How can I properly build a connection string for any supported database provider?

                                1 Solution

                                Creation Date Importance Sort by

                                It is impossible to identify the database type by a connection string. So, XPO requires you to add an additional parameter into the connection string to specify the provider type. The parameter name is XpoProvider. So, the connection string will be like this: XpoProvider=Provider name;Connection string. You can obtain the provider name from a XpoProviderTypeString public constant. Each provider has this constant defined. Below is a list of values of the XpoProvider parameter for each existing database provider:
                                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

                                See Also:
                                How to connect XPO to a database other than SQL Server or Access
                                Database Engines Supported by XPO
                                The Connection String Reference
                                How to use MS SQL Server instead of MS Access as a persistent storage

                                Show all comments
                                • Mr Ying LI 02.28.2015

                                  Sorry, I have a related question. Is there a dialog class to modify/change the existing connection string at run time?

                                  For XPO supports different databases, maybe DevExpress has built one already in the package.

                                  Thank you in advance,

                                  Ying

                                • Alexey (DevExpress Support) 03.01.2015

                                  Hello,

                                  To process your recent post more efficiently, I created a separate ticket on your behalf: T214377: Change connection at runtime. This ticket is currently in our processing queue. Our team will address it as soon as we have any updates.

                                • Wesam Abdallah (JPMC) 02.05.2016
                                  For ASE 15

                                  I get the following error
                                  [C#]
                                  Could not load type Sybase.Data.AseClient.AseConnection

                                   
                                • Jannet (DevExpress Support) 02.05.2016
                                  Hello,
                                  To handle your inquiry in the most efficient manner, let's continue our discussion at Could not load type Sybase.Data.AseClient.AseConnection​.
                                • Dhiogo Roberto 08.17.2016
                                  You can connect to excel files using ODBC? 
                                • Michael (DevExpress Support) 08.17.2016
                                  @Dhiogo: All existing XPO providers are based on the ADO.NET API. We don't have a built-in connection provider for ODBC. You can try to create a custom provider by implementing the IDataStore interface. Refer to the How to create a custom XPO connection provider and then use it in an XAF application article.
                                  Should you have additional questions, please submit a new ticket and describe your requirements in detail.

                                • Ário 03.19.2017
                                  Hi!
                                  How I connect to xls/xlsx files?

                                  thanks!
                                • Uriah (DevExpress Support) 03.20.2017

                                  Hi Aljr,

                                  I've created a separate ticket on your behalf (T494248: How to connect XPO xls/xlsx files). It has been placed in our processing queue and will be answered shortly.

                                • Jose Ojeda 09.01.2017
                                  I have been struggling for long time to generate the correct connection string, most of the time I have to google it and then do copy and paste.

                                  but after fall in love with Code Rush, I decided to create some shortcuts and templates for each connection string. You can download the templates from here

                                  http://www.bitframeworks.com/coderush/xpo-xaf-connectionstring-shorcuts-coderush-roslyn/

                                  you need to have installed code rush for Roslyn  
                                • Anatol (DevExpress Support) 09.01.2017
                                  Thank you for your contribution, Jose. We highly appreciate it.
                                • Liam Dawson 1 10.18.2017
                                  First of all I want to say that this thread was incredibly helpful for me. However, I have a small questions pertaining to the PostgreSQL connection string. When a Postgre connection is added to the custom connections it requires a ServerName, Username, Password, DatabaseName, and possibly a Port number. However, in the PostgreSQL Provider, there isn't a declaration of Port.  My question is thus, is there a second PostgreSQL Provider string if the Port is set, or is the port simply unneeded?
                                • Liam Dawson 1 10.18.2017
                                  Additionally, I just noticed the same issue with the MySQL connections as well.
                                • Uriah (DevExpress Support) 10.19.2017

                                  Hello Liam,

                                  I've created a separate ticket on your behalf (T566679: Is there a second PostgreSQL Provider string if the Port is set, or is the port simply unneeded). It has been placed in our processing queue and will be answered shortly.