Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Professionals
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Professionals If you are an experienced ASP.NET programmer, this is the forum for your 3.5 questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Professionals section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 28th, 2008, 05:36 AM
Registered User
 
Join Date: Jul 2008
Location: Reading, Berskhire, United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Modifying LocalSqlServer at runtime

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



  #2 (permalink)  
Old July 28th, 2008, 07:15 AM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default

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
  #3 (permalink)  
Old July 28th, 2008, 09:05 AM
Registered User
 
Join Date: Jul 2008
Location: Reading, Berskhire, United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old July 28th, 2008, 11:14 AM
Imar's Avatar
Wrox Author
Points: 72,045, Level: 100
Points: 72,045, Level: 100 Points: 72,045, Level: 100 Points: 72,045, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,083
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #5 (permalink)  
Old July 28th, 2008, 11:44 AM
Registered User
 
Join Date: Jul 2008
Location: Reading, Berskhire, United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old July 28th, 2008, 12:27 PM
Imar's Avatar
Wrox Author
Points: 72,045, Level: 100
Points: 72,045, Level: 100 Points: 72,045, Level: 100 Points: 72,045, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,083
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
  #7 (permalink)  
Old July 29th, 2008, 03:04 AM
Registered User
 
Join Date: Jul 2008
Location: Reading, Berskhire, United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #8 (permalink)  
Old July 29th, 2008, 12:12 PM
Imar's Avatar
Wrox Author
Points: 72,045, Level: 100
Points: 72,045, Level: 100 Points: 72,045, Level: 100 Points: 72,045, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,083
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove the LocalSqlServer entry from machine.confi Muhammad Zeeshan BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 January 24th, 2008 01:26 PM
modifying Webshop mlevans BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 3 September 23rd, 2006 05:12 AM
Modifying collection properties eadred General .NET 3 January 17th, 2006 12:57 PM
Modifying RDL at runtime... gundavarapu BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 December 1st, 2004 07:14 AM
Modifying XML enderjs XML 0 October 7th, 2004 10:29 AM



All times are GMT -4. The time now is 12:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.