Problems with connection strings with DAL in a separate project

Question

I originally wrote a data access layer inside the App_Code folder on my website. Then we developed a web service in a separate project. So that the website and the web service can access the same DAL, I moved it to another project. I have a dataset with tables and to compile the DAL project I had to add a connection string to the application properties settings. But that means that I have to recompile the DAL for each deployment. Also I can have 2 or 3 websites on the server using the same DAL. So I want to set the connection string on every web.config website and leave it on that. Do I have to go through my code and change every time I create an instance of a table adapter? e.g. from

using (MessageQueue adaptor = new MessageQueue()) { return adaptor.GetMessages(UserId, MobileId, StartDate, EndDate); } 

to

 using (MessageQueue adaptor = new MessageQueue()) using (OracleConnection connection = new OracleConnection(OracleUtilities.ConnectionString)) { adaptor.Connection = connection; return adaptor.GetMessages(UserId, MobileId, StartDate, EndDate); } 

Or is there a better way?

Colin

Answer 1

I found the answer I was looking for here:

ASP.Net Community Connection String Required for Guidance

in s_ruchit's answer.

The connection string names in the web.config file must match the connection string name in app.config, which contains the full name. Therefore, in my case, I change

 <add name="ConnectionStringMainDB" connectionString="<myConnStringHere>" providerName="System.Data.OracleClient"/> 

in

 <add name="DatabaseAccess.Properties.Settings.ConnectionStringMainDB" connectionString="<myConnStringHere>" providerName="System.Data.OracleClient"/> 

No other code changes are required! Thanks to Muhammad and freshness too

Answer 2

Use the standard ConnectionStrings configuration section, and you can get the connection string without resorting to the utility class:

System.Configuration.ConfigurationManager.ConnectionStrings

You still have to put the connection string in the app.config or web.config file of each application that requires a database.