MVC Entity Framework MSSQL and MySQL in same project

[XgrinderX]

I am brand spanking new to MVC, leaning MVC4 at the moment due to access to a video tutorial on the subject.

My project will require access to both MSSQL and MySQL. There will be a configuration table in MSSQL that holds the info for various clients, including database connection info. Some will be MSSQL and some will be MySQL with the possibility of different server names, db names, users, and passwords being used to connect to the various databases.

So my default connection and provider info is set to MSSQL in the web.config. I then have a class as follows to connect to the support database to get the connection info:

public class dbSupport : DbContext
{
[indent]public dbSupport() : base("name=DefaultConnection") {}
public DbSet<ClientInfo> ClientInfo {get; set; }[/indent]
}

That code works perfectly, I can retrieve the client database connection info (including provider MSSQL or MySQL) based on an ID passed in from a form.

Now what I would like to do is also have the following:

public class dbData : DbContext
{
[indent]public dbData(String strConnection) : base(strConnection) {}
public DbSet<ClientData> ClientData {get; set; }[/indent]
}

I want to be able to take the connection info I got from the support table and pass it into the dbData class somehow and have it connect to the correct server/database combo to retrieve the data - but I cannot figure out how to do this. I have tried using SQLConnectionStringBuilder and MySqlConnectionStringBuilder to build a connection string and pass it into the dbData constructor but I keep getting errors when it tries to connect, usually a provider error when I look at the InnerException info. I don't know how to tell it to use the MySql provider instead of the default MSSQL provider.

For reference, here is a sample connection string being passed in for MySql (taken from debug info at break point):
server=localhost;database=clientdb;user id=uid;password=somepassword

But how to tell it to use MySql provider instead of MSSQL? Can anyone help?

 

[jbenson001]

I don't know exactly how you are building your strings, as you haven't provided any code.
But, you have to add the provider to the connection string if you haven't.. for example : "ProviderName=MySql.Data.MySqlClient"

 

[XgrinderX]

Thanks for the quick response!

Here's the code I was using to generate the connection string:

// Get database connection info
var clientInfo = _db.ClientInfo.Find(Id);
string dbProvider = clientInfo.dbType.ToString();
string dbServer = clientInfo.dbServer.ToString();
string dbName = clientInfo.dbName.ToString();
string dbUser = clientInfo.dbUser.ToString();
string dbPassword = clientInfo.dbPassword.ToString();

// intialize connection string builder
string providerString = "";
if (dbProvider == "MySql.Data.MySqlClient")
{
	MySqlConnectionStringBuilder sqlBuilder = new MySqlConnectionStringBuilder();
	// set properties for the data source
	sqlBuilder.Server = dbServer;
	sqlBuilder.Database = dbName;
	sqlBuilder.UserID = dbUser;
	sqlBuilder.Password = dbPassword;
	// build the connection string
	providerString = sqlBuilder.ToString();
}
else
{
	SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
	// set properties for the data source
	sqlBuilder.DataSource = dbServer;
	sqlBuilder.InitialCatalog = dbName;
	sqlBuilder.UserID = dbUser;
	sqlBuilder.Password = dbPassword;
	// build the connection string
	providerString = sqlBuilder.ToString();
}

After your advice about adding the provider info, I did some research and added the following code:

// initialize entity connection string builder
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

// set provider name
entityBuilder.Provider = dbProvider;

// set provider specific connection string
entityBuilder.ProviderConnectionString = providerString;

string dbConnString = entityBuilder.ToString();

And so now, the dbConnString looks like this when I set a break point and view the debug info:

provider=MySql.Data.MySqlClient;provider connection string=\"server=localhost;database=clientdb;user id=uid;password=somepassword\"

Now the InnerException is: Keyword not supported: 'provider'.

Appreciate any furthur guidance you can offer.

 

[jbenson001]

Have a look at this very helpful website:

http://www.connectionstrings.com

 

[XgrinderX]

I updated the connection string to look more like what is shown at

http://www.connectionstrings.com,

but I still get "Keyword not supported: 'provider'"

Here's what my updated string looks like:

provider=MySql.Data.MySqlClient;server=localhost;database=clientdb;user id=uid;password=somepassword

How can I tell the entity framework to use a specific provider and then pass it the connection string it needs?

 

[jbenson001]

Try

[b]providerName[/b]="MySql.Data.MySqlClient"

 

[XgrinderX]

Unfortunately that doesn't work either: Keyword not supported: 'providername'

This seems like it should be simple but for some reason I can't get it. I would think this is something that would be fairly commonly needed and I can't find any concrete examples showing how to do it. Thanks for your suggestions so far. If you have any other ideas, I am all ears!!