组合查询以及拼接字符串

组合查询以及拼接字符串2015-01-03首先，什么情况下要用到组合查询呢？总的来说，有两种情况：一是在单个查询中从不同的表返回类似结构的数据；二是对单个表执行多个查询，按单个查询返回数据。在这里，我们说的组合查询是指第二种情况，即要查询的表是固定的，查询条件是不定的并且有多个查询条件。

从例子来看，

例如，输入相应的查询条件

1、教师不等于“0”

2、机器号等于“yang”

两个条件是“与”的关系，我们可以很容易的写出它的sql语句

select * from T_Worklog_Info where id <> "0" and computer="yang"

所以接下来要做的核心就是拼出这条语句。

在U层，我们把输入的查询条件的信息赋给实体，然后把实体传入B层。

Dim student As New Login.Model.CmbQueryInfo Dim Bquery As New Login.BLL.cmbQueryforBLL "定义控件基类 Dim controlArray（2） As System.Windows.Forms.Control Dim table As New DataTable Dim i As Integer controlArray（0） = cmbWord1 controlArray（1） = cmbOperator1 controlArray（2） = txtContent1 "字段名 student.FileName1 = cmbWord1.Text student.FileName2 = cmbWord2.Text student.FileName3 = cmbWord3.Text "操作符 student.Operator1 = cmbOperator1.Text student.Operator2 = cmbOperator2.Text student.Operator3 = cmbOperator3.Text "查询内容 student.QueryContent1 = txtContent1.Text student.QueryContent2 = txtContent2.Text student.QueryContent3 = txtContent3.Text "组合关系 student.CompositionRelation1 = cmbRelation1.Text student.CompositionRelation2 = cmbRelation2.Text DataGridView1.Rows.Clear（） "查询 table = Bquery.cmbQuery（student, "T_Worklog_Info"）

在B层，接收实体之后，开始拼接字符串。

首先，会用到两个方法。

ModifyFields方法主要是把“卡号”等中文字符转换为数据库中的字段“cardno”。

Public Function ModifyFields（student As Login.Model.CmbQueryInfo） As Login.Model.CmbQueryInfoDim str As StringSelect Case student.FileName1Case "卡号"str = "cardno"Case "学号"str = "studentno"Case "姓名"str = "studentname"Case "性别"str = "sex"Case "系别"str = "apartment"Case "年级"str = "grade"Case "班级"str = "class"Case "上机日期"str = "ondate"Case "上机时间"str = "ontime"Case "下机日期"str = "offdate"Case "下机时间"str = "offtime"Case "机房号"str = "computer"Case "机器号"str = "computer"Case "登录日期"str = "LoginDate"Case "登录时间"str = "LoginTime"Case "注销日期"str = "LogoutDate"Case "注销时间"str = "LogoutTime"Case "教师"str = "id"Case Elsestr = ""End Selectstudent.FileName1 = str"student.FileName2, student.FileName3类似Select Case student.CompositionRelation1Case "与"str = "and"Case "或"str = "or"Case Elsestr = "NO"End Selectstudent.CompositionRelation1 = strSelect Case student.CompositionRelation2Case "与"str = "and"Case "或"str = "or"Case Elsestr = "NO"End Selectstudent.CompositionRelation2 = strReturn studentEnd Function