ADO.NET – Read Records in ASP.NET Core

Learn how to Read Records with ADO.NET and display them to users in an HTML table. In this tutorial, you will fetch data from the Inventory table using the SqlDataReader object, the fastest way to read rows in ADO.NET. This is the Read step of CRUD operations.

In this tutorial you will:

• Connect to SQL Server using SqlConnection
• Run a SELECT query with SqlCommand
• Loop through the results with SqlDataReader
• Show the Inventory records in a table in an ASP.NET Core MVC view

Prerequisite: This lesson follows Learn ADO.NET by Building CRUD Operations in ASP.NET Core, where you created records in the Inventory table. Complete that tutorial first so your table has data to display.

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 “Microsoft.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.

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 Microsoft.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
   
    }
}

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.

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.

To show the Inventory records to users, we render them inside an HTML table in the Razor view. Inside the <tbody> element, a foreach loop goes through every record in the model and outputs each one as a table row (<tr>) with a data cell (<td>) for each column.

<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 column uses the SQL Server money data type, we format it as US currency in C# before displaying it in the table. The string.Format() method does this with two arguments: new CultureInfo("en-US") as the first, which adds the $ sign, and the format string {0:C2} as the second, where C means currency and 2 sets two decimal places, so a value like 25.5 appears as $25.50. The code is shown 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)

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:

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

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

Here we will 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

In this tutorial, you learned how to read records from a SQL Server database table using ADO.NET in ASP.NET Core. You ran a SELECT query with SqlCommand, looped through the results with SqlDataReader, and displayed the Inventory records to users in an HTML table, with the Price field formatted as US currency. This completes the Read part of CRUD operations.

Key Takeaways

• SqlDataReader is a fast, forward-only reader that streams rows from SQL Server one at a time.
• A foreach loop in the Razor view turns each record into a table row.
• string.Format() with {0:C2} formats money values as currency with two decimal places.
• Close or dispose the connection and reader after use, ideally with using statements, to avoid resource leaks.

What’s Next?

Now that you can create and read data, the next step is changing it. In the next tutorial, you will learn how to update records with ADO.NET in ASP.NET Core and save edited values back to the database.

Frequently Asked Questions

How do I read records from SQL Server using ADO.NET in ASP.NET Core? – Open a SqlConnection, create a SqlCommand with a SELECT statement, call ExecuteReader(), and loop with while (reader.Read()) to access each row.

What is SqlDataReader in ADO.NET? – SqlDataReader is a forward-only, read-only object that retrieves rows from SQL Server one at a time, which makes it fast and memory-efficient.

What is the next step after reading records? – Updating existing records.