C#/Entity Framework 6: Azure SQL/SQL Server, a standard approach to Connection Strings

In this post a strategy will be presented for handling the connection strings associated with Entity Framework models. Additional methods will be developed for the entities class associated with this model. These methods in conjunction with a more consistent approach to managing connection strings will provide an approach that can be applied to on-premises SQL Server instances and Azure SQL databases.

Connection Strings and Project Settings

In a previous post "C#/Entity Framework 6: Project and Connection String Management" an example was shown where a connection string was copied from the App.config of a class library containing an Entity Framework model to the App.config file of an executable invoking the classes associated with the Entity Framework model. An example of the connection string created by Entity Framework within an App.config is as follows and off course the same would be applicable if the connection string was contained in a web.config:

The App.config file above belongs to a console application, MainProgram, that will be used to demonstrated connection string management.

Connection strings are a category of project application settings that can be managed from Visual Studio via a project's properties. To access a project properties right click on the project within Solution Explorer and select Properties:

The Settings properties for the MainProgram console application are as follows:

Remember the App.config file for the MainProgram console application contains a connection string but this connection string does not show up in the settings tab for the project. The connection string in the console application's App.config is as follows (note the name is SchoolEntities):

<add name="SchoolEntities" connectionString="metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

In order for the SchoolEntities connection to be visible in Project | Settings it would need to be named in the following form:
    <application namespace>.Properties.Settings.SchoolEntities

To add a connection to the project's settings (and hence its App.config or web.Config) click in the link:

Clicking on the previous link enables settings to be added to the project. When the Settings tab is displayed, clicking on the Type drop down lists the viable settings types that can be created. To create a connection string select settings type (Connection string) as is shown below:

A name can be entered for the connection string which is shows below to be SchoolEntities:

Within the Value text box of the SchoolEntities setting is a button labeled as .... Click on the button displays the Connection Properties dialog:

From the previous dialog a connection can be configure and tested via the Test Connection Button. Once the connection is configured, clicking on OK will save the connection settings as a connection string within the Value property of the settings entry:

The connection string is saved as follows in the App.config where the newly created connection string is named MainApplication.Properties.Settings.SchoolEntities:

The newly created connection string takes the following form in App.config:
    <add 

      name="MainApplication.Properties.Settings.SchoolEntities"
      connectionString="Data Source=localhost\SQL2016;Initial Catalog=School;Integrated Security=True"
      providerName="System.Data.SqlClient" />

The text of the newly created connection string is follows:

Data Source=localhost\sql2016;Initial Catalog=School;Integrated Security=True

The connection string above is an ADO.NET style connection string

Converting an ADO.NET Connection String to an Entity Framework Format Connection String

The text of the connection string created by Entity Framework also contained in the App.config is as follows:

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;

The the text demarcated in bold face above shows that within the Entity Framework connection string is the actual text of an ADO.NET connection string. It does not take Steven Hawking or Mr. Spock to deduce that String.Format could easily be used to convert an ADO.NET connection string into a connection string suitably for consumption by an Entity Framework model.

The first step in this conversion is to remove the &quote; contained in the string. These are used in App.config because it is an XML and &quote; indicates a double quote within a string. To tweak the string so it is C# ready (String.Format ready) replace &quotl with \".

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework\"

To complete making the Entity Framework connection string usable so String.Format can inject an ADO.NET connection string inside the Entity Framework, replace the existing ADO.NET connection string with a {0}:

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"

Extending the Entity Framework Entities Class

Next we need to determine the appropriate location for the code that converts ADO.NET connection strings into Entity Framework connection strings. Recall that the code used to originally access the Entity Framework model was follows:

To see of Entity Framework implemented the SchoolEntities class, right click on the class name in the source code and select Go to Definition from the context menu:

The code generated by Entity Framework to implement SchoolEntities is as follows as found in the file, DataAccess\School.Context.cs:

The negative of the previous implementation is that there is a lone constructor and requires that connection string exists in the App.config/Web.Cong and this connection string must be named SchoolEntities.

The pro of the previous implementation is that it is a partial class so it is possible to create a second class containing additional constructors, properties and methods associated with the SchoolEntities partial class. The SchoolEntitiesExtra.cs file was added to the DataAccess class library and is implemented as follows in order to provided additional implementation to the SchoolEntities partial class:

using System;
using System.Data.Entity;
using System.Configuration;

namespace DataAccess
{
    public partial class SchoolEntities : DbContext
    {
        private const string _entityFrameworWrapper =            "metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"";

        private SchoolEntities(
             string efConnectionNameorEFConnectionString) : 
                      base(efConnectionNameorEFConnectionString)
        {           
        }

