Registration open for ASP.NET Core and Angular instructor-led online courses. Courses conducted by Bipin Joshi on weekends. Click here for more details.

ADO.NET Series - Executing SELECT commands

Introduction

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.

Example

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

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.

Summary

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 a software consultant, trainer, author and yoga mentor having 22+ years of experience in software development. He also conducts online courses on ASP.NET MVC, ASP.NET Core and Design Patterns. 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.