dynamic connection string?

Discussion in 'ASP .Net Web Controls' started by kb, Mar 4, 2005.

  1. kb

    kb Guest

    ASP.NET C#

    how can i set this up:

    i have a login page

    if i login as clientA then i want to use clientA database
    if i login as clientB then i want to use clientB database

    both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:

    Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd

    any suggestions would be much appriciated

    thanks
    kb
     
    kb, Mar 4, 2005
    #1
    1. Advertising

  2. kb

    MasterGaurav Guest

    Make connection string as:

    connectionStringKey = ... Initial Catalog = {0}; user=....

    When you want to use connection string:

    string connString = String.Format(connectionStringKey, database_name);

    --
    Cheers,
    Gaurav Vaish
    http://www.mastergaurav.org
    http://mastergaurav.blogspot.com
    ----------------------
     
    MasterGaurav, Mar 5, 2005
    #2
    1. Advertising

  3. kb

    Johnpc Guest

    You could maintain a server side xml file for your user like

    <users>
    <user name='[username]'><sqldb>[dbname]</sqldb></user>
    </users>

    and return the db name for the user when an as needed using

    dbname = xml.selectSingleNode("users/user/[. =
    '[username]']/sqlbd").nodeTypedvalue '!?chech my xpath for correctness

    and insert it into your connection string

    Persist Security Info=False;Data Source=local;Initial Catalog= + dbname
    +;User ID=user;Password=pwd


    "kb" wrote:

    > ASP.NET C#
    >
    > how can i set this up:
    >
    > i have a login page
    >
    > if i login as clientA then i want to use clientA database
    > if i login as clientB then i want to use clientB database
    >
    > both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:
    >
    > Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd
    >
    > any suggestions would be much appriciated
    >
    > thanks
    > kb
    >
     
    Johnpc, Mar 5, 2005
    #3
  4. here's what the appSettings section of your web.config could look like:

    <configuration>
    <appSettings>
    <add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/>
    <add key="OtherConnectionString" value="Server=(local);Database=DBName2;UID=sa;PWD="/>
    </appSettings>
    </configuration>

    This is a nice way to manage it. You can change the connection string
    easily without rebuilding the app or restarting IIS or anything, and the
    change goes into effect immediately.

    Then in your code behinds you can get the connection string like this:
    Dim sConn as string
    If User = UserA Then
    sConn = ConfigurationSettings.AppSettings("DSN")
    Else
    sConn = ConfigurationSettings.AppSettings("OtherConnectionString")
    End If

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net


    "kb" <> wrote in message news:%...
    ASP.NET C#

    how can i set this up:

    i have a login page

    if i login as clientA then i want to use clientA database
    if i login as clientB then i want to use clientB database

    both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:

    Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd

    any suggestions would be much appriciated

    thanks
    kb
     
    Steve C. Orr [MVP, MCSD], Mar 6, 2005
    #4
  5. kb

    quaester Guest

    It depends what you want to achieve, the solution suggested by others is
    workable.

    I've implemented my solution for this scenario by puting my connection
    string in database and prior to login to my portal, all database access is
    using a minimal rights account, which can execute only a few stored procedure
    to get authenticated and retrieve the connection string. In this manner, your
    connection string can be really "dynamic".

    Another method that you may try out is to use the username and password
    (some security stuff need to be taken care here) to login to the sql, if it
    throws you out, you are not athorized. And in your sql, you need to setup
    those accounts for the users (the logins in sql), and ofcourse, the default
    database. That means you will no longer set the initial catalog in your
    connection string, but keep the setting in your sql accounts management.

    there's not really any right or wrong, or which is best, as it depends on
    your architecture and how many users and also how the maintenance work you
    would want.

    "kb" wrote:

    > ASP.NET C#
    >
    > how can i set this up:
    >
    > i have a login page
    >
    > if i login as clientA then i want to use clientA database
    > if i login as clientB then i want to use clientB database
    >
    > both databases are on the same sql server, iam thinking could i have a dynamic connection sting in the web.config or somewhere else? so i would have something like this:
    >
    > Persist Security Info=False;Data Source=local;Initial Catalog= + databaseNameGoesHere +;User ID=user;Password=pwd
    >
    > any suggestions would be much appriciated
    >
    > thanks
    > kb
    >
     
    quaester, Mar 9, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gaurav
    Replies:
    0
    Views:
    16,182
    Gaurav
    Nov 7, 2003
  2. =?Utf-8?B?TWlrZSBNb29yZQ==?=

    Connection String object Convert to String Variable Type

    =?Utf-8?B?TWlrZSBNb29yZQ==?=, Oct 26, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,081
    Norman Yuan
    Oct 26, 2004
  3. =?Utf-8?B?TWlrZSBNb29yZQ==?=

    Dynamic Sql Connection String

    =?Utf-8?B?TWlrZSBNb29yZQ==?=, Sep 15, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    880
    S. Justin Gengo
    Sep 15, 2005
  4. Replies:
    3
    Views:
    859
    Samuel R. Neff
    Aug 3, 2007
  5. Wei  Lu
    Replies:
    2
    Views:
    5,965
    Wei Lu
    Feb 16, 2009
Loading...

Share This Page