Wednesday, March 21, 2012

Referring to Sql connection string in web.config from code behind

Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.

For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.

Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.

Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:

Private sqlConnAs New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")

Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:

sdaPersonnel =New SqlDataAdapter(strSqlPersonnel, sqlConn)

Then there's the SqlConnections set up by the wizard on the aspx side:

<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING%>" ...>

And for the connection in the web.config:

<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />

So, what would be the code in the code-behind page to refer to the connection string in the web.config file?

Thanks!

To refer to the connection string via code: ConfigurationManager.ConnectionStrings["<ConnectionStringName>"]|||

Hey, Stiletto, Thanks! That did the trick (or, at least, it got me on the right track). I wasn't exaclty sure how to actually add that to my connection string code, so I had to do some hunting and trying different syntax, but ended up with this working:

Private sqlConnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DATABASECONNECTIONSTRING").ConnectionString)
Thanks again!

No comments:

Post a Comment