XPO POST 4: Connection Strings

by | Sep 27, 2018 | DevExpress XPO from step 1 to N, XPO

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

  1. 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.
  2. 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.

XPO POST 3: The XPO Providers

by | Sep 26, 2018 | DevExpress XPO from step 1 to N, XPO

An XPO provider is a class that XPO uses to communicate natively to the RDBMS, most of these providers are subclasses of the base provider for SQL provider that lives in DevExpress.Data assembly.

Why is important to know how each provider works? here are some of the reasons:

  • You can change the default column type created for any of the .net data types when is translated to a database column type
  • You can alter the queries which the XPO provider generate so they will be more suited for your requirements, example handle the Index and tablespaces in Oracle
  • Support for more specialized connection strings for your RDBMS.
  • Support another version of a database engine that is currently not supported on XPO. One of my first task as an XPO user was to extend the XPO provider for the pervasive database to add support for an older version of the DBMS.

Each XPO providers is registered with a unique ID, this ID is used on the connection string as the value for the XpoProvider parameter. The ID allows XPO to know which provider should be used to create an IDataStore. You can see how XPO use the XpoProvider parameter in the connection string here

The methods that XPO actually use to create the data store is GetConnectionProvider and it will return an IDataStore instance. The main class that implements IDataStore is DataStoreBase that is the base class for ConnectionProviderSql and all the XPO providers.

When you add or extend an XpoProvider you need to create a new unique ID for it and register on your application startup. Here is an example of how to create and register your custom XpoProvider

XPO POST 2: The processor architecture

by | Sep 18, 2018 | DevExpress XPO from step 1 to N, XPO

Now that we have installed XPO is time to talk a little bit about processor architecture. If you are using a 64 bits processor you can compile your .net application with the following architectures

  1. Any CPU
  2. x64
  3. x86

XPO depends on ADO.NET DataAdapters in order to connect to the different databases and ADO.NET depends on DbProviderFactory to load the correct DataAdapter

Now that we know about all the processor’s architectures and DataAdapters we can understand which adapter will be loaded by processor architecture, see the following table:

Processor architecture X86 OS X64 OS
Any CPU Will load the 32 bits Data Adapters Will load the 64 bits data adapter
X64 Won’t run on 32 bits OS Will load the 64 bits data adapter
X86 Will load the 32 bits Data Adapters Will load the 32 bits Data Adapters

 

This does not seem important now, but I come from a time when 64 bits what’s not common at all so sometimes when you were developing your application you end up working on 64-bits OS running a 32-bits database and connecting to the database using the 32-bits DataAdapters.

So, you were not able to compile your application using “Any CPU” because that will try to instantiate a DataAdapters on its 64 bits version.

Nowadays most of the DataAdapters are either Any CPU or the adapter has been compiled to each supported architecture.

There is a well know scenario where you try to connect to an MS Access Database using the OleDb.Net provider and you won’t be able to connect on 64 bits OS since XPO will ask the DbProviderFactory to instantiate an OleDb Data Adapter for 64 bits architecture and that actually does not exist.

In conclusion, knowing how ADO.NET uses the DbProviderFactory will help you to understand which DataAdapter will XPO try to load in your application.

XPO POST 1: How to obtain the XPO library

by | Sep 18, 2018 | DevExpress XPO from step 1 to N, Nuget, XPO

Well, let’s start at the beginning. How do I gain access to use XPO, well there are several install XPO on your next DotNet project, here is a list of them.

  1. If you own any of DevExpress license you already have access to XPO you can see the available licenses here
  2. Download the NuGet package: XPO just recently became free to use class library, that’s right, you get to use the most powerful ORM on the DotNet world for free, no questions asked, you can read more about that here. Now to obtain the NuGet package you can use any of the following approaches:

a) Use the console command: Install-Package DevExpress.Xpo -Version 18.1.6

b) Search for the NuGet package using the following package id “DevExpress.Xpo”

Any XPO project should contain references to at least 2 libraries

  • DevExpress.Xpo.vXX.X.X in this library you will find all the base objects and attributes necessary to create  persistent objects
  • DevExpress.Data.vXX.X.X, in this assembly you will find all the necessary objects to query the database using criteria operators and you will also find the ConnectionProviderSql used as a base class for all XPO Providers

After installing the NuGet package or adding the assembly references you are ready to start creating your O.R.M classes

How to install all the nuget references to run XPO with SQLite on Xamarin Android, iOS and Forms with a single package

by | Sep 18, 2018 | Nuget, Xamarin, XPO

When I got the news that XPO will run on DotNetCore, NetStandard and Xamarin I was super excited about all the new possibilities and to be able to port all the years of experience with XPO to the Xamarin platform.

A few days after the announcement of XPO being able to run on DotNetCore and NetStandard developer express publish a video tutorial and the source on GitHub.

As always like in any video tutorial most of the setup steps are not shown, so you need to do a little research to make your personal project work and I would say most of the time these steps are not that obvious.

I decided to test XPO on Xamarin forms and save data on an SQLite database, so as with every Xamarin project the first step is to install all the necessary nuggets for your project. So, to make it easier for myself I check the source code that DevExpres publish and I was surprised about how many references you need to run SQLite on both Android and iOS platforms. If you want to check the complete list of nuget references needed, click on the following links

To set up all the nuggets reference on both platforms took me around 15 minutes which I think it’s a lot, but then I said, “it does not matter, you only have to do this once right”… well no, since I was so excited that XPO is now able to run on Xamarin I started to create a lot of test projects and migrate some old projects too and every time I have to run to the process of install all the nuggets references.

So, to make my life easier I decided to create a NuGet package with all the references for each platform so here they are

On iOS you will need to add the following lines to your application class inside of the main method

//Initialize SQLite with the sqlite3 provider

SQLitePCL.Batteries_V2.Init();

I hope both Nugets will save you time when you start using XPO on Xamarin