机房重构组合查询之模板模式

前言：

　　前人的经验让我们少走了很多的弯路。机房收费系统四大核心：组合查询，结账，上下机，报表。 从第一遍收费系统中我们就知道，很多窗体都一样，而且要实现的也是差不多的功能，所以在我们学了模板模式之后，组合查询这一方面的问题就可以得到很好的处理了。模板模式的使用大大的减少了我们的代码量。

内容：

model 层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Public Class InquireMotherModel    Private _cmbName1 As String 'd定义字段名     Private _cmbName2 As String    Private _cmbName3 As String    Private _cmbOper1 As String '定义操作符    Private _cmbOper2 As String    Private _cmbOper3 As String    Private _txtInquire1 As String    Private _txtInquire2 As String '定义查询内容    Private _txtInquire3 As String    Private _cmbShip1 As String    Private _cmbShip2 As String '定义组合关系    Private _cmbShip3 As String    Private _Dbname As String '定义 数据库名    Public Property Dbname As String        Get            Return _Dbname        End Get        Set(value As String)            _Dbname = value        End Set    End Property    Public Property cmbName1 As String        Get            Return _cmbName1        End Get        Set(value As String)            _cmbName1 = value        End Set    End Property    Public Property cmbName2 As String        Get            Return _cmbName2        End Get        Set(value As String)            _cmbName2 = value        End Set    End Property    Public Property cmbName3 As String        Get            Return _cmbName3        End Get        Set(value As String)            _cmbName3 = value        End Set    End Property    Public Property cmbOper1 As String        Get            Return _cmbOper1        End Get        Set(value As String)            _cmbOper1 = value        End Set    End Property    Public Property cmbOper2 As String        Get            Return _cmbOper2        End Get        Set(value As String)            _cmbOper2 = value        End Set    End Property    Public Property cmbOper3 As String        Get            Return _cmbOper3        End Get        Set(value As String)            _cmbOper3 = value        End Set    End Property    Public Property txtInquire1 As String        Get            Return _txtInquire1        End Get        Set(value As String)            _txtInquire1 = value        End Set    End Property    Public Property txtInquire2 As String        Get            Return _txtInquire2        End Get        Set(value As String)            _txtInquire2 = value        End Set    End Property    Public Property txtInquire3 As String        Get            Return _txtInquire3        End Get        Set(value As String)            _txtInquire3 = value        End Set    End Property    Public Property cmbShip1 As String        Get            Return _cmbShip1        End Get        Set(value As String)            _cmbShip1 = value        End Set    End Property    Public Property cmbShip2 As String        Get            Return _cmbShip2        End Get        Set(value As String)            _cmbShip2 = value        End Set    End PropertyEnd Class</span>

IDAL层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Imports ModelPublic Interface IinquireMother    '增加查询的接口    Function CheckInquire(ByVal userinfo As Model.InquireMotherModel) As DataTableEnd Interface</span>

Facatory 层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;"> '创建组合查询的接口    Public Function InquireMother() As IDAL.IinquireMother        Return CType(Assembly.Load("DAL").CreateInstance("DAL.InquireMotherDAL"), IDAL.IinquireMother)    End Function</span>

DAL层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Imports ModelImports IDALImports SQLHelperImports System.Data.SqlClientPublic Class InquireMotherDAL : Implements IDAL.IinquireMother    Dim shelper As New SQLHelper.SqlHelper    Public Function CheckInquire(userinfo As InquireMotherModel) As DataTable Implements IinquireMother.CheckInquire        Dim cmdText As String        Dim dt As DataTable        cmdText = "select * from " & userinfo.Dbname & " where "        '只有一个查询条件        If Trim(userinfo.cmbShip1) = "" Then            cmdText = cmdText & userinfo.cmbName1 & userinfo.cmbOper1 & "'" & userinfo.txtInquire1 & "'"        Else            '有两组查询条件            If Trim(userinfo.cmbShip2) = "" Then                cmdText = cmdText & userinfo.cmbName1 & userinfo.cmbOper1 & "'" & userinfo.txtInquire1 & "'" & userinfo.cmbShip1 & " " & userinfo.cmbName2 & userinfo.cmbOper2 & "'" & userinfo.txtInquire2 & "'"            Else                cmdText = cmdText & userinfo.cmbName1 & userinfo.cmbOper1 & "'" & userinfo.txtInquire1 & userinfo.cmbShip1 & " " & userinfo.cmbName2 & userinfo.cmbOper2 & "'" & userinfo.txtInquire2 & "'" & userinfo.cmbShip2 & " " & userinfo.cmbName3 & userinfo.cmbOper3 & "'" & userinfo.txtInquire3 & "'"            End If        End If        dt = shelper.ExecSelect(cmdText, CommandType.Text)        Return dt    End FunctionEnd Class</span>

