DbDataReader，NextResult（）和填充多个表 [英] DbDataReader, NextResult() and filling more than one table

问题描述

这个问题是延续我的<一个href="http://stackoverflow.com/questions/11345761/how-to-fill-dataset-with-multiple-tables">$p$pvious一。没有进入太多细节，我填的数据集与2有关的1对多表。 所以，我现在的问题是 - 为什么code工作好

This question is continuation of my previous one. Without going into too much details, I'm filling dataset with 2 related 1-to-many tables. So, my question now is - why this code works good

public DataAgencyR_DataSet SelectOne(int id)
{
    DataAgencyR_DataSet result = new DataAgencyR_DataSet();
    using (DbCommand command = Connection.CreateCommand())
    {
        try
        {               
            command.CommandText = SqlStrings.SelectDataAgencyR_SelectOne();

            var param = ParametersBuilder.CreateByKey(command, "ID_DeclAgenc", id, "ID_DeclAgenc");
            command.Parameters.Add(param);
            Connection.Open();
            using (DbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    System.Diagnostics.Trace.WriteLine(String.Format("{0}-{1}", reader[0], reader[1]));
                }
                System.Diagnostics.Trace.WriteLine("-------------");
                reader.NextResult();
                while (reader.Read())
                {
                    System.Diagnostics.Trace.WriteLine(String.Format("{0}-{1}", reader[0], reader[1]));
                }
            }

        }
        catch (DbException e)
        {
            Logger.Error(e.Message, e);
            throw new DataAccessException("Error occurs while SelectOne method porcessed", e);
        }
        finally
        {
            if (Connection.State != ConnectionState.Closed)                     Connection.Close();
        }
    }
    return result;
}

public static string SelectDataAgencyR_SelectOne()
{
    return "SELECT a.* FROM t0_DataAgency_R a WHERE a.SetToPartners = 1 AND a.ID_DeclAgenc = @ID_DeclAgenc;" +
                   "SELECT c.* FROM t01_ChoiceParam_R c JOIN t0_DataAgency_R a on a.ID_DeclAgenc = c.ID_DeclAgenc WHERE SetToPartners = 1 AND a.ID_DeclAgenc = @ID_DeclAgenc";
}

这不是

public DataAgencyR_DataSet SelectOne(int id)
{
    DataAgencyR_DataSet result = new DataAgencyR_DataSet();

    using (DbCommand command = Connection.CreateCommand())
    {
        try
        {               
            command.CommandText = SqlStrings.SelectDataAgencyR_SelectOne();             
            var param = ParametersBuilder.CreateByKey(command, "ID_DeclAgenc", id, "ID_DeclAgenc");
            command.Parameters.Add(param);
            Connection.Open();
            using (DbDataReader reader = command.ExecuteReader())
            {
                result.t0_DataAgency_R.Load(reader);
                reader.NextResult();
                result.t01_ChoiceParam_R.Load(reader);
            }
        }
        catch (DbException e)
        {
            Logger.Error(e.Message, e);
            throw new DataAccessException("Error occurs while SelectOne method porcessed", e);
        }
        finally
        {
            if (Connection.State != ConnectionState.Closed) Connection.Close();
        }
    }
    return result;
}


public static string SelectDataAgencyR_SelectOne()
{
    return "SELECT a.* FROM t0_DataAgency_R a WHERE a.SetToPartners = 1 AND a.ID_DeclAgenc = @ID_DeclAgenc;" +
                   "SELECT c.* FROM t01_ChoiceParam_R c JOIN t0_DataAgency_R a on a.ID_DeclAgenc = c.ID_DeclAgenc WHERE SetToPartners = 1 AND a.ID_DeclAgenc = @ID_DeclAgenc";
}

在第二个例子中，我只填写result.t0_DataAgency_R表 - 但不是result.t01_ChoiceParam_R。它为什么能够如此？

After second example, I have filled only result.t0_DataAgency_R table - but not result.t01_ChoiceParam_R. Why can it be so?

在此先感谢

推荐答案

DataTable.Load 自动前进读者到下一个结果。所以，你应该删除您显式调用 NextResult 。

DataTable.Load automatically advances the reader to the next result. So you should remove your explicit call to NextResult.

含义：

using (DbDataReader reader = command.ExecuteReader())
{
    result.t0_DataAgency_R.Load(reader);
    result.t01_ChoiceParam_R.Load(reader);
}

这篇关于DbDataReader，NextResult（）和填充多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！