INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MVC Entity Framework MSSQL and MySQL in same project

MVC Entity Framework MSSQL and MySQL in same project

(OP)
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:

CODE --> c#

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

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:

CODE --> c#

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

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?

RE: MVC Entity Framework MSSQL and MySQL in same project

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"

RE: MVC Entity Framework MSSQL and MySQL in same project

(OP)
Thanks for the quick response!

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

CODE --> c#

// 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:

CODE --> c#

// 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.

RE: MVC Entity Framework MSSQL and MySQL in same project

(OP)
I updated the connection string to look more like what is shown at 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?

RE: MVC Entity Framework MSSQL and MySQL in same project

Try

CODE

providerName="MySql.Data.MySqlClient" 

RE: MVC Entity Framework MSSQL and MySQL in same project

(OP)
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!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close