Use SqlDataReader to Read Data in ASP.NET Core

In the previous article we learned the basics of the ADO.NET data provider for SQL Server. We also took a glance at the SQL Server data provider's object model. Now it's time to put those objects to use in a simple CRUD web application. To that end this article kicks off the development by creating a simple repository for performing the CRUD operations.

In this article we create a simple repository with five methods namely SelectAll(), SelectByID(), Insert(), Update(), and Delete(). These methods will be asynchronous and will do the respective task.

To get started, create a new ASP.NET Core web application using the empty project template. If you created a project as a pert of the previous article, you can open the same project to develop this example.

Then open NuGet package manager dialog and install the Microsoft.Data.SqlClient package as shown below:

And

Then add a new C# class file named GlobalUsings.cs in the project root and place the following code in it.

global using System.Data;
global using Microsoft.Data.SqlClient;
global using System.ComponentModel.DataAnnotations;
global using AdoNetIDemo.DataAccess;

We imported a few namespaces globally. Especially System.Data and Microsoft.Data.SqlClient are important because they provide the main data access classes.

Next, open appsettings.json file and place the following database connection string.

"ConnectionStrings": {
  "AppDb": "data source=.;initial catalog=Northwind;
integrated security=true"
}

Here, I am going to use the Northwind database and Customers table for this example. Make sure to change the database connection string as per your setup.

Next, add a folder named DataAccess in the project root and then add a new C# class file called Customer.cs in it.

Once we fetch data from the Customers table we need to send it to the UI. We will create a record type called Customer for this purpose. You could have also used traditional C# class instead of a record.

Open the Customer.cs file and add the following code to it:

namespace AdoNetIDemo.DataAccess;

public record Customer(
    [Required] string CustomerID, 
    [Required] string CompanyName, 
    [Required] string ContactName, 
    [Required] string Country
);

As you can see, we declared a record called Customer that has four properties namely CustomerID, CompanyName, ContactName, and Country. All the properties are decorated with [Required] attribute for the sake of data validation.

I have used a shortcut way of declaring a record (positional record). If you want you can also use a more elaborate class-like syntax involving init only properties :

public record Customer
{
    [Required]
    public string CustomerID { get; init; }  

    [Required]
    public string CompanyName { get; init; }

    [Required]
    public string ContactName { get; init; }

    [Required]
    public string Country { get; init; }
}

You can read more about record type in the official documentation here and here.

Add the AdoNetIDemo.DataAccess namespace to GlobalUsings.cs file before moving ahead.

Now add a new interface named ICustomerRepository to the DataAccess folder and write the following code in it.

namespace AdoNetIDemo.DataAccess;

public interface ICustomerRepository
{
    Task<List<Customer>> SelectAll();
    Task<Customer> SelectByID(string id);   
    Task<int> Insert(Customer customer);
    Task<int> Update(Customer customer);
    Task<int> Delete(int id);
}

The ICustomerRepository interface contains the skeleton of our repository class and has five methods - SelectAll(), SelectByID(), Insert(), Update(), Delete().

Since we want our repository to be async, we return Task from the interface methods.

Now add CustomerRepository class into the DataAccess folder and implement ICustomerRepository interface in it.

namespace AdoNetIDemo.DataAccess;


public class CustomerRepository : ICustomerRepository
{
  // your code here
}

The CustomerRepository class will need the database connection string stored in the appsettings.json file. So, we inject IConfiguration object into the constructor and read the connection string as shown below:

private readonly string connectionString;

public CustomerRepository(IConfiguration config)
{
    connectionString = config.GetConnectionString("AppDb");
}

The GetConnectionString() method of IConfiguration accepts the connection string name from the ConnectionStrings section and returns that connecting string value.

We will now implement the SelectAll() method of  the repository that reads the Customers table. Take a look :

public async Task<List<Customer>> SelectAll()
{
    using var connection = new SqlConnection(connectionString);

    var query = "Select CustomerID, CompanyName, ContactName, 
Country From Customers Order By CustomerID";

    using var command = new SqlCommand(query, connection);

    try
    {
        await connection.OpenAsync();
        using var reader = await command.ExecuteReaderAsync();
        List<Customer> items = null;
        if (reader.HasRows)
        {
            items = new List<Customer>();
            while (await reader.ReadAsync())
            {
                var item = new Customer(
                    CustomerID: reader.GetString(0),
                    CompanyName: reader.GetString(1),
                    ContactName: reader.GetString(2),
                    Country: reader.GetString(3));
                items.Add(item);
            }
        }
        await reader.CloseAsync();
        await connection.CloseAsync();
        return items;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return null;
    }
}

