change connection string dynamically

[sam44]

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. 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]

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...

 

[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]

way to get emotional mark!

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]

Hi tfsmag,
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.

 

[Mark Rae]

I'm not sure what you mean?...

Me neither...???

 

[tfsmag]

Ok, I went through and re-read the whole thread here (i actually missed
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]

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? Did I miss
something?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.

 

[sam44]

Hi Kevin,
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]

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]

Well, heck, all he needs to do is modify the XSD, right?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.

 

[Mark Rae]

Well, heck, all he needs to do is modify the XSD, right?

Did you read the thread from top to bottom...?

 

[sam44]

I've fixed the problem

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]

No, I missed some. But it sounds like he's come up with a solution he's
happy with.

--

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.

 

[GCeaser]

First let me say that I admire Sam's ability to shrug off the several -
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]

Thank you George for helping
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

 

[qishaq]

Having same problem

I am having the same problem. i have the business object in a seperate class which is not inherited from Page class. so i am not able to get the session in the business class.

i used HttpContext.Current.Session[session] to retreive the session.

could you please help..

Qaisar

"sam44" <[email protected]> wrote in message
news:[email protected]...

> 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.