VB.NET 7 Communicating with PostGreSQL Database Through Table Adapters, How to Dynamically Change Connection String?

Question

I have a working VB.NET project where I can communicate with a PostGreSQL database using a dataset and adding table adapters to it.

In the Visual Studio designer, when I go to add a new TableAdapter it prompts me to a "TableAdapter Configuration Wizard". From there I set my connection information and then I am ready to rock and roll in my code.

I would like to have the flexibility to dynamically change the connection string of my table adapter in my code. I have a configuration file I will read from and the connection string will need to change in production, having this flexibility will allow me to only have to change the configuration file and not have to rebuild any code when we move to production.

Here is a snippet of how I am using the table adapters in my code:

Dim mhcVAgingAdapter As New mhc_V_Aging_TAdapter()
Dim customerVAgingAdapter As New customer_V_Aging_TAdapter()

Dim newestModifyInMHCDB_Str As String
Dim newestModifyInMHCDB_Date As DateTime = New DateTime

' Find the newest modify date we have in our table
newestModifyInMHCDB_Str = mhcVAgingAdapter.GetOldestModifyDate()
If (newestModifyInMHCDB_Str IsNot Nothing) Then newestModifyInMHCDB_Date = DateTime.Parse(newestModifyInMHCDB_Str)

' Check if they have anything newer
If (customerVAgingAdapter.CountNewerRecords(newestModifyInMHCDB_Date, gWareHouseCode) < 1) Then
    vb_log_printf(gsPrgName, 1, myName & $": no records in the PostGreSQL vAging table have a modify date greater than [{newestModifyInMHCDB_Date}], our tables are up to date.")
    Exit Sub
End If

I'd like to move the adapters to a global variable, and in my initialisation method set the connection string based on the values in my config file. For now I would just like to understand how to change the connection string of the table adapter.

I want to be able to dynamically change the connection string of my table adapter.

Answer 1

So this answer does come from @jmcilhinney's last comment.

In the designer on visual studio the TableAdapter has a property called "ConnectionModifier", setting this to public achieves what I want. Here are some snippets for how my code is working.

' Globally defined adapter and NPGSQL connection objects
Public gCustomerTblAdapter As New customer_V_Aging_TAdapter()
Public gCustomerConnection As NpgsqlConnection

The connection string is read off of my configuation file, I can then set the global connection object's connection string, and then set the global table adapter's connection to be this connection object.

        connectionString = "Host=" & configPropertySplit(1) _
                        & ";Port=" & configPropertySplit(2) _
                        & ";Database=" & configPropertySplit(3) _
                        & ";Username=" & configPropertySplit(4) _
                        & ";Password=" & configPropertySplit(5)

    gCustomerConnection = New NpgsqlConnection(connectionString)
    gCustomerTblAdapter.Connection = gCustomerConnection

Now I can call on this adapter anywhere and do what I want to do, here is a method where I check if the connection works or not.

 Public Function CanWeConnectToCustomerDB() As Integer

    Dim myName As String = "CanWeConnectToCustomerDB"
    Dim rtn As Integer = GP.BAD

    Try
        gCustomerTblAdapter.Connection.Open()
        rtn = GP.GOOD
        gCustomerTblAdapter.Connection.Close()
    Catch ex As Exception
        vb_log_printf(gsPrgName, 0, myName & ": error cannot connect to customer database, exception [" & ex.Message & "]")
        rtn = GP.BAD
    End Try

    Return (rtn)
End Function