How to add the “Provider Name” in Connection Strin

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've added the connection string in the constructor. But is there any way to add the "Provider Name" to the connection string?

Answer 1

Is there a particular reason why you want to have the connection string hard coded in the db context. Normally it should be stored in the config file. You can specify the provider in the config file and refer the connection string from your context. That will solve your problem.

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

And in your config 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 DbConnection type that can be passed to DbContext which was built by the underlying provider and has the connection string built properly.

So to achieve 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>

recall, that DBContext has an overloaded constructor

 public DbContext(DbConnection existingConnection, bool contextOwnsConnection)

So you just need the Dbconnection built by the underlying factory provider. See

 public interface IDbConnectionFactory

which is implmented by these 3 types:

System.Data.Entity.Infrastructure.SqlCeConnectionFactory System.Data.Entity.Infrastructure.LocalDbConnectionFactory System.Data.Entity.Infrastructure.SqlConnectionFactory

So here is an example using SQLConnectionFactory. That returns a DBConnection. Which can be passed to DBContext. You can repeat/change or make variable at your programming leisure. 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;
    }