OracleDbUtils 2.0.1

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, generating reports via Oracle Reports, and exporting data to Excel.

✨ 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 (supports Oracle Reports and Weblogic)
• ✅ Excel export with custom column mapping
• ✅ Custom attribute-based mapping system
• ✅ Built-in DI registration extensions
• ✅ Comprehensive exception handling

📦 Installation

Install via NuGet:

dotnet add package OracleDbUtils

🧩 Dependency Injection Setup

Configure Oracle Database Context

builder.Services.AddOracleDbContext(options =>
{
    options.ConnectionString = "your_oracle_connection_string";
    options.HasDefaultWhere = false; // Optional: enables default WHERE clause filtering
    options.DefaultWhereGetter = "PACKAGE.GET_DEFAULT_WHERE"; // Optional: PL/SQL function for default WHERE
});

Configure Oracle Report Service

builder.Services.AddOracleReportService(options =>
{
    options.ServerName = "your_report_server";
    options.ServerUrl = "http://your-report-server:port";
    options.DbLogin = "username/password@datasource";
    options.IsWeblogic = false; // Set true if using Weblogic
});

Configure Excel Export Service

builder.Services.AddExportService();

🚀 Quick Start

1. Execute a PL/SQL Package Procedure

public class MyProcEntity : OracleBaseEntity
{
    [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 reportBytes = await reportService.GenerateOracleReport(reportRequest);
File.WriteAllBytes("HR_Report.pdf", reportBytes);

4. Export Data to Excel

public class EmployeeCriteria
{
    public int DepartmentId { get; set; }
}

public class EmployeeExportDto
{
    [ExcelColumn("Employee ID")]
    public int Id { get; set; }

    [ExcelColumn("Employee Name")]
    public string Name { get; set; } = string.Empty;

    [ExcelColumn("Department")]
    public string Department { get; set; } = string.Empty;
}

var criteria = new EmployeeCriteria { DepartmentId = 10 };
var results = await dbContext.SelectViewAsync<EmployeeExportDto>(...);

var excelBytes = exportService.ExportResults(criteria, results);
File.WriteAllBytes("Employees.xlsx", excelBytes);

🧱 Core Components

IDbContext

Interface to execute Oracle procedures and queries:

Task<T> ExecutePackageAsync<T>(T entity, string programUnitName) where T : OracleBaseEntity;
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);

OracleBaseEntity

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

ExcelColumnAttribute

Maps class properties to Excel column headers:

[ExcelColumn("Column Title")]
public string PropertyName { 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.

IExportService

Generates Excel files from query results using ClosedXML:

byte[] ExportResults<TC, TR>(TC criteria, List<TR> results)
    where TC : class
    where TR : class;

⚠️ Exception Handling

OracleDbUtils provides specialized exceptions for better error handling:

• OraException: Base exception with error code and message
• OraConfigurationException: Configuration-related errors
• OraValidationException: Entity validation failures
• OraPaginationException: Pagination constraint violations
• OraDatabaseException: Database operation errors
• OraReportGenerationException: Report generation failures
• OraReportResponseException: Report server response errors
• OraAuthenticationException: Authentication/authorization errors

⚠️ Pagination Rules

OraclePaginationDto enforces:

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

Violations will throw OraPaginationException.

🔧 Requirements

• .NET 8.0 or later
• Oracle.ManagedDataAccess.Core (automatically included)
• ClosedXML (for Excel export)
• 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
• Generating Excel reports from Oracle query results
• Multi-tenant applications with dynamic WHERE clause filtering