How to add a connection string to the web.config file using WebConfigurationManager during run-time.

It is now possible to add a dynamically build connection string to the configuration files especially to the web.config file during the run-time by using the WebConfigurationManager class.

WebConfigurationManager class simply provides access to the configuration files for web applications and using the WebConfigurationManager class is the preffered way of working with the configuration files.
WebConfigurationManager class belongs to the System.Web.Configuration namespace. WebConfigurationManager class provides write option on the configuration files and make changes in any level of configuration settings with the open methods OpenWebConfiguration, OpenMachineConfiguration, OpenMappedWebConfiguration and OpenMappedMachineConfiguration.
These open methods enables .Microsoft Framework programmers to write or alter any settings in the .config files.

One important class we will deal during this article or tutorial is the SqlConnectionStringBuilder class. We will use SqlConnectionStringBuilder class to build the connection string easily without living the complexity of a connection string syntax.
As SqlConnectionStringBuilder class has properties and an instance of this class will have those properties to set the connection string properties like DataSource, UserID, Password, IntegratedSecurity, etc. we do not need to memorize the correct syntax of a connection string.
Probably you might have experienced problems since you have typed User or UserId instead of User ID while building a conenction string in the web.config files. Because of these such problems a connection string builder class was one of the suggested items from the .NET Framework developers community.
And now we have the SqlConnectionStringBuilder class.

Last thing you will see in this editing configuration files for connection strings tutorial is how we can keep the connection string in an other configuration file rather keeping the connectionStrings in the web.config file.
And we will see how we can edit this new configuration file which stores the connection strings without any change in our .NET framework codes.
All these simplicity can be obtained by using the configSource attribute of the connectionStrings in the configuration file web.config.

How to build a connection string using SqlConnectionStringBuilder class

To build a connection string, we can use one of the ConnectionStringBuilder classes. These classes can be listed as follows and is used for different types of data connections:

SqlConnectionStringBuilder
OleDbConnectionStringBuilder
OdbcConnectionStringBuilder
OracleConnectionStringBuilder

I'm going to use SqlConnectionStringBuilder class since as Microsoft Framework developers we are mostly connecting to MS SQL Server database instances.

In order to use SqlConnectionStringBuilder class, we should import System.Data.SqlClient namespace into our source codes as:

Imports System.Data.SqlClient


 Dim connectionStringBuilder As New SqlConnectionStringBuilder()



 'Using SQL Authentication

 connectionStringBuilder.DataSource = "localhost"

 connectionStringBuilder.UserID = "dnn"

 connectionStringBuilder.Password = "dnn"

 connectionStringBuilder.InitialCatalog = "CTP6"



 'ConnectionString property will give us the final connection string

 connectionStringBuilder.ConnectionString

Code

The above code will produce the below connection string:


"Data Source=localhost;Initial Catalog=CTP6;User ID=dnn;Password=dnn"

Code

If we decide to use the Windows Authentication, we can make the following changes while we are setting the properties of the SqlConnectionStringBuilder class instance


 Dim connectionStringBuilder As New SqlConnectionStringBuilder()



 'Using Windows Authentication

 connectionStringBuilder.DataSource = "localhost"

 connectionStringBuilder.IntegratedSecurity = True

 connectionStringBuilder.InitialCatalog = "CTP6"



 'ConnectionString property will give us the final connection string

 connectionStringBuilder.ConnectionString

Code

And the final connection string that is built will be as:


"Data Source=localhost;Initial Catalog=CTP6;Integrated Security=True"

Code

How to store connection strings in an other configuration file rather than web.config

If you use configSource attribute in a web.config configuration section, it points to the name of the configuration file that is associated with that configuration section.

For instance, if we set the configSource attribute in our connectionStrings section as follows:


 <connectionStrings configSource="connections.config"/>

Code

Than we can create a connections.config file in the root folder of our web application, and place all the connection strings information within this configuration file.

All we should do is copying the main tags which are for this example <connectionStrings>, </connectionStrings> and the contents between these tags into this new configuration file.

Below is a sample connection.config file, which is set as the connectionStrings tag configSource attribute value.


<connectionStrings>

 <add name="LocalhostCTP6" connectionString="Data Source=localhost;Initial Catalog=CTP6;User ID=dnn;Password=dnn" providerName="System.Data.SqlClient" />

 <add name="DynamicConnection" connectionString="Data Source=localhost;Initial Catalog=CTP6;User ID=dnn;Password=dnn" />

</connectionStrings>

Code

How to add a new connection string to the configuration file web.config during run-time

In this section, we will code out web application in order to create and add a new connection string in the configuration file web.config or in the configSource file set in the connectionStrings section.

It is important that we will use WebConfigurationManager class, OpenWebConfiguration method, call the GetSection method or just for connection strings and application settings call related collections ConnectionStrings or AppSettings, then call the ConnectionStrings.Add Add method and last Save() method to reflect the changes.


'Use OpenWebConfiguration method of WebConfigurationManager object in order to write to configuration file
Dim webConfiguration As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)



'If you want to get any other section than the ConnectionStrings and the AppSettings sections, you can use the GetSection method.
'Dim connections As ConnectionStringsSection = DirectCast(cfg.GetSection("connectionStrings"), ConnectionStringsSection)



'ConnectionStrings section can be directly accessibly by calling the webConfiguration.ConnectionStrings method.
Dim connections As ConnectionStringsSection = webConfiguration.ConnectionStrings



'Before adding a new connection string, check whether it is already included in the configuration file.
If connections.ConnectionStrings("DynamicConnectionNT") Is Nothing Then

connections.ConnectionStrings.Add(New ConnectionStringSettings("DynamicConnectionNT", connectionStringBuilder.ConnectionString))

End If



'Save the changes to in order to reflect them into the configuration file.
webConfiguration.Save()

Code