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

最新推荐文章于 2017-09-21 15:41:50 发布

EricaLSR

最新推荐文章于 2017-09-21 15:41:50 发布

阅读量1.4k

点赞数

分类专栏： ------VB.NET VB.NET版的机房重构

本文链接：https://blog.csdn.net/lishaoran369/article/details/43915591

版权

------VB.NET 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

VB.NET版的机房重构

6 篇文章 2 订阅

订阅专栏

自己经历了几天画图的郁闷期以后，就开始编写代码！这个编写代码的一路是顺风顺水，没有什么起伏，以至于让我忘了后边还有组合查询，上下机，结账，这三个大难题！这不，就在组合查询这里遇到了一个“大问题”！问题就是如图：（我的数据库中有学号为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)

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

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 Sub
End Class

子窗体的代码为：

Imports System.Collections
Public 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 Sub
End Class

这是做好以后的效果图：

小结：

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

EricaLSR

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
26
评论
VB.NET版机房重构----模板方法+存储过程实现组合查询

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

扫一扫