How to get SQL Connection String from App.Config / Web.Config using C#
Jun 06, 2013
TRAILER
This may seems to be very hilarious to all ninja programmers but it possibly help starters and also save me the trouble to remember it !!
MOTION PICTURE
What would you do without a connection string when you wish to talk to a database from your application. Good practice – adding connection string in the app.config or web.config and use it wherever needed in application.
This helps in changing connection string without changing (compiling or deploying) our code.
How to define connection string:
The configuration file has dedicated section for this purpose called “connectionStrings”, where you can add multiple entries of connection strings you wish to configured for your application.
The connection string element contains three elements:
-
Name: Name for the connection string entry which can be used to reference that connection string. (Mandatory)
-
ConnectionString: The connection string that contains the specification to make the connection to the underlying database. (Mandatory)
-
ProviderName: The name of the ADO.NET provider which can be used to access the underlying database. (Optional) [Default value:
System.Data.SqlClient]
Simple Example:
<configuration>
<connectionStrings>
<add name="YourConnectionStringName"
connectionString="Data Source=PathToYourDatabase;
Integrated Security=SSPI;Initial Catalog=NameOfYourDatabase;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
How to retrieve connection string from App.Config or Web.Config:
The code to retrieve connection string from configuration file (app.config or web.config) is fairly simple using ConfigurationManager from System.Configuration
Eg:
var myConnectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
How would I know the connection string is valid:
Sometime it may happen that you mess up with the connection string and by mistake add a connection string that may not work or is not valid string.
There is a way to check if the connection string value is valid or not using implementations of DbConnectionStringBuilder class. There is a implementation of DbConnectionStringBuilder for each supported provider. For Sql you can use SqlConnectionStringBuilder.
SqlClient Example:
var myConnectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
var connectionStringBuilder = new SqlConnectionStringBuilder { ConnectionString = myConnectionString };
It will throw ArgumentException if the connection string is not valid.
Also using the above class you can get the value of individual keys [sections] of the connection string.
CREDITS