Using SqlDataReader on multiple results.

Suppose we plan to execute multiple select query and how do we fetch result using SqlDataReader. Well this can be achieved by using SqlDataReader.NextResult

Example

Create test database and sample tables

create table employee (id int identity(1,1), name varchar(256), primary key(id))
create table employeeaddress (id int identity(1,1), empaddress varchar(256), primary key(id))


insert into employee values('Johan')
insert into employee values('Leon')


insert into employeeaddress values('Eindhoven, The Netherlands')

Now lets fetch results using SqlDataReader, C# program as follows

string cmdText = @"select * from employee;select * from employeeaddress";

            using (SqlConnection sqlCon = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=testDB;Integrated Security=SSPI;"))

            {

                using(SqlCommand sqlCmd = new SqlCommand(cmdText,sqlCon))

                {

                    sqlCon.Open();

                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    int ResultIndex =1;

                    do

                    {

                        Console.WriteLine("Results from table - " + ResultIndex);

                        while (reader.Read())

                        {

                            Console.WriteLine(reader[0]);

                        }

                        

                        ++ResultIndex;

                    } while (reader.NextResult());

                    reader.Close();

                }

            }