ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

SQL ExecuteReader example in C#

Updated on June 14, 2016
sirama profile image

I am a software engineer. I have been working with C++, MFC, and .net technologies for 15 years. 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 imageAUTHOR

      sirama 

      4 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 

      4 years ago

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)