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

Use a standard ConnectionStrings configuration section and you will be able to retrieve the connection string without resorting to a utilities class:

System.Configuration.ConfigurationManager.ConnectionStrings

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

Answer 2

First of all you have to add reference in your library of System.Configuration in .Net tab from reference window
then open your settings.Designer.cs file, you will look like this..

public string yourConname_ConnectionString {
        get {
            return ((string)(this["yourConname_ConnectionString"]));
        }
    }

Replace this with....

public string yourConName_ConnectionString {
        get {
            return ((string)(System.Configuration.ConfigurationManager.ConnectionStrings["yourwebconfigConName_ConnectionString"].ToString()));
        }

Answer 3

I found the answer I was looking for here:

http://stackoverflow.com/questions/899346/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