I have a VB.NET method, which connects to a database and reads the value. And this value is used multiple times in multiple places. The method looks like below:

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

I want to connect to database only once.
As the value in database never changes (or changes rarely).
Is there a way to achieve this?

CodePudding user response：

Storing the return value in the calling code is probably the best option. However, you can also use lazy initialization as follows.

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

It would be controversial to use the above code in a real project, but I hope it is instructive.