Changing the connectionstring of a wizard generated TableAdapter at runtime from an ObjectDataSource

The Visual Studio 2005 Dataset designer allows you to create a DAL using a typed dataset and easily bind this to a GridView with the help of an ObjectDataSource. By default, in the TableAdapter generated, the visibility of the encapsulated Connection object is set to private.

For the calling code to specify its own Connection object, the visibility of the Connection property has to be made public. This can be done by setting the ConnectionModifier property of the TableAdapter to public.

ConnectionModifier

Once that is done, changing the Connection at runtime can easily be done as shown below:

DataSet1TableAdapters.CustomersTableAdapter adapter = new DataSet1TableAdapters.CustomersTableAdapter();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = MyConnectionManager.ConnectionString;
adapter.Connection = conn;
DataTable table = adapter.GetData();

What if an ObjectDataSource were using this TableAdapter and we wanted to change the ConnectionString at runtime?

The ObjectDataSource, thankfully, exposes an ObjectCreated event. This event gets raised after the object specified by the TypeName property is created.  We can obtain a reference to this object by the ObjectInstance property exposed by the ObjectDataSourceEventArgs object. The Connection property can then be changed with a little sprinkle of reflection as shown below:

protected void ObjectDataSource1_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
{
    if (e.ObjectInstance != null)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = MyConnectionManager.ConnectionString;
        e.ObjectInstance.GetType().GetProperty("Connection").SetValue(e.ObjectInstance, conn, null);
    }
}

This is useful in places where you wanted to set the ConnectionString to use based on the role of the web user. As always, if you know of a better way to do this, please post a comment. Thanks.

24 Comments

  • Got a question on this: I am using a class library, and I need to the database.xsd data Adapters to refer to the web.config files connection string info... I am at a loss when it comes to directing the adapters to the correcct connection string info...

  • Warren,

    To see the connection string being used for a table adapter, click on the table adapter in the designer and look at the "Connection" property.

  • Thanks for your help on this.
    I have converted your code above to VB.NET.  Does this look correct to you...
    If e.ObjectInstance IsNot Nothing Then
               Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
               conn.ConnectionString = ConfigurationManager.ConnectionStrings("WebConfigConnString").ConnectionString
               e.ObjectInstance.GetType().GetProperty("Connection").SetValue(e.ObjectInstance, conn, Nothing)
           End If

  • THANKS FOR YOUR HELP, NOW I CAN CHANGE TABLEADAPTER CONNECTION

  • Thank you for your posting. I tried to use your code, but I got an error at  e.ObjectInstance.GetType().GetProperty("Connection").SetValue(e.ObjectInstance, conn, null);
    says: Cannot convert from string to System.Reflection.BindingFlages. If you can tell me why, I would be greatly appreciate it.

  • I tried to post a message earlier, somehow it did not go throught. VS2005 does not recongnize "Connection"..
    The error says"Can not convert string to System.Reflection.BindingFlags."
    Please help if you know why.
    Thanks very much in advance.

  • Mark,

    Did you do exactly as stated above? Check your code again.

  • I searched high and low last week for such a post. Submitted my own question this week, and while waiting for a response search again and found this one. It does exactly what I was looking for. Thanks heaps.

  • I used this VB code and get the error "NullReferenceException was unhandled by user code". It points to the last occurrence of e.ObjectInstance. Any ideas?

  • I also get the "NullReferenceException was unhandled by user code" error using the C# code.

    It seems to be having problems with GetProperty("Connection") part.

  • Awesome!  Saved me a huge headache!

  • hi:
    if my database is microsoft access,
    it got an error:
    Keyword not supported: 'provider'.
    how can i do, thx :)

  • Wouldn't this have to be done for every table adaptor in the target database?

  • Thanks! This makes the ObjectDataSource even more useful for me now.

  • I also have the issue of NullReferenceException was unhandled by user code" error using the C# code.

    If I look at e.ObjectInstance.GetType().GetProperties(), i get the following results:

    {System.Reflection.PropertyInfo[3]}
    [0]: {Boolean ClearBeforeFill}
    [1]: {System.ComponentModel.ISite Site}
    [2]: {System.ComponentModel.IContainer Container}

    As you can see, there is no connection property. Does the above method work on a web project, or only a desktop app?

  • It Works!!! Thanks a Lot... U hv it... Gr8

  • I tried to provide the ObjectCreated event to the ObjectDataSource, but I got this error saying that this 'System.Web.UD.WebControls.ObjectDataSource' does not have a public property named 'ObjectCreated'.

    Here is how I coded the tag for it:



    Please advise, what is wrong here. Thank you.

  • hi, the problem i'm having is similar: i want to simply "Add Connection" but use



    instead of a real database. Then, later depending on environment, i can change settings in "connections.config" file to go to different databases. This should be this easy- why I cannot do it this way?

  • Excellent piece.Answers issues with clear simple
    advice.

  • my code is
    protected void ObjectDataSource1_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
    {
    if (e.ObjectInstance != null)
    {
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["TestLib.Properties.Settings.MyConn"].ToString();
    e.ObjectInstance.GetType().GetProperty("Connection").SetValue(e.ObjectInstance, conn, null);
    }

    }

    but it get the error message.
    How can I do?

  • Excellent. Just what I needed. I am developing an app with dynamic connection strings (connecting to one of hundreds of databases pending on who runs it)

  • You are the best man!

    For several months I have been searching for this issue, and your solution works great!

    Thanks again

  • thank u very very much

  • "NullReferenceException was unhandled by user code"

    Solution: Change the connection modifier of the table adapter to "Public"

Comments have been disabled for this content.