Changing Connection String in TableAdapter

This article describes a way to change a Connection String in TableAdapter

Level: Intermediate

Knowledge Required:

• Typed DataSet
• TableAdapter
• Partial Class
• Project Settings

Description:
As we have used the DataSet Designer from Visual Studio, to create a Typed DataSet. Usually our Typed DataSet contains TableAdapter which interacts with Physical Database. When we first time add Typed DataSet using DataSet Designer, it adds a Connection String in the Project Setting. This connection string is by default accessed by the TableAdapter. Note that the connection string in Project Setting is defined with Scope = Application, which makes it a read-only property, therefore cannot be changed from code.

But sometimes it is required to change the Connection String, for example if different users log in the software then connection string will be different for each user with respect to their Login IDs and Passwords.

So to resolve this issue we can directly access the Connection property of TableAdapter to set the ConnectionString.

MyTableAdapter.Connection.ConnectionString = "...."

But another approach is to modify the TableAdapter using Partial Class and add methods that shall allow us change the Connection String, example,

Namespace CityDataSetTableAdapters
   Partial Class CityTableAdapter
       Public Sub New(ByVal sConStr As String)
           Me.New()
           Me.Connection.ConnectionString = sConStr
       End Sub

       Public Sub SetNewConnectionString(ByVal sConStr As String)
           Me.Connection.ConnectionString = sConStr
       End Sub
   End Class
End Namespace

Now we can create another Setting in our Project with Scope = User and then we will change this setting according to current login in our code.



Usage:

Dim adpCity As CityDataSetTableAdapters.CityTableAdapter
Dim tblCity As CityDataSet.CityDataTable

My.Settings.MyConnectionString = "Data Source=.;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=user;Password=password"
adpCity = New CityDataSetTableAdapters.CityTableAdapter(My.Settings.MyConnectionString)
tblCity = adpCity.GetData()
Debug.Print(tblCity.Count)