Fetching Oracle Data in ASP.NET Core MVC using Repository and Service Patterns

In modern web development, keeping your data access logic separate from your business logic is essential for building scalable and maintainable applications. This tutorial demonstrates how to retrieve records from an Oracle Database—specifically handling the common ORA-01861 literal format error—using a robust architecture in ASP.NET Core MVC.

Prerequisites

Visual Studio 2022.
Oracle Managed Data Access (Oracle.ManagedDataAccess.Core) NuGet package.
Dapper NuGet package for lightweight ORM mapping.
Bootstrap 5 for the user interface.

1. The Model (`CommissionRecord.cs`)

First, we define a class that maps directly to the columns of your Oracle table, such as COMMISSION_INFORMATION_DETAIL. Note that we match the exact database column names (including any specific spellings like COMMISION_DATE).

public class CommissionRecord
{
    public string KioskId { get; set; }
    public string Product { get; set; }
    public int Transactions { get; set; }
    public decimal Commission { get; set; }
    public DateTime CommisionDate { get; set; } 
    public int CommissionDetailId { get; set; }
}

2. The Repository Layer (`ICommissionRepository.cs`)

The repository handles the raw SQL communication. To prevent the ORA-01861: literal does not match format string error, we use parameterized queries instead of hardcoding strings into the SQL.

public interface ICommissionRepository
{
    Task<IEnumerable<CommissionRecord>> GetRecordsAsync(string kioskId, DateTime date);
}

public class CommissionRepository : ICommissionRepository
{
    private readonly string _connectionString;
    public CommissionRepository(IConfiguration config) => _connectionString = config.GetConnectionString("OracleDb");

    public async Task<IEnumerable<CommissionRecord>> GetRecordsAsync(string kioskId, DateTime date)
    {
        using var conn = new OracleConnection(_connectionString);
        
        // TRUNC() ensures we compare only the date part, ignoring the time
        const string sql = @"SELECT KIOSK_ID as KioskId, PRODUCT, TRANSACTIONS, 
                             COMMISSION, COMMISION_DATE as CommisionDate, 
                             COMMISSION_DETAIL_ID as CommissionDetailId 
                             FROM COMMISSION_INFORMATION_DETAIL 
                             WHERE KIOSK_ID = :kId 
                             AND TRUNC(COMMISION_DATE) = TRUNC(:dt)";

        // Dapper safely maps the C# DateTime to an Oracle Date parameter
        return await conn.QueryAsync<CommissionRecord>(sql, new { kId = kioskId, dt = date });
    }
}

3. The Service Layer (`ICommissionService.cs`)

The service layer sits between the controller and the repository. It is responsible for any business logic or validation before querying the database.

public interface ICommissionService
{
    Task<IEnumerable<CommissionRecord>> SearchCommissionAsync(string kioskId, DateTime date);
}

public class CommissionService : ICommissionService
{
    private readonly ICommissionRepository _repository;
    public CommissionService(ICommissionRepository repository) => _repository = repository;

    public async Task<IEnumerable<CommissionRecord>> SearchCommissionAsync(string kioskId, DateTime date)
    {
        // Example logic: Only allow searches for dates in the past or today
        if (date > DateTime.Now) return Enumerable.Empty<CommissionRecord>();

        return await _repository.GetRecordsAsync(kioskId, date);
    }
}

4. The Controller (`CommissionController.cs`)

The controller handles the HTTP requests. We use ViewBag to keep the user's input visible in the search boxes after the results load.

public class CommissionController : Controller
{
    private readonly ICommissionService _service;
    public CommissionController(ICommissionService service) => _service = service;

    [HttpGet]
    public IActionResult Index() => View(new List<CommissionRecord>());

    [HttpPost]
    public async Task<IActionResult> Index(string kioskId, DateTime? searchDate)
    {
        if (string.IsNullOrEmpty(kioskId) || !searchDate.HasValue)
            return View(new List<CommissionRecord>());

        var results = await _service.SearchCommissionAsync(kioskId, searchDate.Value);
        
        ViewBag.KioskId = kioskId;
        ViewBag.SearchDate = searchDate.Value.ToString("yyyy-MM-dd");
        
        return View(results);
    }
}

5. The View with Bootstrap UI (`Index.cshtml`)

A clean, responsive UI helps users navigate data effectively. This layout uses Bootstrap cards and icons for a modern feel.

HTML

@model IEnumerable<CommissionRecord>

<div class="container mt-4">
    <div class="card shadow border-0">
        <div class="card-header bg-dark text-white">
            <h5 class="mb-0">Oracle Data Lookup</h5>
        </div>
        <div class="card-body">
            <form asp-action="Index" method="post" class="row g-3">
                <div class="col-md-5">
                    <label class="form-label">Kiosk ID</label>
                    <input type="text" name="kioskId" value="@ViewBag.KioskId" class="form-control" required />
                </div>
                <div class="col-md-5">
                    <label class="form-label">Search Date</label>
                    <input type="date" name="searchDate" value="@ViewBag.SearchDate" class="form-control" required />
                </div>
                <div class="col-md-2 d-flex align-items-end">
                    <button type="submit" class="btn btn-primary w-100">Search</button>
                </div>
            </form>
        </div>
    </div>

    <table class="table table-hover mt-4">
        <thead class="table-light">
            <tr>
                <th>Product</th>
                <th>Transactions</th>
                <th>Commission</th>
                <th>Date</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var row in Model) {
                <tr>
                    <td>@row.Product</td>
                    <td>@row.Transactions</td>
                    <td class="text-success fw-bold">@row.Commission.ToString("N2")</td>
                    <td>@row.CommisionDate.ToShortDateString()</td>
                </tr>
            }
        </tbody>
    </table>
</div>

6. Registration (`Program.cs`)

Finally, register your dependencies in the application builder.

builder.Services.AddScoped<ICommissionRepository, CommissionRepository>();
builder.Services.AddScoped<ICommissionService, CommissionService>();

Conclusion

By using the Repository and Service patterns, you ensure that your code is clean and decoupled. More importantly, using parameterized queries in Oracle is the key to avoiding format errors and securing your application against SQL injection.

Prerequisites

1. The Model (CommissionRecord.cs)

2. The Repository Layer (ICommissionRepository.cs)

3. The Service Layer (ICommissionService.cs)

4. The Controller (CommissionController.cs)

5. The View with Bootstrap UI (Index.cshtml)

6. Registration (Program.cs)