OracleDbUtils 1.0.0

OracleDbUtils

OracleDbUtils is a .NET utility library that simplifies working with Oracle databases using Oracle.ManagedDataAccess.Core. It provides a clean abstraction layer for executing stored procedures, querying views/tables, and generating reports via Oracle Reports.

✨ Features

• ✅ Easy execution of Oracle PL/SQL procedures and functions
• ✅ Query tables and views with strongly-typed DTOs
• ✅ Pagination support
• ✅ Oracle Reports generation using HTTP API
• ✅ Custom attribute-based mapping system

📦 Installation

Install via NuGet:

dotnet add package OracleDbUtils

🚀 Quick Start

1. Execute a PL/SQL Package Procedure

public class MyProcEntity : OracleEntity
{
    [OracleParameter("P_ID", OracleDbType.Int32)]
    public int Id { get; set; }

    public override void EnsureEntity()
    {
        if (Id <= 0)
            throw new OraException("600", "Id must be positive.");
    }
}

var entity = new MyProcEntity { Id = 123 };
var result = await dbContext.ExecutePackageAsync(entity, "MY_PACKAGE.MY_PROCEDURE");

2. Query a View or Table

public class EmployeeDto : OracleBaseDto
{
    [OracleField("EMP_ID", OracleDbType.Int32)]
    public int Id { get; set; }

    [OracleField("EMP_NAME", OracleDbType.Varchar2)]
    public string Name { get; set; } = string.Empty;
}

var employees = await dbContext.SelectViewAsync<EmployeeDto>(
    viewName: "EMPLOYEES_VIEW",
    conditions: new Dictionary<string, object> { ["DEPT_ID"] = 10 },
    orderBy: "EMP_NAME",
    pagination: new OraclePaginationDto(pageNumber: 0, pageSize: 10)
);

3. Generate an Oracle Report (PDF)

var reportRequest = new OracleReportRequest
{
    ReportName = "HR_REPORT.rdf",
    DestName = "output/report.pdf",
    Parameters = new Dictionary<string, object>
    {
        ["P_DEPT_ID"] = 10
    }
};

var reportService = new OracleReportService(); // implement IReportService
var reportBytes = await reportService.GenerateOracleReport(reportRequest);
File.WriteAllBytes("HR_Report.pdf", reportBytes);

🧱 Core Components

IDbContext

Interface to execute Oracle procedures and queries:

Task<T> ExecutePackageAsync<T>(T entity, string programUnitName) where T : OracleEntity;
Task<List<T>> SelectViewAsync<T>(string viewName, Dictionary<string, object> conditions, string? orderBy = null, OraclePaginationDto? pagination = null) where T : OracleBaseDto, new();
Task<T?> SelectSingleAsync<T>(string viewName, Dictionary<string, object> conditions) where T : OracleBaseDto, new();
Task<int> SelectViewCountAsync(string viewName, Dictionary<string, object> conditions);

OracleEntity

Base class for objects sent to stored procedures. Override EnsureEntity() for validation logic.

OracleBaseDto

Base class for data transfer objects (DTOs) mapped to views/tables.

OracleFieldAttribute

Maps class properties to Oracle view/table columns:

[OracleField("COLUMN_NAME", OracleDbType.Varchar2)]
public string PropertyName { get; set; }

OracleParameterAttribute

Maps class properties to procedure parameters with direction and size control:

[OracleParameter("P_NAME", OracleDbType.Varchar2, direction: ParameterDirection.Input)]
public string Name { get; set; }

OraclePaginationDto

Enforces pagination for view queries (PageNumber ≥ 0, PageSize ∈ [0, 25]).

OracleReportRequest

Model for defining Oracle report generation requests:

new OracleReportRequest
{
    ReportName = "MyReport.rdf",
    DestFormat = "pdf",
    Parameters = new Dictionary<string, object> { ["P_ID"] = 123 }
}

IReportService

Defines a service that returns the raw bytes of an Oracle report file.

⚠️ Pagination Rules

OraclePaginationDto enforces:

• PageNumber ≥ 0
• PageSize ∈ [0, 25]

Violations will throw OraException with code "602".

🔧 Requirements

• .NET 6.0 or later
• Oracle.ManagedDataAccess.Core (automatically included)
• Access to Oracle Database
• Optional: Oracle Reports server (HTTP-accessible)

📄 License

This project is licensed under the MIT License. See the LICENSE file for details.

💬 Feedback / Issues

Found a bug or want to request a feature?
Open an issue on GitHub

🙌 Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

👀 Example Use Cases

• Simplifying complex Oracle DB access in enterprise apps
• Wrapping PL/SQL logic behind simple service calls
• Building report export features in .NET backends