如何在数据集中合并这些两个查询 [英] how to merge this tow queries in dataset

问题描述

 SqlCommand SqlComm =  new  SqlCommand（）; 
 SqlComm.Connection = conn; 
 SqlComm.CommandType = CommandType.Text; 
 SqlComm.CommandText =   select jobcard_creation.jobcard_no，jobcard_creation.vehicle_no，jobcard_creation.vehicle_model，jobcard_creation.date， jobcard_staff.staff_name， 
 +   jobcard_creation.delivery_date，jobcard_creation.total_days，vehicle_type.vehicle_name ，来自jobcard_creation的Job_type.jobtype_name 
 +  在jobcard_creation.department_id上左外连接vehicle_type = vehicle_type.vehicle_id在job_type.jobtype_id = jobcard_creation.job_type 
 +  的左外连接Job_type job join_ jobaff_staff在jobcard_staff.jobcard_id = jobcard_creation.jobcard_id，其中 
 +   jobcard_creation.jobcard_id =' + txtJobCardId.Text +  '和jobcard_creation.type ='N'和jobcard_staff.type ='N'; 
 
 SqlCommand sqlcom1 =  new  SqlCommand（）; 
 sqlcom1.Connection = conn; 
 sqlcom1.CommandType = CommandType.Text; 
 sqlcom1.CommandText =  从jobcard_work_description中选择描述，其中jobcard_id =' + txtJobCardId.Text +  '; 
  //  创建Northwind DataSetXSD实例 

解决方案

您只需要加入 jobcard_work_description 表并包含 describetion 在中选择子句（虽然我怀疑它应该是 description ）例如

< pre lang =sql> 选择 JC.jobcard_no，JC.vehicle_no，JC.vehicle_model，JC。[ date ]，JS.staff_name，
JC.delivery_date，JC.total_days，VT.vehicle_name，JT.jobtype_name，
JWD.describtion
来自 jobcard_creation JC
left 外部 加入 vehicle_type VT JC.department_id = vt.vehicle_id
left 外部 join Job_type JT JT.jobtype_id = JC.job_type
left join jobcard_staff JS on JS。 jobcard_id = JC.jobcard_id
left join jobcard_work_description JWD on JC.jobcard_id = JWD.jobcard_id
其中 JC.jobcard_id=@cardid 和 JC。[ type ] = ' N' 和 JS.type = ' N'

需要注意的事项：

- 我已经包围了列名带方括号的保留字例如 [type] 和 [date]

- 我使用了表别名来制作select子句更易读，例如JC，JT，VT等

- 记下Richard Deeming的评论 - 永远不要使用字符串连接来构建SQL查询。请参阅上面的查询中的 @cardid

另一种方法可能就像。检查这个 [ ^ ]供参考！

  public  DataSet SelectOne（ int  id） 
 {
 DataSet result =  new  DataSet（）; 
 使用（DbCommand command = Connection.CreateCommand（））
 {
 command.CommandText =  @  
 select * from table1 
 select * from table2 
; 
 
  var  param = ParametersBuilder.CreateByKey（command，  ID，id， null ）; 
 command.Parameters.Add（param）; 
 
 Connection.Open（）; 
 使用（DbDataReader reader = command.ExecuteReader（））
 {
 result.MainTable.Load（reader）; 
 reader.NextResult（）; 
 result.SecondTable.Load（读者）; 
  //   ...  
} 
 Connection.Close（ ）; 
} 
 返回结果; 
} 

SqlCommand SqlComm = new SqlCommand();
SqlComm.Connection = conn;
SqlComm.CommandType = CommandType.Text;
SqlComm.CommandText = "select jobcard_creation.jobcard_no,jobcard_creation.vehicle_no,jobcard_creation.vehicle_model,jobcard_creation.date, jobcard_staff.staff_name, "
     + "  jobcard_creation.delivery_date,jobcard_creation.total_days,vehicle_type.vehicle_name,Job_type.jobtype_name from jobcard_creation "
    + " left outer join vehicle_type on jobcard_creation.department_id=vehicle_type.vehicle_id   left outer join Job_type on job_type.jobtype_id=jobcard_creation.job_type  "
    + " left join jobcard_staff on jobcard_staff.jobcard_id=jobcard_creation.jobcard_id  where "
    + " jobcard_creation.jobcard_id='" + txtJobCardId.Text + "' and jobcard_creation.type='N' and jobcard_staff.type='N' ";  

SqlCommand sqlcom1 = new SqlCommand();
sqlcom1.Connection = conn;
sqlcom1.CommandType = CommandType.Text;
sqlcom1.CommandText="select describtion from jobcard_work_description where jobcard_id='" + txtJobCardId.Text + "' ";
// Create instance of Northwind DataSetXSD

解决方案

You just need to join on the jobcard_work_descriptiontable and include describtion in your select clause (although I suspect it should be description) E.g.

select JC.jobcard_no,JC.vehicle_no,JC.vehicle_model,JC.[date], JS.staff_name,
        JC.delivery_date,JC.total_days,VT.vehicle_name,JT.jobtype_name,
        JWD.describtion
from jobcard_creation JC
left outer join vehicle_type VT on JC.department_id=vt.vehicle_id
left outer join Job_type JT on JT.jobtype_id=JC.job_type
left join jobcard_staff JS on JS.jobcard_id=JC.jobcard_id
left join jobcard_work_description JWD on JC.jobcard_id=JWD.jobcard_id
where JC.jobcard_id=@cardid and JC.[type]='N' and JS.type='N'

Couple of things to note:
- I've surrounded column names that are reserved words with square brackets e.g. [type] and [date]
- I've used table aliases to make the select clause a bit more readable e.g. JC, JT, VT etc.
- Take note of the comment from Richard Deeming - NEVER use string concatenation to build a SQL query. See @cardid in my query above

Another approach could be like. Check this[^] for Reference!

public DataSet SelectOne(int id)
{
    DataSet result = new DataSet();
    using (DbCommand command = Connection.CreateCommand())
    {
        command.CommandText = @"
select * from table1
select * from table2
        ";

        var param = ParametersBuilder.CreateByKey(command, "ID", id, null);
        command.Parameters.Add(param);

        Connection.Open();
        using (DbDataReader reader = command.ExecuteReader())
        {
            result.MainTable.Load(reader);
            reader.NextResult();
            result.SecondTable.Load(reader);
            // ...
        }
        Connection.Close();
    }
    return result;
}

这篇关于如何在数据集中合并这些两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！