本文探讨了重构过程中的组合查询问题，强调了窗体继承和模板方法在解决这一问题中的应用。通过窗体继承，可以避免重复代码；模板方法则作为父窗体的模板，定义公共步骤，子窗体则专注于各自独特的实现。文中提供了父窗体的代码示例，展示了如何运用这些技术。
组合查询是重构中的一个重点，也是比较麻烦的，但是我们可以用一个很好的办法来解决它，比如窗体继承，模板方法。关于窗体继承请参考博文：http://blog.youkuaiyun.com/augus3344/article/details/29384877 讲的很详细。这里主要说下模板方法，用这个方法可以为我们省掉很多繁琐的步骤和重复的代码。我们就以父窗体为模板，继承窗体来实现不同的模板，也就是将公共的代码写到模板中，自己特有的代码写到自己的模块，下面看代码。
父窗体的代码
U层代码
<span style="font-size:24px;">ImportsSystem.Windows.Forms
ImportsMicrosoft.Office.Interop.Excel
ImportsMicrosoft.Office.Interop
Public ClassfrmComboQueryUI
Public comboquery As NewEntity.ComboQueryEntity
Public Overridable Subfrmcomboqueryui_Load(sender As Object, e As EventArgs) Handles MyBase.Load
MaximizeBox = False
'将参数传递给实体，赋初值
'因为不同窗体字段不同，所以赋“”，子窗体重写它
comboquery.Field1 = ""
comboquery.Field2 = ""
comboquery.Field3 = ""
'操作符
cboOperator1.Items.Add(">")
cboOperator1.Items.Add("<")
cboOperator1.Items.Add("=")
cboOperator1.Items.Add("<>")
cboOperator2.Items.Add(">")
cboOperator2.Items.Add("<")
cboOperator2.Items.Add("=")
cboOperator2.Items.Add("<>")
cboOperator3.Items.Add(">")
cboOperator3.Items.Add("<")
cboOperator3.Items.Add("=")
cboOperator3.Items.Add("<>")
'关系
cboRelation1.Items.Add("与")
cboRelation1.Items.Add("或")
cboRelation2.Items.Add("与")
cboRelation2.Items.Add("或")
'窗体加载后，后两组控件默认不可用
cboField2.Enabled = False
cboOperator2.Enabled = False
txtContent2.Enabled = False
cboRelation2.Enabled = False
cboField3.Enabled = False
cboOperator3.Enabled = False
txtContent3.Enabled = False
'设置选中单元格就选中行
DataGridView1.SelectionMode =DataGridViewSelectionMode.FullRowSelect
Dim i As Integer
For i = 0 ToDataGridView1.Columns.Count - 1
DataGridView1.Columns(i).Width =DataGridViewAutoSizeColumnsMode.AllCells
Next
End Sub
Public Overridable SubbtnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
'判断组合框不为空
If cboRelation1.Text = ""Then
If cboField1.Text = "" OrcboOperator1.Text = "" Or txtContent1.Text = "" Then
MsgBox("第一行查询条件不能为空，请完善查询信息！", CType(vbOKOnly +MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
End If
If cboRelation1.Text <>"" Then
If cboField1.Text = "" OrcboOperator1.Text = "" Or txtContent1.Text = "" Or
cboField1.Text = ""Or cboOperator2.Text = "" Or txtContent2.Text = "" Then
MsgBox("第二行查询条件不能为空，请完善查询信息！", CType(vbOKOnly +MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
Else
If cboRelation2.Text <>"" Then
If cboField1.Text ="" Or cboOperator1.Text = "" Or txtContent1.Text ="" Or
cboField2.Text = ""Or cboOperator2.Text = "" Or txtContent2.Text = "" Or
cboField3.Text = ""Or cboOperator3.Text = "" Or txtContent3.Text = "" Then
MsgBox("第三行查询条件不能为空，请完善查询信息！", CType(vbOKOnly +MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
Exit Sub
End If
End If
End If
'将参数传给实体
comboquery.dbName = GetdbName()
comboquery.Field1 =ToEnglish(cboField1.Text)
comboquery.Field2 =ToEnglish(cboField2.Text)
comboquery.Field3 =ToEnglish(cboField3.Text)
comboquery.Operator1 =cboOperator1.Text.Trim
comboquery.Operator2 =cboOperator2.Text.Trim
comboquery.Operator3 =cboOperator3.Text.Trim
comboquery.Content1 =txtContent1.Text.Trim
comboquery.Content2 =txtContent2.Text.Trim
comboquery.Content3 =txtContent3.Text.Trim
comboquery.Relation1 =ToEnglish(cboRelation1.Text)
comboquery.Relation2 =ToEnglish(cboRelation2.Text)
'给B层ComboQuery方法传递参数
Dim cboll As New BLL.ComboQueryBLL
'为了将dt类型转化为泛型
Dim myquery As New Entity.MyQueryEntity
'如果没有返回结果
If cboll.ComboQuery(comboquery) IsNothing Then
MsgBox("没有记录，请重新设置查询条件",vbOKOnly, vbExclamation)
DataGridView1.DataSource = Nothing
Else
'将结果返回给myquery实体
myquery =cboll.ComboQuery(comboquery)
'如果连接的是Linelog表，DataGridView1和LineEntity匹配
If comboquery.dbName ="Linelog" Then
DataGridView1.DataSource =myquery.LineEntity
'设置标题行
DataGridView1.Columns(0).HeaderText = "卡号"
DataGridView1.Columns(1).Visible = False
DataGridView1.Columns(2).Visible = False
DataGridView1.Columns(3).Visible = False
DataGridView1.Columns(4).Visible = False
DataGridView1.Columns(5).Visible = False
DataGridView1.Columns(6).Visible = False
DataGridView1.Columns(7).HeaderText = "消费时间"
DataGridView1.Columns(8).HeaderText = "消费金额"
DataGridView1.Columns(9).Visible = False
DataGridView1.Columns(10).HeaderText = "上机时间"
DataGridView1.Columns(11).HeaderText = "上机日期"
DataGridView1.Columns(12).HeaderText = "下机时间"
DataGridView1.Columns(13).HeaderText = "下机日期"
DataGridView1.Columns(14).HeaderText = "机器名"
Else
'如果连接的是v_studentinfo表，DataGridView1和RegisterEntity匹配
If comboquery.dbName ="v_studentinfo" Then
DataGridView1.DataSource =myquery.RegisterEntity
'设置标题行
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).Visible = False
DataGridView1.Columns(9).Visible = False
DataGridView1.Columns(10).Visible = False
DataGridView1.Columns(11).Visible = False
Else
'如果连接的是WorklogEntity表，DataGridView1和WorklogEntity匹配
DataGridView1.DataSource =myquery.WorklogEntity
'设置标题行
DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(1).HeaderText = "教师号"
DataGridView1.Columns(2).HeaderText = "机器名"
DataGridView1.Columns(3).Visible = False
DataGridView1.Columns(4).HeaderText = "上机时间"
DataGridView1.Columns(5).HeaderText = "上机日期"
DataGridView1.Columns(6).HeaderText = "下机时间"
DataGridView1.Columns(7).HeaderText = "下机日期"
End If
End If
End If
End Sub
''' <summary>
''' 模板方法，定义虚函数ToEnglish，查询字段转化为数据库字段
''' </summary>
''' <paramname="cboName"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Overridable FunctionToEnglish(cboName As String) As String
Return ""
End Function
''' <summary>
'''模版方法，获得数据库表名
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Overridable Function GetdbName() AsString
Return ""
End Function
''' <summary>
''' 第一个组合关系不为空
''' </summary>
''' <paramname="sender"></param>
''' <paramname="e"></param>
''' <remarks></remarks>
Protected SubcboRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) HandlescboRelation1.SelectedIndexChanged
If cboRelation1.Text = ""Then
cboField2.Enabled = False
cboField3.Enabled = False
cboOperator2.Enabled = False
cboOperator3.Enabled = False
cboRelation2.Enabled = False
txtContent2.Enabled = False
txtContent3.Enabled = False
Else
cboField2.Enabled = True
cboOperator2.Enabled = True
txtContent2.Enabled = True
cboRelation2.Enabled = True
End If
End Sub
''' <summary>
''' 第二个组合关系不为空
''' </summary>
''' <paramname="sender"></param>
''' <paramname="e"></param>
''' <remarks></remarks>
Protected SubcboRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) HandlescboRelation2.SelectedIndexChanged
If cboRelation2.Text = ""Then
cboField3.Enabled = False
cboOperator3.Enabled = False
txtContent3.Enabled = False
Else
cboField3.Enabled = True
cboOperator3.Enabled = True
txtContent3.Enabled = True
End If
End Sub
''' <summary>
''' 导出到excel
''' </summary>
''' <paramname="sender"></param>
''' <paramname="e"></param>
''' <remarks></remarks>
Public Overridable SubbtnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
'打开Excel的应用程序
Dim ExcelADD As New Excel.Application()
'添加一个工作本
Dim ExcelWordBook As Excel.Workbook =ExcelADD.Workbooks.Add()
'添加一个表
Dim ExcelWorkSheet As Excel.Worksheet =ExcelWordBook.Sheets("sheet1")
'显示Excel
ExcelADD.Visible = True
'用于将DataGridView中的表赋值到Excel中的表中
Dim i As Integer
Dim j As Integer
'将DataGridView中的字段名赋值给Excel表中的第一行
Dim Cols As Integer
For Cols = 1 ToDataGridView1.Columns.Count
ExcelWorkSheet.Cells(1, Cols) =DataGridView1.Columns(Cols - 1).HeaderText
Next
'将DataGridView表格中的内容导入到Excel表中
For i = 0 To DataGridView1.RowCount - 1
'DataGrideView中的表头行不作为行数来计算
For j = 0 ToDataGridView1.ColumnCount - 1
'Excel的第一行是标题，所以应从第二行开始，i+2注意DataGridView的坐标是先列后行
ExcelWorkSheet.Cells(i + 2, j +1) = DataGridView1(j, i).Value.ToString()
Next
Next
End Sub
''' <summary>
''' 清除窗体内容
''' </summary>
''' <paramname="sender"></param>
''' <paramname="e"></param>
''' <remarks></remarks>
Public Overridable SubbtnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
Call ClearAll(Me)
DataGridView1.DataSource = Nothing
End Sub
End Class</span>B层代码传递
<span style="font-size:24px;">Public ClassComboQueryBLL
''' <summary>
''' 传递组合查询的条件
''' </summary>
''' <paramname="encboquery"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ComboQuery(ByVal encboqueryAs Entity.ComboQueryEntity) As Entity.MyQueryEntity
Dim DataAccess As New DAL.DataAccess
Dim IComboQuery As IDAL.IComboQuery
'定义一个接口变量
IComboQuery =DataAccess.CreateComboQuery
'得到实际的数据库访问实例
Dim encbo =IComboQuery.ComboQuery(encboquery)
If encbo Is Nothing Then
Return Nothing
Else
Return encbo
End If
End Function
End Class</span>D层完成与数据库的交互，以及将datatable转化为泛型
<span style="font-size:24px;">ImportsSystem.Data.SqlClient
Imports IDAL
Imports Entity
Public ClassComboQueryDAL : Implements IDAL.IComboQuery
''' <summary>
''' 组合查询
''' </summary>
''' <paramname="encboquery">要查询的实体</param>
''' <returns>信息表</returns>
''' <remarks></remarks>
Public Function ComboQuery(encboquery AsEntity.ComboQueryEntity) As MyQueryEntity ImplementsIDAL.IComboQuery.ComboQuery
'Dim mylist As New List(OfEntity.ComboQueryEntity)
Dim strSQL As String ="P_ComboQuery" '调用存储过程
'设置参数
Dim prams As SqlParameter() = {NewSqlParameter("@cmbField1", encboquery.Field1),
NewSqlParameter("@cmbField2", encboquery.Field2),
NewSqlParameter("@cmbField3", encboquery.Field3),
NewSqlParameter("@cmbOperation1", encboquery.Operator1),
NewSqlParameter("@cmbOperation2", encboquery.Operator2),
NewSqlParameter("@cmbOperation3", encboquery.Operator3),
NewSqlParameter("@txtContent1", encboquery.Content1),
NewSqlParameter("@txtContent2", encboquery.Content2),
NewSqlParameter("@txtContent3", encboquery.Content3),
NewSqlParameter("@cmbRelation1", encboquery.Relation1),
NewSqlParameter("@cmbRelation2", encboquery.Relation2),
NewSqlParameter("@dbName", encboquery.dbName)}
Dim helper As New SqlHelper
Dim dbcboquery =helper.ExecuteSelect(strSQL, CommandType.StoredProcedure, prams)
'mylist = conEntity.convertToList(OfEntity.ComboQueryEntity)(dbcboquery)
If encboquery.dbName ="Linelog" Then
Return MyReturn2(dbcboquery) '转换为实体类型
Else
If encboquery.dbName ="v_studentinfo" Then
ReturnMyReturn3(dbcboquery) '转换为实体类型
Else
ReturnMyReturn1(dbcboquery) '转换为实体类型
End If
End If
End Function
''' <summary>
''' 教师工作，选择返回的Datatable，转换成泛型集合
''' </summary>
''' <paramname="dt">组合查询，查询的信息</param>
Public Function MyReturn1(dt As DataTable)As MyQueryEntity Implements IComboQuery.MyReturn1
Dim mylist As List(OfEntity.WorklogEntity)
mylist =Entity.conEntity.convertToList(Of Entity.WorklogEntity)(dt)
Dim MyQueryList As New MyQueryEntity
MyQueryList.WorklogEntity = mylist
Return MyQueryList
End Function
''' <summary>
''' 学生上机记录统计，选择返回的Datatable，转换成泛型集合
''' </summary>
''' <paramname="dt">组合查询，查询的信息</param>
Public Function MyReturn2(dt As DataTable)As MyQueryEntity Implements IComboQuery.MyReturn2
Dim mylist As List(OfEntity.LinelogEntity)
mylist =Entity.conEntity.convertToList(Of Entity.LinelogEntity)(dt)
Dim MyQueryList As New MyQueryEntity
MyQueryList.LineEntity = mylist
Return MyQueryList
End Function
''' <summary>
''' 学生基本信息维护，选择返回的Datatable，转换成泛型集合
''' </summary>
''' <paramname="dt">组合查询，查询的信息</param>
Public Function MyReturn3(dt As DataTable)As MyQueryEntity Implements IComboQuery.MyReturn3
Dim mylist As List(OfEntity.RegisterEntity)
mylist =Entity.conEntity.convertToList(Of Entity.RegisterEntity)(dt)
Dim MyQueryList As New MyQueryEntity
MyQueryList.RegisterEntity = mylist
Return MyQueryList
End Function
End Class</span>数据库存储过程
<span style="font-size:24px;">USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[p_ComboQuery] Script Date: 02/15/2015 21:12:33 ******/
SET ANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
ALTER proc[dbo].[p_ComboQuery]
@cmbField1 varchar(10),
@cmbOperation1 varchar(10),
@txtContent1 varchar(10),
@cmbField2 varchar(10),
@cmbOperation2 varchar(10),
@txtContent2 varchar(10),
@cmbField3 varchar(10),
@cmbOperation3 varchar(10),
@txtContent3 varchar(10),
@cmbRelation1 varchar(10),
@cmbRelation2 varchar(10),
@dbName varchar(20)
AS
declare @TempSqlvarchar(500)--临时存放sql语句
BEGIN
SET @TempSql='SELECT * FROM '+@dbName +'WHERE ' +@cmbField1 +@cmbOperation1+char(39) + @txtContent1 + char(39)
if @cmbRelation1 != ''
BEGIN
SET@TempSql=@TempSql+@cmbRelation1+CHAR(32)+@cmbField2+@cmbOperation2+CHAR(39)+@txtContent2+CHAR(39)
if @cmbRelation2!= ''
BEGIN
SET@TempSql=@TempSql+@cmbRelation2+CHAR(32)+@cmbField3+@cmbOperation3+CHAR(39)+@txtContent3+CHAR(39)
END
END
EXECUTE(@TempSql)
end</span>模板的好处就是在于只是父窗体去走大的框架，子窗体去特殊的实现，其实子窗体只需要完成U层的一些补充即可，下面以一个子窗体的U层代码举例
<span style="font-size:24px;">Public ClassfrmSaveStudentUI
Private Sub frmSaveStudentUI_Load(sender AsObject, e As EventArgs) Handles MyBase.Load
cboField1.Items.Add("学号")
cboField1.Items.Add("卡号")
cboField1.Items.Add("姓名")
cboField1.Items.Add("性别")
cboField1.Items.Add("年级")
cboField1.Items.Add("班级")
cboField1.Items.Add("专业")
cboField2.Items.Add("学号")
cboField2.Items.Add("卡号")
cboField2.Items.Add("姓名")
cboField2.Items.Add("性别")
cboField2.Items.Add("年级")
cboField2.Items.Add("班级")
cboField2.Items.Add("专业")
cboField3.Items.Add("学号")
cboField3.Items.Add("卡号")
cboField3.Items.Add("姓名")
cboField3.Items.Add("性别")
cboField3.Items.Add("年级")
cboField3.Items.Add("班级")
cboField3.Items.Add("专业")
End Sub
''' <summary>
''' 把加载的汉字转换成数据库的字段
''' </summary>
''' <param name="cbofield"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Overrides FunctionToEnglish(cbofield As String) As String
Select Case cbofield
Case "学号"
ToEnglish ="studentno"
Case "卡号"
ToEnglish = "cardno"
Case "姓名"
ToEnglish = "sname"
Case "性别"
ToEnglish = "Sex"
Case "年级"
ToEnglish = "Grade"
Case "班级"
ToEnglish = "Class"
Case "专业"
ToEnglish ="Department"
Case "与"
ToEnglish = "and"
Case "或"
ToEnglish = "or"
Case Else
ToEnglish = ""
End Select
End Function
'重写获得表名方法
Public Overrides Function GetdbName() AsString
Return "v_studentinfo"
End Function
End Class</span>只要完成了窗体继承，这些都很方便，真正做到了以一比三，何乐而不为呢？但是组合查询这里大家要注意的是转化为泛型的时候会遇到一些问题，以往的转化都是在D层调用一个函数就好了，但是这次不太一样，我们会发现转化完之后显示出来的是字段。原因就是我们的datatable中字段和实体字段不相符，解决方法就是在组合窗体实体中引入继承窗体的实体。就是我父窗体U层中引入的MyQueryEntity实体，D层的三个特殊函数。下面是我MyQueryEntity代码：
<span style="font-size:24px;"> Public ClassMyQueryEntity
'转化为LinelogEntity
Private strLinelogEntity As List(OfLinelogEntity)
Public Property LineEntity() As List(OfLinelogEntity)
Get
Return strLinelogEntity
End Get
Set(ByVal value As List(OfLinelogEntity))
strLinelogEntity = value
End Set
End Property
'转化为RegisterEntity
Private strRegisterEntity As List(OfRegisterEntity)
Public Property RegisterEntity() As List(OfRegisterEntity)
Get
Return strRegisterEntity
End Get
Set(ByVal value As List(OfRegisterEntity))
strRegisterEntity = value
End Set
End Property
'转化为WorklogEntity
Private strWorklogEntity As List(OfWorklogEntity)
Public Property WorklogEntity() As List(OfWorklogEntity)
Get
Return strWorklogEntity
End Get
Set(ByVal value As List(OfWorklogEntity))
strWorklogEntity = value
End Set
End Property
End Class
</span>师傅说以后我们都会用到转化泛型，泛型的好处很多，以后我们会慢慢的体会到，现在的公司也严格要求用泛型，以后就是泛型的天下了，现在我们就遇到问题解决问题，以后的工作就会轻松很多啊！
扫一扫
1463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
