Switching Data Connections for an ObjectDataSource at Runtime
To paraphrase a posit from my previous post, the ASP.NET tool set gives web developers quite a set of giant’s shoulders upon which to stand. Yet a real world requirement can still be just out of reach despite this height advantage.
I came across such a requirement in a recent project. I had designed a page to display the results of a query against some customer payment data, implemented with the familiar combination of a GridView (I know, I’m obsessed) bound to an ObjectDataSource bound to a Typed DataSet. The trick was that the application had to be able to query data from one of potentially hundreds of different ODBC DSNs, based on a user’s selection in a drop down on the page.
As it turns out, the ObjectDataSource raises an ObjectCreated event. When it is bound to a Typed DataSet, said object being created is an instance of our TableAdapter, who just happens to be the keeper of our data connection. In the code-behind for the page, we can create a handler for this event, in which we can use Reflection to set this object’s Connection property, thusly (yes, we’re using VB):
Protected Sub MyObjectDataSource_ObjectCreated(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.ObjectDataSourceEventArgs) _ Handles MyObjectDataSource.ObjectCreated 'get the connection for the selected company Dim myConnection As OdbcConnection = _ CreateConnection(myConnectionDropDown.SelectedValue) 'set the connection for the ObjectDataSource using reflection If e.ObjectInstance IsNot Nothing Then e.ObjectInstance.GetType().GetProperty("Connection"). _ SetValue(e.ObjectInstance, myConnection, Nothing) End If End Sub
“But dude,” I hear you say in your best Ted Theodore Logan voice, “why didn’t you just use the Page’s Load handler to set the TableAdapter’s Connection property before calling the query, and then totally assign its results to the GridView’s DataSource property?”
Shut up, Ted.