Parse Enum with FlagsAttribute to a SqlParameter

Question

I've got an Enum with Flags attribute.

  [Flags]
    public enum AlcoholStatus
    {
        NotRecorded = 1,
        Drinker = 2,
        NonDrinker = 4
    }

I am creating a Sqlparameter as below.

new SqlParameter("@AlcoholStatus", SqlDbType.VarChar) {Value = (int) AlcoholStatus}

If AlcoholStatus has all the values (NotRecorded | Drinker | NonDrinker) it returns 7 as the value for the SqlParameter.

I am parsing this parameter for a stored procedure and I prefer if I can parse the value as "1,2,3,". What's the best way of doing this?

Or is there any other easy way to filter records by parsing integer value 7 to the stored procedure?

EDIT : This happens in a filter functionally where user wants to see people with any of above statuses. It's a quite complicated sql query. There I filter AlcoholStatus as below

WHERE AlcoholStatus IN "1,2,4,"

Answer 1

Leaving the aside of whether that enum makes sense as a [Flags], IMO the only sensible way of storing this data is in an int column. Just store the value - nothing else is required. Trying to coerce it as a varchar is a mistake. The job of the DB is to store data, not to care about presentation.

If you need to filter by this you can use bitwise operators in the search, but note that performance is impacted since when searching by combination (rather than via equality, which can use a non-clustered index). If you need to search on arbitrary combinations, then consider denormalization instead. If you only need to search on predictable bits then you can lift those out with a calculated persisted indexed column - i.e. you can have an automatic IsDrinker column (bit) that represents bit 2.

Answer 2

You can pass 7 down to the stored procedure and kiss goodbye to any benefits you might get from an index on the AlcoholStatus column if you start AND'ing values in a WHERE clause.

Something like the below might be what you're after if you need to build up a string of IDs. Optimize as necessary - I've not taken into account that there are only three flags in your enum, for example.

You'll also want to watch out for SQL injection if you're fiddling with strings in your SQL when building up statements.

string values = "";
for(int i = 0; i < 30; ++i) {
  if((((int)AlcoholStatus) & (1 << i)) != 0) {
    if(values != "") {
      values += ",";
    }

    values += (i + 1).ToString();
  }
}