VB.NET实现仅单次读取数据库配置值的方案咨询

你当前的VB.NET方法每次调用都会建立数据库连接读取配置值，但该配置几乎不会变化，想要实现仅连接一次数据库获取值的需求，以下是几种可行的方案：

原方法代码 #

Public Function IsConfigurationEnabled() As Integer

    Dim IsEnabled As Integer
    Dim sqlText As String
    sqlText = "select value from [dbo].[Settings] where Configuration='XXX'"
    Dim connection As SqlConnection = New SqlConnection()
    Dim cmd As SqlCommand
    connection.ConnectionString = "Data Source=localhost;Initial Catalog=XXX;Integrated Security=True"
    Try
        connection.Open()
        cmd = New SqlCommand(sqlText, connection)
        IsEnabled = Convert.ToInt32(cmd.ExecuteScalar())
        cmd.Dispose()
        connection.Close()
    Catch ex As Exception
        AuditTrail(vbLogEventTypeError, "IsConfigurationEnabled :Error opening SQL Connection ")
    End Try
    Return IsEnabled
End Function

方案1：静态字段+懒加载 #

用Shared静态字段存储配置值，第一次调用方法时读取数据库，之后直接返回缓存值，同时处理读取失败的默认值：

' 静态字段标记初始状态为-1，表示未加载配置
Private Shared _cachedIsEnabled As Integer = -1

Public Function IsConfigurationEnabled() As Integer
    ' 仅当未加载过时才访问数据库
    If _cachedIsEnabled = -1 Then
        Dim sqlText As String = "select value from [dbo].[Settings] where Configuration='XXX'"
        ' 使用Using语句自动释放资源，比手动Dispose更安全
        Using connection As New SqlConnection("Data Source=localhost;Initial Catalog=XXX;Integrated Security=True")
            Using cmd As New SqlCommand(sqlText, connection)
                Try
                    connection.Open()
                    _cachedIsEnabled = Convert.ToInt32(cmd.ExecuteScalar())
                Catch ex As Exception
                    AuditTrail(vbLogEventTypeError, "IsConfigurationEnabled :Error opening SQL Connection ")
                    ' 读取失败时设置默认值，比如0代表禁用
                    _cachedIsEnabled = 0
                End Try
            End Using
        End Using
    End If
    Return _cachedIsEnabled
End Function

说明：静态字段在应用生命周期内仅初始化一次，后续调用直接返回缓存值，避免重复连接数据库。

方案2：程序启动时预加载 #

在应用启动阶段（比如控制台Main方法、WinForm的Form_Load事件）提前读取配置值，存入全局静态类，后续直接引用：

' 全局静态类存储应用配置
Public Module AppConfig
    Public IsConfigurationEnabled As Integer
End Module

' 启动时初始化配置（示例：控制台程序的Main方法）
Sub Main()
    LoadConfiguration()
    ' 其他启动逻辑
End Sub

Private Sub LoadConfiguration()
    Dim sqlText As String = "select value from [dbo].[Settings] where Configuration='XXX'"
    Using connection As New SqlConnection("Data Source=localhost;Initial Catalog=XXX;Integrated Security=True")
        Using cmd As New SqlCommand(sqlText, connection)
            Try
                connection.Open()
                AppConfig.IsConfigurationEnabled = Convert.ToInt32(cmd.ExecuteScalar())
            Catch ex As Exception
                AuditTrail(vbLogEventTypeError, "LoadConfiguration :Error opening SQL Connection ")
                AppConfig.IsConfigurationEnabled = 0
            End Try
        End Using
    End Using
End Sub

说明：后续业务代码直接通过AppConfig.IsConfigurationEnabled获取值，无需再触发数据库操作，适合启动阶段即可确定配置的场景。

方案3：Lazy(Of T)实现线程安全懒加载 #

如果应用是多线程环境，用Lazy(Of Integer)可以确保线程安全的懒加载，避免多个线程同时触发数据库读取：

' 线程安全的懒加载对象，仅在首次访问Value时执行初始化逻辑
Private Shared ReadOnly _lazyIsEnabled As New Lazy(Of Integer)(Function()
    Dim sqlText As String = "select value from [dbo].[Settings] where Configuration='XXX'"
    Using connection As New SqlConnection("Data Source=localhost;Initial Catalog=XXX;Integrated Security=True")
        Using cmd As New SqlCommand(sqlText, connection)
            Try
                connection.Open()
                Return Convert.ToInt32(cmd.ExecuteScalar())
            Catch ex As Exception
                AuditTrail(vbLogEventTypeError, "IsConfigurationEnabled :Error opening SQL Connection ")
                Return 0
            End Try
        End Using
    End Using
End Function)

Public Function IsConfigurationEnabled() As Integer
    ' 首次访问Value时才会执行数据库读取
    Return _lazyIsEnabled.Value
End Function

说明：Lazy(Of T)默认是线程安全的，多个线程同时访问只会执行一次初始化逻辑，适合多线程应用场景。

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