simple connectionstring question

[hawbsl]

Our situation is this:

Our applications are built with the aid of the Dataset Designer in
Visual Studio 2005.

Using the Dataset Designer seems to require a defined Connectionstring
(specified through the TableAdapter Configuration Wizard). That's
fine. At least, that's fine in the development environment.

But the database path on the customer's site will always be different
from the one embedded in our connectionstring. The simple solution is
to have an xml file and pick up the connectionstring (or at least the
DB path) from that. The customer has his xml file pointing to the
correct location on his network and we have our own xml file pointing
to the correct location on our network.

Then, in the InitConnection in each TableAdapter, we replace the
wizard built
Me._connection.ConnectionString = MyConnectionString
with
Me._connection.ConnectionString = ConnectionStringFromXMLFile

But we have to remember to redo this every time the wizard overwrites
the Designer.vb file.

Is there a way to have a dynamic, flexible connectionstring without
this clumsy business of constantly rechanging the wizard built
InitConnection?

Thanks in advance for any help,

Hawbsl

 

[amdrit]

Personally, I am not a fan of having the table adaper components responsible
for obtaining the connectionstring issue. I perfer to have the client app
tell those components what the environment looks like, since it is closest
to the user. I know a lot of people believe the connectionstring is too
sensitive and needs to be handled by the components, I just disagree.

What I typically do to accomodate your goal is to have a base adapter class
that requires a connectionstring to function. All the basic wiring is held
in that class as well, including transaction support for multistep
transactions. Then for each table adapter object, I derive that class and
copy the specific wiring the wizard creates over to the new class obect. I
just put it all in a region section so it is easier to find and replace.
The base class uses the generic dbclient objects so I can specialize oracle,
sqlclient, odbc, or oldb.

so I have a base library for my DAL. It knows how to perform data
operations generically. Then for each database library I want to use, I
inherit the base objects and fill in the stubs(CreateAdapter, CreateCommand,
CreateConnection) and so on the hard work is still done in the main
library.

Then, from the client machine, I push the connectionstring in a factory
object when creating a table adapter, datareader, datacommand.

I know this goes way off topic and perhaps you don't care about all that.
No, I do not know of a way to alter the manner it gets a connectionstring
when using the wizard. I just use the wizard to create the custom query
bits for my main body of code. When I create a new query, I just copy that
one function over to the main body of code.

 

[James Pemberton]

What we've done is create a class that handles all of the functions for a
given table or group of tables. Within the class we have a function that
gets a connection string for a settings variable, which can change depending
on whether we are using our main, test, or disaster recovery database. then
these classes can be reused in any set of applications and the connection
string is customizable.

Does this make sense?

 

[hawbsys]

Personally, I am not a fan of having the table adaper components responsible
for obtaining the connectionstring issue.  I perfer to have the client app
tell those components what the environment looks like, since it is closest
to the user.  I know a lot of people believe the connectionstring is too
sensitive and needs to be handled by the components, I just disagree.

What I typically do to accomodate your goal is to have a base adapter class
that requires a connectionstring to function.  All the basic wiring is held
in that class as well, including transaction support for multistep
transactions.  Then for each table adapter object, I derive that class and
copy the specific wiring the wizard creates over to the new class obect.  I
just put it all in a region section so it is easier to find and replace.
The base class uses the generic dbclient objects so I can specialize oracle,
sqlclient, odbc, or oldb.

so I have a base library for my DAL.  It knows how to perform data
operations generically.  Then for each database library I want to use, I
inherit the base objects and fill in the stubs(CreateAdapter, CreateCommand,
CreateConnection) and so on  the hard work is still done in the main
library.

Then, from the client machine, I push the connectionstring in a factory
object when creating a table adapter, datareader, datacommand.

I know this goes way off topic and perhaps you don't care about all that.
No, I do not know of a way to alter the manner it gets a connectionstring
when using the wizard.  I just use the wizard to create the custom query
bits for my main body of code.  When I create a new query, I just copy that
one function over to the main body of code.

No, I do care about "all that". Thanks for your long answer. Is there
any chance you could post a simplified example so that it's easier to
follow what you're saying about "pushing the connectionstring in a
factory object". Thanks.

Hawbsl

 

[hawbsys]

What we've done is create a class that handles all of the functions for a
given table or group of tables.  Within the class we have a function that
gets a connection string for a settings variable, which can change depending
on whether we are using our main, test, or disaster recovery database.  then
these classes can be reused in any set of applications and the connection
string is customizable.

We have the same notion; a class which handles all the functions for a
given table. I still don't see how that helps with the
connectionstring which is embedded within the wizard-generated code.

Hawbsl

 

[hawbsys]

Our situation is this:

Our applications are built with the aid of the Dataset Designer in
Visual Studio 2005.

Using the Dataset Designer seems to require a defined Connectionstring
(specified through the TableAdapter Configuration Wizard). That's
fine. At least, that's fine in the development environment.

But the database path on the customer's site will always be different
from the one embedded in our connectionstring. The simple solution is
to have an xml file and pick up the connectionstring (or at least the
DB path) from that. The customer has his xml file pointing to the
correct location on his network and we have our own xml file pointing
to the correct location on our network.

Then, in the InitConnection in each TableAdapter, we replace the
wizard built
    Me._connection.ConnectionString = MyConnectionString
with
    Me._connection.ConnectionString = ConnectionStringFromXMLFile

But we have to remember to redo this every time the wizard overwrites
the Designer.vb file.

Is there a way to have a dynamic, flexible connectionstring without
this clumsy business of constantly rechanging the wizard built
InitConnection?

Thanks in advance for any help,

Hawbsl

I think I've found the solution, which is to add

Me.Item("<name of connection string setting>")
= ConnectionStringFromXMLFile

to the MySettings SettingsLoaded event.

Single line of code. Not overwritten or changed by any wizards.

As suggested at:
http://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

Hawbsl