Problems with connection strings with DAL in separate project

Question

I originally wrote a data access layer inside an App_Code folder in my website. Then we developed a web service in a separate project. So that both the website and the web service can access the same DAL, I moved it to a different project. I have a dataset with tableadapters and to make the DAL project compile I had to add the connection string to the Application property settings. But this means that I have to recompile the DAL for every deployment. Also I may have 2 or 3 websites on the server using the same DAL. So I want to set the connection string in each website's web.config and leave it at that. Do I have to go through my code and change every time i create an instance of a tableadapter? 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:

Guidance needed ASP.Net app connection string

in the answer by s_ruchit.

The connection string names in the web.config have to match the connection string name in the app.config - which contains a fully qualified name. So in my case I change

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

to:

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

No other code changes required! Thanks to Muhammed and freshr too