How to: Manage ConnectionStrings

This topic provides an example on how to manage connection strings in n-tier applications.

N-tier application architecture provides a model for developers to create a flexible and reusable application. By breaking up an application into tiers, developers only have to modify or add a specific layer, rather than rewriting the entire application again. There should be a presentation tier, a business or data access tier, and a data tier.

However, there are scenarios which require separation of the Data Access Layer (Business Object Model) from the application.

The SofiaCarRental.WPF project contains references to SofiaCarRental.Model, and is integrated with the Telerik.DataAccess.Core NuGet package. Suppose, you have the following code in your main (start up) project.

using (FluentModel dbContext = new FluentModel())
{
   Customer customer = dbContext.Customers.FirstOrDefault();
}
Using dbContext As New FluentModel()
 Dim _customer As Customer = dbContext.Customers.FirstOrDefault()
End Using

When you try to execute the previous code in your executable project, you will receive an exception saying something like this: System.Configuration.ConfigurationErrorsException: A connection string was referenced by name 'XXXConnection' but could not be resolved through the web.config or the executables app.config file.

The reason is that all settings (including the connection string) are taken from the executable project (e.g. the SofiaCarRental.WPF project). By default, all database connection details are part of the App.config file that belongs to the class library (e.g. the SofiaCarRental.Model project) that contains the model. Having in mind that the class library is deployed as a compiled assembly and it contains the App.config file as an embedded resource, the OpenAccessContext is unable to retrieve the correct connection string.

The solution is:

  1. Open the App.config file from the project that contains the model.
  2. Locate the <connectionStrings> section.

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
     <connectionStrings>
    
       <add name="SofiaCarRental21Connection" 
            connectionString="data source=(local);initial catalog=SofiaCarRental_v2.2;
                              integrated security=True" 
            providerName="System.Data.SqlClient" />
    
     </connectionStrings>
    </configuration>
    
  3. Copy the connection string.

  4. Open the Web.config file of your project. In case your project doesn't contain a configuration file, you will need to add a new one.
  5. Paste the connection string in the corresponding <connectionStrings> section of the config file in the main (executable) project.

Constructing ConnectionString Manually

An alternative approach is to initialize the OpenAccessContext manually, by using the different overloads of the OpenAccessContext class constructor. For more information, see How to: Manually Initialize an OpenAccessContext.

See Also