how to configure multiple databases for one asp.net website

Hello:
I am working on a website that will require the use of multiple
databases - a unique database for each user in fact. It is somewhat of
a light CRM application and it is important to keep a user's customer
information in their own database.
So I am hoping to get input on the best way to implement this. The
approach I am using so far includes storing the database connection
string in a property of the user's profile object. However, this
requires referencing the profile object each time I hit the database.
And for pages that are using the sqldatasource control, that means
assigning the connectionstring property for that control in the
page_load event outside of an "if not page.ispostback" block. So I
wonder if there isn't a better way.
Any feedback would be really appreciated here.
ChrisHow about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||How about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||Aaron:
Thanks for your reply. I actually left out some stuff in my post to
keep the thread somewhat open-ended. Your suggestion actually looks
very close to what I ended up implementing. I am storing a database
connection string in a profile object, then loading that into a session
variable when the user logs in. It sounds like I'm on the right track
so that's reassuring. Thanks for the peace of mind!
Chris