Updating a Named Connection String in Memory VB.Net

[Philip]

Hello All,

I wanted to post this so no one else has to go through what I went through trying to figure out how to dynamically change the connection string when using .trdx files as the urisource in the windows forms report viewer and having each customer with their own database being able to run the canned reports that we make for our software.

First, when you design the report, give the SQL Datasource a Name and check the box to save it in the configuration file (I did this from the Telerik Standalone Report Designer. This will not expose your connection information to end users and allow them to create alternative reports for your canned reports without letting them see the connection password.

Second, add a line into your [appname].exe.config file for the connection string and leave the connection string value BLANK. It should look something like this (put this right under <configSections> and I think the casing matters on <connectionStrings>:

<connectionStrings>
    <add name="Marketplace" providerName="System.Data.SqlClient" connectionString="" />
  </connectionStrings>

Next, if you have a connection string either encrypted (we encrypt our connection password only and read it into memory on login to our application and assign it to a global variable called ADOnet), you will want to set the named connectionstring in memory and have it refresh. In VB.Net, this can be done by using the following code:

Dim config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Dim connectionStringsSection = DirectCast(config.GetSection("connectionStrings"), ConnectionStringsSection)
connectionStringsSection.ConnectionStrings("Marketplace").ConnectionString = ADONet
config.Save()
ConfigurationManager.RefreshSection("connectionStrings")

In order to get ConfigurationManager to show up under System.Configuration you need to add System.Configuration explictily to your references in your project (just imports or using will not give you configuration manager)

Anyway, this will update your settings for your named connection in your reports in memory while in runtime.

Hope this helps you to not spend the 5 hours of research and testing it took me to find this solution.

Philip Carter
http://emanageone.com

[Philip]

One more thing you should know about doing this.

In the application.exit you will want to blank out the connection string so no one can see it by doing the following:

Dim config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Dim connectionStringsSection = DirectCast(config.GetSection("connectionStrings"), ConnectionStringsSection)
connectionStringsSection.ConnectionStrings("Marketplace").ConnectionString = ""
config.Save()
ConfigurationManager.RefreshSection("connectionStrings")

Notice the "" in the connectionstring =

This will keep the value from saving physically to the file and keep your passwords secure.

[Hinata]

Hi Philip,

Thanks very much for sharing this. I was doing something similar by deserializing the TRDX report to a report object and then setting its DataSource.ConnectionString programmatically. The approach you suggested will help me to make some adjustments and maintain the named conn string information in the configuration file.

[Philip]

I found a caveat to this solution that everyone should be aware of. Starting in Windows Vista, applications no longer have access to their own directory in program files. In order to change the configuration settings, the application will need write access to its directory. We created a batch file that sets the security on our application folder to allow it access. I will post it a little later in this thread as soon as I make it more generic.

[Philip]

One more update to this post.

Instead of trying to get people to open up security on our application's directory in Program Files, we made the decision to change our setup program to install our software into the AppData Folder where we would not have to change the security in order to gain access to the config file to change the connectionstring at will.

So  far so good.