Entity Framework 6 set connection string in code

Question

I have a dll that uses the Entity Framework 6 to do some database operations. I'm using a database first approach. The model and everything concerning the Entity Framework, like the connection string in the App.config, were created via the wizzard in Visual Studio.

So I compiled the dll and put it together with the corresponding .config in the folder where the application using the dll expects it.

Everything works fine until I get to the point where an actual database call is made. There I get the error:

Cannot find connection string for MyDatabaseEntity

The automatically generated connectionstring is, as I said, in the config file of the dll. I cannot change the App.config of the application. But the application hands over an object that has all the information I need to build the connection string myself. So I'm looking for a way to set the connection string in the code without relying on a config file. All the tutorials I find for a database first approach use this method though. I found a post here that says to simply give the connection string as a parameter when creating the Object like

MyDatabaseEntities = new MyDatabaseEntities(dbConnect);

but ´MyDatabaseEntities´ doesn't have a constructor that takes any parameters

public partial class MyDatabaseEntities : DbContext
{
    public MyDatabaseEntities()
        : base("name=MyDatabaseEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<MyTable> MyTable { get; set; }
}

How do we do this using DB First. It will override any changes in my file? — Zapnologica
– Zapnologica, Commented Apr 19, 2018 at 8:56

Alexander Derck · Accepted Answer · 2015-12-18 08:18:36Z

25

How about:

public partial class MyDatabaseEntities : DbContext
{
public MyDatabaseEntities(string connectionString)
    : base(connectionString)
{
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    throw new UnintentionalCodeFirstException();
}

public virtual DbSet<MyTable> MyTable { get; set; }

}

Then initialize your database like you did before:

string myConnectionString = "...";
MyDatabaseEntities = new MyDatabaseEntities(myConnectionString);

answered Dec 18, 2015 at 8:18

Alexander Derck

14.7k5 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

FNR Over a year ago

I wasn't aware that the string at base() was the connection string. Thank you , that did it.

Alexander Derck Over a year ago

Yes, it tries to find the connectionstring with the name specified in the parenthesis in the config file of your startup project

Alexander Derck Over a year ago

@Dev-Systematix You can't used &quot, use single quotes ' instead.

Dev-Systematix Over a year ago

If we will not use EntityConnectionStringBuilder then it will not identify Data Source keyword and raise exception

Ahmed Suror Over a year ago

I tried this but got an exception:

UnintentionalCodeFirstException: 'The context is being used in Code First mode with code that was generated from an EDMX file for either Database First or Model First development.  This will not work correct ...

and to get rid of that, I delete the line: throw new UnintentionalCodeFirstException();, but there is other problem, which is every time I Update model from database the partial DbContext class is being regenerated and I lose modifications

|

Mahib · Accepted Answer · 2018-01-09 17:58:38Z

9

I had the similar issue. My Edmx and App.Config was in a different project. My startup project was different, had 3 different connection strings, we need to choose one on the fly depending on the environment. So couldn't use a fixed connection string. I created a partial class overload of the Context.cs using the same namespace. Following was my default Context.cs;

namespace CW.Repository.DBModel
{

  public partial class CWEntities : DbContext
  {
      public CWEntities()
        : base("name=CWEntities")
      {
      }

      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
        throw new UnintentionalCodeFirstException();
      }
  ...
  ...
  }
}

My partial class overload;

namespace CW.Repository.DBModel
{
    public partial class CWEntities : DbContext
    {
        public CWEntities(string ConnectionString)
            : base(ConnectionString)
        {
        }        
    }
}

Lastly, as my connection strings were not for EF, I converted them to a EF connection string.

public static string GetEntityConnectionString(string connectionString)
    {
        var entityBuilder = new EntityConnectionStringBuilder();

        // WARNING
        // Check app config and set the appropriate DBModel
        entityBuilder.Provider = "System.Data.SqlClient";
        entityBuilder.ProviderConnectionString = connectionString + ";MultipleActiveResultSets=True;App=EntityFramework;";
        entityBuilder.Metadata = @"res://*/DBModel.CWDB.csdl|res://*/DBModel.CWDB.ssdl|res://*/DBModel.CWDB.msl";

        return entityBuilder.ToString();
    }

Lastly, the calling

