Set the SQL Server CE connection string for Entity Framework at runtime

Typically when you create your EF data model wizard will politely add all necessary settings in you app or web.config. That's all fine and dandy, but what if you have SQL Compact Database and you have multiple copies or database names could change and run-time. There is tone of other reasons why you might not want to store your connection strings in plain text config files. Basically you must pass connection string to your Entity Model at run-time.

First we need to extend our generated DbContext class with additional constructor that accepts connection string and pass it on to base class. At the moment of writing, this applies to EF 6.x and EF 5.x and I am using Database first approach, not sure if it works for Code First. Generated DbContext partial class can be found in solution tree if you expand .edmx file it is the one with EdmxFileName.Context.cs name.      

public partial class DBEntities : DbContext
        public DBEntities()
            : base("name=DBEntities")

        public DBEntities(string connectionString)
            : base(connectionString) {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
            throw new UnintentionalCodeFirstException();

And this would be the method for getting connection string:

 public static string GetSqlCeConnectionString(string fileName, string password) {
            var connectionStringBuilder = new SqlCeConnectionStringBuilder();
            connectionStringBuilder.DataSource = fileName;
            connectionStringBuilder.Password = password;
            var entityConnectionStringBuilder = new EntityConnectionStringBuilder();
            entityConnectionStringBuilder.Metadata = "res://*/YourEdmxFileName.csdl|res://*/YourEdmxFileName.ssdl|res://*/YourEdmxFileName.msl";
            entityConnectionStringBuilder.Provider = "System.Data.SqlServerCe.4.0";
            entityConnectionStringBuilder.ProviderConnectionString = connectionStringBuilder.ToString();

            return entityConnectionStringBuilder.ConnectionString;

Next, just pass that connection string to DBContext constructor:

using (var db = new DBEntities(connectionString)) {

If something goes wrong when connecting to database or accessing compiled resources this blog post can be very useful.

About me

Bizic Bojan is Co-Founder of Amida IT-Services GmbH and Software Architect with focus on .NET, C++, Python and Cloud Native solutions. 



The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.