Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
302 views
in Technique[技术] by (71.8m points)

c# - Connecting to database for Multi-Tenant application?

I am in the process of creating a Multi-Tenant asp.net application.

I am deciding between separate MSSQL databases or schemas.

However, I cannot find any information on how I can change the database (ideally) or user account dynamically, depending on which one the user should connect to.

I will most likely have a Base Table which defines which database the user should connect to.

Doing this using Linq to SQL is easy but I am not using Linq everywhere because the tables are quite dynamic and the schema is like to change very often.

What is the best method of doing this? I am happy to look at using Schemas, but I don't want it to become very messy but I would also need to do a similar approach but somehow impersonate that user in the same way to pickup a default schema.

I know you can dynamically change the web.config connection string but I have tried that and it physically changes the file contents which also refreshes the app pool and causes me lots of other issues.

Thanks

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If you have set up multiple database for multiple tenants, you can create connection string based on the tenant.

You can simply add an overload to your db context constructor that accepts connection string as input:

public partial class SampleDbEntities
{
    public SampleDbEntities(string connectionString)
        : base(connectionString)
    {
    }
}

Then wherever you need to create an instance of your db context, use this overload and inject suitable username and password in the connection string based on your tenant detection strategy.

For example when your connection string looks like this:

var connectionTemplate =
    @"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;" +
    @"provider=System.Data.SqlClient;" +
    @"provider connection string=""data source={0};" +
    @"initial catalog={1};" +
    @"persist security info=True;" +
    @"user id={2};" +
    @"password={3};" +  
    @"MultipleActiveResultSets=True;App=EntityFramework""";

string connection = string.Format(connectionTemplate, 
    @"(localdb)v11.0", @"TestDB", @"user1" , @"password1");

var db = new SampleDbEntities(connection);

Note:

  • Create connection string template based on the connection string which is in your web.config.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...