Is it possible to leave connectionString dynamically?

Asked

Viewed 588 times

1

I’ve read something and I know it’s possible to leave one connectionString dynamically in an ASP.NET MVC application. However, it is possible to create n connections where each authenticated user in the system has its own connection to a particular database?

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

If anyone knows another way to resolve this situation suggestions will be welcomed, because at my level of knowledge, a web application only has one connection, where n users do the queries, recordings, etc... and if you change this connection changes to all users who are accessing the same.

#EDIT - 12/08 15:18

I got some head start. As I use Entity, follow 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 at the moment after the user login, where in the column of the user table of the main database is the name of connectionString, store the name in the session and use the same.

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

sql asp.net-mvc database entity-framework postgresql

  • It is possible yes, just create multiple conections on the web.config or generate them by concatenating strings.

  • In the option to generate the string, how would I declare the Provider? And today I use the Entity to make the connection

  • When creating your Entities you call the above constructor by passing the string : base("stringdeconexao"). I believe that’s what you want.

  • I tried that, but when I put the string(Server=localhost;Database=DATABASE;User Id=postgres;Password=123456;) and not the same name as in web.config, the Entity generates an exception something like: "Invalid keyword server", because it does not recognize the Provider="Npgsql".

  • Strange, the name of the constructor variable is nameOrConnectionString. Have you ever tried to take a connection string that works on the web.config and play exactly as it is in the constructor? Sometimes the string is wrong.

  • Yes, I’ve tested it that way, I thought it was strange too... But I believe that if you omit Provider, it looks for Sqlserver, which unlike Npgsql does not use Server but Datasource...

Show 1 more comment

1 answer

2


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

public UsuariosContext = new UsuariosContext();

Having this, one can implement this excellent Extension dynamic way connection exchange, 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.
        }
    }
}

Use: 

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

  • A while later, here I am again I had left this solution aside and called for one where I manually put in the Web.config connection string, but it has not been very effective. If I have a standard connection/db, where the system will fetch the user data and only after authentication, the database is changed, at what point do I use the Mudardatabase? After the user authentication?

  • Exactly. The ideal is to maintain a standard context where user information can be easily searched.

  • However, even going through this method and not generating any Exception, the connection still remains the default. As I noticed, for each instance that the main entity is called, connectionString is the default and not the modified one. Don’t you have a way to make this a static method? public Entidadesdb(string str = "Connection") : base(str) ː }

  • Just creating a context for each connection, which I don’t see logic for your case.

  • But then, how do I dynamically and "definitively" change the connection with the bank? Or every time I call the Entity I’ll have to call Mudarbanco ?

  • Every time you call the Entity you’ll have to call Mudarbanco.

  • Was it really that simple? http://i.imgur.com/dJuzHgs.jpg

  • Yes, why should it be complex?

  • http://answall.com/questions/162733/contextos-exclusivos-de-usu%C3%A1rios-autenticadas da uma olhada @Cigano

Show 4 more comments

Browser other questions tagged sql asp.net-mvc database entity-framework postgresql

You are not signed in. Login or sign up in order to post.