Changing XSD ConnectionString at Runtime for a Multitenant app

Question

I'm changing our application from "one set of code & one database" to "one set of code to multiple databases (one database per customer)".

The original code is VS2005 ASP.NET(VB) & lots of XSD's in a separate DLL. The web.config's ConnectionString would override the one stored in the DLL at runtime.

Now I need to change the ConnectionString every time I declare a Data adapter/Dataset/table, because the call could be going to a different database from the last call.

Has anyone got any hints on this?

Answer 1

After a bit of research, it seems that an XSD has a property called ConnectionModifier.

To find it, on your XSD diagram, click the TableAdapter part of the diagram (where the queries are defined).

In the properties window, change ConnectionModifier to Public and click Save. (This seems to change the property for all the Datasets on that page too.)

Back in the main code of your site you can now do something like this:

'declare the adapter as normal
Dim AdapterTest As New DataSetTestTableAdapters.TestTableAdapter

'pass the new connection object into the now visible property
AdapterTest.Connection = New Data.SqlClient.SqlConnection("Data Source=Myserver;Initial Catalog=TEST;Integrated Security=True;")

It only takes a connection object.

I have yet to give this a proper test! Unfortunatley, a new connection object will have to be passed every time you declare something from an XSD.