Connect an XAF Application to a Database Provider
- 7 minutes to read
When an application runs for the first time, its database is created automatically. During the following runs in a debug mode, the application connects to the same database and updates it as required. When the application runs in a release mode, the database is not updated by default. For details, refer to the Create and Update the Application’s Database topic. If you ever need to have your database recreated, just drop it from the database server or remove the file, and it will be recreated automatically. By default, an XAF solution is configured to use an instance of the Microsoft SQL Server Express on the local system, accessed via the integrated security. A database will be created on that server under the name of the solution you created. However, XPO, EF 6, and EF Core, which serve as an Object-Relational Mapping layer, support over a dozen database management systems. So you can change the default connection. To do this, you need to specify the required connection setting, and reference the required database provider connector assembly. This topic describes several approaches that allow you to connect an XAF application to the required database provider.
Approach 1. Specify the Connection String via the Application Projects’ Configuration Files
When you need to access the connection string in a compiled application, specify it via the application projects’ configuration files.
- App.config - a WinForms application project’s configuration file.
- Web.config - an ASP.NET Web Forms application project’s configuration file.
- appsettings.json - an ASP.NET Core Blazor application project’s configuration file.
By default, these configuration files contain the connectionStrings section, which has a number of commented sample connection strings. You can uncomment and customize one of the existing connection strings, or specify a new connection string in the following way. Note that by default, XAF application projects are configured to connect to Microsoft SQL Server Express LocalDB.
WinForms and Web Forms
<connectionStrings>
<add name="ConnectionString" connectionString=
"Integrated Security=SSPI;Pooling=false;Data Source=(localdb)\mssqllocaldb;Initial Catalog=MySolution" />
</connectionStrings>
Blazor
{
"ConnectionStrings": {
"ConnectionString": "Integrated Security=SSPI;Pooling=false;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=MySolution",
// ...
},
// ...
}
Important
In XPO applications, the initial connection string declaration sets Pooling to false, thus overriding the default true value and disabling SQL Server Connection Pooling. We do not recommend deleting “Pooling = false;“ or setting Pooling to true. XPO uses a separate connection with disabled pooling to create a new database. If pooling is enabled, a SQL server attempts to create a database and tries to use an existing connection from the pool. This causes an error, because the connection is not notified that the database was created.
The Security System requires Multiple Active Result Sets in EF Core-based applications connected to the MSSql database. We do not recommend that you remove “MultipleActiveResultSets=True;“ from the connection string or set the MultipleActiveResultSets parameter to false.
If you connect to a different server, you need to perform two additional steps.
- Add the required database provider to the application projects. The complete list of the assemblies required for different database providers can be found in the following documents:
- In XPO-based applications, specify an appropriate XpoProvider parameter value in the connection string. The complete list of possible values for different database providers can be found in the following section: How to create a correct connection string for XPO providers.
In .NET Framework applications, when the connection string is commented or not specified in a configuration file, the connection string specified in the Application Designer is used. This is provided by the following code, which is automatically added in each XAF solution.
WinForms
public static void Main(string[] arguments) {
MySolutionWinApplication winApplication = new MySolutionWinApplication();
//...
if(ConfigurationManager.ConnectionStrings["ConnectionString"] != null) {
winApplication.ConnectionString = ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString;
}
winApplication.Setup();
winApplication.Start();
//...
}
ASP.NET Web Forms
public class Global : System.Web.HttpApplication {
protected void Session_Start(object sender, EventArgs e) {
WebApplication.SetInstance(Session, new MySolutionWebApplication());
//...
if(ConfigurationManager.ConnectionStrings["ConnectionString"] != null) {
WebApplication.Instance.ConnectionString = ConfigurationManager.
ConnectionStrings["ConnectionString"].ConnectionString;
}
WebApplication.Instance.Setup();
WebApplication.Instance.Start();
}
//...
}
Note
As you can see from the code above, the element named “ConnectionString” is used to obtain the connection string from the configuration file. So do not rename this element unless you change the corresponding string in the application code.
Approach 2. Specify the Connection String via the Application Designer (.NET Framework Applications Only)
You can specify the connection string via the Application Designer‘s Connection section. Select the Connection item in this section to modify the ConnectionString property in the Properties window. Additionally, you can replace the SQL Connection with another available connection. For this purpose, drag the required connection from the Toolbox to the Connection section, and specify the ConnectionString property as required.
When you drag the required connection from the Toolbox to the Connection section, the required database provider assembly is automatically referenced. If the required connection component is not available, right-click the Toolbox to invoke the Choose Toolbox Items dialog, tick off the required connection component and click Ok.
Note
The connection string specified in the application configuration file overrides the connection string specified in the Application Designer. This is done to provide application administrators with an option to change the database connection without recompiling the application.
Get the Connection String to a Database (XPO-Based Approach)
You can obtain properly formatted connection strings via XPO data store adapter classes. These classes are located in the DevExpress.Xpo.DB namespace of the DevExpress.Xpo.v21.2 assembly. Each adapter class contains the GetConnectionString method. Use this method to obtain a connection string to the database using specified parameters. All XPO adapter classes are listed in the Database Systems Supported by XPO topic.
The following code snippet demonstrates how to connect to a “MyApp” MySql database located on the “DBSERVER” server using the “usr” account with the “pwd” password. The MySql.Data assembly must be referenced in the application project.
WinForms
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
//...
public static void Main(string[] arguments) {
MySolutionWinApplication winApplication = new MySolutionWinApplication();
//...
winApplication.ConnectionString =
MySqlConnectionProvider.GetConnectionString("DBSERVER", "usr", "pwd", "MyApp");
winApplication.Setup();
winApplication.Start();
//...
}
ASP.NET Web Forms
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
//...
public class Global : System.Web.HttpApplication {
protected void Session_Start(object sender, EventArgs e) {
WebApplication.SetInstance(Session, new MySolutionWebApplication());
//...
WebApplication.Instance.ConnectionString =
MySqlConnectionProvider.GetConnectionString("DBSERVER", "user", "pwd", "MyApp");
WebApplication.Instance.Setup();
WebApplication.Instance.Start();
}
//...
}
If you want to specify the connection string using the first or the second approach described above, look at the value returned by the GetConnectionString method in the debugger, and copy it to the Application Designer or the configuration file. Note that a string returned by the GetConnectionString method contains the additional XpoProvider parameter. It identifies the database type to which a connection should be established. So, a string returned by the GetConnectionString method is not fully compatible with the standard connection string format. You can use this connection string in configuration files without modification, but the extra parameter has to be removed to use the string in the Application Designer.