ADO.NET – Read Records in ASP.NET Core

Last Updated: August 5, 2022

In this tutorial we will Read Records with ADO.NET and display these records in the form of a table. The records will be read from the Inventory table of the database. We will use SqlDataReader object for implementing this operation.

Can we use ADO.NET in .NET Core? Yes, we can use ADO.NET in .NET Core application. First we have to add Data Provider for the database. For example – We can install “System.Data.SqlClient” NuGet package for .NET Data Provider for SQL Server. After we have a data provider, we can start building ADO.NET codes and communicate with any database.

Page Contents

This tutorial is a part of ADO.NET series.

1. Learn ADO.NET by building CRUD Operations in ASP.NET Core
2. ADO.NET – Create Records in ASP.NET Core
3. ADO.NET – Read Records in ASP.NET Core
4. ADO.NET – Update Records in ASP.NET Core
5. ADO.NET – Delete Records in ASP.NET Core
6. How to do ADO.NET SqlTransaction with Try Catch Block
7. Learn to use ADO.NET SqlBulkCopy class with Example

ADO.NET Read Records Example

In the Home Controller, update the Index Action’s code as shown below.

using ADO.Models;
using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.Data.SqlClient;

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);
        }

        // other actions
   
    }
}

Explanation

We declared a List type variable – List inventoryList = new List(). To this variable we will be adding all the inventory records and then return this variable to the Index View as a Model.

Interesting article – How to use Select2 jQuery plugin in ASP.NET CORE

Next, we are executing a simple select query using the ADO.NET SqlCommand’s ExecuteReader() method. This will give us a SqlDataReader object to loop through all the records in the connected environment. This code is shown below:

string sql = "Select * From Inventory";
SqlCommand command = new SqlCommand(sql, connection);
 
using (SqlDataReader dataReader = command.ExecuteReader())
{
…
}

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

At the same time we are adding all the column’s value to an Inventory type 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 we add this Inventory object to the List object.

inventoryList.Add(inventory);

So this way, all my Inventory table’s records are filled to the List object. Lastly, we return this object to the View.

return View(inventoryList);

We also have to add the Index View inside Views ➤ Home folder where these records are displayed.

The Index View code is given below:

@model IEnumerable<Inventory>

@{
	ViewData["Title"] = "Read Inventory";
}

<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>

The View takes @model IEnumerable<Inventory> model because the Index action returns a List of Inventory objects to the view.

We are showing these records in an Html Table. See inside the tbody element, we loop through all these records and show them one by one in tr & td elements. 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 field is a SQL Money Type therefore we are showing it up to 2 places of decimal, with a $ sign in front. 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 up to 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 we show 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 action and show all the inventory records read from the database. Check the below image:

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

Read Records in ADO.NET by executing Stored Procedure

Now we will learn to use ADO.NET to execute Stored Procedures. The stored procedures can do a variety of functions, like in our case returns all the records of the database table. Next, with SqlDataReader we will loop through these records in C# code. So, first add the stored procedure (given below) to the SQL Server database:

CREATE PROCEDURE [dbo].[ReadInventory]
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();
}
…

Run the application once more and the records will be displayed just like before, but this time we made use of a stored procedure.

Read Records with ADO.NET SqlDataAdapter

We will now use ADO.NET SqlDataAdapter to read records from the Inventory table and send them to the Index View for displaying. So all we have do is to change the Index action method code to the one with SqlDataAdapter, as shown 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);
}

Once the DataTable is filled with records, we loop through them by the use of the below given code:

foreach(DataRow dr in dataTable.Rows)
{
}

We get the column’s value like:

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

Test it yourself by running the application once more.

You can download the source code using the below link:

Download

Conclusion

In this ASP.NET Core tutorial we learned to Read Records from a database table using ADO.NET. In the next tutorial we will see – ADO.NET – Update Records in ASP.NET Core.