Using identity fields in a switch statement

Question

Hi

I have a SQL lookup table like this:

CREATE TABLE Product(Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))

I've databound a ASP.NET DropDownList to a LLBLGen entity. User selects a product, and the Id get saved. Now I need to display some product specific details later on. Should I use the Product's 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 the name, and hope that never changes?

switch (selectedProduct.Name)
{
case "product one":
  break;    
}

Or is there a better alternative?

Answer 1

If you want your ProductID's to be fixed (which doesn't seem to be a good idea), then you can use IDENTITY INSERT (in SQL Server, at least) to ensure ProductID values are the same between installations. But, I would normally only do this for static reference data.

You can also use Visual Studio's T4 templates to generate enums directly off the database data

Answer 2

For this situation, there are three common solutions I have seen:

1. Hard code the ID - this is quick and dirty, not self-documenting (you don't know what product is being referred to), and prone to breakage as you pointed out. I never use this method anymore.
2. Enums - I use this when the table is small and static. So, ProductType would be a possible candidate for this. This is self-documenting code, but still creates an awkward connection between code and data where if records are inserted with different IDs than you planned for, then things break. You can mitigate this by automating the Enum generation in various ways, but it still feels wrong. E.g., if your unit tests are inserting records into the Product table, it will be difficult for them to recreate the Enum at that point. Also, if you have 100,000 records, the Enum approach starts to look pretty dumb.
3. Add an additional column, that is a non-changing identifier. I often use AlphaCode as my column name. So in your case it would look like:

    switch (selectedProduct.AlphaCode)
    {
        case "PRODUCT_ONE":
            break;

    }

This lets you use an AlphaCode that is self-documenting, allows you to reinsert data without caring about the autoincrement PK value, and lets you change the product name without affecting anything. If you use the AlphaCode approach, ensure that you put a unique index on this column.

The other solution, which is often the most preferable one, is to move this logic to the database. E.g., if product 1 is the product you always want to show by default when its category is selected, you could add a column to your table called IsHeroProduct. Then your query becomes:

    if (selectedProduct.IsHeroProduct)
    {
        //do stuff

    }

Answer 3

Some ORMs (LLBLGen at least) can handle this for you; but generating a strong type of enums. I've never used that though.

In these cases, I always just go with an enum that I write myself, but I make sure that all the fields are equal, and update if any change. It becomes more interesting when you work across databases (as I do), but if you take care, it is simple enough.

Answer 4

If you know of all the items in this table (which I guess you do if you can do a switch on them) and want them the same for each installation then maybe it should not be an identity column and you should insert 1, 2, 3 with the products themselves.