机房重构—组合查询

      在进行组合查询的时候，因为三个组合查询的窗体十分的相似，我们就可以使用模板模式，这样不仅可以减少我们代码的书写量，还可以省去我们不少制作窗体的时间。

      首先，我们要先建立一个模板窗体

 

      然后，我们在建立窗体的时候需要选择“继承的窗体”，就可以完美的把模板窗体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