Map bitwise enum to sql column value

Question

I have a bitwise enum with FlagsAttribute set over it like this -

[FlagsAttribute]
public enum MyEnum
{
    None = 0,
    First = 1,
    Second = 2,
    Third = 4,
    Five = 8,
    Six = 16,
    Seven = 32,
    Eight = 64,
    Nine = 128
}

Now, in C# i am storing this value in a property say MyProperty and on save i write this property in my SQL database in integer column. Suppose if i select First,Second,Five from code then in database it will be saved as '11'.

I know i can fetch value from DB and just need to typecast int value to MyEnum and it will give me the values. But, i want some manipulation to be done on SQL data in some Stored procedure where obviously i can't typecast it to Enum value. So, is there a way out which can let me know about the individual values.

Like in example if 11 is stored, any way that i can get it as "1+2+8"

Answer 1

This may help to get you started:

Select 11 & 1 As 'First'
  , 11 & 2 As 'Second'
  , 11 & 4 As 'Third'
  , 11 & 8 As 'Five'
  , 11 & 16 As 'Six'
  , 11 & 32 As 'Seven'
  , 11 & 64 As 'Eight'
  , 11 & 128 As 'Nine';

Where 11 is your stored value.

This will return non-zero values for each value that is set (i.e. Select 11 & 1 As 'First' returns 1, Select 11 & 2 As 'Second' returns 2, Select 11 & 4 As 'Third' returns 0 and so on.

Answer 2

You can do bitwise operations in SQL

Select  *
From    MyTable
Where   MyEnum = (1 | 2 | 8)

Return which flags are set

Select  Case when (MyEnum & 1) = 1 Then 1 else 0 End as First,
        Case when (MyEnum & 2) = 2 Then 1 else 0 End as Second,
        Case when (MyEnum & 4) = 4 Then 1 else 0 End as Third,
        Case when (MyEnum & 8) = 8 Then 1 else 0 End as Fourth,
        Case when (MyEnum & 16) = 16 Then 1 else 0 End as Fifth
From    MyTable