Web.config and Connection String Management

[Johnson]

I'm trying to fix a "sub optimal" situation with respect to connection
string management. Your thoughtful responses will be appreciated.

I just started with a new client who has a bunch of legacy ASP.NET
applications that all manage connection strings in Web.config the same way,
like this:

This client has one Web.config file per application, and that one Web.config
file is duplicated across all environments (i.e., dev machines, test, and
production all have the exact same Web.config file, connection strings and
all).

For connection strings they specify one connection string per computer the
application may run on, including all development machines, all test
servers, and production server. The name of each connection string includes
the name of each computer on which the application may run, plus the name of
the connection string. They have a computer naming convention where each dev
box name is an abbreviation of the developer's name. More formally, it's
like this:

[computer name] + [connection string name]

Here are examples:
<!-- Local DEV Workstations -->
<add name="BSmithConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="RJohnsonConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="GBrooksConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Test Server -->
<add name="InventoryTestServer1ConnectionStringToInvent oryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Production Server -->
<add name="InventoryAppServer1ConnectionStringToInvento ryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />

You can see that the connection strings are identical except for the
computer name (which equates to developer name or "environment"). So yes,
for each new developer, the connection string must be duplicated and tweaked
(changing only the name).

The application, upon startup, looks to Environment.MachineName to determine
which connection string it is to use. So, if the application is running on a
machine for which there is an associated entry in Web.config, then it will
use that connection string.

WHY would they do this? From talking with the existing staff, it appears
that they don't want to deal with versioning Web.config. When moving the
application to different environments or their source control system, they
want to be able to copy the entire application, Web.config and all.

But their strategy (1) creates brittle connection strings, as one must
modify Web.config when moving to a new computer (or hiring another
developer; or a developer leaves...); (2) they in fact have a versioning
problem in that whenever a new connection string is needed, all developer's
copy of Web.config must be updated, etc.

What I'd like to propose to the client - and I'm requesting your thoughtful
responses on this proposal - is to break out the connection strings into a
separate .config file that is referenced from Web.config. This separate
config file would have only one set of connection strings per envoronment
(e..g, 3 connection strings total - one each to the dev, test, and
production). They wouldn't have to modify Web.config whenever a new
developer shows up or they need to run the app on a new computer. These
connection strings would not incorporate the host computer name at all.

Thoughts?

Thanks.

Thoughts? Is there something better I could do - other than breaking out
connection strings into a separate config file that is then referenced from
Web.config?

[sloan]

If its 2.0 or above......its already done for you.
Note: In winforms or console apps, you'll have to add a POST BUILD EVENT
for the ExternalConnectionStrings.config file:
App.Config <<<<<<< This is the filename, not the contents of the file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

<connectionStrings configSource="ExternalConnectionStrings.config" />

</configuration>

ExternalConnectionStrings.config<<<<<<< This is the filename, not the
contents of the file

<connectionStrings>
<add name="LocalDatabaseInstance"
connectionString="server=.;database=MYDB;User ID=user1;password=password1"
providerName="System.Data.SqlClient"/>

</connectionStrings>



"Johnson" <[e-mail] in message
news:eI*************@TK2MSFTNGP04.phx.gbl...

I'm trying to fix a "sub optimal" situation with respect to connection
string management. Your thoughtful responses will be appreciated.

I just started with a new client who has a bunch of legacy ASP.NET
applications that all manage connection strings in Web.config the same
way, like this:

This client has one Web.config file per application, and that one
Web.config file is duplicated across all environments (i.e., dev machines,
test, and production all have the exact same Web.config file, connection
strings and all).

For connection strings they specify one connection string per computer the
application may run on, including all development machines, all test
servers, and production server. The name of each connection string
includes the name of each computer on which the application may run, plus
the name of the connection string. They have a computer naming convention
where each dev box name is an abbreviation of the developer's name. More
formally, it's like this:

[computer name] + [connection string name]

Here are examples:
<!-- Local DEV Workstations -->
<add name="BSmithConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="RJohnsonConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="GBrooksConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Test Server -->
<add name="InventoryTestServer1ConnectionStringToInvent oryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Production Server -->
<add name="InventoryAppServer1ConnectionStringToInvento ryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />

You can see that the connection strings are identical except for the
computer name (which equates to developer name or "environment"). So yes,
for each new developer, the connection string must be duplicated and
tweaked (changing only the name).

