none
TableAdapterManager.Connection.ConnectionString RRS feed

  • Question

  • Hi!

    I'm currently trying to use a typed TableAdapterManager, but I need to change the connection string that is originally supplied (the one I set in the designer) at runtime. I tried setting the TableAdapterManager.Connection.ConnectionString property, thinking that it would propagate that value to each TableAdapter. This did not happen, only 1 of the 3 TA's connectionstrings was changed, the other 2 still had the value I supplied in the designer.

    What am I doing wrong? Should I manually set each connection string for each TA? If so, what is the purpose of TableAdapterManager.Connection?

    Thank you!
    Tuesday, October 13, 2009 8:00 PM

All replies

  • The Best way to change your connection is first to create your connection in the Settings. Go To My Project >>> Settings >>> Rename your Settings To myConnection for the Type pick (Connection String) Scope is Application Value= pick your connection from the elipsis button Wizard 
     
    Then Go to your DataSet and change your connection from the Property Window by changing the Connection from the Drop down List, the connection string automatically change.


    John
    Tuesday, October 13, 2009 9:36 PM
  • Thank you John, but like I stated in my original post, the connection string must be built during run-time (I use user instance and connect to a .mdf file specified by the user). Your approach does not help me.
    Wednesday, October 14, 2009 12:58 PM
  • Here is the code for supplying connection at run-time :
    TextBox1 is the string of connection, you can create a combobox with a list of connection for the user to select from.
    For Simplicity I used TextBox

    #Region "Visual Studio Namespace"
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    #End Region
    
    
        Private Sub FillDataSet()
            Dim ds As New DataSet
    
            Using Conn As New SqlConnection(TextBox1.Text.ToString)
                Using Adapter As New SqlDataAdapter("Select * From dbo.Customers", Conn)
                    Adapter.SelectCommand.CommandType = CommandType.Text
                    Adapter.Fill(ds, "Customers")
                    Me.DataGridView1.DataSource = ds.Tables("Customers")
                End Using
            End Using
        End Sub

    John
    Wednesday, October 14, 2009 2:14 PM
  • Once again, thank you for your time John, but you seem to misunderstand my question. I understand how to change a connection string on an individual TableAdapter. My original question concerns TableAdapterManagers, like the title clearly states. Changing the connection of a TAM does not seem to change the connection of each TA individually, and I cannot access the connection property of a TA if it is a member of a TAM.
    Wednesday, October 14, 2009 2:20 PM
  • Sorry for misunderstanding.

    I don't think you can change connection from the TableAdapterManager cause it's responsible for Hierarchichal Update and it takes its connection from TableAdapter.
    TableAdapterManager is not a framework class. It's a custom class generated for each strongly-typed DataSet.


    John
    Wednesday, October 14, 2009 2:39 PM
  • Yes the custom class is generated by the DataSet designer. The class exposes a Connection property, and I can change it. My problem is that it does not propagate the change to all underlying TA's. Only a few.

    Anyone has an idea? There seems to be little documentation about TAM's.

    Wednesday, October 14, 2009 2:46 PM
  • Hi Billy,


    This is the Connection property’s code of dataadapter I get from reflector.

     

    internal SqlConnection Connection

    {

        get

        {

            if (this._connection == null)

            {

                this.InitConnection();

            }

            return this._connection;

        }

        set

        {

            this._connection = value;

            if (this.Adapter.InsertCommand != null)

            {

                this.Adapter.InsertCommand.Connection = value;

            }

            if (this.Adapter.DeleteCommand != null)

            {

                this.Adapter.DeleteCommand.Connection = value;

            }

            if (this.Adapter.UpdateCommand != null)

            {

                this.Adapter.UpdateCommand.Connection = value;

            }

            for (int i = 0; i < this.CommandCollection.Length; i++)

            {

                if (this.CommandCollection[i] != null)

                {

                    this.CommandCollection[i].Connection = value;

                }

            }

        }

    }

     

    private void InitConnection()

    {

        this._connection = new SqlConnection();

        this._connection.ConnectionString = Settings.Default.SQLServer2005DBConnectionString;

    }

     

    The high lighting part is the real part that the connection string is set.

    Maybe you can wirte a partial class for your adapter for the connection string.


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 15, 2009 7:48 AM
  • Thank you Yichun_Feng. This does seem to be the problem: The TableAdapter's Connection is never set to the proper connection. Assigning the TAM's Connection property should have propagated the setting to each TA, but it is never done, and InitConnection checks for the app.config's connection. This makes it impossible to change connectionstrings at runtime. I've filed a bug on Connect, and will get back to this thread with the result of my inquiry.
    Monday, November 16, 2009 4:13 PM
  • Billy, We will have to set the connection for each TA manually until Microsoft fixes this bug. The bug you submitted on Connect - I later noticed Microsoft had marked it "closed". So they will not fix it.

    Then I decided to submit this bug again on Connect with code example, but no response yet.

    http://rajmsdn.wordpress.com/2009/12/09/strongly-typed-dataset-connection-string/

    Thursday, January 14, 2010 10:18 PM
  • Hi Raj,

    MS has provided a workaround that is applicable so long as you have access to the source code of the DLL that implements the TAM. This is my case, so I have not pursued the issue further. This is a show stopping bug for someone who does not have access to the source code of said DLL, as the "Connection" property of each TA is marked as "Friend" by the designer when they are created.

    The bug report can be found here. You are welcome to vote it up if this issue affects you. Good luck!

    Friday, January 15, 2010 1:41 PM
  • Hi Billy,

    I did vote it up. But MS has closed your bug report. The one I reported is still open , so please vote up and also remember to click on the "I can too" link on the right side.

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=519333

    Tuesday, January 19, 2010 4:54 PM