Hi folks,

Wondered if anyone out there could help me with a problem I am having.

I am developing a web site to be used by several different clients. Each client insists on their data being held in a separate database to other clients, so I am hoping to be able to have one web site, and then in the web.config file have a different ConnectionString for each client, each pointing to a different database in a back end SQL Server.

I want to hold the login information for each client in their own data database (by ensuring the tables in the default security database ASPNETDB.mdf file are in their own database).

At runtime, I want the client to specify what there account number is when they initially open the web site, and on entering this, the pages that display data from the database use the account number to dynamically change the data source to the correct database. I can get this part to work, simply by changing the SQLDataSource of say a GridView at runtime in the Page_Load event, so that side of things works nicely.

My problem come when the user logs in. I have a single "LocalSqlServer" ConnectionString setting in the web.config file, which has a connection string as follows:-

"Data Source=mySqlServer;Initial Catalog=MyDefaultSecurity;Trusted_Connection=True"

Because I want to hold login info for each client in their own database, at runtime when they have entered their account number, I want to change the connection string of the LocalSqlServer to something like:-

"Data Source=mySqlServer;Initial Catalog=Acc1234;Trusted_Connection=True"

where the clients database is called Acc1234 (i.e. they have entered Acc1234 when they initally open the web site).

I don't seem to be able to change the connection string for LocalSqlserver initially stored in the web.config file, as I get an error "configuration is read only".
However, I have seen code in the professional ASP.Net 3.5 book that seems to suggest you can dynamically change connection strings at runtime, but this seems to throw up the read only error mentioned above.

Is there any way I can do this, or am I going to have to have a separate web site for each client, each with their own web.config file with the LocalSqlserver connection string pointing to their own database?

Any help anyone could give would be really appreciated.

Just in case it's relevant, I am using Microsoft's Visual Web Developer 2008 Express edition, with a backend SQL Server Developer Edition, and I code in Vb.Net (not C#).

Kind regards,

Matt

You could just create a Public Variable or Property (Perhaps in a class file) that pulls the web.config Connection string as it's default value and then change it how you need based on the account number and use that Variable/Property to connect to the database instead of the web.config...

Jason Hall

Hi Jason,

Thanks for the reply!

I don't think this will help me, as the built in login server controls are based on whatever the LocalSqlServer connectionstring is set to in the web.config file.

If you change the connection string used for LocalSqlServer in the web.config file to point to a different database, then the security membership/profile and role manager use that new database.
My problem is how to change the web.config file at runtime.

I even looked at possibly trying to change the login server controls to look at a different connection string other than LocalSqlServer, but I can't find any properties that allow you to do this.

Thanks for the attempt anyway.

Cheers,

Matt

Hi there,

What exactly are you trying to accomplish? It's pretty easy to have the controls look at a different connectionstring. Simply redefine the membership and other relevant elements in web.config as outlined here:

http://help.maximumasp.com/smarterti...?articleid=878

This way, your controls will look at the connection string defined in your web.config, not LocalSqlServer anymore.

However, I am not sure if you can change the connection string on a user by user basis at run-time. I had a quick look at the Membership API and didn't find anything that enables this. I could be wrong though as I haven't examined this extensively.

One solution that comes to mind to work around this is download the membership's source and then change the behavior of GetConnectionString.

For more info about the source

http://weblogs.asp.net/scottgu/archi...13/442772.aspx

And more info about creating your own providers:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=404

However, I see a potentially bigger problem. If your clients haven't logged in yet, how do you identity them? That is, when they try to log in, how would you know what database to login to?

Personally, I think you're better off creating multiple web sites. That way, each site can have its own configuration and this database. You can easily share common data through virtual directories.

Hope this helps,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar,

Many thanks for the reply!
I have just read your book "Begininng ASP.Net 3.5...", and it is great! Very well written with loads of useful info.

You're right, I am trying to change the connectionstring on a user by user basis at runtime. I was going to have a default web page where the users entered an account number, and I would store that and use it to determine which database the user connects to (my databases would have the account number in their names). Then, having entered an account number (and storing it away), the user would be taken to a login page with the login server controls.

If I could either manipulate the "LocalSqlServer" setting that I have defaulted to something in web.config, or if I could manipulate the login server controls to change them from looking at "LocalSqlServer" to another connectionstring setting in web.config, then I think it would work, but it looks like I may have to have a separate web site for each client.

Many thanks for your help and info though, it will be a great help to me.

Cheers,

Matt

Changing LocalSqlServer is easy as well; all you need is this:

<connectionStrings>
  <clear />
  <add name="LocalServerServer" connectionString="..." providerName="System.Data.SqlClient"/>
</connectionStrings>

This removes the existing connection string and then adds a new one with the same name.

Glad you like my book....

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar,

Thanks for this, but is there any way I can do this at runtime on a user by user basis?

That is to say, when a user opens the web site and enters an account number (but before they have logged in), the LocalSqlServer connectionstring setting in the web.config file is "cleared" and a new one is added based on what the user has entered as an account number.

Cheers,

Matt

Like I said: I am not 100% sure, but I don't think so. I think you need to use a custom provider or setup multiple web sites...

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.