The application, upon startup, looks to Environment.MachineName to
determine which connection string it is to use. So, if the application is
running on a machine for which there is an associated entry in Web.config,
then it will use that connection string.

WHY would they do this? From talking with the existing staff, it appears
that they don't want to deal with versioning Web.config. When moving the
application to different environments or their source control system, they
want to be able to copy the entire application, Web.config and all.

But their strategy (1) creates brittle connection strings, as one must
modify Web.config when moving to a new computer (or hiring another
developer; or a developer leaves...); (2) they in fact have a versioning
problem in that whenever a new connection string is needed, all
developer's copy of Web.config must be updated, etc.

What I'd like to propose to the client - and I'm requesting your
thoughtful responses on this proposal - is to break out the connection
strings into a separate .config file that is referenced from Web.config.
This separate config file would have only one set of connection strings
per envoronment (e..g, 3 connection strings total - one each to the dev,
test, and production). They wouldn't have to modify Web.config whenever a
new developer shows up or they need to run the app on a new computer.
These connection strings would not incorporate the host computer name at
all.

Thoughts?

Thanks.

Thoughts? Is there something better I could do - other than breaking out
connection strings into a separate config file that is then referenced
from Web.config?

[=?Utf-8?B?YnJ1Y2UgYmFya2Vy?=]

the seperate file is ok, but you still need to version it and have to be
careful with xcopy deployments. when I've used this approach, I generally end
using an approch like your customer, where every machine setting is in the
file.

you can also look at web deployment projects, which edit the web.config when
building the staging server. the main issue is you need to build a staging
for each enviroment.

you can reduce the number of connect strings by having a default
(localhost), as usually all the developers have the same settings.
-- bruce (sqlwork.com)
"Johnson" wrote:

I'm trying to fix a "sub optimal" situation with respect to connection
string management. Your thoughtful responses will be appreciated.

I just started with a new client who has a bunch of legacy ASP.NET
applications that all manage connection strings in Web.config the same way,
like this:

This client has one Web.config file per application, and that one Web.config
file is duplicated across all environments (i.e., dev machines, test, and
production all have the exact same Web.config file, connection strings and
all).

For connection strings they specify one connection string per computer the
application may run on, including all development machines, all test
servers, and production server. The name of each connection string includes
the name of each computer on which the application may run, plus the name of
the connection string. They have a computer naming convention where each dev
box name is an abbreviation of the developer's name. More formally, it's
like this:

[computer name] + [connection string name]

Here are examples:
<!-- Local DEV Workstations -->
<add name="BSmithConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="RJohnsonConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<add name="GBrooksConnectionStringToInventoryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Test Server -->
<add name="InventoryTestServer1ConnectionStringToInvent oryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />
<!-- Production Server -->
<add name="InventoryAppServer1ConnectionStringToInvento ryDB"
connectionString="server=DevServer1; InitialCatalog="Inventory" ..." />

You can see that the connection strings are identical except for the
computer name (which equates to developer name or "environment"). So yes,
for each new developer, the connection string must be duplicated and tweaked
(changing only the name).

The application, upon startup, looks to Environment.MachineName to determine
which connection string it is to use. So, if the application is running on a
machine for which there is an associated entry in Web.config, then it will
use that connection string.

WHY would they do this? From talking with the existing staff, it appears
that they don't want to deal with versioning Web.config. When moving the
application to different environments or their source control system, they
want to be able to copy the entire application, Web.config and all.

But their strategy (1) creates brittle connection strings, as one must
modify Web.config when moving to a new computer (or hiring another
developer; or a developer leaves...); (2) they in fact have a versioning
problem in that whenever a new connection string is needed, all developer's
copy of Web.config must be updated, etc.

What I'd like to propose to the client - and I'm requesting your thoughtful
responses on this proposal - is to break out the connection strings into a
separate .config file that is referenced from Web.config. This separate
config file would have only one set of connection strings per envoronment
(e..g, 3 connection strings total - one each to the dev, test, and
production). They wouldn't have to modify Web.config whenever a new
developer shows up or they need to run the app on a new computer. These
connection strings would not incorporate the host computer name at all.

Thoughts?

Thanks.

Thoughts? Is there something better I could do - other than breaking out
connection strings into a separate config file that is then referenced from
Web.config?