Introduction

This article will provide methods to selectively change named connection strings (which are created under project properties, settings tab of a Windows Forms project) stored in an application app.config file. Selective, meaning using a class to read named connection strings, select a connection by name or to load named connections into either a ComboBox or ListBox control, selecting the current item which in turn obtain the underlying connection string. This will be shown for both handwritten code using managed data providers e.g. OleDb, SqlClient, Oracle etc.

Benefits

  • The DataSource for a connection string can be updated without redistributing an application. This means when a server name and/or database name changes but not table names or fields the only change required is to distribute the modified configuration file.
  •  Storage of development, test and production configuration information can be stored in the application configuration file to toggle between those environments.

Changing database connection environments

Let’s look at how connection strings are stored in an application configuration file. Below there are three connection strings, one for development, test, production, and home created by creating one setting per connection string under project properties, settings tab and have been validated they work.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Basics.My.MySettings.Development"
             connectionString="Data Source=DevelopmentServer;
             Initial Catalog=NorthWindAzure;Integrated Security=True"
             providerName="System.Data.SqlClient" />
        <add name="Basics.My.MySettings.Test"
             connectionString="Data Source=TestServer;
             Initial Catalog=NorthWindAzure;Integrated Security=True"
             providerName="System.Data.SqlClient" />
        <add name="Basics.My.MySettings.Production"
             connectionString="Data Source=ProductionServer;
             Initial Catalog=NorthWindAzure;Integrated Security=True"
             providerName="System.Data.SqlClient" />
        <add name="Basics.My.MySettings.Home"
             connectionString="Data Source=KARENS-PC;
             Initial Catalog=NorthWindAzure;Integrated Security=True"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
</configuration>

While the following connections were generated when using Visual Studio's data wizards to create type classes for database table accessed using TableAdapter components.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="ChangeConnectionStringRuntimeOleDb.My.MySettings.Database1ConnectionString"
             connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"
             providerName="System.Data.OleDb"/>
        <add name="ChangeConnectionStringRuntimeOleDb.My.MySettings.NorthWindConnectionString"
             connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\NorthWind.accdb"
             providerName="System.Data.OleDb"/>
        <add name="ChangeConnectionStringRuntimeOleDb.My.MySettings.CustomersMainConnectionString"
             connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CustomersMain.accdb"
             providerName="System.Data.OleDb"/>
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
    </startup>
</configuration>

Both work the same way, a connection is identified by name, sometimes by index to retrieve the corresponding connection string.

The first step to providing methods to change connection strings is to read the named connection strings from the current application's application configuration file. This is done with the following method located in a class project which can be added to a Visual Studio solution to use.

Private Sub GetInformation()
    ConfigurationManager.ConnectionStrings.Cast(Of ConnectionStringSettings)().Select(
        Function(item, index) New ConfigurationItem With {.Data = item, .Index = index}).ToList _
        .ForEach(
            Sub(configurationItem)
                If configurationItem.Data.Name.Contains(".") Then
                    Items.Add(
                        New ConnectionInfo With
                            {
                                .Name = configurationItem.Data.Name.Replace(_removeThis, ""),
                                .ConnectionString = configurationItem.Data.ConnectionString,
                                .Index = configurationItem.Index
                            })
                End If
            End Sub)
End Sub

Connections are exposed using the following property.

Public Property Items As New List(Of ConnectionInfo)

The following method returns a connection string by its name.

Public Function FindConnection(ByVal connectionName As String) As String
 
    Dim theConnection = Items _
        .FirstOrDefault(
            Function(item)
                Return item.Name = connectionName
            End Function).ConnectionString
 
    Return theConnection
 
End Function

To use the above in a project, add a reference to ConfigurationLibrary to a project. Add an Import statement for ConfigurationLibrary to a form. Next, create a new instance of ProjectConnections.

Public Class Form1
    ''' <summary>
    ''' Responsible for obtaining connection details
    ''' </summary>
    Private connections As ProjectConnections = New ProjectConnections()

Drop a ComboBox (in this case serverConnectionComboBox) on the form and set the DataSource as follows.

serverConnectionComboBox.DataSource = connections.Items

Place a button on the form for getting the current selected named connection with the following code to get the current connection string.

Dim selected As String =
        CType(serverConnectionComboBox.SelectedItem, ConnectionInfo).
        ConnectionString

Once this is done a connection can be made to work with data directly in a form or code module. If the data operations are performed in a data class the following is how it may be accomplished.

In the data class, create a property for the connection string e.g.  

Public Property DatabaseConnectionString() As String

Create a new instance of the data class in the form

Private dataOperations As New DataOperations

To get the connection string from a button click event

Private Sub changeConnectionButton_Click(sender As Object, e As EventArgs) _
Handles changeConnectionButton.Click
 
    dataOperations.DatabaseConnectionString =
    CType(serverConnectionComboBox.SelectedItem, ConnectionInfo).
        ConnectionString
 
    ReadData()
 
End Sub

Method to make a call to the data class and set the DataTable to a BindingSource component which in turn is set as the DataSource of a DataGridView.

Private Sub ReadData()
    bsCustomers.DataSource = dataOperations.GetCustomersAndContactTypes()
 
    If dataOperations.IsSuccessFul Then
        DataGridView1.DataSource = bsCustomers
 
        For Each column As DataGridViewColumn In DataGridView1.Columns
            column.HeaderText = column.HeaderText.SplitCamelCase()
        Next
 
        DataGridView1.ExpandColumns()
 
    Else
        MessageBox.Show("Failed to read data" &
            $"{Environment.NewLine}{dataOperations.LastExceptionMessage}")
    End If
 
End Sub

Visual of the ComboBox and button to read data from a SQL-Server database.


Custom ComboBox for connections

The following provides various methods to interact with named connections



Source code

Imports System.ComponentModel
Imports System.Windows.Forms
Imports ConfigurationLibrary
 
 
''' <summary>
''' Custom ComboBox designed to work with a specific DataSource of type
''' <see cref="ConnectionInfo">List(Of ConnectionInfo)</see>. To keep
''' things simple limited  assertion is performed which means use this incorrectly and
''' a run time exception will be thrown.
''' </summary>
Public Class ConnectionComboBox
    Inherits ComboBox
    ''' <summary>
    ''' Setup DropDownStyle and size
    ''' </summary>
    Public Sub New()
        DropDownStyle = ComboBoxStyle.DropDownList
        Size = New Drawing.Size(144, 21)
    End Sub
    ''' <summary>
    ''' Get a connection string by name which must exists and set this item as the
    ''' selected item, otherwise a runtime exception is thrown.
    ''' </summary>
    ''' <param name="pName">Name of the named connection string</param>
    ''' <returns>Connection string for pName</returns>
    Public Function ConnectionStringByName(pName As String) As String
        Dim itemList = DataSourceConnectionInformation.
                Select(Function(item, index) New With {.Name = item.Name, .Index = item.Index})
 
        SelectedIndex = itemList.FirstOrDefault(Function(item) item.Name = pName).Index - 1
        Return DataSourceConnectionInformation.
            FirstOrDefault(Function(item) item.Name = pName).ConnectionString
    End Function
    ''' <summary>
    ''' Determine if there is a selected item
    ''' </summary>
    ''' <returns>
    ''' True is has a selection, False if no current selection
    ''' </returns>
    <Browsable(False)>
    Public ReadOnly Property HasCurrentSelection() As Boolean
        Get
            Return SelectedIndex > -1
        End Get
    End Property
    ''' <summary>
    ''' Determines if the DataSource is
    ''' <see cref="ConnectionInfo">List(Of ConnectionInfo)</see>
    ''' </summary>
    ''' <returns>
    ''' True if proper set for
    ''' <see cref="List(Of ConnectionInfo)">List(Of ConnectionInfo)</see>, False if not
    ''' </returns>
    <Browsable(False)>
    Public ReadOnly Property HasProperDataSource() As Boolean
        Get
            If DataSource IsNot Nothing Then
                Return DataSource Is GetType(List(Of ConnectionInfo))
            Else
                Return False
            End If
        End Get
    End Property
    ''' <summary>
    ''' Get current <see cref="ConnectionInfo">ConnectionInfo</see> or a new ConnectionInfo
    ''' </summary>
    ''' <returns>Current item as <see cref="ConnectionInfo">ConnectionInfo</see></returns>
    <Browsable(False)>
    Public ReadOnly Property SelectedConnectionInfo As ConnectionInfo
        Get
            If HasCurrentSelection Then
                Return CType(SelectedItem, ConnectionInfo)
            Else
                Return New ConnectionInfo()
            End If
        End Get
    End Property
    ''' <summary>
    ''' Current item's ConnectionString
    ''' </summary>
    ''' <returns>ConnectionString</returns>
    ''' <remarks>
    ''' Removes providerName from connection string
    ''' </remarks>
    <Browsable(False)>
    Public ReadOnly Property SelectedConnectionString() As String
        Get
            If HasCurrentSelection Then
                Return CType(SelectedItem, ConnectionInfo).
                    ConnectionString.
                    Replace(""" providerName=""System.Data.SqlClient", "")
            Else
                Return ""
            End If
        End Get
    End Property
    ''' <summary>
    ''' Return current select item connection name
    ''' </summary>
    ''' <returns>Connection name</returns>
    <Browsable(False)>
    Public ReadOnly Property SelectedConnectionName() As String
        Get
            If HasCurrentSelection Then
                Return CType(SelectedItem, ConnectionInfo).Name
            Else
                Return ""
            End If
        End Get
    End Property
    ''' <summary>
    ''' Selected item connection index in app configuration file
    ''' </summary>
    ''' <returns>Current connection index from list</returns>
    <Browsable(False)>
    Public ReadOnly Property SelectedConnectionIndex() As Integer
        Get
            If HasCurrentSelection Then
                Return CType(SelectedItem, ConnectionInfo).Index
            Else
                Return -1
            End If
        End Get
    End Property
    ''' <summary>
    ''' Return underlying DataSource as a list of <see cref="ConnectionInfo">ConnectionInfo</see>
    ''' </summary>
    ''' <returns><see cref="ConnectionInfo">List(Of ConnectionInfo)</see></returns>
    <Browsable(False)>
    Public ReadOnly Property DataSourceConnectionInformation() As List(Of ConnectionInfo)
        Get
            Return CType(DataSource, List(Of ConnectionInfo))
        End Get
    End Property
    ''' <summary>
    ''' Control description
    ''' </summary>
    ''' <returns>About information</returns>
    <Browsable(True), Category("About"),
        DescriptionAttribute("Provides access to ConnectionInfo items")>
    Public ReadOnly Property Details() As String
        Get
            Return "Custom ComboBox for ConnectionInfo class"
        End Get
    End Property
End Class

Using the ComboBox

Setup is the exactly the same as the prior example, create an instance of the class ProjectConnections. Next, compile the custom ComboBox (provided in the source code accompanying this article), drop the ComboBox on to a form.

Set the DataSource to connections.Items as done in the prior example.  To get a named connection use the following code.

dataOperations.DatabaseConnectionString =
    ConnectionComboBox1.ConnectionStringByName("Home")

To get the selected connection from the ComboBox.

dataOperations.DatabaseConnectionString =
    ConnectionComboBox1.SelectedConnectionString

In the following example in form load there is a check for a specific user (a developer), if they are the current user do not load data but for others load using a specific server. When the user is not a developer, the ComboBox is not displayed which would mimic an application in the wild. The developer sees the ComboBox and can select any of the servers.

Imports ConfigurationLibrary
Imports ExtensionsLibrary
''' <summary>
''' This code sample demonstrates how to have a developer
''' and customer access to data. Dev mode is entered by identifying
''' the current user by current user name, if not a match then
''' customer mode is used.
'''
''' Dev mode provides a list of servers to select from along with
''' showing primary keys of two tables read via a JOIN.
'''
''' </summary>
''' <remarks>
''' BindingNavigator action buttons disabled as
''' they play not part in this code sample.
''' </remarks>
Public Class Form1
    ''' <summary>
    ''' Responsible for obtaining connection details
    ''' </summary>
    Private connections As ProjectConnections = New ProjectConnections()
    ''' <summary>
    ''' Responsible for reading data from SQL-Server
    ''' </summary>
    Private dataOperations As New DataOperations
 
    ''' <summary>
    ''' Logic to determine if in developer mode or customer mode
    ''' by checking the current user name.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
        ConnectionComboBox1.DataSource = connections.Items
 
        If Environment.UserName = "Karens" Then
            ConnectionComboBox1.Visible = True
            changeConnectionButton.Visible = True
        Else
            dataOperations.DatabaseConnectionString =
                ConnectionComboBox1.ConnectionStringByName("Home")
 
            ReadData()
        End If
 
 
    End Sub
    ''' <summary>
    ''' While in developer mode this button reads data from the selected
    ''' connection using ReadData procedure.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub changeConnectionButton_Click(sender As Object, e As EventArgs) _
        Handles changeConnectionButton.Click
 
        dataOperations.DatabaseConnectionString =
            ConnectionComboBox1.SelectedConnectionString
 
        ReadData()
 
    End Sub
    ''' <summary>
    ''' When called the connection string has been set in either
    ''' form load event or changeConnectionButton click event.
    ''' </summary>
    Private Sub ReadData()
        bsCustomers.DataSource = dataOperations.GetCustomersAndContactTypes()
 
        If dataOperations.IsSuccessFul Then
            DataGridView1.DataSource = bsCustomers
 
            For Each column As DataGridViewColumn In DataGridView1.Columns
                column.HeaderText = column.HeaderText.SplitCamelCase()
            Next
 
            DataGridView1.ExpandColumns()
 
        Else
            MessageBox.Show("Failed to read data" &
                $"{Environment.NewLine}{dataOperations.LastExceptionMessage}")
        End If
 
    End Sub
    Private Sub exitButton_Click(sender As Object, e As EventArgs) Handles exitButton.Click
        Close()
    End Sub
End Class

Class used in the form to read data from SQL-Server.

Imports System.Data.SqlClient
Imports BaseLibrary
Imports ExtensionsLibrary
 
Public Class DataOperations
    Inherits BaseSqlServerConnections
 
    Public Property DatabaseConnectionString() As String
    Public Sub New()
    End Sub
    Public Sub New(pConnectionString As String)
        DatabaseConnectionString = pConnectionString
    End Sub
 
    Public Function GetCustomersAndContactTypes(Optional pDeveloperMode As Boolean = False) As DataTable
        ConnectionString = DatabaseConnectionString.RemoveProvider
 
        mHasException = False
 
 
        Dim dtCustomers = New DataTable
 
        Dim selectStatement =
                "SELECT  C.CustomerIdentifier , C.CompanyName , CT.ContactTitle , " &
                "C.ContactName , C.[Address] As Street , " &
                "C.City , C.PostalCode As Postal, C.Country , C.ContactTypeIdentifier , C.ModifiedDate " &
                "FROM Customers AS C " &
                "INNER JOIN ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier; "
        Try
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cn.Open()
                    dtCustomers.Load(cmd.ExecuteReader())
                    If Not pDeveloperMode Then
                        dtCustomers.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                        dtCustomers.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                        dtCustomers.Columns("ModifiedDate").ColumnMapping = MappingType.Hidden
                    End If
                End Using
            End Using
        Catch ex As Exception
            mHasException = True
            mLastException = ex
        End Try
 
 
        Return dtCustomers
 
    End Function
 
End Class



Help documentation




The help documentation was generated with SandCastle and the project is set to not build.

Summary

This article has provided means to easily provide methods and a control to perform selective database connections for different environments for development, testing and production which work for all managed data providers along working with Visual Studio data wizards.
It wise to have separate environments rather than test and release in one environment so what has been presented makes it easier.

See also

VB.NET secure connection string 
VB.NET My.Settings deep dive 
DataGridView column configuration 

Requires

Microsoft Visual Studio 
Microsoft SQL-Server 

Note Before running the code sample Basics_SqlServer run script.sql.



Source code

Github repository