机房重构之组合查询

在机房重构的过程中有个难点，那就是组合查询了。

还记得在第一次桥机房的时候，组合查询我是用的字符串的拼接，当时就感觉到字符串拼接比较费劲，需要很多情况的判断然后给出拼接的方式。而且很容易出错，这次我是用的存储过程。

 PROCEDURE [dbo].[Query_OnOfftimerecord] 
@cboFieldA varchar(10),  
@cboOperatorA varchar(10),  
@txtConditionA varchar(10),  
@cboRelationA varchar(10),  
@cboFieldB varchar(10),  
@cboOperatorB varchar(10),  
@txtConditionB varchar(10),  
@cboRelationB varchar(10),  
@cboFieldC varchar(10),  
@cboOperatorC varchar(10),  
@txtConditionC varchar(10)--表名称或视图名称 

AS
declare @TempSql varchar(500)--临时存放sql语句  
--CHAR(32)是空格,CHAR(39)单引号  
BEGIN
set @TempSql='select * from CardRecord where '+CHAR(32)  
+@cboFieldA+@cboOperatorA+CHAR(39)+@txtConditionA+CHAR(39)+  
CHAR(32)+@cboRelationA+CHAR(32)+  
@cboFieldB+@cboOperatorB+CHAR(39)+@txtConditionB+CHAR(39)+  
+CHAR(32)+@cboRelationB+CHAR(32)  
+@cboFieldC+@cboOperatorC+CHAR(39)+@txtConditionC+CHAR(39)  
execute (@TempSql)  
END

<pre name="code" class="vb">

</pre>组合查询中所有框的值，然后以字符串拼接的形式形成SQL语句，然后执行这个SQL语句。<br /><p></p><p><span style="font-size:24px"><span style="white-space:pre"></span>他的优点是通过传递所有参数，减少了逻辑的判断。</span></p><p><span style="font-size:24px"><br /></span></p><p><span style="font-size:24px"><span style="white-space:pre"></span>只分三种情况：</span></p><p><span style="font-size:24px"><span style="white-space:pre"></span>1、只有一个条件：则后两个条件则赋值成和第一个条件一样的条件，中间用and连接。</span></p><p><span style="font-size:24px"><span style="white-space:pre"></span>2、有两个条件：则最后一个条件和倒数第二个一样，中间用and连接。</span></p><p><span style="font-size:24px"><span style="white-space:pre"></span>3、三个条件：这个就是最好的情况，不用单独判断赋值了。</span></p><p><span style="font-size:24px">UI层<span style="white-space:pre"> </span></span></p><pre name="code" class="vb"><pre name="code" class="vb">Dim c1 As String
        Dim c2 As String
        Dim c3 As String
        Dim c4 As String
        Dim c5 As String
        Dim op1 As String
        Dim op2 As String
        Dim op3 As String
        Dim con1 As String
        Dim con2 As String
        Dim con3 As String
        Select Case Comfile1.Text
            Case "卡号"
                c1 = "CardId"
            Case "学号"
                c1 = "StuID"
            Case "上机时间"
                c1 = "Ontime"
            Case "下机时间"
                c1 = "StuSex"
            Case "用户ID"
                c1 = "UserID"
            Case "状态"
                c1 = "CardState"
            Case Else
                c1 = ""
        End Select
        Select Case Comfile3.Text
            Case "卡号"
                c3 = "CardId"
            Case "学号"
                c3 = "StuID"
            Case "上机时间"
                c3 = "Ontime"
            Case "下机时间"
                c3 = "StuSex"
            Case "用户ID"
                c3 = "UserID"
            Case "状态"
                c3 = "CardState"
            Case Else
                c3 = ""
        End Select
        Select Case Comfile2.Text
            Case "卡号"
                c2 = "CardId"
            Case "学号"
                c2 = "StuID"
            Case "上机时间"
                c2 = "Ontime"
            Case "下机时间"
                c2 = "StuSex"
            Case "用户ID"
                c2 = "UserID"
            Case "状态"
                c2 = "CardState"
            Case Else
                c2 = ""
        End Select
        Select Case Comyu1.Text
            Case "与"
                c4 = "and"
            Case "或"
                c4 = "or"
            Case Else
                c4 = ""
        End Select
        Select Case Comyu2.Text
            Case "与"
                c5 = "and"
            Case "或"
                c5 = "or"
            Case Else
                c5 = ""
        End Select
        Dim bll As New BLL.OnOfftime
        Dim dt As DataTable
        op1 = Comop1.Text
        op2 = Comop2.Text
        op3 = Comop3.Text
        con1 = txttext1.Text
        con2 = txttext2.Text
        con3 = txttext3.Text
        If c4 = "" Then//如果第一个组合关系为空，则给后面两个条件的所有控件的值赋成和第一个条件一样的
            c4 = "and"
            c5 = "and"
            c2 = c1
            op2 = op1
            con2 = con1
            c3 = c1
            op3 = op1
            con3 = con1
        End If
        If c4 <> "" And c5 = "" Then//如果第一个组合关系有值，第二个没有，则把第三个条件赋成和第二个条件一样
            c3 = c2
            op3 = op2
            con3 = con2
            c5 = c4
        End If
        dt = bll.zuheQueryrecord(c1, op1, con1, c4, c2, op2, con2, c5, c3, op3, con3) 
        If dt.Rows.Count = 0 Then
            MsgBox("没有记录")
        Else
            DataGridView.DataSource = dt
        End If
    End Sub

 DAL层

<pre name="code" class="vb"> Dim helper As New SqlHelper
        Dim dt As DataTable
        Dim textcmd As String = "Query_OnOfftimerecord"
        Dim sqlparmars As SqlParameter() = {New SqlParameter("@cboFieldA", c1), New SqlParameter("@cboOperatorA", c2), New SqlParameter("@txtConditionA", c3), New SqlParameter("@cboRelationA ", c4), New SqlParameter("@cboFieldB", c5), New SqlParameter("@cboOperatorB", c6), New SqlParameter("@txtConditionB", c7), New SqlParameter("@cboRelationB", c8), New SqlParameter("@cboFieldC", c9), New SqlParameter("@cboOperatorC", c10), New SqlParameter("@txtConditionC", c11)}

        dt = helper.ExecuteDataTable(textcmd, CommandType.StoredProcedure, sqlparmars)               //调用存储过程Query_OnOfftimerecord
        Return dt