[RESOLVED] Create dynamic connection string

how can I able to change my initial catalog before and after authentication?

my logic is to check the if the project exist in a global database
if it does, then change the connectionstring to that project database to validate the users account.

the reason is that I have a multiple project databases with each has users table.

TIA

I'm using c# MVC4 and Entity Framework Database first.

Code:

---

var builder = new SqlConnectionStringBuilder(connectionString);

builder.InitialCatalog = initialCatalog;

conectionString = builder.ConnectionString;

---

That's how you change attributes in a connection string before connecting. If you want to change the database for an open connection then you'd call its ChangeDatabase method.

Ah, just caught the EF bit. In that case you would have to create a new DbContext for a new connection string. I've only done it with an ObjectContext but I assume that a DbContext will accept a connection string when you create it too.

i don't find any source that would dynamically change connectionstring using dbContext, should i use ObjectContext instead? so i could change connection at runtime without the need of web.config or app.config? I would be dealing with a lot of databases.

I just threw together a quick test and here's what the DbContext looks like:

Code:

---

'------------------------------------------------------------------------------
' <auto-generated>
'    This code was generated from a template.
'
'    Manual changes to this file may cause unexpected behavior in your application.
'    Manual changes to this file will be overwritten if the code is regenerated.
' </auto-generated>
'------------------------------------------------------------------------------

Imports System
Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure

Partial Public Class Database1Entities
    Inherits DbContext

    Public Sub New()
        MyBase.New("name=Database1Entities")
    End Sub

    Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
        Throw New UnintentionalCodeFirstException()
    End Sub

    Public Property People() As DbSet(Of Person)

End Class

---

Note that there is only one constructor and it has no parameters, so that's the only way to create an instance. You can add a partial class and add a new constructor that takes a connection string as a parameter. That base constructor that the code above is calling accepts either the name of a connection string in the config file or an actual connection string. Your constructor can simply pass on the connection string it receives as an argument. E.g.

Code:

---

Partial Public Class Database1Entities

    Public Sub New(connectionString As String)
        MyBase.New(connectionString)
    End Sub

End Class

---

I'm not 100% sure but I believe that it will accept a full EF connection string or just a standard ADO.NET connection string. You can experiment a little to see what works.

was able to achieved this by modifying the file DBModel.Context.cs
from

Code:

---

public partial class DBEntities : DbContext
    {
        public DBEntities()
            : base("DBEntities") { }

      ....
    }

---

to..

Code:

---

public partial class DBEntities : DbContext
{

    public DBEntities(string mydbname = "DB01")
            : base(@"metadata=res://*/Models.DBModel.csdl|res://*/Models.DBModel.ssdl|res://*/Models.DBModel.msl;provider=System.Data.SqlClient;provider connection string='data source=(local);initial catalog=" + mydbname + ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'")
        {

....
}

---

and call in my controller

Code:

---

//to use default db
 public DBEntities context = new DBEntities();

//or use with target db
 public DBEntities context = new DBEntities("DB02");

---

it works for me now..i don't know if it's the proper way of doing this in c#.
thanks for the help. if you have other way please do post and share. TIA