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

Showing Answers 1 - 45 of 45 Answers

Ravindra Thakur

  • Nov 19th, 2007
 

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).

Ravindra Thakur

  • Nov 19th, 2007
 


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();

  Was this answer useful?  Yes

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?????

  Was this answer useful?  Yes

ASWINRAMESH

  • Dec 11th, 2007
 

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..
  }
}

 

kumar2ch

  • Jun 27th, 2008
 

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();

  Was this answer useful?  Yes

drsudhak

  • Jul 18th, 2008
 

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.

  Was this answer useful?  Yes

ManglaRitu

  • Aug 1st, 2008
 

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.

rekha969

  • Nov 17th, 2008
 

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.

  Was this answer useful?  Yes

malathi2k5

  • Dec 8th, 2008
 

SqlCommand myCMD = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" +
"SELECT EmployeeID, LastName FROM Employees", nwindConn);
nwindConn.Open();

SqlDataReader myReader = myCMD.ExecuteReader();

do
{
Console.WriteLine("t{0}t{1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
Console.WriteLine("t{0}t{1}", myReader.GetInt32(0), myReader.GetString(1));

} while (myReader.NextResult());

myReader.Close();
nwindConn.Close();

  Was this answer useful?  Yes

Actully datareader can return more then one table but at the starting it contain the reference of first Resultset. to move the second or next resultset you have to use the

dr.NextResultSet();


Method.

  Was this answer useful?  Yes

Yes its quite possible 


For Example:

C# Code

SqlCommand cmd=new SqlCommand("select * from Table1";"select * from Table2");
DataReader dr=cmd.ExecuteReader()
while(dr.Read())
{
Response.Write(dr["Table1_Column"]) ;
Response.Write(dr["Table2_Column"]) ;
}



  Was this answer useful?  Yes

vksingh24

  • Nov 16th, 2009
 

This is possible to retrieve data from two tables using datareader.

SqlDataReader rd=Command.ExecuteReader("SELECT * FROM Employee | Select * From Order");

This will fetch the record from two tables Employee and Order

  Was this answer useful?  Yes

SqlCommand myCmd=new SqlCommand("Select Column1,Column2 From Table1;" +
                                                     "Select Column3,Column4 From Table2",myConn);

myCOnn.open();

SqlDataRedaer myReader=myCmd.ExecuteReader();

While(myReader.NextResult())
{
  Console.WriteLine("t{0}t{1}",myReader.GetName(0),myReader.GetName(1));
  While(myReader.Read())
 {
   Console.WriteLine("t{0}t{1}",myReader.GetInt32(0),myReader.GetString(1)); 
 }


myReader.Close();
myConn.Close();

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions