Global Class for SQL enumerated objects

Question

I'm working on a tool crib VB.NET project, my first real one, so I'm struggling with a few things. In my SQL database I have a few tables that translate into enumerated fields. For example I have code and code type tables. I have a code type with CodeTypeID = 2 and description = Tool Status. These are used to track the location of a tool; Shop, In Transit, On Site, Out of Service, etc. I've set up enumerations for some of these.

Enum CodeType
    User = 1
    ToolStatus = 2
    Repair = 3
    OutOfService = 4
End Enum
ToolStatus
    Shop = 5
    Reserved = 6
    InTransit = 7
    OnSite = 9
    ReturnTransit = 10
    OutOfService = 11
    Repair = 12
End Enum

The problem is if I change a code in SQL I then have to edit my enumerations as well. In the SQL tables I am only storing the CodeID and join to the code table to get the description. Other than an enumeration, I haven't found a good way to load these tables when the project opens that will allow me to code as easily, for example ToolStatus.OnSite to return the number 9 to my SQL table. It just seems inefficient to me. I always try to code my SQL procedures so if something changes I don't have to go and edit a bunch of procedures and would like to do the same here, especially as a simple addition of a new code could force a version update. Is there a better way to do this? I have searched the Internet and haven't found anything that really works as well as enumeration.

Example Code: In the following code both OnSite and ReturnTransit are enumerations. I'm moving items between two datagridviews, a Source and a Target DGV. Also updating the underlying shared datatable.

        Dim cbo As ComboBox = sender
        With cbo
            Select Case .Name
                Case "cboPeeps"
                    dvgFilter = "N"
                Case "cboToolbox"
                    For i As Integer = 0 To dtTools.Rows.Count - 1
                        dtTools.Rows(i)("dgvFilter") = dtTools.Rows(i)("dbFilter")
                    Next
                    If cbo.Items.Count > 0 Then
                        filter = "ToolboxID=" & .SelectedValue.ToString & " And ToolStatus=" & OnSite
                        filter += " And UserID=" & .SelectedItem("UserID").ToString
                    End If
                    dvgFilter = "T"
            End Select
        End With
    End If
    Dim result() As DataRow = dtTools.Select(filter)
    For i As Integer = 0 To result.Count - 1
        result(i)("dgvFilter") = dvgFilter
        If result(i)("dgvToolStatus") <> OutOfService Then result(i)("dgvToolStatus") = ReturnTransit
    Next

There are some ideas in [Best method to store Enum in Database](https://stackoverflow.com/q/2646498/1115360). — Andrew Morton, Feb 02 '22 at 14:58
You probably don't need `Enum`s at all, if you code it right. A table is a close enough analog. Why do you think you need enum? Post some code which shows a reliance on enum which can't be replaced with a SQL table. — djv, Feb 02 '22 at 16:03

score 0 · Answer 1 · answered Feb 10 '22 at 20:31

I was, of course, trying to make this way too difficult. Simple solution along these lines.

Public Class Crepair
Public repairID As Integer
Public techID As Integer
Public repairCodeID As Integer
Public acceptedDate As Date
Public remarks As String
Public isComplete As Boolean

End Class

        With nRepair
            .repairID = row.Cells("RepairID").Value
            .techID = row.Cells("TechID").Value
            .repairCodeID = row.Cells("RepairCodeID").Value
            .acceptedDate = row.Cells("AcceptedDate").Value
            .remarks = row.Cells("Remarks").Value
            .isComplete = row.Cells("IsComplete").Value
        End With

Global Class for SQL enumerated objects

1 Answers1