Ado.NET - retrieve two tables of data at a time by using data reader

GeekInterview.com > Interview Questions > Microsoft > Ado.NET

Go To First | Previous Question | Next Question

Ado.NET | Question 29 of 31

retrieve two tables of data at a time by using data reader

One interview I faced a Q:
how can i retrieve two tables of data at a time by using data reader?
Data reader read and forward only, how is it possible to get 2 tables of data at a time

Total Answers and Comments: 10

Last Update: November 18, 2008 Asked by: sudhakar

Sponsored Links

Best Rated Answer

—

Submitted by: Ravindra Thakur

Hi,
If we execute 2 select command either in stored procedure or in select command and then executereader method fired of command object. it return 2 tables in datareader.

like :
string str="Select * from a;select * from b";
cmd.commandtext=str;
dr=cmd.executereader();

Now it return 2 tables in datareader (dr).

Above answer was rated as good by the following members:
prabhukumar.r, rajani_vaddepalli15, sudip_lahiri

November 19, 2007 10:17:03

Ravindra Thakur

RE: retrieve two tables of data at a time by using dat...

Hi,
If we execute 2 select command either in stored procedure or in select command and then executereader method fired of command object. it return 2 tables in datareader.

like :
string str="Select * from a;select * from b";
cmd.commandtext=str;
dr=cmd.executereader();

Now it return 2 tables in datareader (dr).

Is this answer useful? Yes | No

2 0

Overall Rating: +2

November 19, 2007 10:19:48

Ravindra Thakur

RE: retrieve two tables of data at a time by using dat...

Yes , it is posible to retreve two tables in datareader.

If we pass 2 select command in a string and set that string to commandtext of command object. and execute reader.

ex:
string str="Select * from A;Select * from B";
cmd.commandtext=str;
dr=cmd.executereader();

Is this answer useful? Yes | No

November 29, 2007 02:06:49

jebasingh_23

Member Since: December 2005 Contribution: 1

RE: retrieve two tables of data at a time by using data reader

The datareader will always return the first result only. If you want to get the second table then you need to use dr.NextResult() .

Is this answer useful? Yes | No

December 10, 2007 09:51:14

wrijub

Member Since: November 2006 Contribution: 7

RE: retrieve two tables of data at a time by using data reader

For the following code

----------------------------------------
objCmd.CommandText = "SELECT * FROM [Categories];SELECT * FROM [Customers];";
objRdr = objCmd.ExecuteReader();
----------------------------------------

It Keeps throwing the following exception
-----------------------
System.Data.OleDb.OleDbException: Characters found after end of SQL statement.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
-----------------------

Now what to do?????

Is this answer useful? Yes | No

December 11, 2007 23:53:15

ASWINRAMESH

Member Since: December 2007 Contribution: 1

RE: retrieve two tables of data at a time by using data reader

A situation where we want to pass 2 or more querries to a
DataReader like "Select * from Table1;Select * from Table2"

we have do the following things

while(datareader.nextResultSet())
{
while(datareader.read())
{
.......Processing of result set..
}
}

Is this answer useful? Yes | No

0 1

Overall Rating: -1

May 07, 2008 02:55:20

amplemaddy

Member Since: May 2008 Contribution: 9

RE:

objCmd.CommandText = "SELECT * FROM table1,table2";
objRdr = objCmd.ExecuteReader();

you can try to sql join in a single query like this

Is this answer useful? Yes | No

June 27, 2008 03:06:29

kumar2ch

Member Since: June 2008 Contribution: 3

RE: retrieve two tables of data at a time by using data reader

Yes this possiable to retreive the data from tw tables
useing datareader
syntax:
in sql
select * from EmployeeMaster,Department
sqlcommaned cmd=new sqlcommaned ();
cmd.commanedText="select * from EmployeeMaster,Department";
sqldatareader dr=cmd.ExecuteReader();

Is this answer useful? Yes | No

July 18, 2008 12:46:23

drsudhak

Member Since: July 2008 Contribution: 1

RE: retrieve two tables of data at a time by using data reader

Theoritically not possible.because data reader holds only one result set.

But you can get data from 2 tables into data reader using union or union all in select statement of SqlCommand.

Is this answer useful? Yes | No

August 01, 2008 12:30:36

ManglaRitu

Member Since: August 2008 Contribution: 1

RE: retrieve two tables of data at a time by using data reader

This is absolutely true that theoretically you can't access two resultset concurrently using Datareader due to its nature of having the connection state with database and forward only traversing. However, if it is possible to have the data from two tables using some manipulation in the "Select" Query so that the desired resultset is achieved. If one uses Union/Union All, he/she has to be very careful regarding the select phrase in both sub-queries.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

November 17, 2008 21:48:33

#10

rekha969

Member Since: November 2008 Contribution: 1

RE: retrieve two tables of data at a time by using data reader

Better use joins like
Select field1, field2, field3 from table1 inner join table2 on table1.fieldx = table2.fieldx
field1, filed2 are from first table
field3 from second table
fieldx is common field from both table.

Is this answer useful? Yes | No

Go To Top