Beginning .Net : Working with DataReader with C# Examples and VB.Net Examples

If you need to process the records one by one or iterate the records from database table for that you need to use IDataReader instance. There are various implementation of IDataReader class. We are using System.Data.SqlClient.SqlDataReader class to iterate the records.
The IDataReader interface extends the System.Data.IDataRecord interface. These interfaces declare the functionality to access both the data and the structure of the data contained in the result set.

Here is the example.
In this example we execute two select queries first query is for to get product details and display it's records on screen, and second query is for get employee_master details and display it's table's metadata details on screen.

C# Examples :

        // Create a new SqlConnection object.
        using (SqlConnection objCon = new SqlConnection())
        {
            // Configure the SqlConnection object's connection string.
            objCon.ConnectionString =@"Data Source=JAYESH-PC\SQLEXPRESS;" + 
                                     "Initial Catalog=TempDatabase;" +  
                                     "User ID=sa;Password=ibmx206;";  
            using (SqlCommand objCommand = objCon.CreateCommand())
            {
                objCommand.CommandType = CommandType.Text;
                objCommand.CommandText = "SELECT product_id,product_name FROM product_master " +
                " ORDER BY product_name;SELECT * FROM employee_master";
                
                objCon.Open();
                // Execute the command and obtain a SqlReader.
                using (SqlDataReader objReader = objCommand.ExecuteReader())
                {
                    // Process the first set of results and display the
                    // content of the result set.
                    Response.Write("Prodct details (By Product Name).");

                    while (objReader.Read())
                    {
                        Response.Write(string.Format(" <br/> {0} - {1} ",
                        objReader["product_id"], // Use string index
                        objReader[1])); // Use ordinal index
                    }
                    Response.Write("<br/><br/>");
                    // Process the second set of results and display 
                    objReader.NextResult();
                    Response.Write("Employee Details.");
                    for (int field = 0; field < objReader.FieldCount; field++)
                    {
                        Response.Write(string.Format("<br/><b> Column Name:</b>{0}   <b>Data Type:</b>{1}",
                        objReader.GetName(field),
                        objReader.GetDataTypeName(field)));
                    }
                }
            }
        }

VB.net Examples :

        ' Create a new SqlConnection object.
        Using objCon As New SqlConnection()
            ' Configure the SqlConnection object's connection string.
            objCon.ConnectionString = "Data Source=JAYESH-PC\SQLEXPRESS;" & "Initial Catalog=TempDatabase;" & "User ID=sa;Password=ibmx206;"
            Using objCommand As SqlCommand = objCon.CreateCommand()
                objCommand.CommandType = CommandType.Text
                objCommand.CommandText = "SELECT product_id,product_name FROM product_master " & " ORDER BY product_name;SELECT * FROM employee_master"

                objCon.Open()
                ' Execute the command and obtain a SqlReader.
                Using objReader As SqlDataReader = objCommand.ExecuteReader()
                    ' Process the first set of results and display the
                    ' content of the result set.
                    Response.Write("Prodct details (By Product Name).")

                    While objReader.Read()
                        ' Use string index
                        ' Use ordinal index
                        Response.Write(String.Format(" <br/> {0} - {1} ", objReader("product_id"), objReader(1)))
                    End While
                    Response.Write("<br/><br/>")
                    ' Process the second set of results and display 
                    objReader.NextResult()
                    Response.Write("Employee Details.")
                    For field As Integer = 0 To objReader.FieldCount - 1
                        Response.Write(String.Format("<br/><b> Column Name:</b>{0}   <b>Data Type:</b>{1}", objReader.GetName(field), objReader.GetDataTypeName(field)))
                    Next
                End Using
            End Using
        End Using

Output :

You can access the SqlDataReder objects data using Field name, ordinal index and typed data methods. The typed data contains various methods like GetBoolean, GetByte, GetBytes, GetChar, GetChars, GetDateTime, GetDecimal, GetDouble, GetFloat, GetGuid, GetInt16, GetInt32, GetInt64, GetString, GetValue, and GetValues. Each of the these methods takes an integer argument that identifies the zero-based index of the column from which the data should be returned.

After the use of the data reader, you must have to call its Close method so that you can use the database connection again. IDataReader extends System.IDisposable interface, so that each data reader class implements the Dispose method. Dispose automatically calls Close method.

This article is very useful for .Net Beginners.

Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.