将按位枚举映射到sql列值

本文关键字：sql 列值 映射 枚举 | 更新日期: 2023-09-27 18:08:28

我有一个位枚举，上面像这样设置FlagsAttribute-

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

现在，在C#中，我将这个值存储在一个属性中，比如MyProperty，在保存时，我将此属性写入SQL数据库的integer列中。假设我从代码中选择First,Second,Five，那么在数据库中它将被保存为'11'。

我知道我可以从DB中获取值，只需要将int值类型转换为MyEnum，它就会给我值。但是，我希望在某些存储过程中对SQL数据进行一些操作，显然我不能将其类型转换为Enum值。那么，有没有一种方法可以让我了解个人价值观。

就像在例子中一样，如果存储了11，那么我可以通过任何方式将其获取为"1+2+8"

将按位枚举映射到sql列值

这可能有助于您入门：

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';

其中11是您的存储值。

这将为设置的每个值返回非零值(即Select 11 & 1 As 'First'返回1，Select 11 & 2 As 'Second'返回2，Select 11 & 4 As 'Third'返回0，依此类推(

您可以在SQL 中执行逐位操作

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

返回设置的标志

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

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

(可能的(尾随加号留给读者练习。

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

值26(或11010(对应于一+三+四要获得描述，您可以使用下一个请求

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

它会给你下一个结果One | Three | Four

如果你只想得到ids

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