Hi folks,

I am pretty new to ASP.Net, but these Wrox books are great! I do have a problem that I don't seem to be able to get round though, and if anyone out there could advise me, I would be really grateful.

I am trying to set up a web site where users log in (using the ASP.Net login controls). However, before they do that, they need to choose an "account", which will basically define which back end SQL server database they will use.

My thinking is that several different clients could use this web site, and they will each have their own data database (Clientaccount.mdf) and security database (ASPNETDB_clientaccount.mdf). Supplying the account name will then define which data/security database they will be using, and then they will log in with a username and password.
This keeps different clients data apart (rather than having all data in one database, which is something I don't want to have to do).

My problem comes with dynamically setting the connection strings, based on whatever "account" they have entered (I propose to set the ConnectionStrings after they have entered this "account" and before they log in.
When I try to run the code in Chapter 7 to "modify connection string properties at runtime" (pages 315-316), I keep getting an error "the configuration is read only".

I suspect that either I need to do something else before I can clear and add a connection string, or maybe I am approaching this in the completely wrong way (as I said, I am new to this!).

Can anyone shed any light on this problem, or will I have to manually add connection string entries to the web.config file every time we have a new client joins the web site?

Kind regards

Matt

Hi TwoTrees -

I am wondering - is there a reason that you need to have a separate db for each user? You can key all your tables by user id and have everything in one db. I work on a lot of large enterprise web applications and this is the usual technique.

Thanks,
Jim

Hi Jim,

Many thanks for the prompt reply.

There are two reasons for me wanting to have a separate database for each client (although this may be naive of me):-

1. I guess I feel a bit safer keeping different clients data separate. If it were all in one database, and the database got corrupted, then all clients would be unable to login whilst the problem was fixed. Also, if we needed to restore to a backup, then I am guessing everyones data would be set back to the restored data (although with SQL Server I suspect there may be ways round this - that's probably another discussion!!)

2. Our different clients might want to login with the same user names, for example client A might have a user John Smith who wants his login user name to be JSmith, and client B may also have a John Smith who also wants his login user to be JSmith. From what I have seen so far, you can't have two different users with the same login, even if they have different e-mail addresses (although I may be wrong on this one?).

Hence why the different data databases and different security "LocalSqlServer" databases for different clients.

Like I say, it may be that I am approaching this in completely the wrong way, or it may be that I won't be able to use the built in Login controls to control security.
Having had more time to think about this, I don't think I can achieve what I want anyway, as the built in login controls rely on the "LocalSqlServer" connection string, and if I can't change this at runtime from what it is defaulted to in the web.config file, then there can only be one security database (ASPNETDB.mdf) that the web site can use.

If you have any further thoughts on this, i really do appreciate you taking the time and effort to help me, as I know you have your own stuff to get on with.
Thanks for the info you have given me, it's nice to find out how the professionals do these things.

It sounds like I am approaching this in the wrong way, and that I should just have one data database and one security database, in which case am I correct in thinking that all my data tables will have to have an "account" field to differentiate between different clients (sorry, I use the term client to denote one of my customers, as opposed to a workstation at the user end - apologies for confusing terminology!)?
Also, having just one security database; does mean that user names will have to be unique across all customers?


Once again, many thanks for your time!

Kindest regards,

Matt

1. I guess I feel a bit safer keeping different clients data separate. If it were all in one database, and the database got corrupted, then all clients would be unable to login whilst the problem was fixed. Also, if we needed to restore to a backup, then I am guessing everyones data would be set back to the restored data (although with SQL Server I suspect there may be ways round this - that's probably another discussion!!)

====
Yes, with SQL server, there are ways around this...You could restore the backups and then apply the transaction logs.

====
2. Our different clients might want to login with the same user names, for example client A might have a user John Smith who wants his login user name to be JSmith, and client B may also have a John Smith who also wants his login user to be JSmith. From what I have seen so far, you can't have two different users with the same login, even if they have different e-mail addresses (although I may be wrong on this one?).
====
If you're using the ASPNET authentication database, you might have a problem with duplicate user IDs. However, if you "roll your own", you can use a compound (multiple field) key in the database and require that the ClientID - UserID combination be unique (rather than just the UserID).

----
To answer your original question, the easiest way to have different connection strings is to create multiple connection strings in your web.config, using the clientID or name as the "key" ("Name" for connection strings).