        public static SchoolEntities 
                          CreateWithADOConnectionString(
                              string adoConnectionString)
        {
            string entityFrameworkConnectionString = String.Format(
                       _entityFrameworWrapper, adoConnectionString);

            return 
              new SchoolEntities(entityFrameworkConnectionString);
        }

        public static SchoolEntities 
                        CreateWithEntityFrameworkConnectionString(
                            string efConnectionString)
        {
            return new SchoolEntities(efConnectionString);
        }

        public static SchoolEntities 
                        CreateWithADOConfigurationConnectionName(
                          string adoConnectionName)
        {
            string adoConnectionString = 
                     ConfigurationManager.ConnectionStrings[
                       adoConnectionName].ConnectionString;

            return 
              CreateWithADOConnectionString(adoConnectionString);
        }

        public static SchoolEntities 
               CreateWithEntityFrameworkConfgurationConnectionName(
                   string efConnectionName)
        {
            return new SchoolEntities(efConnectionName);
        }
    }
}

The previous code implements a new connection string which is as follows:

private SchoolEntities(
            string efConnectionNameorEFConnectionString) : 
                      base(efConnectionNameorEFConnectionString)
{           
}

The base class of SchoolEnties is DbContext. The DbContext base class exposes multiple constructors defined in MSDN at DbContext Constructor. A constructor of note is defined as:

The idea is that a SchoolEntities instance can be created if a string is passed to the constructor containing an Entity Framework connection string or the connection name of a Entity Framework connection string stored in App.config/Web.Config.

The following methods within SchoolEntitiesExtra.cs invoke this constructor in order to create an instance of SchoolEntities:

public static SchoolEntities 
                CreateWithEntityFrameworkConnectionString(
                    string efConnectionString)
{
    return new SchoolEntities(efConnectionString);
}

public static SchoolEntities 
       CreateWithEntityFrameworkConfgurationConnectionName(
           string efConnectionName)
{
    return new SchoolEntities(efConnectionName);
}

The method below from SchoolEntitiesExtra.cs converts an ADO.NET formatted connection string into an Entity Framework formatted connection using String.Format as discussed above:

private const string _entityFrameworWrapper =            "metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"";

public static SchoolEntities 
                  CreateWithADOConnectionString(
                      string adoConnectionString)
{
    string entityFrameworkConnectionString = String.Format(
               _entityFrameworWrapper, adoConnectionString);
    return 
      new SchoolEntities(entityFrameworkConnectionString);
}

The following method from SchoolEntitiesExtra.cs takes an ADO.NET connection name as a parameter. The ADO.NET connection string associated with the connection name is retrieved from the application's configuration file (App.config/Web.config). The ADO.NET connection string is passed to CreateWithADOConnectionString in order to create the entities instance:

public static SchoolEntities 
                CreateWithADOConfigurationConnectionName(
                  string adoConnectionName)
{
    string adoConnectionString = 
        ConfigurationManager.ConnectionStrings[
            adoConnectionName].ConnectionString;

    return CreateWithADOConnectionString(adoConnectionString);
}

The ConfigurationManager is a static class. This class's property, ConnectionStrings, is used to look up the ADO.NET formatted connection string by name. At the top of the SchoolEntitiesExtra.cs the namespaces made accessible via using include System.Configuration which is the namespace containing ConfigurationManager. A reference to System.Configuration.dll was added to the DataAccess, class library project.

Azure SQL

Placing the term "Azure SQL" in the title was bit of teaser. The impetus to developing the four methods was because the connection string to Azure SQL generated as project settings are in the for of ADO.NET connection strings. To demonstrate an Azure SQL connection string will be created as part of project seetings. This is achieved as was shown previously by right clicking on the MainApplication project withint Solution Explorer and selecting the Properties entry from the conect menu. When the project Properties are displayed, select the Settings blade:

An additional connection string can be added to settings, in this case the underlying database will be SQL Azure. An example of this is shown below:

To set up the connection string the ... button is clicked on:

An Azure SQL connection is setup usign the Connection Properties dialog just like an on-premise SQL Server connection. The first setp is to enter the server name which Azure calls "SQL Servers" (a rather common sense name. In a previous entry an overview of setting up SQL Azure and determinging the server name on which an Azure SQL dabase resides was presented SQL Azure: Finding the Server Name for SQL Azure Instance.

The Connection Properties dialog filled out to point to an Azrue SQL instance is as follows:

Clicking on the OK button will create a new connection strign this time ponting to a SQL Azure instance. This connection string will be of the form of an ADO.NET connection string but a strategy for converting such a connection string to its corresponding Entity Framework representation has already been presented.

Source Code

The source code for this example is found in GitHub solution, EntityFramework6.V02.Master, in repository: https://github.com/softwarepronto/Blog.