dynamic sqlconnection

I have a database for each of my customers and want to connect to a database depending on who is logged in.

all my users have their database name in the user profile.

how can i use the database name in the user profile to change the initial catalog to connect to aaother database?

thanks

There are 2 options: build the connection string for the user by uing the corresponding database name; or change to corresponding database in SqlCommand. For example:

string dbname="userdbname";

SqlConnection conn = new SqlConnection(@."Data Source=.;Integrated Security=SSPI;Database=" + dbname);

SqlCommand cmd = new SqlCommand("use " + dbname + ";select * from sysobjects", conn);

|||

You can do it probably ten different ways.

1) Don't use the data controls, and use a data access layer that abstracts the database calls, connections, opens, etc from your code, and place the logic to build a connection string on the fly into it.

2) Inherit all your pages from your own page class, that during initialization or load, it goes through all your controls recursively, and changes the connection string of all the datasource controls.

3) Write a function and that does the same as #2, and call it from each pages Page_Load.

4) Do all your data access manually and build your connection string as you see fit.

5) During log in, store the users connection string in a session variable, and always use that.

6) During authentication, store the users connection string in a session variable and always use that.

7) During page_load, build your connection string, and set all your data controls connection string to it.

8) Derive all your pages from your own page class, and during page_load recursively walk the pages control tree and set all sqldatasource's connection string to your own.

ways 9-10 to be filled on by other users.

|||Scratch option #4, lori beat me to it.|||

Thank you for the info i will try it and see how it goes.

Thanks

|||

How about doing it with a SqlDataSource object? I have the following on a page but keep getting an error. The error I'm getting is "incorrect syntax near '@.dbName'". The parameters @.dbName and @.userID are coming from two DropDownList objects which I know are working correctly in binding their data.

<asp:SqlDataSourceID="dsUserHistory"runat="server"ConnectionString="<%$ ConnectionStrings:portal1ConnectionString %>"SelectCommand="USE @.dbName SELECT loginDateStamp, logoutDateStamp FROM user_logins WHERE user_id = @.userID ORDER BY loginDateStamp">
<SelectParameters>
<asp:ControlParameterControlID="ddlUserHistoryCases"DefaultValue="portal1"Name="dbName"PropertyName="SelectedValue"Type="String"/>
<asp:ControlParameterControlID="ddlUserHistoryUsers"DefaultValue="1"Name="userID"PropertyName="SelectedValue"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

I've also tried setting the SelectCommand to "SELECT loginDateStamp, logoutDateStamp FROM @.dbName.dbo.user_logins...", but that doesn't work either obviously. Any ideas?

|||

In dbUserHistory_Selecting:

e.Command.CommandText="USE " & ddlUserHistoryCases.SelectedValue & " " &e.Command.CommandText