SqlDataReader()

The SqlDataReader is a class that is used to create a forward only result set that requires an open data-connection the whole time it's running. You can only iterate over the rows once, while you read through them and do not have the ability to move the cursor back. You have one chance to look at each record and then move on. Remember that you must close your data reader before you close your connection otherwise you'll end up with an orphaned data reader which will hurt performance over time.

using System;

using System.Data.SqlClient;

namespace pc.sqldatareaderExample

{

    class Program

    {

        static string cs =

          @"Data Source=BEAST\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            string GetPersons = "SELECT * from Person;";

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

                try

                {

                    var cmnd = new SqlCommand(GetPersons, conn);

                    conn.Open();

                    using (SqlDataReader dr = cmnd.ExecuteReader())

                        try

                        {

                            if (dr.HasRows)

                                while (dr.Read())

                                    Console.WriteLine($"{dr[0]}) {dr[1]} {dr[2]}");

                        }

                        finally

                        {

                            dr.Close();

                        }

                   

                }

                catch (SqlException sqlEx)

                {

                    //display sql exception

                    Console.WriteLine(sqlEx.Message);

                }

                catch (Exception Ex)

                {

                    //display all other exceptions

                    Console.WriteLine(Ex.Message);

                }

                finally

                {

                    //ensure that the connection is closed everytime

                    conn.Close();

                }

        }

    }

}

One thing to keep in mind is that you can use the data reader to return multiple result sets by appending your select queries and calling nextResult() to move to the next result set.

using System;

using System.Data.SqlClient;

namespace pc.sqldatareaderExample

{

    class Program

    {

        static string cs =

          @"Data Source=BEAST\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            string GetPersons = "SELECT * from Person;SELECT * from Person;";

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

                try

                {

                    var cmnd = new SqlCommand(GetPersons, conn);

                    conn.Open();

                    using (SqlDataReader dr = cmnd.ExecuteReader())

                        try

                        {

                            do

                            {

                                if (dr.HasRows)

                                    while (dr.Read())

                                        Console.WriteLine($"{dr[0]}) {dr[1]} {dr[2]}");

                                Console.WriteLine("\n*** Next Result set***\n");

                            }

                            while (dr.NextResult());

                        }

                        finally

                        {

                            dr.Close();

                        }

                   

                }

                catch (SqlException sqlEx)

                {

                    //display sql exception

                    Console.WriteLine(sqlEx.Message);

                }

                catch (Exception Ex)

                {

                    //display all other exceptions

                    Console.WriteLine(Ex.Message);

                }

                finally

                {

                    //ensure that the connection is closed everytime

                    conn.Close();

                }

        }

    }

}

So the thing to keep in mind is that the SQL data reader can only iterate over each record once as it traverses the set. It can also return multiple queries in one call.