如何设置表适配器的连接字符串（动态设置的连接字符串）

Question

我刚刚从另一位不再在公司工作的开发人员那里继承了“数据库专家”的角色，所以如果我听起来特别菜鸟，请原谅我。

该应用程序是一个 VB.NET 4 应用程序。

我们的表适配器的数据库连接字符串是 my.settings (My.Settings.DBConnectionString) 中的一个字符串，该字符串是在运行时设置的。当我必须修改表适配器时，它们在 My.Settings.DBConnectionString 中看不到任何数据，因此不允许我编辑它们，直到我设置“真实”（或硬编码）连接字符串。现在我想将其改回动态设置的，但 Visual Studio 似乎不想让我这样做。我相信我已经在 .xsd 文件后面的自动生成代码中找到了能够更改特定表适配器的连接字符串的位置，但如果我这样做会发生不好的事情吗？或者除了我不知道的 Visual Studio 一侧的属性窗格之外，是否还有其他一些机制可以更改表适配器的连接字符串？

作为次要问题，这里是否存在不良/非最佳实践？

谢谢！

Answer 1

这听起来像是您的表适配器的连接字符串是由

我不太清楚你动态设置它们的意思，但听起来你可能遇到过经常有争议的（我知道，需要引用......）功能，这意味着你无法设置应用程序运行时的范围设置。这是因为自动生成的类为您的应用程序设置提供只读属性。可以更改用户范围设置（有关详细信息，请参阅前面的链接）。

有一个帖子讨论更改可能有用的应用程序设置。

如果您确实需要在运行时更改应用程序设置，那么可能值得实现您自己的设置机制，该机制使用 XML、注册表或其他内容来存储、检索和更改设置。

希望有帮助

编辑：

我突然想到我没有正确阅读你的问题。不仅您的连接字符串可能源自应用程序设置，而且您的表适配器也可能绑定到该连接字符串？如果是这样，请参阅这篇文章，其中解释了如何您可以注入/更改表适配器的连接字符串。对我来说似乎有点黑客，但它应该有效。

Answer 2

以下是我如何使用 VB 中的模块从 INI 文件更新连接字符串，该模块显示了更新连接字符串的稍微不同的方法。

它允许客户端从应用程序中的表单更新连接。表单上的文本框与模块中的公共字符串相关联。按表单上的“保存”按钮会调用模块中的savedbsetup 方法。然后，应用程序启动时，它会从创建的 INI 文件加载新设置

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

Answer 3

这是我解决运行时更改连接字符串问题的方法。希望这有帮助。

在我的设置中，我有 2 个条目

我有一个名为 DataSet1 的数据集

我有 3 个表单，分别称为 Form1、Form2 和 Form3

Form1 具有以下控件

以及以下代码

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 具有以下控件

具有以下代码

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

Form3 有以下控件

具有以下代码

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