change connection string dynamically

S

sam44

Hi,
At startup the user log on and chooses the name of a client from a
dropdownlist, which then changes dynamically the connection string (the
name of the client indicates which database to use).
I then change dynamically the connection string by doing :
My.Settings.Item("ConnectionString") = "some connection sring"

The problem is that if another user loggs in and chooses another
client, then obviously it changes the connection string again and the
first user will be in trouble !

How can I change the connection string PER user ? In the setting file,
the connection string can only be an application-level variable and
can't be a user variable for some reason :(

Thank you
 
M

Mark Rae

How can I change the connection string PER user ? In the setting file,
the connection string can only be an application-level variable and
can't be a user variable for some reason :(

What do you mean by the "setting" file? Do you mean web.config...?
 
S

sam44

Hi Mark,
No, by setting file I mean the Settings.settings file, which actually
interacts with the app.config file...
Any clue ?
 
J

Juan T. Llibre

It looks like you're writing a Windows Forms App.

If so, you should post your question to the newsgroup:

microsoft.public.dotnet.framework

....on this same server.
 
M

Mark Rae

No, by setting file I mean the Settings.settings file, which actually
interacts with the app.config file...
Ah...

Any clue ?

Yes - you're in the wrong newsgroup. This group is for ASP.NET.
 
S

sam44

No I'm not in the wrong group! I'm doing ASP.net !
My website has a business object, which is a vb.net project in visual
studio, that's why I also have a app.config file. So can someone help ?
 
M

Mark Rae

No I'm not in the wrong group! I'm doing ASP.net !
My website has a business object, which is a vb.net project in visual
studio, that's why I also have a app.config file. So can someone help ?

You know, if you'd said that in the first place... :)

1) Store your "base" connection string in your business object's app.config
file (or anywhere else you like)

2) In your web app's Session_Start event, read in the base connection
string, amend it as necessary, and store the amended string as a Session
variable.

3) Amend your business object to allow the connection string to be passed to
it.

4) When referencing your business object, pass in the amended connection
string from the Session variable.
 
S

sam44

Thanks for your help Mark,
Yeah I should have been more explicit in my first post, sorry.

Regarding your solution, I'm not sure if I can do that in the
Session_Start event. When is this called ?
I need to amend my base connection string, once the user has correctely
logged in (ie, username + password are correct and he chose a valid
client in the list). The the identification stored procedure returns
the new connection string, which is the one I should use.
Is the Session_Start event going to be called each time I create a new
session variable?

Sam
 
M

Mark Rae

Thanks for your help Mark,
Yeah I should have been more explicit in my first post, sorry.
S'OK...

Regarding your solution, I'm not sure if I can do that in the
Session_Start event. When is this called ?

The clue is in the name - the Session_Start event fires every time a session
starts i.e. every time a browser makes a new connection to your site.
I need to amend my base connection string, once the user has correctely
logged in (ie, username + password are correct and he chose a valid
client in the list). The the identification stored procedure returns
the new connection string, which is the one I should use.
Exactly.

Is the Session_Start event going to be called each time I create a new
session variable?

No - see above.
 
S

sam44

The clue is in the name - the Session_Start event fires every time a session
starts i.e. every time a browser makes a new connection to your site.

Ok, then it's too early in the process :) If Session_Start is fired
when the URL is requested, then obviously the user hasn't logged on
yet. Therefore I don't know anything about the new connection string at
that time and I can't amend the original one in this event. Does this
make sense ?

Sam
 
M

Mark Rae

Ok, then it's too early in the process :) If Session_Start is fired
when the URL is requested, then obviously the user hasn't logged on
yet. Therefore I don't know anything about the new connection string at
that time and I can't amend the original one in this event. Does this
make sense ?

No.

1) The user makes an initial connection to your site Session_Start fires.

2) The user logs on.

3) If the user's logon is successful, your site queries whatever it needs to
query to return the correct connection string:

string strConnectionString = FetchConnectionString(strUserID);

4) The connection string is stored in the Session object:

Session["ConnectionString"] = strConnectionString;

5) The user calls a method in your business object:

MyValue =
MyBusinessObject.MyFunction(Session["ConnectionString"].ToString(), <other
arguments>);


With the *greatest* of respect, can I politely suggest that you purchase a
beginner's guide to ASP.NET and read it or, at the very least, do a Google
search for "Global.asax"...
 
T

tfsmag

I don't have too much experience with winforms so excuse me if i'm
incorrect on this. Why can you not have multiple connection strings
stored in your config file? I've set up multiple connection strings in
web.config and been able to use/call them by their name attribute.

I'd say it should be as easy as upon logging in, you set the correct
connection string in a session variable and that user will carry that
connection string for the life of the session. That way each user would
be able to use the correct connection string.

Maybe it's winforms but I guess I don't understand how someone can
change the connection string and change it for someone else?
 
M

Mark Rae

I don't have too much experience with winforms so excuse me if i'm
incorrect on this. Why can you not have multiple connection strings
stored in your config file? I've set up multiple connection strings in
web.config and been able to use/call them by their name attribute.

Because that would be unbelievably stupid!

