Using identification fields in a switch statement
I have a SQL lookup table as follows:
CREATE TABLE Product(Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))
I am binding ASP.NET DropDownList to LLBLGen object. The user selects a product and the ID is saved. Now I will need to display some specific product details later. Should I use a product ID and hope the ID is always the same between installations?
switch (selectedProduct.Id)
{
case 1: //product one
break;
case 2:
case 3: //product two or three
break;
}
or use a name and hope it never changes?
switch (selectedProduct.Name)
{
case "product one":
break;
}
Or is there a better alternative?
source to share
In this situation, there are three general solutions I've seen:
- Hardcode ID is quick and messy, not self-documenting (you don't know what the item refers to) and prone to breakage as you pointed out. I never use this method again.
- Enums . I use this when the table is small and static. Thus, ProductType would be a possible candidate for this. This is self-documenting code, but it still creates an awkward relationship between code and data, where if records are inserted with different IDs than you intended, then things break down. You can mitigate this by automating Enum generation in a variety of ways, but still doesn't feel right. For example, if your unit tests insert records into the Product table, it will be difficult for them to recreate the Enum at this point. Also, if you have 100,000 records, the Enum approach starts to look pretty silly.
- Add an additional column that is an immutable identifier. I often use AlphaCode as my column name. So in your case it will look like this:
switch (selectedProduct.AlphaCode)
{
case "PRODUCT_ONE":
break;
}
This allows you to use AlphaCode, which is self-documenting, allows you to re-insert data without caring about the auto-increment PK value, and allows you to change the product name without affecting anything. If you are using the AlphaCode approach, make sure you put a unique index on this column.
Another solution, which is often the preferred solution, is to move this logic to the database. For example, if product 1 is a product that you always want to show by default when you select its category, you can add a column to your table called IsHeroProduct. Then your request will look like this:
if (selectedProduct.IsHeroProduct)
{
//do stuff
}
source to share
If you want your ProductID to be fixed (which doesn't seem like a good idea), you can use IDENTITY INSERT
(in SQL Server, at least) to ensure that the ProductID values are the same across installations. But generally, I only do this for static reference data.
You can also use Visual Studio T4 templates to generate enums
directly from database data
source to share
Some ORMs (at least LLBLGen) can handle this for you; but generating strong enum type. I have never used this.
In these cases, I always go with an enum that I write myself, but I am sure all fields are equal and update if any change. It gets more interesting when you work with databases (as I do), but if you take care, it is simple enough.
source to share