Learn ASP.NET Web Forms and ASP.NET MVC. Intensive courses for professional developers. Conducted by Bipin Joshi in Thane. Read more details here.
<%@ Page %>

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.
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
      (Database.GetConnectionString());
      cmd=new SqlCommand();
      //open connection
      cnn.Open();
      //set command properties
      cmd.Connection=cnn;
      cmd.CommandText="select * from employees";
      //get query results in data reader
      dr=cmd.ExecuteReader();
      //loop through reader and output values
      while(dr.Read())
      {
        Console.WriteLine(dr.GetValue(1));
      }
      //close connection
      cnn.Close();
    }
  }
}
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 the founder of BinaryIntellect Consulting and conducts professional training programs on ASP.NET in Thane. He is a published author and has authored or co-authored books for Apress and Wrox press. To know more about him click here. To know more about his training programs go here.

Get connected : Twitter  Facebook  Google+  LinkedIn


Tags : ADO.NET Data Access SQL Server
Posted On : 03 May 2003
Current Rating :
Rate this article :


This page is protected by copyright laws. Copying in any form is strictly prohibited. For Copyright notice and legal terms of use click here.

Protected by Copyscape