Home

Data Binding: The SQL Data Reader

 

Introduction

A data command is used to initiate an action to perform on a database. To read data of a database, one of the objects you can use is called a data reader. 

If you use a data reader to read some values, the first value is read, followed by the second value, and so on.

If you are planning to use a Microsoft SQL Server database, you can use the SQL data reader, represented by a class named SqlDataReader. To get a data reader, you can declare a variable of type SqlDataReader. This class does not have a constructor. This means that, to use it, you must (directly) specify where it would read its data.

To provide data to the reader, the SqlCommand class is equipped with the ExecuteReader() method that is overloaded with two versions. The simplest version of this method uses the following syntax:

public SqlDataReader ExecuteReader();

Before using a data reader, you should first create a command that would specify how data would be acquired. Once the data is read, you can pass it to the data reader by assigning the result of a call to a SqlCommand.ExecuteReader() method to a SqlDataReader object.

Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection Connect =
    new SqlConnection("Data Source=(local);" +
              "Database='Exercise';" +
              "Integrated Security=SSPI;"))
        {
            string strItems =
                "SELECT * FROM Employees WHERE EmployeeNumber = '72-644';";
            SqlCommand cmdEmployees =
                new SqlCommand(strItems, Connect);

            Connect.Open();

            SqlDataReader rdr = cmdEmployees.ExecuteReader();
        }
    }
}

Using a SQL Data Reader

Once data is supplied to the reader, you can access it, one value at a time, from top to bottom. To access data that the reader acquired, you can call its Read() method whose syntax is:

public override bool Read();

When reading the values, as mentioned already many times, the data reader reads one value at a time and moves to the next.

As you read and access each value by its index, you can retrieve it and do what you want with it. For example you can assign it to a web control to display to the web page visitor.

When reading the records of a table, the data reader reads one record at a time and moves to the next. Before moving to the next record, you can access the values stored in the current record. To help with this, the columns of the table being read are stored in a collection and each column can be referred to with a numeric index. The first column has an index of 1.

Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection Connect =
    new SqlConnection("Data Source=(local);" +
              "Database='Exercise';" +
              "Integrated Security=SSPI;"))
        {
            string strItems =
                "SELECT * FROM Employees WHERE EmployeeNumber = '72-644';";
            SqlCommand cmdEmployees =
                new SqlCommand(strItems, Connect);

            Connect.Open();

            SqlDataReader rdr = cmdEmployees.ExecuteReader();

            while (rdr.Read())
                txtEmployeeName.Text = rdr[1].ToString();
        }
    }
}
 
 
 

The second column has an index of 2, and so on. Here is an example that gets the values of different fields:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection Connect =
    new SqlConnection("Data Source=(local);" +
              "Database='Exercise';" +
              "Integrated Security=SSPI;"))
        {
            string strItems =
                "SELECT * FROM Employees WHERE EmployeeNumber = '52-260';";
            SqlCommand cmdEmployees =
                new SqlCommand(strItems, Connect);

            Connect.Open();

            SqlDataReader rdr = cmdEmployees.ExecuteReader();

            while (rdr.Read()) {
                txtEmployeeNumber.Text = rdr[0].ToString();
                txtEmployeeName.Text   = rdr[1].ToString();
                txtDateHired.Text      = rdr[2].ToString();
                txtHourlySalary.Text   = rdr[3].ToString();
            }
        }
    }
}

To retrieve the actual data stored in a column, you may need to know the type of information the column is holding so you can read it accurately. Depending on the data type that a column was created with, you can access it as follows:

If the column holds the following data type Use the following method System.Data.SqlTypes Equivalent
bit GetBoolean()   GetSqlBoolean()
char, nchar GetChar()   GetSqlChar()
varchar, nvarchar GetString()   GetSqlString()
text, ntext GetString()   GetSqlString()
binary, varbinary GetBinary()   GetSqlBinary()
decimal GetDecimal() GetDouble() GetSqlDecimal()
float GetFloat()   GetSqlSingle()
int GetInt32()   GetSqlInt32()
money, smallmoney GetDecimal()   GetSqlDecimal()
bigint GetInt64()   GetSqlInt64()
datetime, smalldatetime GetDateTime()   GetSqlDateTime()
smallint, tinyint GetInt16() GetByte() GetSqlInt16()
 

When using one of the Get... or GetSql... methods, before sending the data, you must convert the value read to the appropriate (and probably exact) format. Here are examples:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection Connect =
    new SqlConnection("Data Source=(local);" +
              "Database='Exercise';" +
              "Integrated Security=SSPI;"))
        {
            string strItems =
                "SELECT * FROM Employees WHERE EmployeeNumber = '52-260';";
            SqlCommand cmdEmployees =
                new SqlCommand(strItems, Connect);

            Connect.Open();

            SqlDataReader rdr = cmdEmployees.ExecuteReader();

            while (rdr.Read()) {
                txtEmployeeNumber.Text = rdr.GetString(0);
                txtEmployeeName.Text   = rdr.GetString(1);
                txtDateHired.Text      = rdr.GetDateTime(2).ToString();
                txtHourlySalary.Text   = rdr.GetSqlMoney(3).ToString();
            }
        }
    }
}
 

 

   
 

Home Copyright © 2009-2015, FunctionX, Inc.