Fetching Connection Strings

Tags: Knockout, pubsub, observer, MVC, jQuery, Ajax, EF, Validation, FluentValidation, Visual Studio 2010, ASP.NET, JSON, FullCalendar, Silverlight, Architecture, Vista, IIS, Generics, NHibernate, WCF, RIA Services, Visual Studio 2008, SQL, STORM!, Nullable, ChannelFactory, netTCPBinding, VSPAT, responsive, design, HTML5, CSS3, MVC WebAPI, MVC 4, WebAPI, JQuery Mobile, ScheduleWidget, recurring events, Ninject, Pluggable, CQRS DDD, Windows

Let's assume you wish to store your database connection string in the <connectionStrings> configuration section of Web.config. This is the best practice in my opinion because you can (and should) encrypt the section in production. Now you have two basic options:

(1) fetch the connection string at the UI level and pass it back to the data layer.

(2) remove the dependency altogether and let the data layer fetch it from Web.config

I've done it both ways. With option (1) you have a simple call to the ConfigurationManager:

string connectionString = ConfigurationManager.ConnectionStrings["FooBar"].ConnectionString;

 

Most people do this and it's quick and simple. If you don't want the coupling or you don't feel like parameter passing or you want your unit test assembly to be able to get a connection string without needing to reflect over the UI assembly, then option (2) involves creating a helper method that any assembly in your solution can call. Here's one that opens a config file from an explicit virtual root location and returns a string value that matches the passed in key:

public static string GetConnectionString(string connectionName) {
    string connectionString = string.Empty;
    // reading from explicit IIS virtual root alias name rather than "~"
    Configuration c = WebConfigurationManager.OpenWebConfiguration(@"\Widget");
    if (c != null) {
        ConnectionStringsSection section = c.GetSection("connectionStrings") as ConnectionStringsSection;
        foreach (ConnectionStringSettings css in section.ConnectionStrings) {
            if (css.Name.ToUpper().CompareTo(connectionName.ToUpper()) == 0) {
                connectionString = css.ConnectionString;
                break;
            }
        }
    }
    return connectionString;
} 

 

Then it's pretty simple to fetch a connection string from your DAL layer: 

string connectionString = GetConnectionString("FooBar");
SqlConnection cn = new SqlConnection(connectionString);
Add a Comment