. NET, using two-dimensional array "Combination" query optimization

As for the interface layer, the texture is no longer shown here, just as in the Link blog. All Chinese characters in the drop-down list do not need to be defined in frmWorkRecord_Load. Instead, the Chinese characters in the drop-down list are placed in the Item attribute of ComboBox.

The following is the U-layer code.
[Vb]
Imports Entity
Imports BLL
 
Public Class form1
 
'Define a two-dimensional array so that the Chinese characters in the drop-down list correspond to English characters.
Dim strArr (2, 7) As String
 
Private Sub frmWorkRecord_Load (ByVal sender As Object, ByVal e As System. EventArgs) Handles Me. Load
'Use a two-dimensional array to avoid the previous select case statement
'Implement one-to-one correspondence between "instructor" and "UserID", "computer date", and "OnDate "...
StrArr (0, 0) = "UserID"
StrArr (0, 1) = "OnDate"
StrArr (0, 2) = "OnTime"
StrArr (0, 3) = "OffDate"
StrArr (0, 4) = "OffTime"
StrArr (0, 5) = "Computer"
 
'Implement one-to-one correspondence between "and", "or" and "or"
StrArr (1, 0) = "and"
StrArr (1, 1) = "or"
 
End Sub
 
Private Sub cmdOk_Click (ByVal sender As System. Object, ByVal e As System. EventArgs) Handles cmdOk. Click
Dim arrTeacherE As TeacherE () = {New TeacherE (), New TeacherE (), new TeacherE (), New TeacherE ()}
 
In the 'field 'drop-down list, English letters corresponding to Chinese characters are assigned to the arrTeacherE array.
ArrTeacherE (0). Name = strArr (0, cbxName1.SelectedIndex)
'When the ComboBox control loads a page, the default value is SelectedIndex =-1, and the array subscript cannot be negative. Therefore, you need to judge
If cbxName2.Text = "" Then
ArrTeacherE (1). Name = ""
Else
ArrTeacherE (1). Name = strArr (0, cbxName2.SelectedIndex)
End If
 
If cbxName3.Text = "" Then
ArrTeacherE (2). Name = ""
Else
ArrTeacherE (2). Name = strArr (0, cbxName3.SelectedIndex)
End If
 
'Assign the "operator" to the arrTeacherE Array
ArrTeacherE (3). Operation = cbxOperation1.Text
ArrTeacherE (4). Operation = cbxOperation2.Text
ArrTeacherE (5). Operation = cbxOperation3.Text
 
'Assign the query condition to the arrTeacherE array.
Arrteachere(62.16.txt Name = txtName1.Text
Arrteachere(72.16.txt Name = txtName2.Text
ArrTeacherE(8).txt Name = txtName3.Text
 
The English letters corresponding to Chinese characters in the 'composite relation 'drop-down list are assigned to the arrTeacherE array.
If cbxCompone1.Text = "" Then
ArrTeacherE (9). Compone = ""
Else
ArrTeacherE (9). Compone = strArr (1, cbxCompone1.SelectedIndex)
End If
'Here, the If statement serves the same purpose as the If statement above.
If cbxCompone2.Text = "" Then
ArrTeacherE (10). Compone = ""
Else
ArrTeacherE (10). Compone = strArr (1, cbxCompone2.SelectedIndex)
End If
 
Dim mybll As New TeacherBLL
Dim dt As New DataTable
Dt = mybll. test (arrTeacherE)
If dt. Rows. Count ()> 0 Then
DataGridView1.DataSource = dt
Else
MessageBox. Show ("no record ")
End If
End Sub
End Class

For layer B, the business logic of this function is not very complex. It is only necessary to call the corresponding method of layer D and then return a DataTable (not suitable for Layer 3, haha)

The following is the code for layer D. SqlHelper is used here. I believe you are familiar with SqlHelper. I will not repeat it here, but I will call it here.
[Vb]
Imports Entity
Imports System. Data. SqlClient
 
Public Class OnDutyTeacher
 
Function test (ByVal arrTeacherE () As TeacherE) As DataTable
Dim strSqlText As String = "select * from Teacher_Info where" & arrTeacherE (0 ). name & arrTeacherE (3 ). operation & "&" @ txtName1 "& arrTeacherE (9 ). compone & "& arrTeacherE (1 ). name & arrTeacherE (4 ). operation & "&" @ txtName2 "&" "& arrTeacherE (10 ). compone & "& arrTeacherE (2 ). name & "& arrTeacherE (5 ). operation & "@ txtName3 "&""
 
Dim param () As SqlParameter
Dim intInfluencedRecord As Integer
 
Param = New SqlParameter () {New SqlParameter ("@ txtName1", SqlDbType. char, 10), New SqlParameter ("@ txtName2", SqlDbType. char, 10), New SqlParameter ("@ txtName3", SqlDbType. char, 10 )}
 
Param (0). Value = arrteachere(62.16.txt Name. ToString ()
Param (1). Value = arrteachere(72.16.txt Name. ToString ()
Param (2). Value = arrTeacherE(8).txt Name. ToString ()
 
 
IntInfluencedRecord = SQLHelper. SQLHelper. ExecuteNoQuery (strSqlText, CommandType. Text, param)
If intInfluencedRecord> 0 Then
Return True
Else
Return False
End If
End Function
End Class

This method avoids the judgment of the "Composite relationship" field and then Concatenates the string. Of course, this is not very comprehensive. You also need to add some judgments to the control on the Interface: if the first control of the "Composite relationship" is not empty, the "field", "operator", and "query condition" controls cannot be empty. If the second control of the corresponding "Composite relationship" is not empty, all controls cannot be empty. Otherwise, an error will be reported if the SQL statements on layer D are incomplete.