var Entity = new CWEntities(CWUtilities.GetEntityConnectionString(ConnectionString));

edited Jan 9, 2018 at 17:58

answered Jan 9, 2018 at 17:53

Mahib

4,0936 gold badges58 silver badges65 bronze badges

2 Comments

zagZzig Over a year ago

thanks. It could be better if you mention you partial class overload is on another file instead of your default Context.cs as it is auto-generated by Visual Studio and may be overwritten by Visual Studio.

SaddamBinSyed Over a year ago

@Mahib, I have tried your steps but for the first time calling DB proc is working fine but next time when i call next DbProc its failing with Timeout related error. any advise appreciated.

Dev-Systematix · Accepted Answer · 2017-02-20 13:53:02Z

I got this solution using below code, I can hardcode connection string using C# code without using config file.

 public class SingleConnection
    {
        private SingleConnection() { }
        private static SingleConnection _ConsString = null;
        private String _String = null;

        public static string ConString
        {
            get
            {
                if (_ConsString == null)
                {
                    _ConsString = new SingleConnection { _String = SingleConnection.Connect() };
                    return _ConsString._String;
                }
                else
                    return _ConsString._String;
            }
        }

        public static string Connect()
        {
            //Build an SQL connection string
            SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
            {
                DataSource = "SIPL35\\SQL2016".ToString(), // Server name
                InitialCatalog = "Join8ShopDB",  //Database
                UserID = "Sa",         //Username
                Password = "Sa123!@#",  //Password
            };
            //Build an Entity Framework connection string
            EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
            {
                Provider = "System.Data.SqlClient",
                Metadata = "res://*/ShopModel.csdl|res://*/ShopModel.ssdl|res://*/ShopModel.msl",
                ProviderConnectionString = @"data source=SIPL35\SQL2016;initial catalog=Join8ShopDB2;user id=Sa;password=Sa123!@#;"// sqlString.ToString()
            };
            return entityString.ConnectionString;
        }

and using DbContext using like this:

Join8ShopDBEntities dbContext = new Join8ShopDBEntities(SingleConnection.ConString);

For assignment of property ProviderConnectionString you can use sqlString.ConnectionString.

Cetin Sahin · Accepted Answer · 2018-12-13 08:35:38Z

Thanks a lot . I changed little for Code First EF6.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Data
{
    public class SingleConnection
    {
        private SingleConnection() { }
        private static SingleConnection _ConsString = null;
        private String _String = null;

        public static string ConString
        {
            get
            {
                if (_ConsString == null)
                {
                    _ConsString = new SingleConnection { _String = SingleConnection.Connect() };
                    return _ConsString._String;
                }
                else
                    return _ConsString._String;
            }
        }

        public static string Connect()
        {
            string conString = ConfigurationManager.ConnectionStrings["YourConnectionStringsName"].ConnectionString;

            if (conString.ToLower().StartsWith("metadata="))
            {
                System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder efBuilder = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder(conString);
                conString = efBuilder.ProviderConnectionString;
            }

            SqlConnectionStringBuilder cns = new SqlConnectionStringBuilder(conString);
            string dataSource = cns.DataSource;


            SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
            {
                DataSource = cns.DataSource, // Server name
                InitialCatalog = cns.InitialCatalog,  //Database
                UserID = cns.UserID,         //Username
                Password = cns.Password,  //Password,
                MultipleActiveResultSets = true,
                ApplicationName = "EntityFramework",

            };
            //Build an Entity Framework connection string
            EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
            {
                Provider = "System.Data.SqlClient",
                Metadata = "res://*",
                ProviderConnectionString = sqlString.ToString()
            };
            return entityString.ConnectionString;
        }
    }
}

Ozmen Celik · Accepted Answer · 2019-09-22 20:40:45Z

0

You can use singleton patter for it . For example

private YouurDBContext context;

    public YouurDBContext Context
    {
        get
        {
            if (context==null)
            {
                context = new YouurDBContext();
            }
            return context;

        }
        set { context = value; }
    }

answered Sep 22, 2019 at 20:40

Ozmen Celik

1698 bronze badges

Collectives™ on Stack Overflow

Entity Framework 6 set connection string in code

5 Answers 5

11 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

11 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related