Parse Enum with FlagsAttribute to a SqlParameter

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,"

Answers


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.


Need Your Help

Sorting days of the week based based on current day of the week

c# linq sorting

I'm trying to sort the days of the week using the DayOfWeek enum in C#, using Linq, using the following code snippet-

How do you apply a list of lambda functions to a single element using an iterator?

python list lambda iterator yield

I want to apply a list of lambda functions to a single element using an iterable that has to be created with yield.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.