.NET Managing more than one connection string from Web.config

If you have worked with a Data Access Layer than you may have to connect to different databases, each located in a separate server and with different permissions. Therefore, this article expands on the technique of creating a custom list that contains all the connection strings and allowing developers to select one during design time without having to know more than just the name of the connection. Written in C# 2.0


Make sure to add your namespace reference

using System.Configuration;



First we need to create a ConnectionList that inherits from IList<>


/**
 * Data_Access_Layer - Management
 * ConnectionsList contains all the connection items from the web.config file
 * The methods contained in the List<> Already contains all the necessary methods
 * to manage the list, except for Insert (ConnectionStringSettings item) and 
 * this[string name] which exist to ease the management and use of connection
 * strings with the Asp.Net Application
 **/
public class ConnectionsList : IList<ConnectionStringSettings>

You may implement this class in whatever way you see fit. Just keep in mind that in this example is added the method Insert(ConnectionStringSettings item) and the this[string name] Index property.


public void Insert(ConnectionStringSettings item)
        {
            int index = list.Count; //inserts the item into the end of the list
            this.Insert(index, item);
        }
public ConnectionStringSettings this[string name] //search for item by name
        {
            get
            {
                int index = 0;
                while (string.Compare(this[index].Name, name, true) != 0)//ignore casing for string compare
                {
                    if (index < list.Count - 1) index++; //count should not surpass the number of items in the list
                    else throw new Exception("Connection with name " + name + " was not found, check your web.config file and ConnectionString property in your BusinessObject to verify the strings match");
                }
                return this[index];
            }
            set
            {
                int index = 0;
                while (string.Compare(this[index].Name, name, true) != 0)
                {
                    if (index < list.Count - 1) index++;
                    else throw new Exception("Connection with name " + name + " was not found, check your web.config file and ConnectionString property in your BusinessObject to verify the strings match");
                }
                this[index] = value;
            }
        }

Then,  create a class that will handle the connections, fill the ConnectionList and allow the developer to select a connection based on the connection name.






public
class Connection_Manager { internal System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(Resources.ConfigPath.ToString()); internal ConnectionsList connections; /// <summary> /// List of connections retrieved from the web.config file and set as a list /// </summary> public ConnectionsList ConnectTo { get { return connections; } } /// <summary> /// Fills the ConnectionList object with instances of connection strings /// </summary> public Connection_Manager() { connections = GetConnections(); } /// <summary> /// Retrieves all the connections listed in the web.config file /// </summary> /// <returns>The List of connections retrieved</returns> private ConnectionsList GetConnections() { ConnectionsList list = new ConnectionsList(); //instantiates the list of connections foreach (ConnectionStringSettings conn in rootWebConfig.ConnectionStrings.ConnectionStrings) { if(conn.Name!= "LocalSqlServer")//all the strings except the .\SQLExpress connection for ASPNETDB list.Add(conn); } return list; } }


When used, the Connection_Manager class allows developers to reference the connections by simply typing

ConnectionList.ConnectTo["SQLServer-Connection"];




you may download the code source here

By Carlos Casalicchio   Popularity  (1978 Views)
Picture
Biography - Carlos Casalicchio
Information Technology Executive | IS | IT | DEV | since 1999. Worked, lived, and graduated in Utah. Experienced in infrastructure projects, as well as developement, project management, business, HR consulting and coaching.