Read Records using ADO.NET in ASP.NET Core Application

Read Records using ADO.NET in ASP.NET Core Application

I will now create Read operation in ADO.NET to display all inventory records in the View. I will use SqlDataReader object for implementing this operation.

Go to the ‘Home’ Controller and remove the old Index Action method from it. Next add the new updated Index Action to the controller, so that your controller code looks as shown below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ADO.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

namespace ADO.Controllers
{
    public class HomeController : Controller
    {
        public IConfiguration Configuration { get; }
        public HomeController(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IActionResult Index()
        {
            List<Inventory> inventoryList = new List<Inventory>();

            string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                string sql = "Select * From Inventory";
                SqlCommand command = new SqlCommand(sql, connection);

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        Inventory inventory = new Inventory();
                        inventory.Id = Convert.ToInt32(dataReader["Id"]);
                        inventory.Name = Convert.ToString(dataReader["Name"]);
                        inventory.Price = Convert.ToDecimal(dataReader["Price"]);
                        inventory.Quantity = Convert.ToInt32(dataReader["Quantity"]);
                        inventory.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
                        
                        inventoryList.Add(inventory);
                    }
                }

                connection.Close();
            }
            return View(inventoryList);
        }

        // removed for clarity
    }
}

Explanation

On the first line I have declared a list type variable – List inventoryList = new List();. I will be adding all the inventory records to it and then return it to the Index View as a Model.

Next, I am executing a simple ‘select query’ using the SqlCommand’s ExecuteReader() method. This will get me a SqlDataReader object to loop through all the records in the Connection environment. This code is shown below:

string sql = "Select * From Inventory";
SqlCommand command = new SqlCommand(sql, connection);

using (SqlDataReader dataReader = command.ExecuteReader())
{
…
}

Next, I use the .Read() method of SqlDataReader class to advance from one record to the next.

At the same time I am adding all the column’s value to an Inventory object like this:

Inventory inventory = new Inventory();
inventory.Id = Convert.ToInt32(dataReader["Id"]);
inventory.Name = Convert.ToString(dataReader["Name"]);
inventory.Price = Convert.ToDecimal(dataReader["Price"]);
inventory.Quantity = Convert.ToInt32(dataReader["Quantity"]);
inventory.AddedOn = Convert.ToDateTime(dataReader["AddedOn"]);

Finally I add adding this ‘Inventory’ object to my List object.

inventoryList.Add(inventory);

So this way all my Inventory tables records are filled to my span class=”code”>List object. Lastly, I return this object to my View.

return View(inventoryList);

Now, you also have to add the Index View inside Views/Home folder in order to show all these records.

The Index View code is given below:

@model IEnumerable<Inventory>

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ADO - Read Inventory</title>
    <link rel="stylesheet" asp-href-include="lib/bootstrap/dist/css/*.min.css" />
</head>
<body>
    <div class="container-fluid">
        <h1>Inventory <a asp-action="Create" class="btn btn-sm btn-secondary">Create</a></h1>
        <table class="table table-bordered table-sm table-striped">
            <thead>
                <tr><th>Id</th><th>Name</th><th>Price</th><th>Quantity</th><th>Added On</th></tr>
            </thead>
            <tbody>
                @if (Model == null)
                {
                    <tr><td colspan="5" class="text-center">No Model Data</td></tr>
                }
                else
                {
                    @foreach (var p in Model)
                    {
                        <tr>
                            <td>@p.Id</td>
                            <td>@p.Name</td>
                            <td>@string.Format(new System.Globalization.CultureInfo("en-US"),"{0:C2}", p.Price)</td>
                            <td>@p.Quantity</td>
                            <td>@string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)</td>
                        </tr>
                    }
                }
            </tbody>
        </table>
    </div>
</body>
</html>

The View takes @model IEnumerable model because the Index action sends a List of Inventory objects.

I am showing these records in an Html Table. So inside the ‘tbody’ element I loop through all these records and show them one by one in a ‘tr’ element. The corresponding code is shown below:

<tbody>
    @if (Model == null)
    {
        <tr><td colspan="5" class="text-center">No Model Data</td></tr>
    }
    else
    {
        @foreach (var p in Model)
        {
            <tr>
                <td>@p.Id</td>
                <td>@p.Name</td>
                <td>@string.Format(new System.Globalization.CultureInfo("en-US"),"{0:C2}", p.Price)</td>
                <td>@p.Quantity</td>
                <td>@string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)</td>
            </tr>
        }
    }
