StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODbDataReader, NextResult() and filling more than one table
text
Body
copied!<p>This question is continuation of my <a href="https://stackoverflow.com/questions/11345761/how-to-fill-dataset-with-multiple-tables">previous</a> 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</p> <pre><code>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"; } </code></pre> <p>and this is not</p> <pre><code>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"; } </code></pre> <p>After second example, I have filled only result.t0_DataAgency_R table - but not result.t01_ChoiceParam_R. Why can it be so?</p> <p>Thanks in advance</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload