Dynamic connection string for entity framework code first for multiple database providers at runtime EF6 ( mssql, mysql, oracle )

c# entity-framework entity-framework-6 mysql sql-server

Question

I am trying to connect multiple database provider using entity framework code first model, by generating connection string dynamically. But it's not working as expected.

Here is code to review :

 switch (providerName)
        {
            case "System.Data.SqlClient":
                entityBuilder = new EntityConnectionStringBuilder();
                entityBuilder.Provider = providerName;
                //ConfigurationManager.ConnectionStrings["mssql"].ConnectionString
                break;
            case "MySql.Data.MySqlClient":
                entityBuilder = new EntityConnectionStringBuilder();
                entityBuilder.Provider = providerName;
                //ConfigurationManager.ConnectionStrings["mysql"].ConnectionString
                break;
            default:
                break;
        }

        switch (providerName)
        {
            case "System.Data.SqlClient":
                conn = new SqlConnectionStringBuilder(entityBuilder.ProviderConnectionString)
                {

                    UserID = databaseUserId,
                    Password = databasePassword,
                    DataSource = serverName,
                    IntegratedSecurity = false,
                    InitialCatalog = databaseName

                }.ConnectionString;

                break;
            case "MySql.Data.MySqlClient":
                conn = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder(entityBuilder.ProviderConnectionString)
                {
                    Server = serverName,
                    UserID = databaseUserId,
                    Password = databasePassword,
                    Database = databaseName,
                    PersistSecurityInfo = true

                }.ConnectionString;
                break;
            default:
                break;
        }

Web.config

<entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="mssqllocaldb" /> </parameters> </defaultConnectionFactory> <providers> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider></providers> </entityFramework>

 <DbProviderFactories>
  <remove invariant="MySql.Data.MySqlClient" />
  <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>

DbContext constructor

  ` public partial class ModelCodeFist : DbContext
{
    public ModelCodeFist(string connection)
        : base(connection)
    {
    }
}

`

Also how can i change default provider at runtime, as EF takes sql connector as defaultConnectionFactory

Dynamically connecting to mssql is working, but for mysql it's generating error because the default connector is still sqlclient, please check image below : enter image description here

Let me know, if i missed something Thanks!

1
1
5/16/2017 6:26:36 AM

Popular Answer

You want to create your own ProviderFactory implementing IDbConnectionFactory

public class MyProviderFactory : IDbConnectionFactory
{    
    public DbConnection CreateConnection(string nameOrConnectionString)
    {
        if ("mySqlName".equals(nameOrConnectionString))
        {
            return new MySqlConnection("<Connection String>");
        }
        else 
        {
            return new SqlConnection("<Connection String>");
        }
    }
}

Then set this in your app.config as the DefaultConnectionFactory

<defaultConnectionFactory type="MyAssmebly.MyProviderFactory, MyAssmebly">
  <parameters>

Not tested code, certainly not performance tested!

0
6/4/2018 11:39:00 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow