还记得第一次做机房的时候做组合查询功能吗？那时候的我从刚开始的一个一个判断，到后面的先是让关系框和后面的条件框不能用，根据条件框的填充情况来决定关系框是否能用。这样一步步的走过来的……
到了第二次的机房重构，我发现之前好多的代码都是重复的，做了那么多的无用功！作为一个优秀的程序员，我们要尽可能的减少自己的代码量，让我们的代码能够复用。这里我们要明白复用可不是复制哦！
在机房中，我们的基本学生信息维护、查看上机状态、上机信息统计和操作员工作记录四个窗体除了字段名不一样以外，几乎一样的，所以我们在做组合查询功能的时候用到了窗体的继承。
窗体的继承就是在父窗体中写好公共的部分，不一致的地方可以写一个虚方法，然后让子类们进行重写这个虚方法。这样，我们就只需要一个U层、一个B层、一个接口和一个抽象工厂，一个D层就可以实现四个窗体各自的功能了。
U层主要用来判断一下各个输入框和选择框是否为空，给实体参数和B层方法传值，定义转换数据库字段的虚方法和获取表名的虚方法。
<span style="font-size:18px;">/*************************************************B层
'作者：邢玉
'小组：
'说明：组合查询模板
'创建日期：2015.8.9
'版本号：
'**********************************************/
Imports System.Windows.Forms
Public Class frmGroupQuery
'定义一个保护类型的变量，子窗体也可以访问，以下是另一种写法
'Protected groupcheck As JFEntity.GroupEntity = New JFEntity.GroupEntity()
Protected groupcheck As New JFEntity.GroupEntity
Private Sub frmGroupQuery_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
''' <summary>
''' 清空查询条件和结果
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btnclear_Click(sender As Object, e As EventArgs) Handles btnclear.Click
cmbFiled1.Text = ""
cmbFiled2.Text = ""
cmbFiled3.Text = ""
cmbOperator1.Text = ""
cmbOperator2.Text = ""
cmbOperator3.Text = ""
txtContent1.Text = ""
txtContent2.Text = ""
txtContent3.Text = ""
cmbRelations1.Text = ""
cmbRelations2.Text = ""
DataGridView1.DataSource = ""
End Sub
''' <summary>
''' 退出
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btncancel_Click(sender As Object, e As EventArgs) Handles btncancel.Click
Me.Dispose()
End Sub
Private Sub btninquire_Click(sender As Object, e As EventArgs) Handles btninquire.Click
'判断组合框不为空
If cmbRelations1.Text = "" Then '如果第一个组合关系框为空
If cmbFiled1.Text = "" Or cmbOperator1.Text = "" Or txtContent1.Text = "" Then
MsgBox("第一行查询条件不能为空！", , "提示")
Exit Sub
End If
End If
If cmbRelations1.Text <> "" Then '如果选择了第一个组合关系
If cmbFiled2.Text = "" Or cmbOperator2.Text = "" Or txtContent2.Text = "" Then
MsgBox("第二行查询条件不能为空！", , "提示")
Exit Sub
End If
Else
If cmbRelations2.Text <> "" Then '选择了第二个组合关系
If cmbFiled1.Text = "" Or cmbOperator1.Text = "" Or txtContent1.Text = "" Or
cmbFiled2.Text = "" Or cmbFiled2.Text = "" Or txtContent2.Text = "" Or
cmbFiled3.Text = "" Or cmbFiled3.Text = "" Or txtContent3.Text = "" Then
MsgBox("第三行查询条件不能为空", , "提示")
Exit Sub
End If
End If
End If
'给实体赋值
groupcheck.GetTable = GetTable()
groupcheck.cmbField1 = GetDBName(cmbFiled1.Text.Trim())
groupcheck.cmbField2 = GetDBName(cmbFiled2.Text.Trim())
groupcheck.cmbField3 = GetDBName(cmbFiled3.Text.Trim())
groupcheck.cmbOperator1 = cmbOperator1.Text.Trim()
groupcheck.cmbOperator2 = cmbOperator2.Text.Trim()
groupcheck.cmbOperator3 = cmbOperator3.Text.Trim()
groupcheck.txtContent1 = txtContent1.Text.Trim()
groupcheck.txtContent2 = txtContent2.Text.Trim()
groupcheck.txtContent3 = txtContent3.Text.Trim()
groupcheck.cmbRelation1 = GetDBName(cmbRelations1.Text.Trim())
groupcheck.cmbRelation2 = GetDBName(cmbRelations2.Text.Trim())
'给外观层方法传递参数
Dim dt As New DataTable
Dim Ugroupcheck As New Facade.GroupCheckFacade
dt = Ugroupcheck.FGroupCheck(groupcheck)
If (dt.Rows.Count = 0) Then
MsgBox("没有符合条件的记录！请重新设置查询条件！", , "提示")
DataGridView1.DataSource = Nothing
Else
'DataGridView1.DataSource = dt
Call Todatagridview()
End If
End Sub
'当第一个组合关系框的内容发生改变时对第二行查询框是否可用进行判断
Private Sub cmbRelations1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelations1.SelectedIndexChanged
cmbFiled2.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
''' <summary>
''' 定义虚函数GetDBName，获取不同数据库的字段名
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Overridable Function GetDBName(ByVal control As String) As String
Return ""
End Function
'定义虚函数GetTable，获取不同数据库的表名
Protected Overridable Function GetTable() As String
Return ""
End Function
''' <summary>
''' 把表显示到datagridview中
''' </summary>
''' <remarks></remarks>
Protected Overridable Sub Todatagridview()
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.AllCells
End Sub
''' <summary>
''' 调用模块中的方法，导出Excel表
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub GroupBox1_Enter(sender As Object, e As EventArgs)
Call ExportExcel(DataGridView1)
End Sub
Private Sub btnExcel_Click(sender As Object, e As EventArgs) Handles btnExcel.Click
Call ExportExcel(DataGridView1)
End Sub
</span>
<span style="font-size:18px;">'/*****************************************
'类 名 称：GroupCheckBLLvb
'命名空间：JFBLL
'创建时间：2015/7/27 16:46:57
'作 者：邢玉
'小 组：
'修改时间：
'修 改 人：
'版 本 号：v1.0.0
’******************************************
Imports IDAL
Imports JFEntity
Public Class GroupCheckBLLvb
''' <summary>实现工厂和接口的方法
''' 组合查询，父窗体
''' </summary>
''' <param name="group"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GroupCheck(ByVal group As JFEntity.GroupEntity) As DataTable
Dim Igroupcheck As IDAL.IGroupCheckDAL
Dim table As New DataTable
Igroupcheck = Factory.LoginFactory.GroupCheck
table = Igroupcheck.IGroupCheck(group)
If table.Rows.Count = 0 Then
' Return Nothing
'Else
Return table
End If
Return table
End Function
End Class
</span>
D层：
<span style="font-size:18px;">'/*****************************************
'类 名 称：SqlGroupCheckDAL
'命名空间：JFDAL
'创建时间：2015/7/27 16:42:43
'作 者：邢玉
'小 组：
'修改时间：
'修 改 人：
'版 本 号：v1.0.0
’******************************************
Imports System.Data.SqlClient
Imports IDAL
Public Class SqlGroupCheckDAL : Implements IGroupCheckDAL
'实例化sqlHelper
Private SqlHelper As SQLHelper.sqlHelper = New SQLHelper.sqlHelper()
Public Function IGroupCheck(ByVal groupcheck As JFEntity.GroupEntity) As DataTable Implements IGroupCheckDAL.IGroupCheck
Dim sqlparam As SqlParameter() = {New SqlParameter("@cmbFiled1", groupcheck.cmbField1),
New SqlParameter("@cmbFiled2", groupcheck.cmbField2),
New SqlParameter("@cmbFiled3", groupcheck.cmbField3),
New SqlParameter("@cmbOperator1", groupcheck.cmbOperator1),
New SqlParameter("@cmbOperator2", groupcheck.cmbOperator2),
New SqlParameter("@cmbOperator3", groupcheck.cmbOperator3),
New SqlParameter("@txtContent1", groupcheck.txtContent1),
New SqlParameter("@txtContent2", groupcheck.txtContent2),
New SqlParameter("@txtContent3", groupcheck.txtContent3),
New SqlParameter("@cmbRelation1", groupcheck.cmbRelation1),
New SqlParameter("@cmbRelation2", groupcheck.cmbRelation2),
New SqlParameter("@tableName", groupcheck.GetTable)} '设置参数
Dim strSql As String = "PROC_GroupQuery" '调用存储过程
table = helper.GetDataTable(strSQL, CommandType.StoredProcedure, prams)
Return table
End Function
End Class</span></span>
存储过程：
<span style="font-size:18px;">-- =============================================
-- Author:邢玉
-- Create date: 2015/7/27
-- Description:组合查询
-- =============================================
ALTER PROCEDURE [dbo].[PROC_GroupQuery]
-- Add the parameters for the stored procedure here
@cmbFiled1 varchar(10),
@cmbOperator1 varchar(10),
@txtContent1 varchar(10),
@cmbFiled2 varchar(10),
@cmbOperator2 varchar(10),
@txtContent2 varchar(10),
@cmbFiled3 varchar(10),
@cmbOperator3 varchar(10),
@txtContent3 varchar(10),
@cmbRelation1 varchar(10),
@cmbRelation2 varchar(10),
@tableName varchar(20)
AS
declare @TempSql varchar(500)--临时存放Sql语句
--BEGIN
--SET @TempSql='SELECT * FROM '+@tableName +'WHERE'+@cmbFiled1 +@cmbOperator1 +char(39)+@txtContent1 +char(39)
--if @cmbRelation1 != ''
--BEGIN
--SET @TempSql=@TempSql +@cmbRelation1 +CHAR(32)+@cmbFiled2 +@cmbOperator2 +CHAR (39)+@txtContent2 +CHAR (39)
--if @cmbRelation2 != ''
--BEGIN
--SET @TempSql=@TempSql +@cmbRelation2 +CHAR (32)+@cmbFiled3 +@cmbOperator3 +CHAR (39)+@txtContent3 +char(39)
--end
--end
--EXECUTE(@TempSql)
BEGIN
SET @TempSql='SELECT * FROM '+@tableName +' WHERE ' +@cmbFiled1 +@cmbOperator1+char(39) + @txtContent1 + char(39)
if (@cmbRelation1 != '')
BEGIN
SET @TempSql=@TempSql+@cmbRelation1+CHAR(32)+@cmbFiled2 +@cmbOperator2+CHAR(39)+@txtContent2+CHAR(39)
if (@cmbRelation2 is not null )
BEGIN
SET @TempSql=@TempSql+@cmbRelation2+CHAR(32)+@cmbFiled3+@cmbOperator3+CHAR(39)+@txtContent3+CHAR(39)
END
END
EXECUTE(@TempSql)
END
</span>
抽象工厂
<span style="font-size:18px;">'/*****************************************编辑完父窗体之后就是要添加我们继承的子窗体了。
'类 名 称：LoginFactory
'命名空间：Factory
'创建时间：2015/6/7 16:18:59
'作 者：邢玉
'小 组：
'修改时间：
'修 改 人：
'版 本 号：v1.0.0
’******************************************
Imports System.Configuration '添加对配置文件的引用
Imports System.Reflection '添加对反射的应用
Imports IDAL
Imports System.Data
''' <summary>
''' 实例化一个JFDAL中的GroupCheck表的类
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GroupCheck() As IGroupCheckDAL
Dim className As String = AssemblyName + "." + db + "GroupCheckDAL"
Dim Igroupcheck As IDAL.IGroupCheckDAL
Igroupcheck = CType(Assembly.Load(AssemblyName).CreateInstance(className), IGroupCheckDAL)
Return Igroupcheck
End Function</span>
第一步：添加继承窗体：
第二步：选择被继承的窗体：
最后在子类中重写父类的虚方法：
<span style="font-size:18px;">/*************************************************我们组合查询的逻辑就是首先只让第一行的条件框可用，关系框和其他的条件框都不可用，只有当第一行的条件框填满的时候，第一个关系框才能用，只有第一个关系框选择了关系之后，第二行的条件框才能用。以此类推……这样我们就省了好多次的判断语句，代码又少了，而且逻辑也清晰了。
'作者：邢玉
'小组：
'说明：组合查询模板
'创建日期：2015.8.9
'版本号：
'**********************************************/
Public Class frmStuInfo
'重写转换成数据库字段的方法
Public Overrides Function GetDBName(control As String) As String
Select Case (control)
Case "卡号"
Return "cardID"
Case "学号"
Return "studentID"
Case "姓名"
Return "studentName"
Case "性别"
Return "sex"
Case "系别"
Return "department"
Case "年级"
Return "grade"
Case "班级"
Return "class"
Case "与"
Return "and"
Case "或"
Return "or"
Case Else
Return ""
End Select
End Function
'重获表名的方法
Protected Overrides Function GetTable() As String
groupcheck.GetTable = "Student_Info"
Return groupcheck.GetTable
End Function
Private Sub frmStuInfo_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 Sub Todatagridview()
Dim table As New DataTable
Dim frmGroupQuery As New frmGroupQuery
Dim FacadeGroupQuery As New Facade.GroupCheckFacade
Try
table = FacadeGroupQuery.FGroupCheck(groupcheck)
If table.Rows.Count = 0 Then
table.Clear()
DataGridView1.DataSource = Nothing
DataGridView1.Refresh()
Else
DataGridView1.DataSource = table
'DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(0).HeaderText = "学号"
DataGridView1.Columns(1).HeaderText = "姓名"
DataGridView1.Columns(2).HeaderText = "系别"
DataGridView1.Columns(3).HeaderText = "系别"
DataGridView1.Columns(4).HeaderText = "年级"
DataGridView1.Columns(5).HeaderText = "班级"
End If
Catch ex As Exception
MsgBox(ex.Message, vbOKOnly, "提示")
End Try
End Sub
End Class
</span>
我们的每一次将就就是在阻止自己的进步，只有不将就才是发现的源动力！