asp.net a multi-condition retrieval example with three layers

Three layer of the project into the interface layer, business logic layer and data access layer, the following for you to introduce the next asp.net how to use the three-layer to achieve multiple conditions of retrieval, interested friends can refer to the following

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 names

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 ss = new list ();

if (!string. IsNullOrEmpty (request.form["txtname"))//If the user enters text in the name box

{

Searchmodel model = new Searchmodel ();

Model. Name = "BookName";//the field to be manipulated is the book title

Model. Value = request.form["Txtname"];//corresponds to the text entered by the user

Model. Action = action.like;//Action for like

Ss. ADD (model);

}//similar to the following

if (!string. 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 Books = Searchbll.searc (ss);//Here call BLL for action

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

Public list Search (List ss)//Receive incoming conditional model class collection and iterate over it

{

String sql = "SELECT * from T_books where isdelete=0";//Start stitching SQL statements

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 list = new list ();

DataTable table = sqlhelper.executedatatable (sql, CommandType.Text);//Send the spliced SQL statement to the query 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

The

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