It is highly recommended to keep the connection strings of a Database in a separate configuration file so that you can change database connection related information such as password or database name without even modifying or recompiling your source code.
Storing database connection strings in source code can lead to security issues as sensitive information like Username and Passwords are available in the code and it can also cause maintenance problems like you change the password of your DB and then you need to change it everywhere in your code.
.NET Framework has a separate configuration file in both Windows Forms Application (App.config) and ASP .NET Web Application (Web.config). Developers can use this file’s configuration section to store connection string information such as a connection string name or provider type etc.
How to store connection strings in App.config/Web.config file
Open App.config/Web.config, inside the <configuration> element write the code as below
<connectionStrings>
<add name="SqlServer"
providerName="System.Data.SqlClient"
connectionString="Data Source=PCName-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;"+
"UserId=username;Password=password" />
<add name="Oracle"
providerName="System.Data.OracleClient"
connectionString="Data Source=localhost;Initial Catalog=Test;Integrated Security=True;"+
"UserId=username;Password=password" />
</connectionStrings>
Using the add element you can add connectionStrings to your App.config/Web.config file. You can add Connection Strings of as many databases as you want. Here we have added a connection string for Oracle and Sql Server.
How to retrieve connection string information in .NET Application using C#.
After saving the connection string in App.config/Web.config file you can use System.Configuration.ConfigurationManager class to read this connection string in code.
For that add a reference of System.Configuration.dll in your project.
Right Click on References->Add Reference.
Select .NET Tab. Find System.Configuration there. Click on OK.
The reference should get successfully added to your project.
Now add the namespace using System.Configuration in your code.
Till now probably you have been creating a Connection like this, hardcoding the connection string in your code files.
SqlConnection conn = new SqlConnection("Data Source=PCName-PC\\SQLEXPRESS;UserId=username;Password=password;" +
"Initial Catalog=Test;Integrated Security=True");
Change the above code with the code below.
public static string cnString = ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString; SqlConnection conn = new SqlConnection(cnString);
or use
public static ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["SqlServer"]; public static string connString = conSettings.ConnectionString; SqlConnection conn = new SqlConnection(connString);
The ConnectionStringsSettings class provides properties to read connection string settings in your program as following code shows.
string name = conSettings.Name; string providerName = conSettings.ProviderName; string connString = conSettings.ConnectionString;
That’s it. This is the easy way to save your connection strings at one place and freedom from several maintenance problems.
