在VB.NET中使用INI文件连接多个SQL数据库

Great question! To expand your current code to support both ABC and XYZ databases, we'll adjust your INI file to hold separate connection settings for each, then update the VB.NET code to read these sections and manage individual connections. Here's a step-by-step solution:

Step 1: Update the INI File Structure #

First, modify your Conn.ini file to include distinct sections for each database. Each section will store the server name, database name, username, and password specific to that connection:

[ABC]
sname=YourSQLServerName
Dbase=ABC
Una=YourUsername
Pwd=YourPassword

[XYZ]
sname=YourSQLServerName
Dbase=XYZ
Una=YourUsername
Pwd=YourPassword

Step 2: Add Helper Classes for INI Reading and Connection Settings #

We'll add two helper components to make managing multiple connections clean and scalable:

1. A class to reliably read sections and key-values from the INI file (using Windows API calls).
2. A class to store connection settings and generate valid SQL connection strings.

Imports System.Runtime.InteropServices

' Helper class to read INI file content
Public Class IniFileReader
    <DllImport("kernel32.dll", CharSet:=CharSet.Auto)>
    Private Shared Function GetPrivateProfileSectionNames(lpszReturnBuffer As IntPtr, nSize As Integer, lpFileName As String) As Integer
    End Function

    <DllImport("kernel32.dll", CharSet:=CharSet.Auto)>
    Private Shared Function GetPrivateProfileString(lpAppName As String, lpKeyName As String, lpDefault As String, lpReturnedString As StringBuilder, nSize As Integer, lpFileName As String) As Integer
    End Function

    ' Get all section names from the INI file
    Public Shared Function GetAllSections(iniPath As String) As List(Of String)
        Dim bufferSize As Integer = 1024
        Dim buffer As IntPtr = Marshal.AllocHGlobal(bufferSize)
        Dim bytesReturned As Integer = GetPrivateProfileSectionNames(buffer, bufferSize, iniPath)

        If bytesReturned = 0 Then
            Marshal.FreeHGlobal(buffer)
            Return New List(Of String)()
        End If

        Dim sections As String = Marshal.PtrToStringAuto(buffer)
        Marshal.FreeHGlobal(buffer)

        Return sections.Split({vbNullChar}, StringSplitOptions.RemoveEmptyEntries).ToList()
    End Function

    ' Get a specific key value from a section
    Public Shared Function GetKeyValue(sectionName As String, keyName As String, iniPath As String) As String
        Dim sb As New StringBuilder(255)
        GetPrivateProfileString(sectionName, keyName, "", sb, sb.Capacity, iniPath)
        Return sb.ToString()
    End Function
End Class

' Class to store database connection settings and generate connection strings
Public Class DbConnectionSettings
    Public Property ServerName As String
    Public Property DatabaseName As String
    Public Property Username As String
    Public Property Password As String

    ' Generate a valid SQL Server connection string
    Public Function GetConnectionString() As String
        ' Adjust this if you use integrated security (replace with Integrated Security=True)
        Return $"Data Source={ServerName};Initial Catalog={DatabaseName};User ID={Username};Password={Password};Integrated Security=False;"
    End Function
End Class

Step 3: Modify the Main Form Code to Manage Multiple Connections #

Update your Form1 code to load all database connections from the INI file and use them as needed. We'll store connections in a dictionary for easy access by database name:

Imports System.Windows.Forms
Imports System.IO
Imports System.Data.SqlClient
Imports System.Collections.Generic

Public Class Form1
    Private ReadOnly _iniFilePath As String = Path.Combine(Application.StartupPath, "Conn.ini")
    ' Dictionary to store connection settings keyed by database name (ABC, XYZ)
    Private _dbConnections As Dictionary(Of String, DbConnectionSettings) = New Dictionary(Of String, DbConnectionSettings)()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Load all database connections when the form starts
        LoadDbConnections()
    End Sub

    Private Sub LoadDbConnections()
        Dim sections As List(Of String) = IniFileReader.GetAllSections(_iniFilePath)

        For Each section In sections
            ' Create settings object for each database section
            Dim settings As New DbConnectionSettings() With {
                .ServerName = IniFileReader.GetKeyValue(section, "sname", _iniFilePath),
                .DatabaseName = IniFileReader.GetKeyValue(section, "Dbase", _iniFilePath),
                .Username = IniFileReader.GetKeyValue(section, "Una", _iniFilePath),
                .Password = IniFileReader.GetKeyValue(section, "Pwd", _iniFilePath)
            }

            ' Add to our dictionary for easy access
            _dbConnections.Add(section, settings)
        Next
    End Sub

    ' Example: Connect to ABC database (attach this to a button click event)
    Private Sub btnConnectABC_Click(sender As Object, e As EventArgs) Handles btnConnectABC.Click
        If _dbConnections.ContainsKey("ABC") Then
            Dim connString As String = _dbConnections("ABC").GetConnectionString()
            Using conn As New SqlConnection(connString)
                Try
                    conn.Open()
                    MessageBox.Show("Successfully connected to ABC database!")
                    ' Add your database operations here (e.g., read/write data)
                Catch ex As Exception
                    MessageBox.Show($"Error connecting to ABC: {ex.Message}")
                Finally
                    ' Ensure connection is closed even if an error occurs
                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If
                End Try
            End Using
        Else
            MessageBox.Show("ABC database settings not found in Conn.ini")
        End If
    End Sub

    ' Example: Connect to XYZ database (attach this to a button click event)
    Private Sub btnConnectXYZ_Click(sender As Object, e As EventArgs) Handles btnConnectXYZ.Click
        If _dbConnections.ContainsKey("XYZ") Then
            Dim connString As String = _dbConnections("XYZ").GetConnectionString()
            Using conn As New SqlConnection(connString)
                Try
                    conn.Open()
                    MessageBox.Show("Successfully connected to XYZ database!")
                    ' Add your database operations here
                Catch ex As Exception
                    MessageBox.Show($"Error connecting to XYZ: {ex.Message}")
                Finally
                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If
                End Try
            End Using
        Else
            MessageBox.Show("XYZ database settings not found in Conn.ini")
        End If
    End Sub
End Class

Key Benefits #

• Scalability: Add more databases later by just adding a new section to the INI file—no code changes required.
• Resource Safety: Uses Using blocks to ensure SQL connections are properly disposed of after use.
• Error Handling: Includes basic error catching to help diagnose connection issues.

内容的提问来源于stack exchange，提问作者Lalit