SqlDataReader object in ADO.NET - Part 8

Suggested Videos
Part 5 - Sql injection tutorial
Part 6 - Sql injection prevention
Part 7 - Calling a stored procedure with output parameters

In this video we will learn about
1. The purpose of SqlDataReader
2. Creating an instance of SqlDataReader
3. Reading data using SqlDataReader's Read() method

SqlDataReader reads data in the most efficient manner possible. SqlDataReader is read-only and forward only, meaning once you read a record and go to the next record, there is no way to go back to the previous record. It is also not possible to change the data using SqlDataReader. SqlDataReader is connection oriented, meaning it requires an active connection to the data source, while reading data. The forward-only nature of SqlDataReader is what makes it an efficient choice to read data.

You cannot create an instance of SqlDataReader using the new operator as shown below. If you try to new up SqlDataReader, you will get a compilation error stating - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.
SqlDataReader rd = new SqlDataReader();

The SqlCommand object's ExecuteReader() method creates and returns an instance of SqlDataReader. 
SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);
SqlDataReader reader = command.ExecuteReader();



Another important point to keep in mind is that, SqlDataReader is connection oriented and the connection needs to be opened explicitly, by calling the Open() method on the connection object, before calling the ExecuteReader() method of the command object.

The simplest way to bind a SqlDataReader object to a GridView(Data-bound control), is to assign it to the DataSource property of the GridView control and then call the DataBind() method as shown below. Notice that, just like the SqlConnection object, SqlDataReader is wrapped in an using block. This will ensure that the SqlDataReader is closed in a timely fashion, and that we don't run out of available connections to the database.
string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        ProductsGridView.DataSource = reader;
        ProductsGridView.DataBind();
    }
}

Please note that, finally block, can also be used to close the SqlConnection and SqlDataReader objects. 


If for some reason, you want to loop thru each row in the SqlDataReader object, then use the Read() method, which returns true as long as there are rows to read. If there are no more rows to read, then this method will return false. In the following example, we loop thru each row in the SqlDataReader and then compute the 10% discounted price. 
string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Create the DataTable and columns. This will 
        // be used as the datasource for the GridView
        DataTable sourceTable = new DataTable();
        sourceTable.Columns.Add("ID");
        sourceTable.Columns.Add("Name");
        sourceTable.Columns.Add("Price");
        sourceTable.Columns.Add("DiscountedPrice");

        while (reader.Read())
        {
            //Calculate the 10% discounted price
            int OriginalPrice = Convert.ToInt32(reader["UnitPrice"]);
            double DiscountedPrice = OriginalPrice * 0.9;

            // Populate datatable column values from the SqlDataReader
            DataRow datarow = sourceTable.NewRow();
            datarow["ID"] = reader["ProductId"];
            datarow["Name"] = reader["ProductName"];
            datarow["Price"] = OriginalPrice;
            datarow["DiscountedPrice"] = DiscountedPrice;

            //Add the DataRow to the DataTable
            sourceTable.Rows.Add(datarow);
        }

        // Set sourceTable as the DataSource for the GridView
        ProductsGridView.DataSource = sourceTable;
        ProductsGridView.DataBind();
    }
}

SQL script to create the table we used in the Demo.
Create table tblProductInventory 
(
ProductId int identity primary key,
ProductName nvarchar(50),
UnitPrice int
)

Script to populate data
Insert into tblProductInventory values('iPhone',350)
Insert into tblProductInventory values('Apple Laptops',1250)
Insert into tblProductInventory values('Books',110)
Insert into tblProductInventory values('Acer Laptops',1150)
Insert into tblProductInventory values('iPads',450)

Note: Please make sure you have the following namespace declarations in the code behind file.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

6 comments:

  1. Venkat Sir... you have changed many lives... thanks for your efforts...

    ReplyDelete
  2. Mr.Venkat,
    I am living in USA having 20+ years experience in Controls Engineering. I salute you for all the help you are providing to fresh as well as experienced people.
    Regards,
    Your Student

    ReplyDelete
  3. Hi sir .
    i want to know how to return sql datareader values like
    In data Access Layer i have sql Data Reader.i want to read these values fro dr and populate these values on my application Layer..
    Data Access Layer::

    sqldatareader dr=new sqlDatareader();
    if(dr.read()){
    dr[0].tostring();
    .
    .
    dr[n].tostring();
    ------------------------
    how these values i get on application layer?

    ReplyDelete
  4. Hello Saleem,

    You can create a custom object to hold the values returned from the SqlDataReader and return that to your application layer.


    //Custom Class
    public class myObject
    {
    public string Name {get;set;}
    }

    ...

    In DataAccess Layer:

    public myObject GetValuesFromDatabase()
    {
    //connection
    ...
    using(SqlDataReader rdr = command.ExecuteReader())
    {

    MyObject customObj = new MyObject();
    while( rdr.Read() )
    {
    customObj.Name = rdr["columnName"].ToString();
    }
    //Once you loop over the SqlDataReader and set the obj properties; return the object.
    return customObj;
    }

    }

    ReplyDelete
  5. //Hopefully This will help you.
    //Take a class library named anything. But I've taken DAL. //That means Data Access Layer. Then...

    public static class DataAccess
    {
    #region User Authentication
    //Creating a boolean type method which will return only true or false depending on whether the Usernme and Password is matched or not;
    //Remember that this method will take values from the GUI textbox and then match it with the database using GetAccess() method below;
    public static bool isAuthenticatedUser(string paraSetUsername, string paraSetPassword)
    {
    bool isAuthentic = false;
    isAuthentic = GetAccess(paraSetUsername, paraSetPassword);//Here is the method GetAccess() below which take two parameters USername and Password
    return isAuthentic;
    }

    //
    private static bool GetAccess(string paraGetUsername, string paraGetPassword) //These parameters will keep passing the value of user's input into the selected field!
    {
    string connectionString = "Data Source=.; Initial Catalog=YourDatabaseName; Integrated Security=True";
    SqlConnection conn;
    SqlCommand cmd;
    SqlDataReader dataReader;
    string sqlSUCKER = string.Format("SELECT pass FROM Register WHERE username = @username");
    string suckedHashedPassword = string.Empty;
    conn = new SqlConnection(connectionString);
    try
    {
    conn.Open();
    cmd = new SqlCommand(sqlSUCKER, conn);
    cmd.Parameters.AddWithValue("@username", paraGetUsername);
    dataReader = cmd.ExecuteReader();

    while (dataReader.Read())
    {
    suckedHashedPassword = dataReader.GetValue(0).ToString();
    }
    if (SampleHash.SampleHash.VerifyHash(paraGetPassword, "SHA256", suckedHashedPassword.ToString()))
    {
    return true;
    }
    else
    return false;
    }
    catch (Exception ex)
    {
    return false;
    }
    finally
    {
    conn.Close();
    }
    }
    #endregion
    }

    ReplyDelete
  6. I'm getting following error while executing this program .Cannot implicitly convert type 'System.Data.SqlClient.SqlDataReader' to 'Test1.SqlDataReader'

    Plz help

    ReplyDelete

It would be great if you can help share these free resources