<%@ Page %>

ADO.NET Series - Executing SELECT commands


In the previous article of the series we saw how to connect with a SQL server database. Connection with database is useful only if we can execute SQL statements against the database. This article is meant to show you how to execute SELECT statements against SQL server database and iterate through the returned results using DataReader.

Namespaces and classes Involved

In order to work with this example you will need classes from System.Data.SqlClient namespace. This example uses SqlConnection, SqlCommand and SqlDataReader classes.

The SqlConnection class represents a connection to the database. This class is used for any communication between your application and the database.

SqlCommand class represents an SQL command along with additional information. It can contain any SQL statement such as SELECT, INSERT, UPDATE and DELETE. In addition it can also contain stored procedures. This object is used to execute a SQL command or stored procedure against a database and return appropriate results. The SqlCommand object provides methods such as ExecuteReader(), ExecuteNonQuery() and ExecuteScalar() that actually execute these queries.

Once you execute a SELECT statement you would also want to work with the data it returned. SqlDataReader can be used here. SqlDataReader is a read only and forward only cursor. You get an instance of SqlDataReader as a result of ExecuteReader() method call of the SqlCommand object.


The following example shows how to connect with a database, execute a SELECT query and get the results in a DataReader.
using System;
using System.Data;
using System.Data.SqlClient;

//This sample shows how to fetch records 
//in data reader via command object

namespace ADONETSamples
  class Sample2
    static void Main1(string[] args)
      //declare connection,command and datareader
      SqlConnection cnn;
      SqlCommand cmd;
      SqlDataReader dr;
      //create connection
      cnn=new SqlConnection
      cmd=new SqlCommand();
      //open connection
      //set command properties
      cmd.CommandText="select * from employees";
      //get query results in data reader
      //loop through reader and output values
      //close connection
Here, we open a connection with a database using SqlConnection object. We also create an SqlCommand object and set its CommandText property to the SELECT statement. We also set its Connection property to the database connection we just created. We then call its ExecuteReader() method which returns a SqlDataReader instance. We then simply iterate through the DataReader using its Read() method. Individual fields of the row can be accessed by GetValue() method of the DataReader. You may also use more specific methods such as GetString() or GetDate() if you know data type of the column.


In this article we saw how to execute SQL SELECT statements against a database and fetch the results in a DataReader. We also saw how to iterate through the DataReader and access various field values.

Bipin Joshi is an independent software consultant, trainer, author, and meditation teacher. He has been programming, meditating, and teaching for 25+ years. He conducts instructor-led online training courses in ASP.NET family of technologies for individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.

Get connected : Facebook  Twitter  LinkedIn  YouTube

Posted On : 03 May 2003

Tags : ADO.NET Data Access SQL Server