Asp.net multi-condition search example with three layers

Asp.net multi-condition search example with three layers

The project is divided into three layers: interface layer, business logic layer, and data access layer. The following describes how asp.net uses three layers to implement multi-condition search. If you are interested, refer

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

It is also known that multi-condition search is based on the selected condition items, and then spell SQL statements

Therefore, since SQL statements must be spelled out based on the conditions selected by the user, the user's selection must be received at the interface layer. At this time, the problem arises:

Is it true that I want to spell SQL statements at the interface layer, and the functions can be fully implemented? But does it mean that you have broken the three-layer principle?

So what is Layer 3?

Then I had a good SQL statement at the data access layer, and then the problem came again:

In the data access layer, the user knows which conditions are selected. Based on the layered principle, data such as textBox1.Text cannot be transmitted to the data access layer.

In fact, the solution is the second method, but a conditional model class is used in the middle to pass the user's choice.

The condition model class is as follows:

Public class SearchModel

{

Public string Name {get; set;} // record the database field Name

Public string Value {get; set;} // record Value

Public Action {get; set;} // record the corresponding Action

}

It's hard to see what the role of this class is, so let's take a look at it ~

Prepare an enumeration:

Public enum Action

{

Lessthan,

Greatthan,

Like,

Equart

}

Corresponding to several operations in the data, such as <,>, like, =, etc., you can add as needed

Of course, you can also use numbers, but it is best not to use devil numbers, so define an enumeration ~ Just move your finger.

Assume that you want to perform multi-condition search on a book table.

Code in the interface layer:

List Ss = new List ();

If (! String. IsNullOrEmpty (Request. Form ["txtName"]) // If you enter text in the Name box

{

SearchModel model = new SearchModel ();

Model. Name = "BookName"; // The Name of the field to be operated.

Model. Value = Request. Form ["txtName"]; // the corresponding Value is the text entered by the user.

Model. Action = Action. Like; // The operation is like.

Ss. Add (model );

} // The following is similar

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); // you can call Bll to perform the following operations:

Bll will not talk about it first, mainly the SQL concatenation at the Dal layer.

Public List Search (List Ss) // receives and traverses the passed conditional model class.

{

String SQL = "select * from T_Books where IsDelete = 0 and"; // start concatenating 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); // import the spliced SQL statement to start querying the database

Foreach (DataRow row in table. Rows)

{

T_Books book = GetModelByDataRow. GetBooks (row );

List. Add (book );

}

Return list; // return a set of qualified books.

Assume that the conditions entered by the user are:

Finally, paste the SQL statement for testing and splicing as follows:

Select * from T_Books where IsDelete = 0 and BookName like '% C ++ %' and Author like '% JChubby %' and CategoryId = 15 and PublisherId = 16 and ISBN like '% 1111% 'and Discount = 1