机房重构之组合查询

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

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

 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)--表名称或视图名称 ASdeclare @TempSql varchar(500)--临时存放sql语句  --CHAR(32)是空格,CHAR(39)单引号  BEGINset @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