How do you retrieve two tables of data at the same time by using data reader

Retrieve two tables of data at the same time by using data reader

Include 2 select statements either in a stored procedure or in a select command and call the ExecuteReader() method on the command object. This will automatically fill the DataReader with 2 Tables of data.

The datareader will always return the data from first table only. If you want to get the second table then you need to use ReaderObject.NextResult() method. The NextResult() method will return true if there is another table. The following code shows you how do it.

//Create the SQL Query with 2 Select statements

string SQLQuery = "Select * from Customers;Select * from Employees;";

//Create the Connection Object

SqlConnection ConnectionObject = new SqlConnection(ConnectionString);

//Create the Command Object

SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);

//Open the connection

ConnectionObject.Open();

//Execute the command. Now reader object will have 2 tables of data.

SqlDataReader ReaderObject = CommandObject.ExecuteReader();

//Loop thru the tables in the DataReader object

while (ReaderObject.NextResult())

{

while (ReaderObject.Read())

{

//Do Something

}

}

//Close the Reader

ReaderObject.Close();

//Close the Connection

ConnectionObject.Close();

See More:

Type of key ued In MS SQL

Access Modifier in C# .net

Features of .NET Framework in c#

Sql Server Stored Procedure

Find the higest salary in Sql Server