Single Application -> Multiple Databases

Discussion in 'ASP .Net' started by David W, Oct 26, 2007.

  1. David W

    David W Guest

    We have a setup where we have a single web application, but the user can be
    attached to any of a hundred different (identically structured) databases
    depending on their login credentials.

    Currently we are storing the connection string in Session and in each page's
    Page_Init we reset any SqlDataSource's connection string. This works
    reasonably well, though not ideal:
    SqlDataSource1.ConnectionString = Session("dbconn")

    I would like to start experimenting with creating a full data access layer
    using an ObjectDataSource and bound controls. This is easy enough to set up
    after following the tutorials on www.asp.net and I added the DataSet/XSD,
    but I do not see a way to set the underlying connection string. It is
    pulling the original connection string from web.config. I don't see a class
    file that I can edit and I can't access the properties of the DAL through
    the ObjectDataSource.

    Any idea how I can accomplist this?

    Thanks.
     
    David W, Oct 26, 2007
    #1
    1. Advertising

  2. "David W" <> wrote in message
    news:u%...

    > We have a setup where we have a single web application, but the user can
    > be attached to any of a hundred different (identically structured)
    > databases depending on their login credentials.


    Any particular reason for this structure, AAMOI...?

    > Currently we are storing the connection string in Session and in each
    > page's Page_Init we reset any SqlDataSource's connection string. This
    > works reasonably well, though not ideal:
    > SqlDataSource1.ConnectionString = Session("dbconn")
    >
    > I would like to start experimenting with creating a full data access layer
    > using an ObjectDataSource and bound controls. This is easy enough to set
    > up after following the tutorials on www.asp.net and I added the
    > DataSet/XSD, but I do not see a way to set the underlying connection
    > string. It is pulling the original connection string from web.config. I
    > don't see a class file that I can edit and I can't access the properties
    > of the DAL through the ObjectDataSource.


    I guess it would depend on the backend RDBMS. If it's SQL Server, I would go
    for the factory pattern described in Microsoft's DAAB:
    http://www.dotnetjunkies.com/Article/29EF3A4F-A0C2-4BB2-A215-8F87F100A9F9.dcik


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
     
    Mark Rae [MVP], Oct 26, 2007
    #2
    1. Advertising

  3. David W

    sloan Guest

    //SqlDataSource1.ConnectionString = Session("dbconn")//

    Ahhh......


    Just stored the connection string NAME.... as the most recent picked item




    {
    ConnectionStringSettingsCollection connectionStrings =
    ConfigurationManager.ConnectionStrings;

    ConnectionStringSettings connection;
    foreach ( connection in connectionStrings) {



    string connectionStringName = connection.Name;
    string connectionString = connection.ConnectionString;
    string providerName = connection.ProviderName;

    Debug.Print(connectionStringName);
    }



    this.GridView1.DataSource = connectionStrings;
    this.GridView1.DataBind();
    }



    Get rid of those stupid "auto generated adapters" (FillTable) things when
    you create your Strong DataSet.
    Those things are the devil.


    You can get something like the EnterpriseLibrary.Data to help if you want.
    Either way, write a real DAL object ... which will populate your strong
    dataset... and pull the connection string info from the config file, using
    the simple NAME passed into your DAL object.


    That auto generated stuff is for the birds, esp when you want to pick the
    database dynamically.


    class ZebraData
    {
    public static ZebraStrongDS GetAllZebras( string connectionStringName ,
    string zebraColor )
    {
    //pull the connection string from the config file with the objects above
    //
    ZebraStrongDS returnDS = new ZebraStrongDS ();

    // i threw in zebraColor as a param...
    //call LoadDataSet method here, to populate the returnDS with
    resultset(s) from a stored procedure or something

    }

    }



    You can check my blog, I have several NLayered (and now 1 NTiered example)
    there:
    http://sholliday.spaces.live.com/Blog/



    "David W" <> wrote in message
    news:u%...
    > We have a setup where we have a single web application, but the user can
    > be attached to any of a hundred different (identically structured)
    > databases depending on their login credentials.
    >
    > Currently we are storing the connection string in Session and in each
    > page's Page_Init we reset any SqlDataSource's connection string. This
    > works reasonably well, though not ideal:
    > SqlDataSource1.ConnectionString = Session("dbconn")
    >
    > I would like to start experimenting with creating a full data access layer
    > using an ObjectDataSource and bound controls. This is easy enough to set
    > up after following the tutorials on www.asp.net and I added the
    > DataSet/XSD, but I do not see a way to set the underlying connection
    > string. It is pulling the original connection string from web.config. I
    > don't see a class file that I can edit and I can't access the properties
    > of the DAL through the ObjectDataSource.
    >
    > Any idea how I can accomplist this?
    >
    > Thanks.
    >
    >
    >
    >
     
    sloan, Oct 26, 2007
    #3
  4. David W

    David W Guest

    "Mark Rae [MVP]" <> wrote in message
    news:...
    > "David W" <> wrote in message
    > news:u%...
    >
    >> We have a setup where we have a single web application, but the user can
    >> be attached to any of a hundred different (identically structured)
    >> databases depending on their login credentials.

    >
    > Any particular reason for this structure, AAMOI...?
    >


    By law (HIPAA), for performace (smaller tables and multiple servers),
    management (easier to move around databases to different servers as needed,
    backups, restores, etc.), security (separate logins for each database, and
    no chance of accidental crossover (easier row level security). Each
    customer has their own database and there is no crossover (other than some
    lookup tables that get pushed to every database each release) so it works
    out very well in our situation. There is one base database that stores the
    location of each database for each customer.

    Yes, its SQL Server 2005 Standard.

    I'll look at that appication block, but I don't think that ties in directly
    to the DAL from looking at it.

    -Dave
     
    David W, Oct 26, 2007
    #4
  5. Mark Rae [MVP], Oct 26, 2007
    #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. SStory
    Replies:
    1
    Views:
    996
    Steve C. Orr [MVP, MCSD]
    Nov 4, 2003
  2. Ken North
    Replies:
    0
    Views:
    618
    Ken North
    Jul 14, 2005
  3. Replies:
    3
    Views:
    557
    Roedy Green
    Jul 31, 2007
  4. Philip Gray
    Replies:
    0
    Views:
    178
    Philip Gray
    Aug 25, 2005
  5. ela
    Replies:
    12
    Views:
    364
    Uri Guttman
    Apr 6, 2009
Loading...

Share This Page