前言:
组合查询用到了模板方法,因为有三个这样的窗体是差不多的,因此咱们直接套用设计模式中的模板方法去解决这样的问题。
内容:
UI层代码:
Public Class frmGroupQuery
'用于接收文本框或时间控件的内容
Public txtContent1 As String = ""
Public txtContent2 As String = ""
Public txtContent3 As String = ""
Protected GroupQuery As New Entity.EntityGQuery '定义一个组合查询实体
Private Sub frmGroupQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'操作符
cmbOperation1.Items.Add(">")
cmbOperation1.Items.Add("<")
cmbOperation1.Items.Add("=")
cmbOperation1.Items.Add("<>")
cmbOperation2.Items.Add(">")
cmbOperation2.Items.Add("<")
cmbOperation2.Items.Add("=")
cmbOperation2.Items.Add("<>")
cmbOperation3.Items.Add(">")
cmbOperation3.Items.Add("<")
cmbOperation3.Items.Add("=")
cmbOperation3.Items.Add("<>")
'关系
cmbRelation1.Items.Add("与")
cmbRelation1.Items.Add("或")
cmbRelation2.Items.Add("与")
cmbRelation2.Items.Add("或")
'时间控件不可见,只有当选择日期是可见
DTP1.Visible = False
DTP2.Visible = False
DTP3.Visible = False
'窗体加载,只有第一行是可用的
cmbField2.Enabled = False
cmbField3.Enabled = False
cmbOperation2.Enabled = False
cmbOperation3.Enabled = False
cmbRelation2.Enabled = False
txt2.Enabled = False
txt3.Enabled = False
'当选中dgvGroupQuery控件就选中行
dgvGroupQuery.SelectionMode = DataGridViewSelectionMode.FullRowSelect '选择一整行
Dim i As Integer
For i = 0 To dgvGroupQuery.Columns.Count - 1
dgvGroupQuery.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells
Next
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub Rdim()
ReDim Preserve arrayControl(10) '重定义数组维数
'初始化数组
arrayControl(0) = New Term(cmbField1, "卡号")
arrayControl(3) = New Term(cmbField2, "充值金额")
arrayControl(6) = New Term(cmbField3, "学号")
arrayControl(1) = New Term(cmbOperation1, "姓名")
arrayControl(4) = New Term(cmbOperation2, "年纪")
arrayControl(7) = New Term(cmbOperation3, "专业")
arrayControl(2) = New Term(txt1, "学院")
arrayControl(5) = New Term(txt2, "性别")
arrayControl(8) = New Term(txt3, "类型")
arrayControl(9) = New Term(cmbRelation1, "状态")
arrayControl(10) = New Term(cmbRelation2, "状态1")
End Sub
'清空
Private Sub BtnClear_Click(sender As Object, e As EventArgs) Handles BtnClear.Click
Call Rdim()
If AllEmpty(arrayControl) Then
Exit Sub
End If
dgvGroupQuery.DataSource = ""
End Sub
'定义虚函数,将控件中文字转换为数据库中的字段
Public Overridable Function GetDBName(ByVal control As String) As String
Return ""
End Function
'定义虚函数,获取表名
Protected Overridable Function GetTable() As String
Return ""
End Function
'定义虚函数,把标题显示出来
Protected Overridable Sub Todgv()
'设置列宽
dgvGroupQuery.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
End Sub
Private Sub BtnQuery_Click(sender As Object, e As EventArgs) Handles BtnQuery.Click
If cmbField1.Text = "上机日期" Or cmbField1.Text = "下机日期" Then
txtContent1 = Format(DTP1.Value, "yyyy-MM-dd HH:mm:ss")
Else
txtContent1 = txt1.Text
End If
If cmbField2.Text = "上机日期" Or cmbField2.Text = "下机日期" Then
txtContent2 = Format(DTP2.Value, "yyyy-MM-dd HH:mm:ss")
Else
txtContent2 = txt2.Text
End If
If cmbField3.Text = "上机日期" Or cmbField3.Text = "下机日期" Then
txtContent3 = Format(DTP3.Value, "yyyy-MM-dd HH:mm:ss")
Else
txtContent3 = txt3.Text
End If
'第一关系为空
If cmbRelation1.Text = "" Then
If cmbField1.Text = "" Or cmbOperation1.Text = "" Or txtContent1 = "" Then
MsgBox("请检查第一行条件是否完整!", , "提示")
Exit Sub
End If
End If
'第一个关系不为空
If cmbRelation1.Text <> "" Then
If cmbField2.Text = "" Or cmbOperation2.Text = "" Or txtContent2 = "" Then
MsgBox("请检查第二行条件是否完整!", , "提示")
Return
End If
End If
If cmbRelation2.Text <> "" Then
If cmbField3.Text = "" Or cmbOperation3.Text = "" Or txtContent3 = "" Then
MsgBox("请检查第三行条件是否完整!", , "提示")
Return
End If
End If
GroupQuery.tableName = GetTable()
GroupQuery.cmbField1 = GetDBName(cmbField1.Text.Trim())
GroupQuery.cmbField2 = GetDBName(cmbField2.Text.Trim())
GroupQuery.cmbField3 = GetDBName(cmbField3.Text.Trim())
GroupQuery.cmbOperation1 = cmbOperation1.Text.Trim()
GroupQuery.cmbOperation2 = cmbOperation2.Text.Trim()
GroupQuery.cmbOperation3 = cmbOperation3.Text.Trim()
GroupQuery.txtContent1 = txtContent1
GroupQuery.txtContent2 = txtContent2
GroupQuery.txtContent3 = txtContent3
GroupQuery.cmbRelation1 = GetDBName(cmbRelation1.Text.Trim())
GroupQuery.cmbRelation2 = GetDBName(cmbRelation2.Text.Trim())
Dim table As New DataTable
Dim facade As New Facade.GroupQueryFacade
table = facade.selectGQFacade(GroupQuery)
If (table.Rows.Count = 0) Then
MsgBox("没有符合条件的记录,请重新设置查询条件", , "提示")
dgvGroupQuery.DataSource = Nothing
Else
Call Todgv()
End If
End Sub
'第一个组合关系改变,第二行可用
Private Sub cmbRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation1.SelectedIndexChanged
cmbField2.Enabled = True
cmbOperation2.Enabled = True
txt2.Enabled = True
cmbRelation2.Enabled = True
End Sub
'第二个组合关系改变,第三行可用
Private Sub cmbRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation2.SelectedIndexChanged
cmbField3.Enabled = True
cmbOperation3.Enabled = True
txt3.Enabled = True
End Sub
'针对查询与日期、时间有关的问题
Private Sub cmbField1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField1.SelectedIndexChanged
If cmbField1.Text = "上机日期" Or cmbField1.Text = "下机日期" Then
DTP1.Format = DateTimePickerFormat.Custom
DTP1.Visible = True
txt1.Visible = False
txtContent1 = Format(DTP1.Value, "yyyy-MM-dd HH:mm:ss")
Else
DTP1.Visible = False
txt1.Visible = True
txtContent1 = txt1.Text
End If
End Sub
'针对查询与日期、时间有关的问题
Private Sub cmbField2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField2.SelectedIndexChanged
If cmbField2.Text = "上机日期" Or cmbField2.Text = "下机日期" Then
DTP2.Format = DateTimePickerFormat.Custom
DTP2.Visible = True
txt2.Visible = False
txtContent2 = Format(DTP2.Value, "yyyy-MM-dd HH:mm:ss")
Else
DTP2.Visible = False
txt2.Visible = True
txtContent2 = txt2.Text
End If
End Sub
'针对查询与日期、时间有关的问题
Private Sub cmbField3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField3.SelectedIndexChanged
If cmbField3.Text = "上机日期" Or cmbField3.Text = "下机日期" Then
DTP3.Format = DateTimePickerFormat.Custom
DTP3.Visible = True
txt3.Visible = False
txtContent3 = Format(DTP3.Value, "yyyy-MM-dd HH:mm:ss")
Else
DTP3.Visible = False
txt3.Visible = True
txtContent3 = txt3.Text
End If
End Sub
End Class