VS 2012 [RESOLVED] Create dynamic connection string-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Create dynamic connection string

  1. #1

    Thread Starter
    Fanatic Member VBKNIGHT's Avatar
    Join Date
    Oct 2000
    Location
    Port25
    Posts
    619

    Resolved [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.

    If a post has helped you then Please Rate it!

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,954

    Re: Create dynamic connection string

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,954

    Re: Create dynamic connection string

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Fanatic Member VBKNIGHT's Avatar
    Join Date
    Oct 2000
    Location
    Port25
    Posts
    619

    Re: Create dynamic connection string

    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.

    If a post has helped you then Please Rate it!

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,954

    Re: Create dynamic connection string

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Fanatic Member VBKNIGHT's Avatar
    Join Date
    Oct 2000
    Location
    Port25
    Posts
    619

    Re: Create dynamic connection string

    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
    Last edited by VBKNIGHT; May 9th, 2013 at 06:23 PM.

    If a post has helped you then Please Rate it!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.