ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

SQL ExecuteReader example in C#

Updated on June 14, 2016
sirama profile image

I am a software engineer. I have been come across C++,MFC, .net technologies. I like playing video games & reading books.

1. Introduction

In this article, we will see how do we select set of data in the form of rows and columns called Records. To retrieve the records, SqlCommand Object’s ExecuteReader method will be used. The ExecuteReader method call is suitable when the underlying command object is set with the Select statement. Have a look at the below Picture:

SqlDataReader
SqlDataReader | Source

The above picture shows that the ExecuteReader executes SQL Select statement (set in the CommandText property of SqlCommand) on the SQL server database. We know that Select statement will return one or more records and those records are packed in the SqlDataReader by the ExecuteReader method and then returned to the caller. The SqlDataReader is based on the forward-only cursor and once the data accessed we cannot locate that data and make changes. Ok, let us go on creating an example for this:

First, create a visual C# Console application. Once the project is created, in the program.cs add the below-using statements:

//Sample 01: Required Name Space
using System.Data;
using System.Data.SqlClient;

2. Opening the connection

As specified in the previous article, to open a connection to the SQL Server database we need a connection string. The below code builds the connection string and uses that to create the SqlConnection object. Once the object is active, the Open method is called on it to establish a connection with the SQL Server database. Below is code for it:

//Sample 02: Form the SQL Connection String and Open 
//           Connection Object.
string connection_string = "Data Source=(Local);Initial " +
    "Catalog=Pubs; Persist Security Info=True;" +
    "User ID=sa;Password=rohith";

SqlConnection con = new SqlConnection(connection_string);
con.Open();

3. Frame SqlCommand object

Ok, the SqlConnection object is ready. Let us go with creating the SqlCommand object. To create a command object we need a valid SQL Statement. In this example, the SQL Statement actually selects set of records from the Employee table of the Pubs database. This select statement is set to the CommandText property of the SqlCommand. Note that the SqlConnection object formed in the previous section also assigned to the Connection property of the SqlCommand object. Below is the code:

//Sample 03: Create Command Object and associate that 
//           with connection object
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Select Emp_id, lname +','+fname " +
    "as EmpName,job_id, job_lvl from employee";

4. SqlDataReader and iterating it

Have a look at the below depiction:

Iterating DataReader
Iterating DataReader | Source

First, the method ExecuteReader (Noted as 1) is called from the SqlCommand object and this method call return the SqlDataReader (Noted as 2). The reader object R now represents the rows and columns of the data. To iterate through all the rows, a while loop is formed (Noted as 3) based on Read () method’s return value.

This method returns a row at a time and advances to the next record. Inside the loop, each column value is read from the SqlDataReader R by referring the column name used to form the select query [Noted as 4]. You can simply understand it like this, the read method points to a record and after that each value is picked by column name. The column values read are printed in the console output for display. Below is code:

//Sample 04: Execute the Select statement and 
//           store the results in SqlDataReader
SqlDataReader R = cmd.ExecuteReader();

//Sample 05: Iterate through the reader
while (R.Read())
{
    Console.WriteLine("Name & id:{0}[{1}]", 
        R["EmpName"], 
        R["Emp_id"]);
    Console.WriteLine("Job Id:{0}, Level:{1}",
        R["job_id"],
        R["job_lvl"]);
    Console.WriteLine();
}

5. Clean Up

The SqlConnection, SqlCommand etc., needs to be released after the usage. Below code shows releasing the resources used in this example:

//Sample 06:Close all the Objects
R.Close();
R.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();

When you use using block, this clean-up is automatic. That is we don’t need to put the above code. Have a look at the below skeleton code sample:

using(SqlConnection con = <XYXYXYXYX>)

{

//Coding goes here

}

In the above skeleton, the SqlCoennection object is created inside the using block. Within the "using block", there is a set of code the runs between the pairs opening & closing curly brasis. When the code execution crosses the closing } curly braces, the object that is created in the using block automatically getting released. When I write next example, I will show this.

6. Complete Code and Output

Below is the complete code for this example and it output:

using System;
using System.Collections.Generic;
using System.Text;

//Sample 01: Required Name Space
using System.Data;
using System.Data.SqlClient;

namespace ExecuteReader
{
    class Program
    {
        static void Main(string[] args)
        {
            //Sample 02: Form the SQL Connection String and Open 
            //           Connection Object.
            string connection_string = "Data Source=(Local);Initial " +
                "Catalog=Pubs; Persist Security Info=True;" +
                "User ID=sa;Password=rohith";

            SqlConnection con = new SqlConnection(connection_string);
            con.Open();

            //Sample 03: Create Command Object and associate that 
            //           with connection object
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Select Emp_id, lname +','+fname " +
                "as EmpName,job_id, job_lvl from employee";

            //Sample 04: Execute the Select statement and 
            //           store the results in SqlDataReader
            SqlDataReader R = cmd.ExecuteReader();

            //Sample 05: Iterate through the reader
            while (R.Read())
            {
                Console.WriteLine("Name & id:{0}[{1}]", 
                    R["EmpName"], 
                    R["Emp_id"]);
                Console.WriteLine("Job Id:{0}, Level:{1}",
                    R["job_id"],
                    R["job_lvl"]);
                Console.WriteLine();
            }

            //Sample 06:Close all the Objects
            R.Close();
            R.Dispose();
            cmd.Dispose();
            con.Close();
            con.Dispose();
        }
    }
}
Code Output
Code Output | Source

7. Closing Notes

The SQLCommand object supports different methods to read data from the database.

  1. ExecuteSclalar - This is useful to read single data element from the Database
  2. ExecuteNonQuery - Use to execute DML statement on the Database
  3. ExecuteReader - Useful to read rows and columns (i.e) one or more records from the database.

Comments

    0 of 8192 characters used
    Post Comment

    • sirama profile image
      Author

      sirama 3 years ago

      I agree with paritoshmmmec. Forgot to specify that. Using blocks will take care disposing the object when it goes out of scope.

    • profile image

      paritoshmmmec@gmail.com 3 years ago

      use using to make sure connection has been disposed else there will a big problem in you code