VB.NET版机房重构----模板方法+存储过程实现组合查询

自己经历了几天画图的郁闷期以后，就开始编写代码！这个编写代码的一路是顺风顺水，没有什么起伏，以至于让我忘了后边还有组合查询，上下机，结账，这三个大难题！这不，就在组合查询这里遇到了一个“大问题”！问题就是如图：（我的数据库中有学号为1234和1205两条记录，但是显示的却只有一条记录！）

我的存储过程是这么写的：

alter PROCEDURE [dbo].[proc_StudentBInfo] 

@Fields1 varchar(50),

@Operators1 varchar(50),  

 @Check1 varchar(50),  

 @Relation1 varchar(50),  

 @Fields2 varchar(50),  

 @Operators2 varchar(50),  

 @Check2 varchar(50),  

 @Relation2 varchar(50),  

 @Fields3 varchar(50),  

 @Operators3 varchar(50),  

 @check3 varchar(50),

 @listName varchar(20)

 

AS

declare @strSQL varchar(500)

BEGIN

    SET @strSQL ='select * from '+@listName+char(32)+'where'+char(32)+@Fields1+@Operators1+char(39)+@Check1+char(39)+char(32)

    if (@Relation1<>null)

    begin

    set @strSQL =@strSQL+char(32)+@Relation1+char(32) +@Fields2+@Operators2+char(39)+@Check2+char(39)+char(32)

        if @Relation2 <>null

        begin

        set @strSQL =@strSQL +@Relation2+char(32) +@Fields3 +@Operators3+char(32) +@check3+char(32)

    end 

    end

    EXECUTE(@strSQL) 

END

无论怎么修改存储过程，它都不会出现两条记录，十期的两个师哥给我调试代码的时候，觉得就是这个存储过程中的代码写错了。于是用了一种数据库的调试方法，就是先给条件赋值，再运行调试，具体如图

也就是调试你查询的那个表并根据需要赋值，然后进行逐语句调试，找出问题在哪里！这个方法真是让我长了见识。
正确的存储过程为：

大家看出不同来了吧！那为什么用“ ‘ ’ ”就可以而用null就不可以呢？我经过查询得到的回答是：在T-SQL命令中，判断一个值是不是空值，要用IS NULL ，而不是用“=”代替“IS”。这个需要进一步证实，大家可以参考这个网页：http://www.dzwebs.net/3338.html

好了存储过程写好了，就等着代码了（以查询学生信息为例）：

父窗体的代码：

Public Class frmMDICombination    Protected enCombinationStudent As New Entity.CombinationQueryEntity()    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click        DataGridView1.DataSource = Nothing        '第一个组合关系为空，第一行不能为空          If ComboBox7.Text = "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(2)            arrayControl(0) = ComboBox1            arrayControl(1) = ComboBox4            arrayControl(2) = TextBox1            If ComboBox1.Text = "" Then                MessageBox.Show("请输入字段!")                Exit Sub            ElseIf ComboBox4.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf TextBox1.Text = "" Then                MessageBox.Show("请输入具体内容！")                Exit Sub            End If        End If        '第一个组合关系不为空，前两行不能为空          If ComboBox7.Text <> "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(5)            arrayControl(0) = ComboBox1            arrayControl(1) = ComboBox4            arrayControl(2) = TextBox1            arrayControl(3) = ComboBox2            arrayControl(4) = ComboBox5            arrayControl(5) = TextBox2            If ComboBox1.Text = "" Then                MessageBox.Show("请输入字段!")                Exit Sub            ElseIf ComboBox2.Text = "" Then                MessageBox.Show("请输入字段！")                Exit Sub            ElseIf ComboBox4.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf ComboBox5.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf TextBox1.Text = "" Then                MessageBox.Show("请输入具体内容！")                Exit Sub            ElseIf ComboBox7.Text = "" Then                MessageBox.Show("请选择组合关系！")                Exit Sub            End If        End If        '第二个组合关系不为空，都不能为空          If ComboBox8.Text <> "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(8)            arrayControl(0) = ComboBox1            arrayControl(1) = ComboBox4            arrayControl(2) = TextBox1            arrayControl(3) = ComboBox2            arrayControl(4) = ComboBox5            arrayControl(5) = TextBox2            arrayControl(6) = ComboBox3            arrayControl(7) = ComboBox6            arrayControl(8) = TextBox3            If ComboBox1.Text = "" Then                MessageBox.Show("请输入字段!")                Exit Sub            ElseIf ComboBox2.Text = "" Then                MessageBox.Show("请输入字段！")                Exit Sub            ElseIf ComboBox4.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf ComboBox5.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf TextBox1.Text = "" Then                MessageBox.Show("请输入具体内容！")                Exit Sub            ElseIf TextBox2.Text = "" Then                MessageBox.Show("请输入具体内容！")                Exit Sub            ElseIf ComboBox7.Text = "" Then                MessageBox.Show("请选择组合关系！")                Exit Sub            ElseIf TextBox3.Text = "" Then                MessageBox.Show("请输入具体内容！")                Exit Sub            ElseIf ComboBox6.Text = "" Then                MessageBox.Show("请输入操作符！")                Exit Sub            ElseIf ComboBox8.Text = "" Then                MessageBox.Show("请选择组合关系！")                Exit Sub            End If        End If        enCombinationStudent.listName = GetdbName() '给实体赋值        enCombinationStudent.Field1 = ToEnglish(ComboBox1.Text)        enCombinationStudent.Operator1 = ComboBox4.Text        enCombinationStudent.QueryContext1 = TextBox1.Text        enCombinationStudent.Field2 = ToEnglish(ComboBox2.Text)        enCombinationStudent.Operator2 = ComboBox5.Text        enCombinationStudent.QueryContext2 = TextBox2.Text        enCombinationStudent.Field3 = ToEnglish(ComboBox3.Text)        enCombinationStudent.Operator3 = ComboBox6.Text        enCombinationStudent.QueryContext3 = TextBox3.Text        enCombinationStudent.Relation1 = ToEnglish(ComboBox7.Text)        enCombinationStudent.Relation2 = ToEnglish(ComboBox8.Text)        Dim table As New DataTable        Dim fselectStudent As New Facade.CombinationStudentDBFacade        Call DateView()    End Sub    ''' <summary>    ''' 定义虚函数，得到数据库中的不同的表名    ''' </summary>    ''' <returns></returns>    ''' <remarks></remarks>    Public Overridable Function GetdbName() As String        Return ""    End Function       Public Overridable Function ToEnglish(turnName As String) As String        Return ""    End Function    Protected Overridable Sub Dateview() '定义虚函数Dview，将数据显示在控件中      End Sub    Private Sub Button1_Load(sender As Object, e As EventArgs) Handles MyBase.Load        Label1.Text = "输入查" + Chr(10) + "询条件"        ComboBox4.Items.Add("=")        ComboBox4.Items.Add(">")        ComboBox4.Items.Add("<")        ComboBox4.Items.Add("<>")        ComboBox5.Items.Add("=")        ComboBox5.Items.Add(">")        ComboBox5.Items.Add("<")        ComboBox5.Items.Add("<>")        ComboBox6.Items.Add("=")        ComboBox6.Items.Add(">")        ComboBox6.Items.Add("<")        ComboBox6.Items.Add("<>")        ComboBox7.Items.Add("与")        ComboBox7.Items.Add("或")        ComboBox8.Items.Add("与")        ComboBox8.Items.Add("或")        ComboBox2.Enabled = False        ComboBox3.Enabled = False        ComboBox5.Enabled = False        ComboBox6.Enabled = False        TextBox2.Enabled = False        TextBox3.Enabled = False    End Sub    Private Sub ComboBox7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox7.SelectedIndexChanged        ComboBox2.Enabled = True        ComboBox5.Enabled = True        TextBox2.Enabled = True    End Sub    Private Sub ComboBox8_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox8.SelectedIndexChanged        ComboBox3.Enabled = True        ComboBox4.Enabled = True        TextBox3.Enabled = True    End Sub    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click        drivExcel(DataGridView1)    End SubEnd Class  

子窗体的代码为：

Imports System.CollectionsPublic Class frmStudentChild    Private Shared fSC As frmStudentChild   '声明一个静态类变量，类似C#中static      Private Sub New()   '初始化为私有，外部代码不能直接new来实例化该窗体          ' 此调用是设计器所必需的。          InitializeComponent()        ' 在 InitializeComponent() 调用之后添加任何初始化。      End Sub    Public Shared Function GetInstance() As frmStudentChild  '得到该窗体实例的方法          If fSC Is Nothing OrElse fSC.IsDisposed Then  '当窗体关闭或者被处理过就实例化该窗体，因为关闭后不会将变量设置为nothing，只是将窗体dispose掉              fSC = New frmStudentChild            'fSC.MdiParent = frmMain        End If        Return fSC  '如果已经实例化则返回窗体本身      End Function    ''' <summary>    ''' 重写父类中获得表的方法    ''' </summary>    ''' <returns></returns>    ''' <remarks></remarks>    Public Overrides Function GetdbName() As String        Return "StudentInfo"    End Function    Public Overrides Function ToEnglish(turnName As String) As String        Select Case turnName            Case "学号"                Return "StudentNo"            Case "姓名"                Return "StudentName"            Case "性别"                Return "Sex"            Case "年级"                Return "Grade"            Case "学院"                Return "College"            Case "专业"                Return "Professional"            Case "与"                Return "and"            Case "或"                Return "or"            Case Else                Return ""        End Select    End Function    Protected Overrides Sub Dateview() '定义虚函数Dview，将数据显示在控件中          DataGridView1.DataSource = Nothing '清空控件内容         Dim selectSD As New Facade.CombinationStudentDBFacade()        Dim table As DataTable        table = selectSD.fSelectStudentBD(enCombinationStudent)        If table.Rows.Count = 0 Then            DataGridView1.DataSource = Nothing            MessageBox.Show("没有查询到内容！")        Else            DataGridView1.DataSource = Nothing            DataGridView1.DataSource = table            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    End Sub    Private Sub frmStudentChild_Load_1(sender As Object, e As EventArgs) Handles MyBase.Load        Me.Text = "学生信息查询" '表名          Dim turnField As New Hashtable '定义处理字符串转换的hashtable          Dim FieldName() As String        Dim FieldValue() As String  '使用哈希表将数据库字段进行转换          '数据库中字段对应相应英文          FieldName = {"学号", "姓名", "性别", "年级", "学院", "专业"}        FieldValue = {"StudentNo", "StudentName", "Sex", "Grade", "College", "Professional"}        '放到Field字段中          ComboBox1.Items.AddRange(FieldName)        ComboBox2.Items.AddRange(FieldName)        ComboBox3.Items.AddRange(FieldName)        '中文关键字，英文value添加到hashtable          For i As Integer = 0 To FieldName.Count - 1            turnField.Add(FieldName(i), FieldValue(i))        Next    End SubEnd Class

这是做好以后的效果图：

小结：

模板方法其实就是把窗体相同的代码写到一个父窗体中，然后让子窗体直接作为继承窗体就可以了。我的这个模板方法还用到了单例模式，防止其他窗体可以随便New一个子窗体。并且这个关于数据库调试的语句非常重要，算是又掌握了一点只是吧！又一个颗粒归仓了！