Set ConnectionString for EntityFramework Programmatically.

Question

We have a Database (MySQL) say DemoDB. We have different accounts using the db. So one db for each account is created say Account1DB, Account2DB. The Tables stored procedures and functions are all same. We need to develop a Web API and provide some service for inserting and updating data. The user logs in by specifying AccountName ( say from a WPF application where the fields are UserName, Password and Account ComboBox). We need to set the Initial Catalog of the connection string to the account to which the User Logs in ( Actually we need to set the Initial Catalog even before logging in the user as we need to check his credentials based on account).

An Example WPF Screen:

UserName : A

Password: Secret

Account: Account1

When the user clicks on login this is what I would like to do :

1:-> Set Initial Catalog in Connection String to Account1DB

2:-> Then all the DB Operations(Including Authentication) the user does should be on Account1DB Schema

I would like to go with Database First approach. How do a approach this?

Thanks in Advance,

Abhilash D K

Andrey Alonzov · Accepted Answer · 2017-03-21 09:03:16Z

1

You can change you class derived from DbContext to take connectionString as a parameter and pass it to base DbContext constructor.

public partial class MyContext : DbContext
{
    public MyContext() : base("name=MyDbName")
    {
    }

    public MyContext(string connectionString) : base(connectionString)
    {
    }

    // Class members
}

Then you can use something like connection string factory:

public class ConnectionStringFactory
{
    public string GetConnectionString(string initialCatalog)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.InitialCatalog = initialCatalog;

        // Set another connection parameters

        return builder.ToString();
    }
}

Now you can work with separate contexts, check credentials and perform all the DB operations, something like this:

    ConnectionStringFactory factory = new ConnectionStringFactory();
    string connectionString = factory.GetConnectionString("Account1DB");
    MyContext context = new MyContext(connectionString);

edited Mar 21, 2017 at 9:03

answered Mar 21, 2017 at 7:15

Andrey Alonzov

3572 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Abhilash D K Over a year ago

Hi Tanks for the answer I will try and let you know :)

Abhilash D K Over a year ago

Hi Where should I call GetConnectionString() method? While creating an object of the MyContext class right?

Andrey Alonzov Over a year ago

Hi. I have edited my post to show an example. You should notice that GetConnectionString method is not fully implemented in my example. Though I don't know a real reason of doing thins like this, I would advise you to use database users and rights. Using separate db for each user seems to be not the best choice.

Abhilash D K Over a year ago

Hi the db is not seperate for each user. But it is seperate for each account. Example AccountName is Disney and Topco. Both have differrent resources. We have db_disney where all resources working for Disney is recorded. The same goes to Topco. While Disney user logs in initial catalog should be db_disney. When Topco user logs in Initial Catalog should be db_topco. That is my actual requirement.

Abhilash D K Over a year ago

Can I do this. While adding ADO.NET Entity Data Model we need to set a connection string which will be stored in web.config of the WEB API Project. While user tries to login I can get the connectionstring using ConfigurationManager and then change only the initial catalog?

|

marc_s · Accepted Answer · 2023-03-04 16:44:58Z

0

You can change of course but not preferred. It can be throw exception at runtime without proper message, I challenged like this situation.

You change like this:

DbContext source;  // I assumed you created on

var entitysb = new EntityConnectionStringBuilder
                (System.Configuration.ConfigurationManager
                    .ConnectionStrings["EntityConnection"].ConnectionString);

var sqlsb = new SqlConnectionStringBuilder
                (entitysb.ProviderConnectionString);
source.Database.Connection.ConnectionString 
                = sqlsb.ConnectionString;

edited Mar 4, 2023 at 16:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 21, 2017 at 7:17

orhun.begendi

9373 gold badges16 silver badges32 bronze badges

3 Comments

Andrey Alonzov Over a year ago

To avoid exceptions connection string should be passed to the constructor.

orhun.begendi Over a year ago

by default, if you didn't set any name, it should be "defaultconnection".

Abhilash D K Over a year ago

Hi Tanks for the answer I will try and let you know :)

Collectives™ on Stack Overflow

Set ConnectionString for EntityFramework Programmatically.

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related