Is it possible to leave connectionString dynamically?

Question

Is it possible to leave connectionString dynamically?

Navigation

#1 by (2 votes)

1

I have read something and know that it is possible to leave a connectionString dynamically in an ASP.NET MVC application. But is it possible to create n connections where each authenticated user on the system has its own connection to a particular database?

You know that banks are identical, just change the location of each.

If anyone knows how to solve this situation, suggestions are welcome, because at my level of knowledge, a web application has only one connection, where n users make queries, recordings, etc ... and if you change this connection changes to all users who are accessing it.

#EDIT - 08/08 15:18

I made some progress. As I use Entity, here's what I got:

public class Entidades : DbContext
    {

        public static string teste()
        {
            return HttpContext.Current.Session ["conString"] != null ? (string)HttpContext.Current.Session["conString"] : "Conexao";
        } 

        public Entidades() : base(teste()) {}

        public DbSet<Usuario> Usuario { get; set; }
        public DbSet<Clientes> Clientes { get; set; }...
}

This Session is created right after the user login, where in the column of the main database user table the name of the connectionString is stored, I store the name in the session and use it.

But another question arises, how do I, according to the insertion or update of new users and their connectionString, is inserted automatically in the web.config the Connections? Is there such a possibility?

sql entity-framework postgresql asp.net-mvc

asked by anonymous 12.08.2016 / 15:57

1 answer

GroupControl - BackColor does not change datepicker does not work in dynamic form with Js

score 2 · Accepted Answer

What I would do, instead, is to map a context of separate user information. It saves not only the connection data to the user database, but also additional information about the login and its permissions.

public UsuariosContext = new UsuariosContext();

With this, you can implement this great Extension switch dynamically >, which I translate below with some poetic freedoms:

public static class DbContextExtensions
{
    // todos os parâmetros são opcionais.
    public static void MudarDatabase(
        this DbContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        string configConnectionStringName = "") 
        /* Este último parâmetro é usado quando o nome da connectionString
           usado pelo contexto é diferente do padrão, definido no início da 
           aplicação. */
    {
        try
        {

            var configNameEf = string.IsNullOrEmpty(configConnectionStringName)
                ? "DefaultConnection" 
                : configConnectionStringName;

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

            var sqlConnectionStringBuilder = new SqlConnectionStringBuilder
                (entityConnectionStringBuilder .ProviderConnectionString);

            if (!string.IsNullOrEmpty(initialCatalog))
                sqlConnectionStringBuilder.InitialCatalog = initialCatalog;
            if (!string.IsNullOrEmpty(dataSource))
                sqlConnectionStringBuilder.DataSource = dataSource;
            if (!string.IsNullOrEmpty(userId))
                sqlConnectionStringBuilder.UserID = userId;
            if (!string.IsNullOrEmpty(password))
                sqlConnectionStringBuilder.Password = password;

            sqlConnectionStringBuilder.IntegratedSecurity = integratedSecuity;

            // Ponto de mudança da conexão
            source.Database.Connection.ConnectionString 
                = sqlConnectionStringBuilder.ConnectionString;
        }
        catch (Exception ex)
        {
            // Defina aqui seu tratamento de exceção.
        }
    }
}

Usage:

var db = new AplicacaoContexto();
// Defina abaixo apenas os parâmetros que irão mudar.
db.MudarDatabase(
    initialCatalog: "MeuUsuarioDatabase",
    userId: "usuario",
    password: "senha",
    dataSource: @".\sqlexpress"
);