Add variables to connection string Dynamically

Anil Golamari
Anil Golamari used Ask the Experts™
on
Hi,

I would like to add couple of dynamic variables to the connection strings. But when I try to use System.configuration to add these variables, I am getting below error and can someone help on resolving this issue or suggesting a work around.

	public ConnectionStringSettings GetConnectionStringSettings(string name)
		{
			ValidateInstanceName(name);

            ConnectionStringSettings connectionStringSettings;
            ConnectionStringSettings connectionStringSettings1;
            ConnectionStringSettings connectionStringSettings2;

            ConfigurationSection configSection = configurationSource.GetSection("connectionStrings");
            if ((configSection != null) && (configSection is ConnectionStringsSection))
            {               
                ConnectionStringsSection connectionStringsSection = configSection as ConnectionStringsSection;         
                connectionStringSettings1 = connectionStringsSection.ConnectionStrings[name];

             connectionStringSettings2 = connectionStringSettings1.ConnectionString.Insert(0, Resources.CurrentClient_ApplName + GetClientApplicationName + ";"); 
              connectionStringSettings = connectionStringSettings2.ConnectionString.Insert(0, Resources.CurrentClient_UserID + GetClientUserID + ";");
                                                              
            }
            else
                connectionStringSettings = ConfigurationManager.ConnectionStrings[name];
                
			ValidateConnectionStringSettings(name, connectionStringSettings);
			return connectionStringSettings;
		}
 

Open in new window


Error: Cannot implicitly convert type 'string' to 'System.Configuration.ConnectionStringSettings' on lines 15 & 16 above.

Connection string Example:
Before adding variables:
"Database=xyz,CurrentSchema=abc;UserID=inside;pwd=password"

After adding variables:
"Currentapplicationname=web;currentclientid=client1; Database=xyz,CurrentSchema=abc;UserID=inside;pwd=password"

Any help will be greatly appreciated.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
                var configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                var connSetting = configuration.ConnectionStrings.ConnectionStrings["connTest"];

                connSetting.ConnectionString = "myKey:key;" + connSetting.ConnectionString;

                configuration.Save(ConfigurationSaveMode.Full);

Open in new window

Most Valuable Expert 2012
Top Expert 2008

Commented:
Another approach to building a dynamic connection string, is to use a connection string builder.

SqlConnectionStringBuilder Class
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Create a new SqlConnectionStringBuilder and
        // initialize it with a few name/value pairs.
        SqlConnectionStringBuilder builder =
            new SqlConnectionStringBuilder(GetConnectionString());

        // The input connection string used the 
        // Server key, but the new connection string uses
        // the well-known Data Source key instead.
        Console.WriteLine(builder.ConnectionString);

        // Pass the SqlConnectionStringBuilder an existing 
        // connection string, and you can retrieve and
        // modify any of the elements.
        builder.ConnectionString = "server=(local);user id=ab;" +
            "password= a!Pass113;initial catalog=AdventureWorks";

        // Now that the connection string has been parsed,
        // you can work with individual items.
        Console.WriteLine(builder.Password);
        builder.Password = "new@1Password";
        builder.AsynchronousProcessing = true;

        // You can refer to connection keys using strings, 
        // as well. When you use this technique (the default
        // Item property in Visual Basic, or the indexer in C#),
        // you can specify any synonym for the connection string key
        // name.
        builder["Server"] = ".";
        builder["Connect Timeout"] = 1000;
        builder["Trusted_Connection"] = true;
        Console.WriteLine(builder.ConnectionString);

        Console.WriteLine("Press Enter to finish.");
        Console.ReadLine();
    }

    private static string GetConnectionString()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file. 
        return "Server=(local);Integrated Security=SSPI;" +
            "Initial Catalog=AdventureWorks";
    }
}

Open in new window

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial