.NET下,利用二维数组“组合”查询之优化
至于界面层,这里就不再贴图了,和链接博客中的一样。在这里说明一下:下拉列表中的所有汉字不用在frmWorkRecord_Load中定义了,而是通过将下拉列表中的汉字放到ComboBox的Item属性里。
下面就是U层代码了
[vb]
Imports Entity
Imports BLL
Public Class form1
'定义一个二维数组,使得下拉列表中的汉字与英文对应
Dim strArr(2, 7) As String
Private Sub frmWorkRecord_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'使用二维数组,避免了之前的select case语句
'实现“教师”和“UserID”、“上机日期”和“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"
'实现“与”和“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(), New TeacherE(), New TeacherE(), New TeacherE(), New TeacherE(), New TeacherE(), New TeacherE(), New TeacherE()}
'“字段”下拉列表中与汉字对应英文赋值到arrTeacherE数组中
arrTeacherE(0).Name = strArr(0, cbxName1.SelectedIndex)
'ComboBox控件在页面加载时,默认SelectedIndex=-1,而数组下标不能出现负数,故需要进行判断
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
'将“操作符”赋值到arrTeacherE数组中
arrTeacherE(3).Operation = cbxOperation1.Text
arrTeacherE(4).Operation = cbxOperation2.Text
arrTeacherE(5).Operation = cbxOperation3.Text
'将“查询条件”赋值到arrTeacherE数组中
arrTeacherE(6).txtName = txtName1.Text
arrTeacherE(7).txtName = txtName2.Text
arrTeacherE(8).txtName = txtName3.Text
'“组合关系”下拉列表中与汉字对应英文赋值到arrTeacherE数组中
If cbxCompone1.Text = "" Then
arrTeacherE(9).Compone = ""
Else
arrTeacherE(9).Compone = strArr(1, cbxCompone1.SelectedIndex)
End If
'这里的If语句作用同上面的If语句
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("没有记录")
End If
End Sub
End Class
而对于B层,这个功能的业务逻辑不是很复杂,只是调用D层相应的方法,然后返回一个DataTable就可以了(不太符合三层,哈哈)
下面就是D层的代码了,这里用到了SqlHelper,相信大家对SqlHelper很熟悉了,这里就不再赘述了,只是在这里调用一下。
[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(6)
补充:Web开发 , ASP.Net ,