GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Microsoft  >  Ado.NET
Go To First  |  Previous Question  |  Next Question 
 Ado.NET  |  Question 29 of 31    Print  
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: 13 Last Update: November 05, 2009     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, sandeepbhandigare, gsc0tt
  Sorting Options  
  Page 1 of 2   « First    1    2    >     Last »  
November 19, 2007 10:17:03   #1  
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 | NoAnswer is useful 2   Answer is not useful 0Overall Rating: +2    
November 19, 2007 10:19:48   #2  
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   #3  
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 | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
December 10, 2007 09:51:14   #4  
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   #5  
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 | NoAnswer is useful 1   Answer is not useful 1Overall Rating: -N/A-    
May 07, 2008 02:55:20   #6  
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   #7  
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   #8  
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   #9  
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 | NoAnswer is useful 1   Answer is not useful 0Overall 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
  Page 1 of 2   « First    1    2    >     Last »  


 
Go To Top


 Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape