Changing DataDirectory does not load different data

Question

When the user clicks File | Open, the user gets a FolderBrowserDialog to select a folder containing the MyData.mdf data file. After clicking OK, the app calls

AppDomain.CurrentDomain.SetData("DataDirectory", FolderBrowserDialog.SelectedPath)
OrganizationTableAdapter.Fill(MyDataSet.Organization)

On first run of the app, it works as expected. The data displayed is the data from the MyData.mdf file in the FolderBrowserDialog.SelectedPath folder.

But when the user subsequently clicks File | Open and selects a different folder, while same code executes to change the DataDirectory and Fill the OrganizationTableAdapter, the data displayed remains the same data as the first folder that was opened. The only way to open a different dataset is to exit the app and launch the app again.

OrganizationTableAdapter.ClearBeforeFill is set to True, so I expect the old data to be cleared out and the data from the new directory to be loaded in. That's not what is happening.

I don't understand what is happening or how to fix it.

Answer 1

Hello @James Buss ,

Thanks for your question.

I recommend investigating the connection string resolution and connection pooling behavior. The DataDirectory token in the connection string appears to be resolved during the initial connection establishment. ADO.NET's connection pooling likely caches this connection with the originally resolved path. When you update the DataDirectory value, I suspect the pooled connection continues referencing the initial database file instead of re-evaluating the token with the new path.

You can refer to my following example code:

Imports System.Data.SqlClient

Private Sub LoadDatabaseFromFolder()
    If FolderBrowserDialog1.ShowDialog() <> DialogResult.OK Then Return

    If OrganizationTableAdapter.Connection IsNot Nothing Then
        OrganizationTableAdapter.Connection.Close()
    End If
    OrganizationTableAdapter.Dispose()

    SqlConnection.ClearAllPools()

    AppDomain.CurrentDomain.SetData("DataDirectory", FolderBrowserDialog1.SelectedPath)

    OrganizationTableAdapter = New OrganizationTableAdapter()

    MyDataSet.Organization.Clear()
    OrganizationTableAdapter.Fill(MyDataSet.Organization)
End Sub

I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback.

Answer 2

Hi @James Buss

try this below code will helpful to you.

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim ofd As New OpenFileDialog
    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim tableadapter As SqlDataAdapter

    Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
        ofd.Filter = "MDF Files (*.mdf)|*.mdf"
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim fileName As String = ofd.FileName
            Dim Connstr As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={" & fileName & "};Integrated Security=True;Connect Timeout=30;"
            Using conn = New SqlConnection(Connstr)
                conn.Open()
                Using tableadapter = New SqlDataAdapter("yourquery or storedprocedure", conn)

					// here do your stuff.. 

                End Using
                conn.Close()
            End Using
        End If
    End Sub

End Class

Answer 3

This makes sense. So, after calling SetData("DataDirectory", ...), how do I force the TableAdapter to use a new SqlConnection built from that connection string? What code do I need to do that? The TableAdapter is created in the Designer not through code.

Answer 4

AppDomain.CurrentDomain.SetData("DataDirectory", ...) only affects how connection strings that use |DataDirectory| are built when the connection is first created. Once the TableAdapter’s underlying SqlConnection has been created and opened, changing DataDirectory does not change the connection’s ConnectionString, nor does it force the adapter to rebuild or reopen it.

Result: after the first Fill, the TableAdapter keeps using the original connection (pointing to the first MyData.mdf), so subsequent Fill calls still read from the first database even though DataDirectory has been changed.

To load data from a different folder without restarting the app, the connection must be recreated (or its connection string updated) after changing DataDirectory. Typical patterns:

1. Ensure the connection string in the TableAdapter uses |DataDirectory|.
2. After calling SetData("DataDirectory", ...), force the TableAdapter to use a new SqlConnection built from that connection string, or dispose and recreate the DataSet/TableAdapter instance so that a new connection is created using the updated DataDirectory.
3. Then call OrganizationTableAdapter.Fill(MyDataSet.Organization).

ClearBeforeFill = True only clears the target DataTable before filling; it does not affect which database the adapter connects to.

---

References:

• Access local and remote data in ClickOnce applications
• How to: Change Displayed Data at Run Time in the Windows Forms DataGrid Control
• Set the control to be created when dragging from the Data Sources window