Whether int or varchar is used, it is hard to cope with multiple Status queries. For example, in general thinking, The Enum settings for CustomerStatus are as follows:
Copy codeThe Code is as follows:
[Serializable]
Public enum CustomerStatus
{
New = 0,
Active = 1,
Overdue = 2,
Sushortded = 3,
Closing = 4,
Closed = 5
}
The Status value is stored in the database as an int.
What should I do if I want to search for a Customer in the Active, Overdue, and suincluded statuses on the page? Does the program need to set these three status values?
Concatenate a string and pass it to SQL for processing? Although it can be achieved, it is quite inefficient.
Now we provide a standard solution:
(1). All enumerations that may be used as search criteria should be defined in the following bitwise operation.
Copy codeThe Code is as follows:
Public enum CustomerStatus
{
New = 1,
Active = 1 <1,
Overdue = 1 <2,
Suincluded = 1 <3,
Closing = 1 <4,
Closed = 1 <5
}
(2). During database design, the Status field must be of the int type.
In this way, when we select multiple queries, the Value of @ Status = CustomerStatus. Active | CustomerStatus. Overdue | CustomerStatus. suincluded
(3) The query statement is as follows:
Copy codeThe Code is as follows:
Select *
From Customer
Where [Status] & @ Status = [Status]
If @ Status can be null,
Copy codeThe Code is as follows:
Select *
From Customer
Where (@ Status is null Or [Status] & @ Status = [Status])
With such a simple statement, you can obtain all data rows that meet the @ Status requirements.