Whether you use int or varchar, multiple-selection queries for status are difficult to handle. For example, under normal thinking, the enum for Customerstatus is set as follows:
Copy Code code as follows:
[Serializable]
public enum Customerstatus
{
New = 0,
Active = 1,
Overdue = 2,
Suspended = 3,
Closing = 4,
Closed = 5
}
The status value is stored as int in the database.
What if I want to search the page for a customer with a status of Active,overdue and suspended? Does the program have to put these three status values
A string that is passed to SQL to handle? Although it can be achieved, but rather inefficient.
Now give a standard solution:
(1). All enumerations that may be used as search criteria should be defined as a bitwise operation.
Copy Code code as follows:
public enum Customerstatus
{
New = 1,
Active = 1<<1,
Overdue = 1<<2,
suspended = 1<<3,
Closing = 1<<4,
Closed = 1<<5
}
(2). At the time of database design, the field of status must be of type int.
So when we do multiple selection queries, @status's value= customerstatus.active | Customerstatus. overdue| Customerstatus. Suspended
(3). The query statement is as follows:
Copy Code code as follows:
Select *
From Customer
Where [status] & @Status = [status]
If the @status can be null,
Copy Code code as follows:
Select *
From Customer
Where (@Status is null Or [status] & @Status = [status])
With such a simple statement, you can get all the data rows that meet the @status requirements.
