在进行组合查询的时候，因为三个组合查询的窗体十分的相似，我们就可以使用模板模式，这样不仅可以减少我们代码的书写量，还可以省去我们不少制作窗体的时间。
首先，我们要先建立一个模板窗体
然后，我们在建立窗体的时候需要选择“继承的窗体”，就可以完美的把模板窗体copy下来了。
我们在父窗体中的代码
Public Class frmFather
'定义一个保护类型的变量，子窗体也可以访问
Protected groupcheck As New Model.GroupModel
Private Sub frmFather_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'操作符部分不变，写在父窗体中
cmbOperator1.Items.Add(">")
cmbOperator1.Items.Add("<")
cmbOperator1.Items.Add("=")
cmbOperator1.Items.Add("<>")
cmbOperator2.Items.Add("<")
cmbOperator2.Items.Add(">")
cmbOperator2.Items.Add("=")
cmbOperator2.Items.Add("<>")
cmbOperator3.Items.Add(">")
cmbOperator3.Items.Add("<")
cmbOperator3.Items.Add("=")
cmbOperator3.Items.Add("<>")
'关系
cmbRelations1.Items.Add("与")
cmbRelations1.Items.Add("或")
cmbRelations2.Items.Add("与")
cmbRelations2.Items.Add("或")
'当选中DataGridView空间选中行时
DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
Dim i As Integer
For i = 0 To DataGridView1.Columns.Count - 1
DataGridView1.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells
Next
End Sub
'定义虚方法GetDBName，获取不同数据库的字段名
Public Overridable Function GetDBName(ByVal control As String) As String
Return ""
End Function
'定义虚函数GetTable，获取不同数据库表名
Protected Overridable Function GetTable() As String
Return ""
End Function
'把表显示到DataGridView中
Protected Overridable Sub Todatagridview()
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.AllCells
End Sub
'当第一个组合关系框的内容发生改变时，对第二行查询框是否可用进行判断
Private Sub cmbRelations1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelations1.SelectedIndexChanged
cmbFiled1.Enabled = True
cmbOperator2.Enabled = True
txtContent2.Enabled = True
cmbRelations2.Enabled = True
End Sub
'当第二个组合关系框的内容发生改变时，对第三行查询框是否可用进行判断
Private Sub cmbRelations2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelations2.SelectedIndexChanged
'对控件选择进行限定
cmbFiled3.Enabled = True
cmbOperator3.Enabled = True
txtContent3.Enabled = True
End Sub
继承的窗体的代码
Public Class frmStuCount
Public Overrides Function GetDBName(control As String) As String
Select Case (control)
Case "卡号"
Return "CardNo"
Case "姓名"
Return "Name"
Case "性别"
Return "Sex"
Case "系别"
Return "Department"
Case "班级"
Return "Class"
Case "与"
Return "and"
Case "或"
Return "or"
Case Else
Return ""
End Select
End Function
Private Sub frmStuCount_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cmbFiled1.Items.Add("卡号")
cmbFiled1.Items.Add("姓名")
cmbFiled1.Items.Add("性别")
cmbFiled1.Items.Add("系别")
cmbFiled1.Items.Add("年级")
cmbFiled1.Items.Add("班级")
cmbFiled2.Items.Add("卡号")
cmbFiled2.Items.Add("姓名")
cmbFiled2.Items.Add("性别")
cmbFiled2.Items.Add("系别")
cmbFiled2.Items.Add("年级")
cmbFiled2.Items.Add("班级")
cmbFiled3.Items.Add("卡号")
cmbFiled3.Items.Add("姓名")
cmbFiled3.Items.Add("性别")
cmbFiled3.Items.Add("系别")
cmbFiled3.Items.Add("年级")
cmbFiled3.Items.Add("班级")
End Sub
Protected Overrides Function GetTable() As String
groupcheck.GetTable = "Student"
Return groupcheck.GetTable
End Function
Protected Overrides Sub Todatagridview()
Dim dt As New DataTable
Dim frmFather As New frmFather
Dim fac As New Facade.GroupCheckFAC
Try
dt = fac.GroupCheck(groupcheck)
If dt.Rows.Count = 0 Then
dt.Clear()
DataGridView1.DataSource = Nothing
DataGridView1.Refresh()
Else
DataGridView1.DataSource = dt
DataGridView1.Columns(0).HeaderText = "卡号"
DataGridView1.Columns(1).HeaderText = "姓名"
DataGridView1.Columns(2).HeaderText = "性别"
DataGridView1.Columns(3).HeaderText = "系别"
DataGridView1.Columns(4).HeaderText = "年级"
DataGridView1.Columns(5).HeaderText = "班级"
DataGridView1.Columns(6).HeaderText = "用户类型"
DataGridView1.Columns(7).HeaderText = "余额"
DataGridView1.Columns(8).HeaderText = "注册日期"
DataGridView1.Columns(9).HeaderText = "注册时间"
DataGridView1.Columns(10).HeaderText = "注册教师"
DataGridView1.Columns(11).HeaderText = "状态"
DataGridView1.Columns(12).HeaderText = "备注"
End If
Catch ex As Exception
MsgBox(ex.Message, vbOKOnly, "提示")
End Try
End Sub
End Class
D层调用存储过程
Public Class GroupCheckDAL : Implements IGroupCheck
'实例化sqlHelper
Private SqlHelper As SQLHelper.SqlHelper = New SQLHelper.SqlHelper()
Public Function IGroupCheck(group As GroupModel) As DataTable Implements IGroupCheck.IGroupCheck
Dim paras As SqlParameter() = {New SqlParameter("@cmbField1", group.CmbField1),
New SqlParameter("@cmbField2", group.CmbField2),
New SqlParameter("@cmbField3", group.CmbField3),
New SqlParameter("@cmbOperator1", group.CmbOperator1),
New SqlParameter("@cmbOperator2", group.CmbOperator2),
New SqlParameter("@cmbOperator3", group.CmbOperator3),
New SqlParameter("@txtContent1", group.TxtContent1),
New SqlParameter("@txtContent2", group.TxtContent2),
New SqlParameter("@txtContent3", group.TxtContent3),
New SqlParameter("@cmbRelation1", group.CmbRelation1),
New SqlParameter("@cmbRelation2", group.CmbRelation2),
New SqlParameter("@tableName", group.GetTable)}
Dim strSql As String = "PROC_GroupQuery" '调用存储过程
Dim dt As New DataTable
dt = SqlHelper.ExecSelect(strSql, CommandType.StoredProcedure, paras)
Return dt
End Function
End Class
数据库中的存储过程
ALTER PROCEDURE [dbo].[PROC_GroupQuery]
(@cmbField1 varchar(50),
@cmbOperator1 varchar(50),
@txtContent1 varchar(50),
@cmbRelation1 varchar(50),
@cmbField2 varchar(50),
@cmbOperator2 varchar(50),
@txtContent2 varchar(50),
@cmbRelation2 varchar(50),
@cmbField3 varchar(50),
@cmbOperator3 varchar(50),
@txtContent3 varchar(50),
@tableName varchar(50))
AS
declare @TempSQL varchar(2000)
BEGIN
SET @TempSQL = 'select * from ' +@tableName + ' where' +char(32)+@cmbField1 +@cmbOperator1 +char(39) +@txtContent1 +char(39)
if @CmbRelation1 != ''
begin
SET @TempSQL =@TempSQL +char(32)+@CmbRelation1 +CHAR(32)+@cmbField2 +@cmbOperator2 +CHAR(39) +@txtContent2 +CHAR(39)
if @CmbRelation2 != ''
begin
SET @TempSQL =@TempSQL +char(32)+@CmbRelation2 +CHAR(32)+@cmbField3 +@cmbOperator3 +CHAR(39) +@txtContent3 +CHAR(39)
end
end
EXECUTE(@TempSQL)
END