reader inside a reader

[Guilherme Grillo]

Friends,

Can I execute a Reader inside a Reader?

Example:

while (readerPerguntas.Read())

{

// Puxa as Informações da Tabela de Perguntas

string pergunta = (string)readerPerguntas["Pergunta"];

string dboPerguntaResposta = (string)readerPerguntas["Resposta"];

// Cria um Label com cada pergunta

Label labelPergunta = new Label();

labelPergunta.Text = "<p>" + pergunta + "</p>";

panelPesquisa.Controls.Add(labelPergunta);

// Comando que faz uma busca nas respostas da determinada pergunta

commRespostas = new SqlCommand("SELECT * FROM" + dboPerguntaResposta + "",
conn);

// Executa o Comando

readerRespostas = commRespostas.ExecuteReader(); <<<< THIS LINE GET AN
EXCEPTION



EXCEPTION: There is already an open DataReader associated with this Command
which must be closed first.



What can I do?



Sorry my English.



Grillo

 

[sloan]

Outside of the technical issue.
That is kind of a very bad idea.

Look at the NextResult() if you need multiple ResultSets.

You should hit the database 1 time, and use multiple ResultSets
(NextResult() ) if you need data from more than 1 query.

....

 

[Guest]

You should be able to accomplish what you want with a SQL Subselect / join
and return the results as a DataSet with multiple tables and datareleations
you can set.
Or, if you want a "Sub reader", follow the instructions in the exception
message and use a separate SqlCommand object for it.
-- Peter
http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

 

[sloan]

Yeah, while dataReaders are better performance oriented..

If your choice is between.... A reader with a nested reader...... VS a
dataset (preferably a strongly typed dataset)... then I would go with the
DataSet .........

If you're dealing with < 1000 records (you didn't say, so I don't really
know)... heck even <10,000 records.. then use the DataSet.

The nested reader just seems ... like the wrong plan of attack.

....

Another way to put it, use the dataset over the datareader until you have
reason (memory footprint) not to.

You should be able to accomplish what you want with a SQL Subselect / join
and return the results as a DataSet with multiple tables and
datareleations
you can set.
Or, if you want a "Sub reader", follow the instructions in the exception
message and use a separate SqlCommand object for it.
-- Peter
http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

Friends,

Can I execute a Reader inside a Reader?

Example:

while (readerPerguntas.Read())

{

// Puxa as Informações da Tabela de Perguntas

string pergunta = (string)readerPerguntas["Pergunta"];

string dboPerguntaResposta = (string)readerPerguntas["Resposta"];

// Cria um Label com cada pergunta

Label labelPergunta = new Label();

labelPergunta.Text = "<p>" + pergunta + "</p>";

panelPesquisa.Controls.Add(labelPergunta);

// Comando que faz uma busca nas respostas da determinada pergunta

commRespostas = new SqlCommand("SELECT * FROM" + dboPerguntaResposta +
"",
conn);

// Executa o Comando

readerRespostas = commRespostas.ExecuteReader(); <<<< THIS LINE GET AN
EXCEPTION



EXCEPTION: There is already an open DataReader associated with this
Command
which must be closed first.



What can I do?



Sorry my English.



Grillo

 

[Guilherme Grillo]

Thanks...

I have an idea... JOIN OR SUBQUERIES (SQL)....

I will try it now!

Thanks again!
Grillo

Outside of the technical issue.
That is kind of a very bad idea.

Look at the NextResult() if you need multiple ResultSets.

You should hit the database 1 time, and use multiple ResultSets
(NextResult() ) if you need data from more than 1 query.

...

Friends,

Can I execute a Reader inside a Reader?

Example:

while (readerPerguntas.Read())

{

// Puxa as Informações da Tabela de Perguntas

string pergunta = (string)readerPerguntas["Pergunta"];

string dboPerguntaResposta = (string)readerPerguntas["Resposta"];

// Cria um Label com cada pergunta

Label labelPergunta = new Label();

labelPergunta.Text = "<p>" + pergunta + "</p>";

panelPesquisa.Controls.Add(labelPergunta);

// Comando que faz uma busca nas respostas da determinada pergunta

commRespostas = new SqlCommand("SELECT * FROM" + dboPerguntaResposta +
"", conn);

// Executa o Comando

readerRespostas = commRespostas.ExecuteReader(); <<<< THIS LINE GET AN
EXCEPTION



EXCEPTION: There is already an open DataReader associated with this
Command which must be closed first.



What can I do?



Sorry my English.



Grillo

 

[sloan]

If you create a strong dataset... you can load multiple tables.


Go here:

http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!139.entry

Find the

CustomerData.cs file (and class)

Find this method:

public CustomerOrderInfoDS CustomersGetAllDS()
{
CustomerOrderInfoDS returnDS = new CustomerOrderInfoDS();
Microsoft.ApplicationBlocks.Data.SqlHelper.FillDataset
(m_connectionString, this.PROC_CUSTOMERS_GET_ALL ,returnDS , new
string[]{returnDS.Customer.TableName , returnDS.Order.TableName },null);
return returnDS;
}


That will show you how to get multiple tables into 1 strong dataset.

The issue with your join, is that you're going to repeat alot of data over
and over.

...

CustomerOrderInfoDS is my strong dataset. It has 2 tables in it. Customer
and Order tables.


Take a look, it'll help how you're working and looking at the the problem.

Thanks...

I have an idea... JOIN OR SUBQUERIES (SQL)....

I will try it now!

Thanks again!
Grillo

Outside of the technical issue.
That is kind of a very bad idea.

Look at the NextResult() if you need multiple ResultSets.

You should hit the database 1 time, and use multiple ResultSets
(NextResult() ) if you need data from more than 1 query.

...

Friends,

Can I execute a Reader inside a Reader?

Example:

while (readerPerguntas.Read())

{

// Puxa as Informações da Tabela de Perguntas

string pergunta = (string)readerPerguntas["Pergunta"];

string dboPerguntaResposta = (string)readerPerguntas["Resposta"];

// Cria um Label com cada pergunta

Label labelPergunta = new Label();

labelPergunta.Text = "<p>" + pergunta + "</p>";

panelPesquisa.Controls.Add(labelPergunta);

// Comando que faz uma busca nas respostas da determinada pergunta

commRespostas = new SqlCommand("SELECT * FROM" + dboPerguntaResposta +
"", conn);

// Executa o Comando

readerRespostas = commRespostas.ExecuteReader(); <<<< THIS LINE GET AN
EXCEPTION



EXCEPTION: There is already an open DataReader associated with this
Command which must be closed first.



What can I do?



Sorry my English.



Grillo