Wednesday, July 2, 2008

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)

5 comments:

infmas said...

I used the suggestion to change the connection string, but is generating the error "Object reference not set to an instance of an object".
Some tableadapters works, but another ones generate these error.

Plus information:
- we use WebService that is called by a Winforms application;
- the same WebMethod can be called by different clients, that use different databases (that's why we need to change the connection string at every call);
- some WebMethod can call another WebMethod.

Please, if anyone has some information, I thanks a lot, because I already look on the web and didn't find anything.

Thanks for all.

Arsalan Tamiz said...

Hi infmas,

Where you are getting this error? In the TableAdapter Partial Class?

patrick said...

Your blog is very informative and very helpful. Thanks for this.
Web development

PatrickMiron said...

Is there a way to do this but that it applies to all datatables in my dataset. I have been trying to read up on overidables and extension but I haven't figure anything out yet.

Arsalan Tamiz said...

@PatrickMiron: Actually ConnectionString belongs to TableAdapter class. And normally each DataTable has different TableAdapter class. So you have to do this exercise with each TableAdapter.