Map bitwise enum to sql column value

问题:

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"

回答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.

回答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

回答3:

declare @MyEnum as Int
set @MyEnum = 11
select
  case when ( @MyEnum & 1 ) = 1 then '1+' else '' end +
  case when ( @MyEnum & 2 ) = 2 then '2+' else '' end +
  case when ( @MyEnum & 4 ) = 4 then '4+' else '' end +
  case when ( @MyEnum & 8 ) = 8 then '8+' else '' end
  as Bitses

The (possible) trailing plus is left as an exercise for the reader.

回答4:

ID  Name
1   Zero
2   One
4   Two
8   Three
16  Four
32  Five

Value of 26 (or 11010) corresponds to One+Three+Four To get a description for that you can use next request

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ' | ', '') + Name 
  FROM [Play].[dbo].[Enums]
  where (26 & ID)=ID
Select @Names;

It will give you next result One | Three | Four

if you want to get just ids

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + STR(ID)
  FROM [Play].[dbo].[Enums]
  where (26 & ID)=ID
Select @Names;