In VB.net, use Arrays for "combined" queries

These several forms that have been combined query in the charging system of the data center over the past few days. This time, we chose to use Arrays for query. Next, let's talk about this method. I hope you can correct and learn from each other.

Here I want to explain the example of implementation in a three-tier architecture. If you do not know the three-tier architecture or have never heard of the three-tier architecture, we recommend that you enter "three-tier improvement" in Baidu. csdn. Many of our senior students have summarized the three layers, which is good for you to learn and understand the three layers.

Let's get down to the truth. Let's take a look at the composition of the form (U Layer ).

Three "fields" drop-down lists, three "operations" drop-down lists, and two "Composite relations" drop-down lists have the same entries. They are "card number, student ID, name, gender", "<,>, =, <>", "and, or" in sequence ". And the record to be queried is in a table.

(Note: The following code is only for explanation. I hope you will not consider naming conventions .)

Private sub cmdok_click (byval sender as system. object, byval e as system. eventargs) handles cmdok. click Select case cbxname1.text. trim () case "card No." f1 = "cardno" case "student no." f1 = "Sid" case "name" f1 = "name" case "gender" f1 = "sex" End select case cbxname2.text case "" 'it is important here F2 = "" case "card number" f2 = "cardno" case "student ID" f2 = "Sid" case "name" f2 = "name" case "gender" f2 = "sex" End select case cbxname3.text case "" 'This is important: F3 = "" case "card number" F3 = "cardno" case "student ID" F3 =" sid "case" name "F3 =" name "case" gender "F3 =" sex "End select dim myarr as entity. studente () = {new entity. studente (), new entity. studente (), new entity. studente ()} myarr (0 ). name = F1 myarr (1 ). name = F2 myarr (2 ). name = F3 dim myarr1 () as entity. studente = {new entity. studente, new entity. studente, new entity. studente} myarr1 (0 ). operation = cbxoperation1.text myarr1 (1 ). operation = cbxoperation2.text myarr1 (2 ). operation = cbxoperation3.text dim myarr2 () as entity. studente = {new entity. studente, new entity. studente, new entity. studente} Your name = txtname1.text myarr2(1).txt name = txtname2.text your name = txtname3.text dim E0 as string = "" dim E1 as string = "" select case cbxcompone1.text case "" 'indicates that only query e0 = "" case "and" e0 = "and" case "or" e0 = "or" End select case cbxcompone2.text case "" e1 = """ case "and" e1 = "and" case "or" e1 = "or" End select

Dim myarr3 () as entity. studente = {new entity. studente, new entity. studente, new entity. studente} myarr3 (0 ). compone = E0 myarr3 (1 ). compone = e1 dim mybll as new BLL. studentbll dim dT as new datatable dt = mybll. showsinfocom (myarr, myarr1, myarr2, myarr3) If DT. rows. count ()> 0 then datagridview1.datasource = DT else MessageBox. show ("no record") end if end sub

F1, F2, and F3 are global variables. In addition, for some other judgments, such as when the first "Composite relationship" is empty, the following drop-down list and text box cannot be allowed for operations, no settings are made here.

Next let's take a look at the business logic layer (layer B)

Function ShowSinfoCom(ByVal myarr() As Entity.StudentE, ByVal myarr1() As Entity.StudentE, ByVal myarr2() As Entity.StudentE, ByVal myarr3() As Entity.StudentE) As DataTable        Dim mydal As New DAL.StudentDAL        Dim dt As New DataTable        dt = mydal.GetOnLineCom(myarr, myarr1, myarr2, myarr3)        Return dt    End Function

At last, let's take a look at the Dal layer. In this layer, we only show you the queried SQL statements.

 

Function GetOnLineCom(ByVal myarr() As Entity.StudentE, ByVal myarr1() As Entity.StudentE, ByVal myarr2() As Entity.StudentE, ByVal myarr3() As Entity.StudentE) As DataTable        Dim sql As String = "select * from Student_Info where " & myarr(0).Name & myarr1(0).Operation & " " & myarr2(0).txtName & myarr3(0).Compone & " " & myarr(1).Name & myarr1(1).Operation & " " & myarr2(1).txtName & myarr3(1).Compone & " " & myarr(2).Name & myarr1(2).Operation & myarr2(2).txtName & ""                Return myDataTable    End Function

This is a combined query using arrays. Of course, you can also use concatenation strings and other methods.