YG.ADO 2.0.0

YGADO

A lightweight ADO.NET helper to execute stored procedures or raw SQL and return a DataSet or output parameters without ORM overhead.

YGADO targets .NET .NET 8+ projects.

Why YGADO Exists

YGADO exists because in real production systems, ORMs don’t always fit the problem.

In complex enterprise codebases, data access often starts simple and slowly turns into:

Deep abstract entity graphs Heavy polymorphism Large reporting queries Performance-sensitive projections “Materialize the universe just to read 5 fields”

At some point, materializing full EF Core graphs becomes more expensive than being honest with the database.

YGADO is meant for those moments.

It is intentionally:

Simple Stateless DI-friendly Explicit about SQL Good for reporting, bulk reads, and stored procedures Safe for concurrent use (no shared DbContext, no tracking, no hidden state)

This is not an EF Core replacement. It’s a pragmatic escape hatch for when ORMs get in the way of reliability or performance.

In practice:

Use EF Core for transactional flows and normal CRUD Use YGADO for heavy reporting, complex joins, legacy schemas, or stored-procedure-heavy systems

Frameworks are tools. Production is the truth.

Installation

Include this package in the project file <ItemGroup> <PackageReference Include="YGADO" Version="x.y.z" /> </ItemGroup>

⚠️ Use code inside a Try catch block. For brevity, there is no try catch block in GetDataSet

Usage

//Oracle example: 
string orclconnStr = "Data Source=;User Id=;Password=;";

IOracleDBTool orcl = new OracleDBTool(orclconnStr);

int envlientId = 534;
string sql1 = $"SELECT * FROM CLIENT where ENVCLIENTID = {envlientId}";
string sql2 = $"SELECT * FROM AUTHORIZATION WHERE ENVCLIENTID = {envlientId}";

using (DataSet ds = orcl.GetDataSet(new string[] { sql1, sql2 }))
{
    foreach (DataTable dt in ds.Tables)
    {
        Console.WriteLine(dt.Rows.Count);
        string contents = JsonConvert.SerializeObject(dt, Formatting.Indented);
        Console.WriteLine(contents);
    }
}


// SQL Server example: 
IDBSPTool db = new DBSPTool(connStr);  // This can be placed in program.cs for DI - sample shown below

Dictionary<string, object> inputParms = new Dictionary<string, object>()
{
    {"Status", "Complete" }
};

Dictionary<string, SqlDbType> outputParms = new Dictionary<string, SqlDbType>()
{
    {"ExecutedDate", SqlDbType.DateTime },
    {"ExecuteStatus", SqlDbType.Bit }
};

var result = db.GetDataSet("USPGetEmplRecs", inputParms, outputParms);

Console.WriteLine(JsonConvert.SerializeObject(result, Formatting.Indented));

//or

//-----for Non-DI use of YGLogProvider:

LogProvider logP = new LogProvider(LogLevel.Information, @"C:\Temp", "log.txt", 10, 5000);
var loggerFactory = LoggerFactory.Create(builder =>
{
    builder.ClearProviders();
    builder.AddProvider(logP);
});
// create logger instance
var logger = loggerFactory.CreateLogger<DBSPTool>();
// ------

IDBSPTool db = new DBSPTool(connStr, 30, 30, 3, 500, ILogger); // Connection string, <connTimeout>, <cmdTimeout>, <resiliencyTries>, <resileincyTrymilisecond>, <ILogger>
var result = db.ExecSP("USPTest");

string resultStatus = JsonConvert.SerializeObject(result);

// OR

var result = await db.ExecSPAsync("USPTest");

string resultStatus = result.Item2;

// if using IOC container:

  builder.Services.AddScoped<IDBSPTool, DBSPTool>(_ => new DBSPTool(configuration.GetConnectionString("AppDbContext"), connTimeout, cmdTimeout));


  @inject IDBSPTool _idbsp

   var result = await db.ExecSPAsync("USPTest"); 


   OR for multiple connection strings / multiple query in a loop follow this example:, 
   
    builder.Services.AddYGADO();

    and then inject the IDBSPToolFactory factory: 


    public BGWorker(IOptions<BGWorkerOptions> options, IConfiguration config, ILogger<BGWorker> logger, IDBSPToolFactory dbFactory)
    {
        _intervalInSeconds = options.Value.IntervalInSeconds;
        this._options = options.Value;
        this._config = config;
        this._logger = logger;
        this._dbFactory = dbFactory;
    }

    // usage is like this:

    foreach (var action in _options.MonitorActions)
        {
            IDBSPTool db = _dbFactory.Create(action.ConnStr, 
                                connectionTimeout: 30,
                                commandTimeout: 30,
                                resiliencyRetries: 3,
                                resiliencyWaitMs: 800);
            using DataSet ds = await db.GetDataSetAsync(action.SQLCmd, isQueryText: true, stoppingToken);

Examples

Test project is provided in the solution to illustrate how to invoke SP and get DataSet and output dictionary back

Interfaces:

public interface IDBSPTool
{
    Dictionary<string, object> ExecSP(SqlConnection sqlConn, string spName, Dictionary<string, object> inputParms = null, Dictionary<string, SqlDbType> outputParms = null, bool closeConnectionAfter = false);
    string ExecSP(string sqlText);
    Dictionary<string, object> ExecSP(string spName, Dictionary<string, object> inputParms = null, Dictionary<string, SqlDbType> outputParms = null);
    Task<Tuple<Dictionary<string, object>, string, Exception?>> ExecSPAsync(string spName, Dictionary<string, object> inputParms = null, Dictionary<string, SqlDbType> outputParms = null);
    DataSet GetDataSet(string sqlQuery, bool isQueryText);
    Task<DataSet> GetDataSetAsync(string sqlQuery, bool isQueryText);
    Tuple<DataSet, Dictionary<string, object>, Exception> GetDataSet(string spName, Dictionary<string, object> inputParms = null, Dictionary<string, SqlDbType> outputParms = null);
    Task<(DataSet DataSet, Dictionary<string, object> Output, Exception Error)> GetDataSetAsync(string spName, Dictionary<string, object> inputParms = null, Dictionary<string, SqlDbType> outputParms = null);
}


      public interface IOracleDBTool
        {
            DataTable GetDataTable(string sqlQuery);
            DataSet GetDataSet(string[] sqlQueries);
        }

Output:

The output is a Tuple<DataSet,Dictionary<string, object>, Exception> for GetDataSet with SP or Dictionary for ExecSP.

Dependencies

Microsoft.Data.SqlClient

Contributing

Any new ideas on how to enhance this class without adding much complexity, please adhere to SOLID principle

License

This project is licensed under the MIT License(LICENSE).