What if you have 10,000 users? Are you really going to have a web.config
file with 10,000 different connection strings...?
I'd say it should be as easy as upon logging in, you set the correct
connection string in a session variable and that user will carry that
connection string for the life of the session. That way each user would
be able to use the correct connection string.

Er, that's exactly what I said! Did you actually read my post...?
 
S

sam44

Actually I can't stored multiple connection strings in the config file.
Well I can, but since i've got 1 string per client, and a few hundred
clients, that'd be silly. That's why they are stored in the DB.
Thanks
 
S

sam44

Thanks for the reply Mark. Yet there is an issue at step 5 :
5) The user calls a method in your business object:

MyValue =
MyBusinessObject.MyFunction(Session["ConnectionString"].ToString(), <other
arguments>);

I don't understand how the business object uses the connection string
from the session variable. Let me explain. I have a strongly typed
dataset in my business object (.xsd file), which contains all the
command objects to access the DB. This xsd uses a connection string
stored in the Settings.Settings file (or app.config file). Therefore
this is the one connection that I need to change when the user logon.
Hence the issue, because this connection string is an Application
variable (and cannot be changed to a user-level variable, thanks
Microsoft...). Do you understand my issue ?
With the *greatest* of respect, can I politely suggest that you purchase a
beginner's guide to ASP.NET and read it or, at the very least, do a Google
search for "Global.asax"...

I do have a book :) I should read more of it for sure, but still that
won't solve my problem ;)

Thanks
Sam




Your idea of using a Session variable is good.
Mark said:
Ok, then it's too early in the process :) If Session_Start is fired
when the URL is requested, then obviously the user hasn't logged on
yet. Therefore I don't know anything about the new connection string at
that time and I can't amend the original one in this event. Does this
make sense ?

No.

1) The user makes an initial connection to your site Session_Start fires.

2) The user logs on.

3) If the user's logon is successful, your site queries whatever it needs to
query to return the correct connection string:

string strConnectionString = FetchConnectionString(strUserID);

4) The connection string is stored in the Session object:

Session["ConnectionString"] = strConnectionString;

5) The user calls a method in your business object:

MyValue =
MyBusinessObject.MyFunction(Session["ConnectionString"].ToString(), <other
arguments>);


With the *greatest* of respect, can I politely suggest that you purchase a
beginner's guide to ASP.NET and read it or, at the very least, do a Google
search for "Global.asax"...
 
M

Mark Rae

Thanks for the reply Mark. Yet there is an issue at step 5 :
5) The user calls a method in your business object:

MyValue =
MyBusinessObject.MyFunction(Session["ConnectionString"].ToString(),
<other
arguments>);

I don't understand how the business object uses the connection string
from the session variable.

I've already told you...
Let me explain. I have a strongly typed dataset in my business object
(.xsd file), which contains all the command objects to access the DB.
This xsd uses a connection string stored in the Settings.Settings file
(or app.config file).

So modify it so that the connection string can be passed to it, rather than
hard-coded!
Did you not write the business object?
Don't you have the source code any more?
You're a software developer, aren't you...?

I'm sure when the business object was originally designed, it was never
envisaged that the connection string might ever need to be dynamic. Now your
business requirements have moved on and the requirements for this business
object have changed.

So change it! Sounds to me like it should be a fairly trivial task.

What's the "real" problem here...
 
S

sam44

Did you not write the business object?
yes, I did
Don't you have the source code any more? yes, I do
You're a software developer, aren't you...?
yes, I am

My turn to ask you a question: do you know what a Settings.Settings
file is ?
Try to modify a variable of type 'ConnectionString' from 'Application'
to 'User'. If you manage to do that, let me know, I'd be curious to see
how....

Sam


Mark said:
Thanks for the reply Mark. Yet there is an issue at step 5 :
5) The user calls a method in your business object:

MyValue =
MyBusinessObject.MyFunction(Session["ConnectionString"].ToString(),
<other
arguments>);

I don't understand how the business object uses the connection string
from the session variable.

I've already told you...
Let me explain. I have a strongly typed dataset in my business object
(.xsd file), which contains all the command objects to access the DB.
This xsd uses a connection string stored in the Settings.Settings file
(or app.config file).

So modify it so that the connection string can be passed to it, rather than
hard-coded!
Did you not write the business object?
Don't you have the source code any more?
You're a software developer, aren't you...?

I'm sure when the business object was originally designed, it was never
envisaged that the connection string might ever need to be dynamic. Now your
business requirements have moved on and the requirements for this business
object have changed.

So change it! Sounds to me like it should be a fairly trivial task.

What's the "real" problem here...
 
M

Mark Rae

yes, I am
My turn to ask you a question: do you know what a Settings.Settings
file is ?

Yes I do - the wrong storage mechanism for what you need to achieve here.
Don't use it.
Try to modify a variable of type 'ConnectionString' from 'Application'
to 'User'. If you manage to do that, let me know, I'd be curious to see
how....

You can't - like I *KEEP* telling you, modify your business object so that
it *DOESN'T* work with a hard-coded connection string. Modify it so that you
can *PASS* a dynamic connection string *INTO* it.
 
S

sam44

Ok, then one last question. If I don't use Settings.Setting, however I
still need to use my xsd. How do you actually pass a connection string
to a xsd ? This is actually the one thing I can't figure out and which
stopped me from doing it so far.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top