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:
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:
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?
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?
Talk To Other Members
RE: MVC Entity Framework MSSQL and MySQL in same project
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
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#
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
RE: MVC Entity Framework MSSQL and MySQL in same project
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
CODE
RE: MVC Entity Framework MSSQL and MySQL in same project
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!!