Context: The following code is used to look up a setting values based on multiple layers of configuration within the application. For any given setting (ie: default printer), the application can be configured at the following levels in order of precedence.
- Operation
- Material
- Workstation & Program Combination
- Workstation
- Program
- User
- Plant
When the application requires a setting, it performs a lookup by providing as much information as it has available in context then searches each level and uses the first value it finds. If no value is found, the caller will handle the default action.
Logic Layer
This is the function that will be called by any part of the application that needs to look up a setting value. The caller must specify the setting ID and one or more of the other parameters. The purpose here is to allow the caller to look up just "Workstation" level settings for example.
Public Function SettingLookup(operationID As String,
materialID As String,
userID As String,
plantID As String,
workstationID As String,
programID As String,
settingID As String
) As String()
If settingID = "" Then
Throw New Exception("Setting ID Required")
End If
Dim settingValue = SettingLookupHelper(operationID, materialID, userID, plantID, workstationID, programID, settingID)
If settingValue IsNot Nothing Then
Return JsonConvert.DeserializeObject(Of String())(settingValue)
Else
Return Nothing
End If
End Function
This function is used to access the data access layer and get the settings from the database.
Private Function SettingLookupHelper(operationID As String,
materialID As String,
userID As String,
plantID As String,
workstationID As String,
programID As String,
settingID As String
) As String
Dim settingValue As Setting
If operationID <> "" Then
settingValue = GetOperationSetting(operationID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If materialID <> "" Then
settingValue = GetMaterialSetting(materialID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If workstationID <> "" And programID <> "" Then
settingValue = GetWorkstationProgramSetting(workstationID, programID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If workstationID <> "" Then
settingValue = GetWorkstationSetting(workstationID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If programID <> "" Then
settingValue = GetProgramSetting(programID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If userID <> "" Then
settingValue = GetUserSetting(userID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
If plantID <> "" Then
settingValue = GetPlantSetting(plantID, settingID)
If settingValue IsNot Nothing Then
Return settingValue.Value
End If
End If
Return Nothing
End Function
Data Access Layer
This is an example of the code I have in the data access layer for each of the configuration levels listed above. Each of these files contains code to access a Settings table where the settings are defined and a many-to-many join table (ie: Material_Settings or Operation_Settings) that is used to set the setting values and link it to the entity like a material, operation, or user. There is a lot of nearly duplicated code in my data access layer to support this design.
Public Class MaterialSetting
Inherits Setting
Private _MaterialID As String
Public Property Material_ID() As String
Get
Return _MaterialID
End Get
Set(ByVal value As String)
_MaterialID = value
End Set
End Property
Private _Material As String
Public Property Material() As String
Get
Return _Material
End Get
Set(ByVal value As String)
_Material = value
End Set
End Property
End Class
Public Module MaterialSettingsUtilities
Public Function GetMaterialSetting(materialID As String, settingID As String) As MaterialSetting
Dim parameters As New Dictionary(Of String, Object)
parameters.Add("Material_ID", materialID)
parameters.Add("Setting_ID", settingID)
Dim queryCondition As String
queryCondition = " WHERE Material_ID = HEXTORAW(:Material_ID) AND Setting_ID = HEXTORAW(:Setting_ID) "
Dim settings As Settings = GetMaterialSettingsFiltered(parameters, queryCondition)
If settings.Count > 0 Then
Return CType(settings(0), MaterialSetting)
Else
Return Nothing
End If
End Function
Private Function GetMaterialSettingsFiltered(parameters As Dictionary(Of String, Object), queryCondition As String) As Settings
Dim query As String
query = " SELECT * "
query += " FROM (Materials NATURAL JOIN Material_Settings) "
query += " NATURAL JOIN Settings "
query += queryCondition
Using conn As New OracleConnection(GetConnectionString("WeighScaleDB"))
Using cmd = CreateOracleCommand(query, parameters, conn)
conn.Open()
Using dr = cmd.ExecuteReader
Dim settings As New Settings
If Not dr.Read() Then
Return settings
End If
Do
settings.Add(New MaterialSetting With {
.Setting_ID = ConvertByteArrayToString(TryCast(dr("Setting_ID"), Byte())),
.Material_ID = ConvertByteArrayToString(TryCast(dr("Material_ID"), Byte())),
.Material = dr("Material").ToString(),
.Value = dr("Setting_Value").ToString(),
.Name = dr("Setting_Name").ToString(),
.Description = dr("Setting_Description").ToString(),
.DataType = dr("Setting_Data_Type").ToString(),
.Category = dr("Setting_Category").ToString()
})
Loop While dr.Read()
Return settings
End Using
End Using
End Using
End Function
End Module
This is the Settings object from which each of the subtypes (ie: MaterialSetting) are derived.
Public Class Setting
Private _SettingID As String
Public Property Setting_ID() As String
Get
Return _SettingID
End Get
Set(ByVal value As String)
_SettingID = value
End Set
End Property
Private _SettingName As String
Public Property Name() As String
Get
Return _SettingName
End Get
Set(ByVal value As String)
_SettingName = value
End Set
End Property
Private _SettingDescription As String
Public Property Description() As String
Get
Return _SettingDescription
End Get
Set(ByVal value As String)
_SettingDescription = value
End Set
End Property
Private _SettingDataType As String
Public Property DataType() As String
Get
Return _SettingDataType
End Get
Set(ByVal value As String)
_SettingDataType = value
End Set
End Property
Private _SettingCategory As String
Public Property Category() As String
Get
Return _SettingCategory
End Get
Set(ByVal value As String)
_SettingCategory = value
End Set
End Property
Private _SettingValue As String
Public Property Value() As String
Get
Return _SettingValue
End Get
Set(ByVal value As String)
_SettingValue = value
End Set
End Property
End Class
Any constructive thoughts on would be appreciated. I can post examples of my table structures or any of the other helper or DAL functions shown in this code.
