Sunday, June 19, 2016

ASP.NET Configurations: Retrieve Multiple Connection Strings From The Current Web Application's Web.config File

So you have to work with multiple connection strings that you want to use in the Web.config file and you want to use it in the code behind file.    Below are the steps to retrieve multiple connection strings in the web.config file programmatically.

1. Make sure you have connection strings in your Web.config file.  A connection string looks something like this.

  <connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="AdventureWorksConnectionString" connectionString="Data Source=(local);
Initial Catalog=AdventureWorks;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>


2. Now in the Default.aspx.cs file type in the following in the top of the file, where all the using statements are to use the System.Web.Configuration namespace

using System.Web.Configuration;
using System.Configuration;

Obviously you can use it in any .cs file, the Default.aspx.cs is just an example.


3. In the Page_Load method type in the following line
ConnectionStringSettingsCollection connectStrings = WebConfigurationManager.ConnectionStrings;

foreach (ConnectionStringSettings cstr in connectStrings)
{
Response.Write(cstr.ConnectionString + "<br>");
}

Or you can just do this and you don't have to assign WebConfigurationManager.ConnectionStrings to a variable at all. A lot less typing.
        foreach (ConnectionStringSettings cstr in WebConfigurationManager.ConnectionStrings)
{
Response.Write(cstr.ConnectionString + "<br>");
}

If you run the code you will get the following

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

Data Source=(local);Initial Catalog=Northwind;Integrated Security=True
Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True

Notice the attached SQLEXPRESS file aspnetdb.mdf file in your results, well it's because there's a
hierarchy in the Web.Config file and before .NET even look at the current Web.config  in your web application it looks in the .NET Framework install folder for the default Web.config file, this file is call the "Root Web" configuration level .  That's why the first item in the ConnectionStringSettingsCollection is not Northwind.

If you just want the current web applications Web.config connection strings section all you have to do is add the <clear/> tag right below the <connectionsStrings> opening tag in the current web application's Web.config file, like the xml code below.

  <connectionStrings>
<clear/>
<add name="NorthwindConnectionString" connectionString="Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="AdventureWorksConnectionString" connectionString="Data Source=(local);
Initial Catalog=AdventureWorks;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

The <clear/> tag tells .NET to remove all of the inherited configuration information and just use the configuration of the current web application.  So now the result will be

Data Source=(local);Initial Catalog=Northwind;Integrated Security=True
Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True

Which is what you really wanted in the first place.

No comments:

Post a Comment