BLL层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Imports ModelImports FactoryImports IDALPublic Class InquireMotherBLL    '查询    Public Function CheckInquireMother(ByVal userinfo As Model.InquireMotherModel) As DataTable        Dim fac As New Factory.FactoryDB        Dim icheck As IDAL.IinquireMother        Dim myList As DataTable        icheck = fac.InquireMother        myList = icheck.CheckInquire(userinfo)        Return myList    End FunctionEnd Class</span>

Facade层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Imports BLLImports ModelPublic Class InquireMotherFacade    '查询    Function InquireMother(ByVal userinfo As Model.InquireMotherModel) As DataTable        Dim inquire1 As New BLL.InquireMotherBLL        Dim myList As DataTable        myList = inquire1.CheckInquireMother(userinfo)        Return myList    End FunctionEnd Class</span>

U层

<span style="font-family:KaiTi_GB2312;font-size:18px;color:#333333;">Imports ModelImports Microsoft.Office.Interop.ExcelImports Microsoft.Office.InteropImports System.DataImports System.IOImports System.Windows.FormsPublic Class frminquireMother    Protected InquireMother As New Model.InquireMotherModel    ' 模板方法，定义函数ToEnglish，查询字段转化为数据库字段    Public Overridable Function GetEnglish(cmbName As String) As String        Return ""    End Function    '获取数据库表名    Protected Overridable Function GetdbName() As String        Return ""    End Function    '把表显示到datagridiew中    Protected Overridable Sub Todgv(ByVal inquireMother As InquireMotherModel)    End Sub    '拼接字符串    Public Function Query(frm As frminquireMother, ByVal inquireMother As InquireMotherModel) As String        Dim cmdText As String = "" & frm.GetEnglish(frm.cmbName1.Text) & frm.cmbOper1.Text & "" & inquireMother.txtInquire1 & "'"        '非组合查询        If frm.cmbShip1.Text = "" Then            cmdText = cmdText        Else            '关系1为空时，关系2不为空            If frm.cmbShip1.Text <> "" Then                cmdText = cmdText & frm.GetEnglish(frm.cmbShip1.Text) & "" &         frm.GetEnglish(frm.cmbName2.Text) & frm.cmbOper2.Text & "'" & frm.txtInquire2.Text & "'"            Else                '关系1关系2 都不为空                cmdText = cmdText & frm.GetEnglish(frm.cmbShip1.Text) & "" &         frm.GetEnglish(frm.cmbName2.Text) & frm.cmbOper2.Text & "'" & frm.txtInquire2.Text & "'" & "" &         frm.GetEnglish(frm.cmbShip2.Text) & "" &         frm.GetEnglish(frm.cmbName3.Text) & frm.cmbOper3.Text & "'" & frm.txtInquire3.Text & "'"            End If        End If        Return cmdText    End Function    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click '点击查询按钮        ' Try        '判断组合框不为空        If cmbShip1.Text = "" Then            If Trim(cmbName1.Text) = "" Or Trim(cmbOper1.Text) = "" Or Trim(txtInquire1.Text) = "" Then                MsgBox("请完善第一行的查询条件", 0, "温馨提示")                Exit Sub            End If        End If        If cmbShip1.Text <> "" Then '如果组合关系不为空            If cmbName1.Text = "" Or cmbOper1.Text = "" Or txtInquire1.Text = "" Or cmbName2.Text = "" Or cmbOper2.Text = "" Or txtInquire2.Text = "" Then '那么第二行和第一行的所有都要填满 否则不能进行查询                MsgBox("请完善查询条件", 0, "温馨提示")                Exit Sub            End If        End If        If cmbShip2.Text <> "" Then '如果第二个组合关系部位空            If cmbName1.Text = "" Or cmbOper1.Text = "" Or txtInquire1.Text = "" Or cmbName2.Text = "" Or cmbOper2.Text = "" Or txtInquire2.Text = "" Or cmbName3.Text = "" Or cmbOper3.Text = "" Or txtInquire3.Text = "" Then '那么三个条件都得填满                MsgBox("所有查询条件不能为空，请完善查询条件", 0, "温馨提示")                Exit Sub            End If        End If        '将参数传给实体        InquireMother.Dbname = GetdbName() '获取数据库名称        InquireMother.cmbName1 = GetEnglish(cmbName1.Text)        InquireMother.cmbName2 = GetEnglish(cmbName2.Text)        InquireMother.cmbName3 = GetEnglish(cmbName3.Text)        InquireMother.cmbOper1 = cmbOper1.Text.Trim        InquireMother.cmbOper2 = cmbOper2.Text.Trim        InquireMother.cmbOper3 = cmbOper3.Text.Trim        '查询时非数字要加上‘’        If IsNumeric(txtInquire1.Text) Then            InquireMother.txtInquire1 = txtInquire1.Text.Trim        Else            InquireMother.txtInquire1 = "'" & txtInquire1.Text.Trim & "'"        End If        If IsNumeric(txtInquire2.Text) Then            InquireMother.txtInquire2 = txtInquire2.Text.Trim        Else            InquireMother.txtInquire2 = "'" & txtInquire2.Text.Trim & "'"        End If        If IsNumeric(txtInquire3.Text) Then            InquireMother.txtInquire3 = txtInquire3.Text.Trim        Else            InquireMother.txtInquire3 = "'" & txtInquire3.Text.Trim & "'"        End If        InquireMother.cmbShip1 = GetEnglish(cmbShip1.Text)        InquireMother.cmbShip2 = GetEnglish(cmbShip2.Text)        '查找记录        Dim dt As New Data.DataTable        Dim facadeGeneral As New Facade.InquireMotherFacade        dt = facadeGeneral.InquireMother(InquireMother)        If dt.Rows.Count = 0 Then            MsgBox("没有符合条件的记录", 0, "温馨提示")        Else            '把表显示到dgv中            Call Todgv(InquireMother)        End If        'Catch ex As Exception        ' MsgBox(ex.Message)        ' End Try    End Sub    Private Sub frminquireMother_Load(sender As Object, e As EventArgs) Handles MyBase.Load        '将参数传递给实体，赋初值        '字段名        InquireMother.cmbName1 = ""        InquireMother.cmbName2 = ""        InquireMother.cmbName3 = ""        '操作符        cmbOper1.Items.Add(">")        cmbOper1.Items.Add("=")        cmbOper1.Items.Add("<")        cmbOper1.Items.Add("<>")        cmbOper2.Items.Add(">")        cmbOper2.Items.Add("=")        cmbOper2.Items.Add("<")        cmbOper2.Items.Add("<>")        cmbOper3.Items.Add(">")        cmbOper3.Items.Add("=")        cmbOper3.Items.Add("<")        cmbOper3.Items.Add("<>")        '关系        cmbShip1.Items.Add("与")        cmbShip2.Items.Add("或")        '窗体加载后第二行和第三行的查询不能用        cmbName2.Enabled = False        cmbName3.Enabled = False        cmbOper2.Enabled = False        cmbOper3.Enabled = False        cmbShip2.Enabled = False        txtInquire2.Enabled = False        txtInquire3.Enabled = False        '调整列宽为根据内容自动调节        Dim i As Integer        For i = 0 To dgvRecord.Columns.Count - 1            dgvRecord.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells        Next    End Sub    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click '导出excel        '先添加引用才能用office        Dim myExcel As New Microsoft.Office.Interop.Excel.Application()        myExcel.Application.Workbooks.Add(True)        myExcel.Visible = True        '取出dgvRecord的编号列        Dim m As Integer        For m = 0 To dgvRecord.ColumnCount - 1            myExcel.Cells(1, m + 1) = Me.dgvRecord.Columns(m).HeaderText        Next m        '往Excel表里添加数据        Dim i As Integer        For i = 0 To dgvRecord.ColumnCount - 1            Dim j As Integer            For j = 0 To dgvRecord.ColumnCount - 1                If Me.dgvRecord(j, i).Value Is System.DBNull.Value Then                    myExcel.Cells(i + 2, j + 1) = ""                Else                    myExcel.Cells(i + 2, j + 1) = dgvRecord(j, i).Value.ToString                End If            Next j        Next i    End Sub    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click '退出        Me.Close()    End SubEnd Class</span>

总结：

  model是一个大的实体，里面包括了模板上所有控件的属性。按照七层的思想搭建一条线，作为模板模式的主线，而继承的子窗体重写主线上的一些方法就能实现模板模式的调用。这样只需要写一条线 就可以让很多的窗体都来调用。减少了代码的重复。提高了效率。