CODEWITHSUNDEEP
c#asp.netasp.net-mvcconnection-string
user2742886
user2742886

Reputation: 1

MVC5 Changing database connection string at runtime

I am looking at using MVC5 to compliment an existing ASP.Net application (that is not MVC).

The app has been configured using database first.

I need to change the database connection string at runtime depending on who is logged in. (This is easy to do in the current app through data adapters).

There are over 1000 existing databases and more can created at runtime - so Web.config is not an option. Schema is common to all databases.

So far, I have managed to switch the database on the Controller - but this means changing substantially the generated code - there must be an easier way! - Help!

Added comment:

The model is a SaaS accounting application. Each database stores all ledgers for a client company and has approximately 125 tables within it. They are kept separate for security and also portability (some accountants even require to download the SQL data in it's entirety for their clients).

Example code:

The site is built on the Contoso University model: http://www.asp.net/mvc/tutorials/mvc-5/database-first-development/setting-up-database

It works in its basic form, but I need the connection changed for each clients data: So looking at the Sales ledger - under the Sales controller we have:

public class SalesController : Controller
{

    private Sales db = new Sales();

.....

The Sales definition is:

public partial class Sales : DbContext
{

    public Sales()
        : base("name=Sales")
    {                                                                        
    }

The "name=Sales" links to the WebConfig and gives it the starting database.

Looking at the : base definition takes me to System.Data.Entity.DbContext which is a locked file!

I have trawled through various sites and tried putting the connection string in the Sales class the following being one of the suggestions:

public Sales(string connString) : base (connString)

but this throws:

Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode. To continue using Database First or Model First ensure that the Entity Framework connection string is specified in the config file of executing application. To use these classes, that were from Database First or Model First, with Code First add any additional configuration using attributes or the DbModelBuilder API and then remove the code that throws this exception

Line 32: protected override void OnModelCreating(DbModelBuilder modelBuilder)

Line 33: {

Line 34: throw new UnintentionalCodeFirstException();

Line 35: }

Line 36:

That's where I am stuck - even if I use the same connection string as in the WebConfig!!

(It mentions Code First mode - which it wasn't?)

Upvotes: 0

Views: 3705

Answers (2)

Chiro
Chiro

Reputation: 1

Add a new parameterised Constructor as explained below. In "Connectionstring" pass your connection like:

string Connectionstring="Data Source=;Initial Catalog=abc;Persist Security Info=True;
User ID=dsds;Password=dsdsd;MultipleActiveResultSets=True";

 public Sales()
        : base("name=Sales")
    {                                                                        
    }

        public Sales(string Connectionstring)
            : base(Connectionstring)
        {

        }

Upvotes: 0

Lotok
Lotok

Reputation: 4607

You could have your data access layer access an interface providing the connection string it uses? In the same way, if using EF, it might be a context rather than a connection string

public interface class IClient
{
    string ConnectionString {get;set;}
}

public class DataAccess
{
    private IClient _connectionString;

    public DataAccess(IClient client)
    {
        _connectionString = client.ConnectionString;
    }
}

This of course would change slightly based on your design. I would probably have the authentication for all clients on the same db, then based on who authenticates a factory could return an IClient which could then be used by DAL, Repository or whatever you have. IClient would not really just have a connection string or context, you could have other properties or methods on there too, relevant to requirements.

Internally, the Repository (for example) might use that context or connection string in the constructor, so it establishes the correct connection. Methods would not change.

Upvotes: 1

Related Questions