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 ones, while you read through them and do not have the ability to move the cursor back. You have once chance to look at each record then move on. Remember that you have to 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;

using System.Data.SqlClient;

namespace SQLExample3

{

    class Program

    {

        static string cs = "Data Source=ssrap7;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

            {

               

                try

                {

                    string GetPersons = "SELECT * from Person;";

                    var cmnd = new SqlCommand(GetPersons, conn);

                   

                    conn.Open();

                    using (SqlDataReader dr = cmnd.ExecuteReader())

                    {

                        try

                        {

                            if (dr.HasRows)

                                while (dr.Read())

                                    Console.WriteLine("{0}) {1} {2} born {3}", dr[0], dr[1], dr[2], dr[3]);

                        }

                        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;

using System.Data.SqlClient;

namespace SQLExample3

{

    class Program

    {

        static string cs = "Data Source=ssrap7;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

            {

               

                try

                {

                    //Two select Statments Combined

                    string GetPersons = @"SELECT * from Person;

                                          SELECT * from Person;";

                    var cmnd = new SqlCommand(GetPersons, conn);

                   

                    conn.Open();

                    using (SqlDataReader dr = cmnd.ExecuteReader())

                    {

                        try

                        {

                            do

                            {

                                if (dr.HasRows)

                                    while (dr.Read())

                                        Console.WriteLine("{0}) {1} {2} born {3}", 

                                 dr[0], dr[1], dr[2], dr.GetDateTime(3).ToShortDateString());

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

                }

            }

        }

    }

}

Now because the datareader is forward only, you can't get the actual count of the number of rows in your return set, you'd have to read over each one and increment a counter. Seems like a lot of overhead if that's all you want. Luckily there's an alternative and that's an aggregate function such as count() to let the database handle the count instead of your code, you just have to pass it as a query.

Now the sqlCommand class has a special function called ExecuteScaler() it is used when you need to retieve just one piece of data, this data comes back as an object so you should unbox it before you use it.

using System;

using System.Data;

using System.Data.SqlClient;

namespace SQLExample4

{

    class Program

    {

        static string cs = "Data Source=ssrap7;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)

        {

            //using statement to ensure dispose of

            using (var conn = new SqlConnection(cs))

            {

                try

                {

                    string GetPersons = "SELECT COUNT(id) FROM Person;";

                    var cmnd = new SqlCommand(GetPersons, conn);

                    conn.Open();

                    var retunrnValue = cmnd.ExecuteScalar();

                   

                    if (retunrnValue is Int32)

                        Console.WriteLine("There is {0} Person rows", retunrnValue);

                }

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

                }

            }

        }

    }

}

The above code get's the total number of people in our person table and displays it on screen.