This code begins by creating a new SqlConnection object. Notice the use of using declaration while creating the SqlConnection. That will ensure that the resources consumed by the connection are reclaimed properly when the connection is closed.

We are interested to retrieve all the Customers from the database (We need CustomerID, CompanyName, ContactName, and Country columns). So, we store a SELECT query for doing that in a string variable.

Then we create a SqlCommand object by specifying this query and the SqlConnection. A SqlCommand object represents a select or action query (or stored procedure) that you intend to execute on the database. A SqlCommand requires an open database connection to execute any queries. So, we open the database connection by calling OpenAsync() method of SqlConnection.

To execute this SELECT query against the database we use ExecuteReaderAsync() method of SqlCommand. The ExecuteReaderAsync() method executes the query and returns a SqlDataReader. A SqlDataReader is read-only, forward-only cursor that allows you to iterate through the result set and read one record at a time.

To read and store the customer data we create a List of Customer records (C# record). It makes sense to iterate through the result set only if there are any rows in it. So, we check the HasRows property of SqlDataReader that tells us just that. If HasRows returns true we go ahead and run a while loop. Initially, SqlDataReader's current row pointer is placed before the first row. Calling ReadAsync() method does two things - it advances the row pointer to the next row and reads data for that row.

Inside the while loop we create a Customer item by specifying its positional properties - CustomerID, CompanyName, ContactName, and Country. To set values to these positional properties we use GetString() method of SqlDataReader. The GetString() method accepts a column index (index starts from 0 and sequence is same as they appear in the query) and returns the value from that column. Just like GetString() there are different methods for different data types such as GetInt32(), GetBoolean(), and GetDateTime().

Once a new Customer item is created we add it to the List<Customer>. The ReadAsync() method returns false when it reaches past the end of the result set. We then close the SqlDataReader and SqlConnection by calling their CloseAsync() methods respectively. Strictly speaking closing a connection also closes the data reader. Here, I am explicitly closing both of them just to highlight that SqlDataReader has CloseAsync() method (you will learn more about it in a later part of this article series).

Finally, List<Customer> is returned to the caller.

The whole while loop and data reading logic is placed inside a try-catch block to trap any unexpected errors. In case of any error, we close the SqlConnection if it's open and return null to the caller. The State property is an enumeration of type ConnectionState and contains various possible values such as Closed, Open, and Broken.

This completes the SelectAll() method.

Now let's add the SelectByID() method.  

public async Task<Customer> SelectByID(string id)
{
    using var connection = new SqlConnection(connectionString);

    var query = "Select CustomerID, CompanyName, ContactName, 
Country From Customers Where CustomerID = @CustomerID";

    using var command = new SqlCommand(query, connection);

    command.Parameters.AddWithValue("CustomerID", id);

    try
    {
        await connection.OpenAsync();
        using var reader = await command.ExecuteReaderAsync();
        Customer item = null;
        if (reader.HasRows)
        {
            while (await reader.ReadAsync())
            {
                item = new Customer(
                    CustomerID: reader.GetString
(reader.GetOrdinal("CustomerID")),
                    CompanyName: reader.GetString
(reader.GetOrdinal("CompanyName")),
                    ContactName: reader.GetString
(reader.GetOrdinal("ContactName")),
                    Country: reader.GetString
(reader.GetOrdinal("Country")));
            }
        }
        await reader.CloseAsync();
        await connection.CloseAsync();
        return item;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return null;
    }
}

The SelectByID() method accepts a CustomerID and returns the matching Customer to the caller. The overall working of this method is similar to SelectAll() because here also we need to read Customer data from the database. However, this time we fetch only a single Customer row. Notice the code marked in bold letters.

The SELECT query now has WHERE with a @CustomerID parameter. To specify this query parameter's value we use AddWithValue() method of the SqlCommand's Parameter collection. The AddWithValue() method takes the parameter name and its value. Internally, it adds a SqlParameter object to the Parameters collection.

This time instead of creating a List<Customer> we create just a single Customer record. The item gets filled inside the while loop and is then returned to the caller.

Previously we used GetString() calls and specified the column index. Many a times knowing and hard-coding a column index can be problematic. So, this time we use GetOrdinal() method of SqlDataReader. The GetOrdinal() method accepts a column name and returns its index.

This completes the SelectByID() method.

In the next part of this article series we will add Insert(), Update(), and Delete() methods to the CustomerRepository. 

That's it for now! Keep coding!!