如何用多个表填充数据集？ [英] How to fill Dataset with multiple tables?

问题描述

我正在尝试填充包含2个表与一对多关系的DataSet。
我正在使用DataReader来实现这一点：

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

但是我只有一张表填满了。如何实现我的目标 - 填充两个表？

如果可能，我想使用DataReader DataAdapter。

解决方案

如果您使用多个select语句发出单个命令，则可以使用NextResult方法移动到数据采集器中的下一个结果集： http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult .aspx

我显示如何看起来如下：

  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（）; 
} 
返回结果; 
} 
  

I'm trying to fill DataSet which contains 2 tables with one to many relationship. I'm using DataReader to achieve this :

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

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

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

But I've got only one table filled up. How do I achieve my goal - fill both tables?

I would like to use DataReader instead DataAdapter, if it possible.

解决方案

If you are issuing a single command with several select statements, you might use NextResult method to move to next resultset within the datareader: http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx

I show how it could look bellow:

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屋！