change connection string dynamically
sam44
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
Mark Rae
news:1153150863....@i42g2000cwa.googlegroups.com...
> 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...?
sam44
No, by setting file I mean the Settings.settings file, which actually
interacts with the app.config file...
Any clue ?
Juan T. Llibre
If so, you should post your question to the newsgroup:
microsoft.public.dotnet.framework
...on this same server.
Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================
"sam44" <samuelb...@googlemail.com> wrote in message
news:1153152442....@b28g2000cwb.googlegroups.com...
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.
sam44
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 ?
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.
sam44
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
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.
sam44
> 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
Mark Rae
>> 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 ?
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"...
tfsmag
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?
Mark Rae
news:1153255899....@m79g2000cwm.googlegroups.com...
>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...?
sam44
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
sam44
> 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 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...
sam44
> Don't you have the source code any more?
> You're a software developer, aren't you...?
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 Rae
>> 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 ?
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.
sam44
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.
Mark Rae
> I still need to use my xsd.
Why?
sam44
Because all of my tableadapter and datatables are defined in here.
After trolling the web I've found out I was not the only one in this
situation. It's actually very tricky to pass a connectionstring to the
xsd dynamically, which reassure me in the fact that I'm not a 'complete
idiot' but just someone facing something hard to do. If you think it's
easy then you haven't understood the problem.
Sam
Mark Rae
> Why ?
> Because all of my tableadapter and datatables are defined in here.
> After trolling the web I've found out I was not the only one in this
> situation. It's actually very tricky to pass a connectionstring to the
> xsd dynamically, which reassure me in the fact that I'm not a 'complete
> idiot' but just someone facing something hard to do.
I didn't say you were a complete idiot - in fact, I didn't say you were an
idiot at all.
> If you think it's easy then you haven't understood the problem.
I understand the problem very well!
You have a requirement for functionality which your existing system was not
designed to support and, furthermore, probably can't support in its current
form. You asked what could be done about it, and I told you that (in my
opinion) the only way to fix it was to modify your existing system so that
it did support your current business requirements.
You are unhappy because there doesn't appear to be a quick fix (your Google
searches confirm this), which means a fair bit of work for you to do...
I'm really not sure what more I can tell you... Modify your system, or
don't...
sam44
I'm not unhappy about the fact that there is work to do. I'm unhappy
about how unflexible Microsoft products can be.... An xsd that can't be
dynamically configured easily is a paradox in today's web world...
Mark Rae
> No worries...
> I'm not unhappy about the fact that there is work to do. I'm unhappy
> about how unflexible Microsoft products can be.... An xsd that can't be
> dynamically configured easily is a paradox in today's web world...
Well, that's a different argument... :-)
tfsmag
I did read what you wrote, and re-iterated it. Don't worry man, I'm
sure you'll get the credit for this groundbreaking idea!
if the connectionstrings are in the db, is the field containing the
connection string in the same table as or at least linked to the table
that generates your dropdown list of clients? Could you not set the
connection string in the onselectedindexchange event of the
dropdownlist?
sam44
I'm not sure what you mean?...
I set the connection string once the user submitted the identification
form and the validity of his identification has been checked. This is
not the problem.
The issue is to pass dynamically the new connection string returned by
the stored procedure to the xsd file at a user-level only, as opposed
to application-level.
Sam
Mark Rae
> I'm not sure what you mean?...
Me neither...???
tfsmag
several in the middle initially), and I have a better idea of your
situation and I have to say that I agree with Mark for the most part.
It would seem that the method you've chosen doesn't allow for
dynamically changing things like you need it to. Your business object's
method that uses these connection strings should allow you to pass in a
connection string as an argument. Can I ask what the reasoning was for
storing something you new needed to be dynamic in an xsd file?
Mark Rae
> Can I ask what the reasoning was for storing something you
> knew needed to be dynamic in an xsd file?
From the OP's responses, it seems fairly clear (to me at least) that at the
time he was designing the business object he never envisaged that the
connection string would eventually need to be dynamic, otherwise I'm certain
he'd never have contemplated using an XSD file...
Kevin Spencer
simply a string. It is easy to parse (semicolon-delimited name=value pairs),
and contains login information. Why on earth would a whole series of
Connection Strings have to be stored somewhere when all that is needed is to
replace the login information in a single Connection String? Did I miss
something?
--
HTH,
Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist
What You Seek Is What You Get.
"Mark Rae" <ma...@markNOSPAMrae.com> wrote in message
news:OMP6aB1q...@TK2MSFTNGP03.phx.gbl...
sam44
Actually it would amount to the same thing... modifying parts of the
connection string or the whole connection string is the same thing from
my point of view...
The connection strings are stored in the DB for each client because it
is the way it is in my company, that's all...
Mark,
Well I was 'forced' by my manager to use a xsd as he says it is a great
saving of time, which i believe is bullshit. It is not flexible and it
would have been much clever to write my own database layer acces
library. Now I can't go backward, and I must find a way to go around
this :(
Sam
Mark Rae
news:OPJwUG1q...@TK2MSFTNGP05.phx.gbl...
>I don't get it. The whole thing sounds like a kluge. A Connection String is
>simply a string. It is easy to parse (semicolon-delimited name=value
>pairs), and contains login information. Why on earth would a whole series
>of Connection Strings have to be stored somewhere when all that is needed
>is to replace the login information in a single Connection String?
Of course it is - if the app in question had been designed that way...
> Did I miss something?
Yes - the OP has designed his app with an XSD document which retrieves a
connection string hard-coded into a configuration file, and now wishes he
hadn't...
Kevin Spencer
--
HTH,
Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist
What You Seek Is What You Get.
"Mark Rae" <ma...@markNOSPAMrae.com> wrote in message
news:%23oDqnd2...@TK2MSFTNGP03.phx.gbl...
Mark Rae
> Well, heck, all he needs to do is modify the XSD, right?
Did you read the thread from top to bottom...?
sam44
Here's what I did: I created a UserSession class in my business object,
which upon success of the user identification stores the new connection
string returned by my stored procedure in a member variable.
Then i create a Session variable to store an instance of this
UserSession class so that the new connection string is available to all
my webforms.
Now since I can't pass the connection string to the xsd, I just set the
property .Connection.ConnectionString of my DataAdapters to the new
connection string, each time I create a new instance of a DataAdapter.
It works very well. I've been testing my website with simultaneous
connexion to different client's database, and all went well ! This is
really a relief :)
Thanks to you guys for helping, I appreciate this !
Sam
Kevin Spencer
happy with.
--
Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist
What You Seek Is What You Get.
"Mark Rae" <ma...@markNOSPAMrae.com> wrote in message
news:%23Jfnt8%23qGH...@TK2MSFTNGP05.phx.gbl...
GCe...@aol.com
in my opinion rude - comments that Mark made. That being said I
believe I found a way to resolve this. I am not saying it is perfect
but in my preliminary testing - it seems to work.
In the initialize method of your business object (Sub New() in VB.net)
- allow it to accept a parameter of the connection string. This will
be passed in from the front end - be it ASP.Net web applications or
windows forms applications.
Something like this
Public Sub New(ByVal ps_ConnectionString As String)
In the actual code - put the value of the connection string in the
setting that the business objects will use with a line of code
something like this:
MetisDatabaseClass.My.MySettings.Default.Item("MetisReportingDatabaseConnectionString")
= ps_ConnectionString
Of course make sure the item whose value you set it the connection
string setting that the strongly type data sets are going to use and
you should be OK. I will do some more testing and if I run across any
problems I will let you know.
Hope this helps.
George
graphicsxp
Acutally I solved that problem about 10 days ago, see previous posts :
Here's what I did: I created a UserSession class in my business object,
which upon success of the user identification stores the new connection
string returned by my stored procedure in a member variable.
Then i create a Session variable to store an instance of this
UserSession class so that the new connection string is available to all
my webforms.
Now since I can't pass the connection string to the xsd, I just set the
property .Connection.ConnectionString of my DataAdapters to the new
connection string, each time I create a new instance of a DataAdapter.
Sam