</tbody>

Since the Price is a money field therefore I am showing it upto 2 places of decimal, with a $ sign in front of them. This is done by the string.Format method, which takes first parameter as a CultureInfo(“en-US”) for showing the $ sign, and the second parameter as {0:C2} for showing the money value upto 2 places of decimal. This code is given below:

string.Format(new System.Globalization.CultureInfo("en-US"),"{0:C2}", p.Price)

In the same way I showed the ‘AddedOn’ field in the long form of date, by using the string.Format method as:

string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn)

Testing Read Inventory Feature

Now it’s time to test the working of this feature by simply running your application. The browser will open the Index View and show all the inventory records in the database, like the below image:

read inventory records in index view

If you want to display Inventory Records in “page-by-page” manner then check how to do it by reading my tutorial – Custom Paging in Asp.Net without using controls like GridView and Repeater.
Using Stored Procedure with SqlDataReader

I could also use a Stored Procedure with SqlDataReader to read all inventories from the table. To do this first add the stored procedure to the SQL Server:

CREATE PROCEDURE [dbo].[ReadInventory1]
AS     
BEGIN     
  Select * From Inventory
End

Now in your code just give the SP name for the 1st parameter of SqlCommand object and set the CommandType to StoredProcedure. This code is given below:

…
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "ReadInventory";
    SqlCommand command = new SqlCommand(sql, connection);
    command.CommandType = CommandType.StoredProcedure;

    using (SqlDataReader dataReader = command.ExecuteReader())
    {
        while (dataReader.Read())
        {
            //…
        }
    }

    connection.Close();
}
…

Understanding SqlDataAdapter Object

You can also use SqlDataAdapter class to read data from the database table. But unlike SqlDataReader, the SqlDataAdapter object opens the connection automatically when filling the data from the database, it also closes it as soon as the data is fetched completely.

That means you don’t have to explicitly open and close the connection. When defining an SqlDataAdapter object provide it’s constructor the SqlCommand object, like this:

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlcommand);

The SqlDataAdapter has a .fill() method to add the records from the database to a DataTable object.

So that means after filling, the DataTable has all the records in memory for you to iterate over the records as many times as you need, or even look up a specific record by it’s index.

The below code snippet shows the SqlDataAdapter filling records to a DataTable object.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    DataTable dataTable = new DataTable();

    string sql = "Select * From Inventory";
    SqlCommand command = new SqlCommand(sql, connection);

    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

    // filling records to DataTable
    dataAdapter.Fill(dataTable);
}

If you want to read records using Stored Procedure then the above code becomes:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    DataTable dataTable = new DataTable();

    string sql = "ReadInventory1";
    SqlCommand command = new SqlCommand(sql, connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

    dataAdapter.Fill(dataTable);
}

This time I use SqlDataAdapter to read records from the Inventory table and send them to the Index view for viewing. So all you have do is to change the Index action method code to the one with SqlDataAdapter given below:

public IActionResult Index()
{
    List<Inventory> inventoryList = new List<Inventory>();

    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        DataTable dataTable = new DataTable();

        string sql = "Select * From Inventory";
        SqlCommand command = new SqlCommand(sql, connection);

        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

        dataAdapter.Fill(dataTable);

        foreach(DataRow dr in dataTable.Rows)
        {
            Inventory inventory = new Inventory();
            inventory.Id = Convert.ToInt32(dr["Id"]);
            inventory.Name = Convert.ToString(dr["Name"]);
            inventory.Price = Convert.ToDecimal(dr["Price"]);
            inventory.Quantity = Convert.ToInt32(dr["Quantity"]);
            inventory.AddedOn = Convert.ToDateTime(dr["AddedOn"]);

            inventoryList.Add(inventory);
        }

        connection.Close();
    }
    return View(inventoryList);
}

One the DataTable is filled with records, you need to loop through them like:

foreach(DataRow dr in dataTable.Rows)
{
}

And get the column’s value like:

inventory.Id = Convert.ToInt32(dr["Id"]);

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to Read Records from a database table using ADO.NET. In the next tutorial I will teach you how to Update these Records in the database table using ADO.NET.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.