Where should I put my connectionString in ASP.Net 3.5?

Question

Hi

I have two projects:

1. ASP.Net 3.5 website (frontend, UI)
2. VB Class Library (dataaccess logic)

Where should I save my connectionString, so that I can use if from the class library? And how does this affect where it is placed when I deploy/publish?

Note:

1. I don't want to pass it to every function in my VB Class

Answer 1

We keep ours in the machine.config of each server and have a custom DAL to handle all DB interaction for our web apps.

Answer 2

Put it in the web.config in the connection strings section.

In the VB project use HttpContext.Current.GetSection to retrieve the section.

Answer 3

Depending on how you constructed your DAL -- LINQ, TableAdapters, etc. -- it may automatically look for it in the web.config file. If you created the DAL via a designer, likely it stores the default connection string in the app.config file for you class library. I copy the connection strings section from the app.config file to my web.config and change the connection string to the correct database (I have separate web.config's for DEV/QA/PROD). This makes it trivial since the designer generated code already has the code implemented to retrieve it from the configuration file.

If you are hand-coding your DAL and need to pass in the connection string, I suggest setting up a strongly-typed configuration class that interfaces to the web.config and does lazy loading of your configuration values. Use a factory to create your DAL and inject the configuration class into your factory via constructor so that it knows how to create your DAL with the connectionsString retrieved from the configuration file.

Answer 4

My question came from having spent half a day of trying to make this work, but I kept getting the wrong connection when deploying (where I use another database).

My problem was, that I was using

My.Settings.DefaultConnectionString

...To retrieve the connectionString in my VB Class Library.

After following tvanfossons anwer, I dug around some more and found out, that I can simply use (after referencing system.configuration) :

System.Configuration.ConfigurationManager.ConnectionStrings.Item("DefaultConnectionString").ConnectionString

It looks in the web-config for webapplications and app.config for windows/class library apps.

I am glad it now works, but even more glad I know why. ;)

Answer 5

A fellow developers idea once was that we should store all the connection strings in a database table.

Don't try doing that. You won't get very far. :)

Answer 6

I had the same issue you were having and I ended up using the System.Configuration.ConfigurationManager class to obtain the connection string stored in my web.config file from my class library like Kjensen's answer suggested. This worked wonders, if I had more experience I would vote that answer up.

I needed the connection string to build my Linq2Sql data context, which this method provided me with.

I now build my data context like below (remembering to add a reference to System.Configuration) -

public MyDataContext() : base(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"] .ConnectionString, mappingSource)

And as long as the web.config file contains "MyConnectionString" the configuration manager takes care of the rest.