Connection string and web.config

[Fernando Poinho Malard]

Hello,

I'm developing a website which has a SQL database working through ADO.NET.

When I'm development site I have my personal IIS server as well as my
personal SQL server.
All my connections strings are pointing to this local SQL server.
When I need to upload the modifications to running website I have to
manually change all SQL connections strings because the running SQL server
is placed on other machine and has a different name.

To minimize this extra job I put my connection string into a key inside
web.config file but, even doing this, on all upload job I need to configure
this file.

Another similar problem is related to design time which uses also a
connection to my local SQL server for some DataSet and DataAdapter objects.
When another developer tries to edit these objects it receives an error
message due the same reason, his local SQL server has another name.

How can I make this easier than that?
Is there any way to solve this?

Kindest regards,
Fernando.

 

[Ken Cox [Microsoft MVP]]

Hi Fernando,

To get around the issue of different environments, you can use a whole set
of keys, with a different key in the web.config for every deployment
scenario, including running on the localhost. No edits are required
to the web.config once it is set.

For example, these two keys are for the datasource:

<add key="localhost_DataSource" value="xxxxx" />
<add key="intranet_corp_com_DataSource" value="yyyy" />

In my .vb code, I read the server name and convert it into the key that I
need automatically:

strSERVER_NAME = Replace(Request.ServerVariables("SERVER_NAME"), ".", "_")
strDataBaseServer = ConfigurationSettings.AppSettings(strSERVER_NAME &
"_DataSource")


Does this help?

Ken
MVP [ASP.NET]

 

[George Ter-Saakov]

For local SQL server you should use (local) as a server name (in round
brackets) not your machine name.
So other developer will use his own local SQL server.

It does not seems to be a big job to edit web.config once you are moving it
to production.

But even if you are moving it everyday (like fixing bugs, ... ) then just do
not copy web.config over the one that exists.

George.

 

[Fernando Poinho Malard]

Hi Ken,

This seems to solve the runtime problem but not the design time problem once
the connection string is placed inside the code and, at design time, I can't
change this value...

Another issue is if user has more than one SQL instance running...does
"SERVER_NAME" represents the first instance when machine has more than one
installed?

Regards,
Fernando.

Hi Fernando,

To get around the issue of different environments, you can use a whole set
of keys, with a different key in the web.config for every deployment
scenario, including running on the localhost. No edits are required
to the web.config once it is set.

For example, these two keys are for the datasource:

<add key="localhost_DataSource" value="xxxxx" />
<add key="intranet_corp_com_DataSource" value="yyyy" />

In my .vb code, I read the server name and convert it into the key that I
need automatically:

strSERVER_NAME = Replace(Request.ServerVariables("SERVER_NAME"), ".", "_")
strDataBaseServer = ConfigurationSettings.AppSettings(strSERVER_NAME &
"_DataSource")


Does this help?

Ken
MVP [ASP.NET]

Hello,

I'm developing a website which has a SQL database working through ADO.NET.

When I'm development site I have my personal IIS server as well as my
personal SQL server.
All my connections strings are pointing to this local SQL server.
When I need to upload the modifications to running website I have to
manually change all SQL connections strings because the running SQL server
is placed on other machine and has a different name.

To minimize this extra job I put my connection string into a key inside
web.config file but, even doing this, on all upload job I need to
configure
this file.

Another similar problem is related to design time which uses also a
connection to my local SQL server for some DataSet and DataAdapter
objects.
When another developer tries to edit these objects it receives an error
message due the same reason, his local SQL server has another name.

How can I make this easier than that?
Is there any way to solve this?

Kindest regards,
Fernando.