Connection string management in C#

Connection is the important and primary object in .NET technology.

Many times users will feel the difficulty of making connection between two applications.
You can render any kind of queries and codes to achieve the target but only after making the connection. Isn't it?

Here two approaches are given to connect to any database using C# in .NET application.


APPROACH 1 (Standard)

STEP 1: Save the connection string in App.config file
STEP 2: Use the saved connection string in program


1: Saving in Connection strings section in App.Config file
      <configuration>
           <connectionStrings>
      <add name="MyConnectionString" connectionString="Data Source=(local);Initial Catalog=DatabaseName;                        Persist Security Info=True;UserID=Name Password=MyPwd;Connect  providerName="System.Data.SqlClient"/>
           </connectionStrings>
      </configuration>


[Program code : C#]
2: Fetching connection string from ConfigurationManager: (Standard way)
string MyconnString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;





APPROACH 2 (Advanced) (Saving programmatically)
(No need to touch web/App config files)

[Program code : C#]

[Saving in ConfigurationManager programatically]

            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        config.ConnectionStrings.ConnectionStrings.Clear();

                //For saving the connection string in Configuration file
                string con = "DSN=MyDataBase;Uid=User1;Pwd=*****;";


                config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("NewConnection", con));
                config.Save();

                ConfigurationManager.RefreshSection(config.ConnectionStrings.SectionInformation.SectionName);


        //[Using the stored connection string in our program]
        //[ODBC connection has been used here for example. We can use our own drivers]


                OdbcConnection OdbcCon;
                ConnectionStringSettings connection = ConfigurationManager.ConnectionStrings["NewConnection"];

           if ((connection!= null) && (connection.ConnectionString != null))
             {
                  string MyConnection = connection.ConnectionString;
                  }

                OdbcCon = new OdbcConnection(MyConnection);


               // If you want to remove a particular connection in config file, 
              //you can use the below code before adding a new one to config file
               
            config.ConnectionStrings.ConnectionStrings.Remove("NewConnection");
                config.ConnectionStrings.ConnectionStrings.Clear();



Once you stored the connection string in config file either by programatically or by updating app.config, you can retrieve the parameters (UserID,Password) using the below ways also:

    // [Retrieve using ConnectionStringBuilder]

SqlConnectionStringBuilder con = new   SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
    string userID = con.UserID;
    string password = con.Password;

       // [Retrieve using var type]

    var builder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)
    var userID = builder.UserID;
    var password = builder.Password;

      //[Retrieve using split operator]

    var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    var splitters = connectionString.Split(';');

    string userId;
    string password;

    for(var i = 0;  i < splitters.Length; i++) {
        var splittedSection = splitters[i];
        if(splittedSection.StartsWith("User ID"))
            userId = splittedSection.Substring(splittedSection.IndexOf("=") + 1);

        if(splittedSection.StartsWith("Password"))
            password = splittedSection.Substring(splittedSection.IndexOf("=") + 1);
    }