How to add the "Provider Name" in the connection string to the context file?

Question

I am using Entity Framework 5 Code-first approch. Here is my context file:

using IMS.Domain.Inventory; using IMS.Domain.Security; using IMS.Domain.StoredProcedures; using System; using System.Collections.Generic; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Objects; using System.Linq; using System.Text; using System.Threading.Tasks; namespace IMS.Domain.DBContext { public class IMSDBContext : DbContext { public DbSet<ModuleAccounting> ModuleAccountings { get; set; } public DbSet<ModuleInfo> ModuleInfos { get; set; } public DbSet<ModuleType> ModuleTypes { get; set; } public DbSet<UserAccounting> UserAccountings { get; set; } public DbSet<UserGroup> UserGroups { get; set; } public DbSet<UserInfo> UserInfos { get; set; } // // set a connection string public IMSDBContext() // Constructor of the Context { this.Database.Connection.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=IMSDB;Data Source=.\\SQLExpress"; } } 

}

Here I added a connection string in the constructor. But is there a way to add the “provider name” to the connection string?

Answer 1

Is there a specific reason why you want the connection string to be hardcoded in the db context. Usually it should be stored in a configuration file. You can specify the provider in the configuration file and specify the connection string from your context. This will solve your problem.

  public MyDbContext() : base("Name=MyDbContext") { } 

And in your configuration file

 <connectionStrings> <add name="MyDbContext" connectionString="data source=.\sqlexpress;initial catalog=YourDbName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient"/> </connectionStrings> 

Answer 2

Yes: You can prepare a type of DbConnection that can be passed to a DbContext that was created by the base provider and has the correct connection string.

So, to reach this connection string in CODE ... see below

 <connectionStrings> <add name="MyCTX" connectionString="Data Source=localhost;Initial Catalog=MYDB ;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /> </connectionStrings> 

remember that DBContext has an overloaded constructor

  public DbContext(DbConnection existingConnection, bool contextOwnsConnection) 

So you just need Dbconnection built by the base factory provider. Cm

  public interface IDbConnectionFactory 

which is implanted with types of the 3rd type:

System.Data.Entity.Infrastructure.SqlCeConnectionFactory System.Data.Entity.Infrastructure.LocalDbConnectionFactory System.Data.Entity.InfrastructureSqlConnectionFactory

So, here is an example using the SQLConnectionFActory. This returns a DBConnection. Which can be passed to DBContext. You can repeat / change or make a variable on your program break. For the other 2 providers.

  public DbConnection GetSqlConn4DbName(string dataSource, string dbName) { var sqlConnStringBuilder = new SqlConnectionStringBuilder(); sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource; sqlConnStringBuilder.IntegratedSecurity = true; sqlConnStringBuilder.MultipleActiveResultSets = true; // NOW MY PROVIDER FACTORY OF CHOICE, switch providers here var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString); var sqlConn = sqlConnFact.CreateConnection(dbName); return sqlConn; }