Dynamic Connection String in Database First Entity Framework with Azure

Hello,

In this post, I will show you how to make a dynamic connection string in a Database First Entity Framework. For your information, a Database First is  when you create your Entity Framework model from an existing SQL Database Schema.

First think that you have to know is, when you create the model with Visual Studio, a partial class will be created in your model.

This class contains the constructor of your Entities object and inherits from DbContext class.

public partial class MyDbContext : DbContext
{
//this constructor is when you want to use the connection string coming from the app.config
public MyDbContext()
: base("name=MyDbContextConnectionString")
{
}
...
}

By default this class is looking in the app.config or web.config for the connection string object to be able to connect you to your database.

Hopefully, Entity Framework allows you to override another constructor in this partial class and this constructor is taking a connection string as parameter (Oh great!)

So you just have to add this constructor as display below :

public MyDbContext()
: base("MYCONNECTIONSTRINGHERE"){
}

But ok great we made another constructor but how to make it dynamic ? this is really easy !

In every connection string you have the server, the username, the password, …….  to connect you to the database. This is a string so like any other strings you can simply edit it and pass parameter to the function, there is no limit !

The example below demonstrates how to pass the server address, the username and the password :

//this constructor is when you want to use a dynamic connection string
public MyDbContext(string server, string user, string password)
: base("metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string='data source="+server+",1433;initial catalog=mydb_db;user id="+user+";password="+password+";MultipleActiveResultSets=True;App=EntityFramework'")
{
}

Ok now we can make dynamic connection string, so what about Azure

imagine you have 2 WebApps in Azure, one for your production environment and one other for your test environment. Of course when you develop you don’t want to impact the production with your try and error 🙂

Here comes the power of Azure and the app settings system that allows you to pass some parameters retrieved from the WebApp directly.

So you can easily define specific connection information in your WebApp in dev env, but also different connection information for the prod database in the prod WebApp, without changing your solution code !

I would like to thank Stephane Eyskens MVP (Blog Here) for the code portion, that let you be able to retrieve property from Azure App Setting in C# :

CloudConfigurationManager.GetSetting("MYSETTING");

To learn more about Azure App Setting :
MSDN : Storing Your Secrets in Azure Websites App Settings

How to call it then ?

The following Main class shows you how to instantiate your DBContext object with the Azure App Setting


namespace EntityFrameworkTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new MyDbContext(CloudConfigurationManager.GetSetting("MYSERVER"),CloudConfigurationManager.GetSetting("MYUSERNAME"),CloudConfigurationManager.GetSetting("MYPASSWORD")))
            {
                ...
            }
         }
     }
}
Advertisements
This entry was posted in Azure, C# Solutions and tagged , , , , , , . Bookmark the permalink.

One Response to Dynamic Connection String in Database First Entity Framework with Azure

  1. Nilesh says:

    Very good explanation Loic. Great Blog.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s