Assign connection string to table adapter from session

Question

My application is already developed and now we are going to change the connection string whatever stored in the session object (Bcoz of Distributed Database Management System (DDBMS))

Problem is here.....

In that application There are so many **ObjectDataSource** which are

initialize with the using .XSD file. which is related to the TableAdapter and in which connection string of TableAdapter is assign from the Web.Config File. Now How to change the connection string to whatever stored in session object?

Thanks in advance.

Answer 1

This is one of the reasons I hate Typed Datasets, and is actually one of the short-comings of LinqToSQL as well (but is more easily worked around).

I think you're stuck either regenerating all your typed datasets once you change the connection string, or going in and "customizing" them to use the connection string out of the web.config file at run-time rather than copying it from the web.config at design-time. If you "customize" you then have to worry about losing your customized code whenever you make any changes that cause the typed dataset to be recreated. Such are the pros/cons.

EDIT: I misunderstood the question slightly. Solution is still the same, but only my second proposed solution is viable. You'll have to customize your TableAdapter code and then be careful to maintain it when you make changes to your typed datasets.

Answer 2

I have a winforms app that I had a similar problem. I created a static class that contained my settings (clsGlobal) and one of the properties was a connection string. In the page where I referenced the datatable, I set the connection string to clsGlobal.gstrConnectionString or the connection string property. May be better to do this in an app initialization phase. However, setting the tableadapter connection string upon app startup from a shared property should get you around this.

ie: myTableAdapter.Connection.ConnectionString = clsGlobals.gstrConnectionString;

So, you would just .ToString() your session object and assign it to ta.Connection.ConnectionString.

Answer 3

The generated tabled adapter is a partial class. This means you can add code for that class in another file. You can make the Connection property of the table adapter public and modify it as you wish from the client code. Hardly a good solution, but maybe the only one you have.

Answer 4

To change the connection of an XSD at runtime you'll need to set the ConnectionModifier property of the table adapter to Public. If they're created by the "wizard" they will be set to Friend/Internal (VB/C#) by default.

I had trouble finding the ConnectionModifier property (it's not listed in my vs2005 documentation)! If you click the lower area of the XSD (where the queries are) then it should be visible in the properties window to change.

Then you can set the Connection property (it takes an object not a string) where ever you declare your table adapter.