准备敲组合查询的时候,通过上网查资料、看同学们的博客,本菜鸟渐渐地有了思路,但是到具体实践的时候,遇到了令人纠结的“DataTable”和“List(of )”,关于这两个知识点 毫无疑问各有千秋,网上也有一些介绍,通过查资料分析,总的来说还是用泛型比较好(最后有一些链接推荐给大家),所以自然就决定用泛型了,不过好像 历史上咱们的组合查询还没有 有返回泛型的这种情况(或许是我没有百度到)SO here we go
废话完 具体到组合查询,this查询用到了模板方法:将相同的代码提前到父窗体,这样代码被极好的被复用了,这种思想简洁省事效率高而且不易出错,真正的事倍功半,who 想到的?崇拜ing
UI层代码:
<span style="font-family:SimSun;font-size:18px;"> '第一行不能为空
Dim i As Integer
If GroupBox1.Controls(i).Text = "" Then
MessageBox.Show("第一行查询条件不能为空", "温馨提示")
Exit Sub
End If
'如果第一个组合条件不为空
If cmbCombin1.Text <> "" Then
If GroupBox1.Controls(i).Text = "" Or GroupBox2.Controls(i).Text = "" Then
MsgBox("请完善要查询的条件", vbOK + vbExclamation, "温馨提示")
Exit Sub
End If
End If
'如果第二个组合条件不为空
'判断同时 给实体传参
Dim mylist As New Entity.CHECKEntity
mylist.getTable = GetTable() '得到数据表名字
mylist.GetEntity = GetEntity() '得到实体
mylist.CmbName1 = GetEnglish(cmbName1.Text.Trim)
mylist.CmbName2 = GetEnglish(cmbName2.Text.Trim)
mylist.CmbName3 = GetEnglish(cmbName3.Text.Trim)
'选择关系
mylist.CmbRelation1 = cmbRelation1.Text.Trim
mylist.CmbRelation2 = cmbRelation2.Text.Trim
mylist.CmbRelation3 = cmbRelation3.Text.Trim
'要查询的内容
mylist.TxtQuery1 = txtQuery1.Text.Trim
mylist.TxtQuery2 = txtQuery2.Text.Trim
mylist.CmbRelation3 = txtQuery3.Text.Trim
'组合
mylist.CmbCombination1 = GetEnglish(cmbCombin1.Text.Trim)
mylist.CmbCombination2 = GetEnglish(cmbCombin2.Text.Trim)
'定义实体接信息 *result定义的是Objectt类型*
Dim result As Object
Dim facadep As New Facade.CHECKFacade
result = facadep.QueryFacade(mylist)
If result.count = 0 Then
MsgBox("兔子,么有符合记录的信息", , "温馨提示")
Exit Sub
Else
'把泛型信息传到窗体
Call Todgv(result)
End If</span>
在父窗体中我们需要判断文本内容是否为空,感觉一个一个的判断特别费事而且影响效率,所以我用了GroupBOX控件 包起来文本框,这样判断一个顶三个 ——是不是很聪明?(*^__^*) 个人感觉这样比较省事:
UI层虚方法:
下面是父窗体中定义的一些虚方法,需要子窗体具体的去实现:
<span style="font-family:SimSun;font-size:18px;"> '获得数据库中字段的名字
Protected Overridable Function GetEnglish(cmbName As String) As String
Return ""
End Function
'获得数据库中表名字
'在这里给了我思路,既然可以把表名传过来
'为什么不能把实体名传过来呢?so定义了一个GetEntity
Protected Overridable Function GetTable() As String
Return ""
End Function
'获得实体的名字
Protected Overridable Function GetEntity() As Object
Return ""
End Function
'定义DataGridView中用来显示的虚方法
'这里需要做相应的改动,把从数据库中查到的消息作为参数传到相应的子窗体中
Protected Overridable Sub Todgv(ByVal mylist As Object)
End Sub</span>
相应的子窗体UI层:
<span style="font-family:SimSun;font-size:18px;"><pre name="code" class="vb"> '重载获得数据库中表的名字的方法
Protected Overrides Function GetTable() As String
Return "Worklogin"
End Function
'这是工作记录查询窗体,相应的把实体传给父窗体
Protected Overrides Function GetEntity() As Object
Return "Entity.OlineWorkerEntity"
End Function
'重载在datagridview中显示的方法
Protected Overrides Sub Todgv(result As Object)
'将参数作为数据源赋给datagridview
Showall.DataSource = result
Showall.Columns(0).Visible = False
Showall.Columns(0).HeaderText = "工作人员"
Showall.Columns(1).HeaderText = "级别"
Showall.Columns(2).HeaderText = "上机日期"
Showall.Columns(3).HeaderText = "上机时间"
Showall.Columns(4).HeaderText = "下机日期"
Showall.Columns(5).HeaderText = "下机时间"
Showall.Columns(6).HeaderText = "工作电脑"
Showall.Columns(7).HeaderText = "现在状态"
Showall.DefaultCellStyle.Font = New Font("楷体", 16)
Showall.AutoResizeColumns()
Showall.ReadOnly = True’只读
End Sub
</span>
Facade平淡无奇,所以就不写了,但是注意因为返回的类型不确定,所以要把其定义为Object类型,这也是 从“DataTable”到“List(of )”一个关键所在;
重点的BLL层来了
在这里判断实体类型,相对的通过Factory、IDAL,将其传到D层不同的方法中,之前我是把判断放到D层的,但是感觉不太好,毕竟B层是业务逻辑判断嘛,所以D层还是本本分分的好了:
<span style="font-family:SimSun;font-size:18px;"> Public Function QueryAll(ByVal groupCheck As Entity.CHECKEntity) As Object
'动态将entity送给mylist()
Dim iQuery As IDAL.ICHECK
Dim FctQ As New Factory.CHECKFactory
iQuery = FctQ.SelectOnOff
'学生账单
If groupCheck.GetEntity = "Entity.StudentBillEntity" Then
Dim mylist As List(Of Entity.StudentBillEntity)
mylist = iQuery.IStudentBill(groupCheck)
Return mylist
'值班老师
ElseIf groupCheck.GetEntity = "Entity.OlineWorkerEntity" Then
Dim mylist As List(Of Entity.OnlineWorkerEntity)
mylist = iQuery.IWorkerCord(groupCheck)
Return mylist
'学生信息
ElseIf groupCheck.GetEntity = "Entity.RegisterEntity" Then
Dim mylist As List(Of Entity.RegisterEntity)
mylist = iQuery.IStudentSmg(groupCheck)
Return mylist
Else
'上机记录
Dim mylist As List(Of Entity.OnlineEntity)
mylist = iQuery.IOnline(groupCheck)
Return mylist
End If
End Function
</span>
Factory、IDAL层 没有什么Change 低调飘过 ~~~~~
D层:
CheckQuery部分的代码 “数据库查询”是 下面四个Function都要用到的,难道要敲四遍吗?No way 不仅低效率而且浪费时间(⊙﹏⊙)b,果断放弃,把他单独提出来,用的时候 调 就好了
<span style="font-family:SimSun;font-size:18px;"> Public Function CheckQuery(groupcheck As Entity.CHECKEntity) As DataTable
Dim sql As String
sql = "select * from " & groupcheck.getTable & " where "
'一个查询条件;
'如果第一个组合条件为空则()
If Trim(groupcheck.CmbCombination1) = "" Then
sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'"
Else
'两个查询条件
If Trim(groupcheck.CmbCombination2) = "" Then
sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'" & groupcheck.CmbCombination1 & " " & groupcheck.CmbName2 & groupcheck.CmbRelation2 & "'" & groupcheck.TxtQuery2 & "'"
'三个条件
Else
sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'" & groupcheck.CmbCombination1 & " " & groupcheck.CmbName2 & groupcheck.CmbRelation2 & "'" & groupcheck.TxtQuery2 & "'" & groupcheck.CmbCombination2 & " " & groupcheck.CmbName3 & groupcheck.CmbRelation3 & "'" & groupcheck.TxtQuery3 & "'"
End If
End If
Dim dt As DataTable
dt = LoginSqlHelper.GetDataTable(sql, CommandType.Text)
Return dt
If dt.Rows.Count = 0 Then
Return Nothing
Exit Function
End If
End Function
'上机记录
Public Function IOnline(groupcheck As CHECKEntity) As List(Of OnlineEntity) Implements IDAL.ICHECK.IOnline
Dim QueryDt As DataTable = CheckQuery(groupcheck)
Dim mylist As List(Of Entity.OnlineEntity)
mylist = DataToList.convertToList(Of Entity.OnlineEntity)(QueryDt)
Return mylist
End Function
'账单记录
Public Function IStudentBill(groupcheck As CHECKEntity) As List(Of StudentBillEntity) Implements IDAL.ICHECK.IStudentBill
Dim QueryDt As DataTable = CheckQuery(groupcheck)
Dim mylist As List(Of Entity.StudentBillEntity)
mylist = DataToList.convertToList(Of Entity.StudentBillEntity)(QueryDt)
Return mylist
End Function
'学生信息
Public Function IStudentSmg(groupcheck As CHECKEntity) As List(Of RegisterEntity) Implements IDAL.ICHECK.IStudentSmg
Dim QueryDt As DataTable = CheckQuery(groupcheck)
Dim mylist As List(Of Entity.RegisterEntity)
mylist = DataToList.convertToList(Of Entity.RegisterEntity)(QueryDt)
Return mylist
End Function
'工作记录
Public Function IWorkerCord(groupcheck As CHECKEntity) As List(Of OnlineWorkerEntity) Implements IDAL.ICHECK.IWorkerCord
Dim QueryDt As DataTable = CheckQuery(groupcheck)
Dim mylist As List(Of Entity.OnlineWorkerEntity)
mylist = DataToList.convertToList(Of Entity.OnlineWorkerEntity)(QueryDt)
Return mylist
End Function</span>
代码复用——COOL吧,这样组合查询基本上就OK了,上图show一下:
界面设计不怎么美观,那是因为秉承着实用的观点,其实是有点懒,嘘~~
组合查询是一个很好的舞台,在UI层用哈希表实现加载Combobox的item既方便又不易出错的好助手;単例模式大家也不防试一试,很不错的知识而且也可以让我们在实践中对他们有更进一步的了解,这里限于篇幅就不写了,机房重构、加油哦!!!
总结:
心情好了效率高而且想做事情 ,所以要保持一个良好的心情
凡事“不将就”,返回DateTabel不太好 那 就试试不返回,“不将就”激发创造力啊,嘻嘻
站在巨人的肩膀上,整个过程也是在看代码研究层与层,层之间的调用关系,探索着做出来的;
补充一点:
datatable和泛型总的来说:
如果是 全部内容 提取的话 可以用datatable
datatable消耗内存,无法直观看出字段数据类型
datatable.rows[0][1],返回DateTable表中第一行第二列的值
datatable比较容易写错,而且编译器不检查,不符合面向对象编程的思想
使用datatable需要对数据库中的表有比较熟悉的了解,这样不便于发挥层间的最大效用
http://bbs.csdn.net/topics/300078386
http://www.2cto.com/kf/201208/145060.html
http://bbs.csdn.net/topics/330143216
THANKS FOR YOUR TIME。希望能给大家带来帮助