Database selective connection strings (VB.NET)
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, MySql 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.