asp.net using three-layer multi-condition Retrieval example _ practical skills

As we all know, the three layer divides the project into the interface layer, the business logic layer and the data access layer (take the most basic three layers as an example)

It is also known that multiple conditional retrieval is actually based on a user-selected criteria item and then the SQL statement is spelled

So, since you want to spell the SQL statement based on the criteria items that the user chooses, you have to be sure to receive the user's choice at the interface layer, which is the problem:

Do I have to spell the SQL statement at the interface layer, so it's all right, and the function is fully achievable, but so you're breaking the three-tier principle?

So what do you do with three floors?

So I'm going to spell the SQL statement at the data access layer, and then the question goes:

In the data access layer to spell it so know what the user has chosen a few conditions, according to the principle of layering, it is not possible to pass data such as TextBox1.Text to the data access layer

In fact, the solution is the second way, only the middle through a conditional model class to pass the user's choice

The conditional model classes are as follows:

public class Searchmodel 
{public 
string Name {get; set;} Record database field name public 
string Value {get; set;} Record the corresponding value public 
action action {get; set;} Log the appropriate action 
}

It's hard to see what this class is all about.

After that, you prepare an enumeration:

Public enum Action 
{ 
LessThan, 
Greatthan, like 
, 
Equart 
}

The corresponding data in several operations, such as <,>,like,=, can be added according to their own needs

Of course you can also use numbers, but the devil number is best not to use, so still define an enumeration bar ~ Move the finger to OK

Suppose you want to search a book table for multiple conditions now

Code in the interface layer:

list<searchmodel> ss = new list<searchmodel> (); if (!string. 
IsNullOrEmpty (request.form["txtname"))//If the user entered the text in the name box {Searchmodel model = new Searchmodel (); Model. Name = "BookName";//the field to be manipulated is the title model. Value = request.form["Txtname"];//corresponds to the text model entered by the user. Action = action.like;//operation is like SS. 
ADD (model); }//similar if (!string) below. 
IsNullOrEmpty (request.form["Txtauthor"]) {Searchmodel model = new Searchmodel (); Model. 
Name = "Author"; Model. 
Value = request.form["Txtauthor"]; Model. 
Action = Action.like; Ss. 
ADD (model); } if (!string. 
IsNullOrEmpty (request.form["CategoryID"]) {Searchmodel model = new Searchmodel (); Model. 
Name = "CategoryID"; Model. 
Value = request.form["CategoryID"]; Model. 
Action = Action.equart; Ss. 
ADD (model); } if (!string. 
IsNullOrEmpty (request.form["PublisherID"]) {Searchmodel model = new Searchmodel (); Model. 
Name = "PublisherID"; Model. 
Value = request.form["PublisherID"]; Model. 
Action = Action.equart; Ss. 
ADD (model); } if (!string. IsnulLorempty (request.form["TXTISBN"]) {Searchmodel model = new Searchmodel (); Model. 
Name = "ISBN"; Model. 
Value = request.form["TXTISBN"]; Model. 
Action = Action.like; Ss. 
ADD (model); } if (!string. 
IsNullOrEmpty (request.form["Isdiscount"]) {Searchmodel model = new Searchmodel (); Model. 
Name = "Discount"; Model. 
Value = "1"; Model. 
Action = Action.equart; Ss. 
ADD (model); list<t_books> books = Searchbll.searc (ss);//here invoke BLL to operate

BLL will not say, mainly is the DAL layer of the SQL splicing

Public list<t_books> Search (list<searchmodel> ss)//receives the incoming conditional model class collection and traverses it 
{ 
String sql = ' SELECT * From T_books where isdelete=0 and, start stitching the SQL statement for 
(int i = 0; I < SS. Count; i++) 
{ 
if (ss[i]. Action = = action.like) 
{ 
SQL + ss[i]. Name + "like '%" + ss[i]. Value + "%"; 
} 
if (ss[i). Action = = Action.equart) 
{ 
SQL + ss[i]. Name + "=" + ss[i]. Value; 
} 
if (ss[i). Action = = Action.greatthan) 
{ 
SQL + ss[i]. Name + ">" + ss[i]. Value; 
} 
if (ss[i). Action = = Action.lessthan) 
{ 
SQL + ss[i]. Name + "<" + ss[i]. Value; 
} 
if (I!= SS. Count-1) 
{ 
sql + = "and"; 
} 
} 
list<t_books> list = new list<t_books> (); 
DataTable table = sqlhelper.executedatatable (sql, CommandType.Text);//concatenation of SQL statements will be passed in to start querying the database 
foreach (DataRow row In table. Rows) 
{ 
T_books book = getmodelbydatarow.getbooks (row); 
List. ADD (book); 
} 
Return list;//returns a set of eligible books, complete

Suppose the user enters the condition of the following diagram:

Finally paste the SQL statement of the test stitching, as follows

SELECT * from T_books where isdelete=0 and bookname like '%c++% ' and Author like '%jchubby% ' and CategoryID = and Publi Sherid = ISBN like '%1111% ' and Discount = 1