Working With SqlDataReader Class in ADO.NET

Introduction

In my previous article, I have explained the SqlConnection and SqlCommand classes with thes methods ExecuteNonQuery and ExecuteScalar. Here I am describing the ExecuteReader() method of the SqlCommand class and th SqlDataReader class.

ExecuteReader( ) : It executes SQL statements and returns zero or more rows. So when there is need to show records from database in connected architecture, we use the ExecuteReader() method of the SqlCommand class.

SqlDataReader : This is the class of connected architecture in .NET framework. The SqlDataReader is used to read a row of record at a time which is got using SqlCommand. It is read only, which means we can only read the record; it can not be edited. And also it is forward only, which means you can not go back to a previous row (record). If you have read the next row then you can not return back to the previous row. It is used with the ExecuteReader method of the SqlCommand class. Here is an important thing with SqlDataReader class, that is, when we complete our work with SqlDataReader, we should call it's close method. Because when we are working with SqlDataReader, we can not perform any other operation before calling its close method.

Now we will use these in our application. Open Visual Studio 2010 and create a Windows Forms application. In this application we will read data from the database table using the SqlDataReader class and the ExecuteReader() method of the SqlCommand class. At first we should have a database with some records. In this application I have a database table "student_datail" with some records. I am giving a screen - shot so that it become easily understandable.

Take some UI controls on design page and arrange them in the given manner (look at the following figure).



Write the following code which gets the record from the database table and fills a ListBox with those values.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace SqlDataReaderClass
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader dreader;
        string connstr = "server=.;database=student;user=sa;password=wintellect";
        private void btnshow_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstr);// creating instance of sqlconnection 
            conn.Open(); // open connection
            comm = new SqlCommand(" select * from student_detail", conn); // creating instance of sqlcommand and specifying sql query
            dreader = comm.ExecuteReader();// executing ExecuteReader to get record from database
            while(dreader.Read())// using read() method to read all rows one-by-one
            {            
               
            }
            dreader.Close(); // Calling close() method
            conn.Close(); // Closing SqlConnection
        } 
    }
}

Run the application.

Output

Look another example to show record into TextBox. Arrange the UI as the given figure.

Write the following code

using System.Data.SqlClient;
 
namespace SqlDataReaderClass
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader dreader;
        string connstr = "server=.;database=student;user=sa;password=wintellect";
        private void btnshow_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstr);// creating instance of sqlconnection 
            conn.Open(); // open connection
            comm = new SqlCommand(" select * from student_detail where rollno="+txtrollno.Text+"", conn); // creating instance of sqlcommand and
            // specifying sql query          
            dreader = comm.ExecuteReader();// executing ExecuteReader to get record from database
                while (dreader.Read())// using read() method to read all rows one-by-one
                {
                    txtname.Text = dreader["name"].ToString();
                    txtcourse.Text = dreader[2].ToString();                                 
                }
                dreader.Close(); // closing SqlDataReader
                conn.Close(); // Closing SqlConnection         
        }
                  
    }
}
 
Run the application.

Output

Write roll number in TextBox to show other related records.

Click the "show" button. Related record will be shown in TextBox.

Here are some relate resources

• Working with the SqlTransaction Class in ADO.NET

• Working with DataTable and its Methods in ADO.NET

• SQLDataReader Vs. DataSet

• DataRelation class in ADO.NET

• ADODB Services in .NET Applications Using C#