How to set a table adapter's connection string (to one that is dynamically set)

how to create tableadapter in c#
table adapter manager
tableadapter query with parameter
tableadapter update
tableadapter.fill where clause
tableadapter insert
tableadaptermanager
c# create tableadapter programmatically

I just inherited the role of "Database Guy" from another developer who is no longer with the company, so please forgive me if I sound particularly noobish.

The application is a VB.NET 4 application.

The DB connection string for our table adapters was a string in my.settings (My.Settings.DBConnectionString) that is being set at runtime. When I had to modify the table adapters they couldn't see any data in My.Settings.DBConnectionString and thus did not allow me to edit them until I set a "real" (or hard-coded) connection string. Now I want to change it back to the dynamically set one, but Visual Studio doesn't seem to want to let me do that. I believe I've found the spot in the auto-generated code behind the .xsd file to be able to change the connection string for a particular table adapter, but if I do that will bad things happen? Or is there some other mechanism for changing a table adapter's connection string other than the properties pane on the side of Visual Studio that I am not aware of?

Just as a secondary question, are there bad / not-best practices going on here?

Thanks!

Here is how I solved the problem of changing the connection string at runtime. Hope this helps.

In my settings, I have 2 entries

I have a Dataset called DataSet1

I have 3 forms called Form1, Form2 and Form3

Form1 has the following controls

And the following code

Public Class Form1

    Private Sub GenericoBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles GenericoBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.GenericoBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)

    End Sub



    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.TableAdapterManager.Connection.ConnectionString = My.MySettings.Default._001NewConnectionString

        Me.TableAdapterManager.UpdateAll(Me.DataSet1)

        Label5.Text = My.MySettings.Default._001NewConnectionString

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        My.MySettings.Default("_001NewConnectionString") = "Data Source=" & TextBox1.Text & "\" &
            TextBox2.Text & ";Initial Catalog=001;Persist Security Info=True;User ID=" &
            TextBox3.Text & ";Password=" & TextBox4.Text
        My.MySettings.Default.Save()

        Me.TableAdapterManager.Connection.ConnectionString = My.MySettings.Default._001NewConnectionString
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)

        Try
            Me.GenericoTableAdapter.Fill(Me.DataSet1.generico)
        Catch ex As Exception
            MessageBox.Show("error Form1")
        End Try
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim f As New Form2(TableAdapterManager.Connection.ConnectionString)
        f.ShowDialog()

    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim f As New Form3(TableAdapterManager.Connection.ConnectionString)
        f.ShowDialog()
    End Sub
End Class

Form2 has the following controls

with the following code

Public Class Form2

    Private Sub UtentesBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles UtentesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.UtentesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)

    End Sub
    Public Sub New(ConnectionString As String)
        InitializeComponent()
        Me.TableAdapterManager.Connection.ConnectionString = ConnectionString
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)
    End Sub




    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try
            Me.UtentesTableAdapter.Fill(Me.DataSet1.utentes)
        Catch ex As Exception
            MessageBox.Show("Form2")
        End Try



    End Sub
End Class

and Form3 has the following controls

with the following code

Public Class Form3
    Public Sub New(ConnectionString As String)
        InitializeComponent()
        Me.TableAdapterManager.Connection.ConnectionString = ConnectionString
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)
    End Sub
    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            Me.UtentesTableAdapter.Fill(Me.DataSet1.utentes)
        Catch ex As Exception
            MessageBox.Show("Form3")
        End Try


    End Sub

    Private Sub UtentesBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles UtentesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.UtentesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DataSet1)

    End Sub
End Class

Fill datasets by using TableAdapters, Update , and TableAdapter.Delete ) to manipulate data directly in the database. If you don't want to create these direct methods, set the� By default, every time you run a query to fill a TableAdapter's data table, the existing data is cleared, and only the results of the query are loaded into the table. Set the TableAdapter's ClearBeforeFill property to false if you want to add or merge the data that's returned from a query to the existing data in a data table. Regardless of whether you clear the data, you need to explicitly send updates back to the database, if you want to persist them.

This sounds like your table adapter's connection string is being set by an application setting?

I'm not sure quite what you mean by setting them dynamically, but it sounds like you may have come across the often disputed (I know, citation needed...) feature that means you cannot set application scope settings at runtime. This is because the auto-generated classes provide readonly properties for your app settings. User scope settings can be changed (see previous link for details).

There is a post here discussing changing application settings which may be of use.

If you do need to change application settings at runtime, it may be worth implementing your own settings mechanism which uses XML, the Registry, or something else to store, retrieve, and change settings.

Hope that helps

EDIT:

Suddenly occurred to me that I hadn't read your question properly. Not only is your connection string probably derived from an application setting, but perhaps your table adapter is bound to that connection string? If so, see this post which explains how you can inject/change the connection string for table adapters. Seems like a bit of a hack to me, but it should work.

Directly access the database with a TableAdapter, Enclose the TableAdapter's Update method in a try / catch block. The following example shows how to update the contents of the Customers table� If you don't want to create these direct methods, set the TableAdapter's GenerateDbDirectMethods property to false in the Properties window. If any queries are added to a TableAdapter in addition to the TableAdapter's main query, they are standalone queries that don't generate these DbDirect methods. Send commands directly to a database

Here is how I updated a Connectionstring from a INI file with a Module in VB that shows a slightly different way to update the connectionstring.

It allows the client to update the connection from a form in the application. The textboxes on the form are tied to the Public strings in the module. Pressing the save button on the form calls the savedbsetup method in the module. Then the time the app is launched, it loads the new settings from the created INI file

Imports System.Configuration
Imports System.Environment
Imports System.IO
Module SetupOptions
    Public DBJacksServer As String = "CSTDesktop1\SQLExpress"
    Public DBJacksCatalog As String = "PreJacks"
    Public DBJacksUserName As String = "sa"
    Public DBJacksPassword As String = "dba"
    Public appData As String = $"{GetFolderPath(SpecialFolder.ApplicationData)}/CSTSoftware/Jacks"
    Public DBJacksSetUpFile As String = $"{appData}/DBJackssetup.ini"
    Public DBConnectionStringSettingsName As String = "Jacks.My.MySettings.PreJacksConnectionString"
    Public Sub SaveDBSetup()
        Try
            If Not Directory.Exists(appData) Then
                Directory.CreateDirectory(appData)
            End If
            If File.Exists(DBJacksSetUpFile) Then
                File.Delete(DBJacksSetUpFile)
            End If
            Using sw As StreamWriter = New StreamWriter(DBJacksSetUpFile)
                sw.WriteLine(DBJacksServer)
                sw.WriteLine(DBJacksCatalog)
                sw.WriteLine(DBJacksUserName)
                sw.WriteLine(DBJacksPassword)
            End Using
        Catch ex As Exception
            MessageBox.Show($"Failed to Save DB Settings With this Error: {ex.Message}")
        End Try

    End Sub
Public Sub LoadDBSetup()
    Try
        If File.Exists(DBJacksSetUpFile) Then
            Using sr As StreamReader = New StreamReader(DBJacksSetUpFile)
                DBJacksServer = sr.ReadLine()
                DBJacksCatalog = sr.ReadLine()
                DBJacksUserName = sr.ReadLine()
                DBJacksPassword = sr.ReadLine()
            End Using
            SetDBJacksConnection()
        End If
    Catch ex As Exception
        MessageBox.Show($"Failed to Load DB Settings With this Error: {ex.Message}")
    End Try

End Sub
Public Function GetDBJacksConnection() As String
    'If File.Exists(DBJacksSetUpFile) Then
    '    Return $"Data Source={DBJacksServer};Initial Catalog={DBJacksCatalog};Persist Security Info=True;User ID={DBJacksUserName};Password={DBJacksPassword}"
    'Else
    Return ConfigurationManager.ConnectionStrings(DBConnectionStringSettingsName).ConnectionString
    'End If
End Function
Public Sub SetDBJacksConnection()
    Dim config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
    Dim csSection = config.ConnectionStrings
    csSection.ConnectionStrings(DBConnectionStringSettingsName).ConnectionString = $"Data Source={DBJacksServer};Initial Catalog={DBJacksCatalog};Persist Security Info=True;User ID={DBJacksUserName};Password={DBJacksPassword}"
    config.Save(ConfigurationSaveMode.Modified)
    ConfigurationManager.RefreshSection("connectionStrings")
End Sub
End Module

Update data by using a TableAdapter, Visit my page: https://www.facebook.com/CodeAMinute Related videos Playlist: C# and Access Duration: 7:27 Posted: Aug 20, 2018 When creating a table adapter, if its corresponding table has records, it gets equipped with them and you can use them as you see fit. To pass its records to a table, the generated table adapter is equipped with a method named Fill. This method takes as argument the table class that was generated, it fills it with the records, and returns it.

C# (Part 4) - How to Create TableAdapter Queries, USF MIS Student Success Center. Loading Unsubscribe from USF MIS Student Success Duration: 8:36 Posted: Jan 15, 2015 The DB connection string for our table adapters was a string in my.settings (My.Settings.DBConnectionString) that is being set at runtime. When I had to modify the table adapters they couldn't see any data in My.Settings.DBConnectionString and thus did not allow me to edit them until I set a "real" (or hard-coded) connection string.

Using Table Adapter Queries, This video demonstrates the use of the TableAdapter with a SQL Server dataset.Duration: 7:16 Posted: Jan 26, 2016 Dim queryString As String = _ "SELECT CustomerID, CompanyName FROM dbo.Customers" Dim adapter As SqlDataAdapter = New SqlDataAdapter( _ queryString, connection) Dim customers As DataSet = New DataSet adapter.Fill(customers, "Customers")

VB Week 5: Visual Basic TableAdapter, When I had to modify the table adapters they couldn't see any data in My.Settings .DBConnectionString and thus did not allow me to edit them until I set a "real" (or � In my project I have a dataset which contains a few tables, I added some of the table adapters to a form window and then I noticed the TableAdapterManager component. It appears that I can fill a table adapter that is not on the form using the TableAdapterManager? For example, in the dataset there is a table called ""Descript0"" and I have not put