YGADO 1.6.0

YGADO

A simple ADO to execute a stored procedure and return DataSet or Dictionary of output parameters

Installation

Include this package in the project file

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

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

IDBSPTool db = new DBSPTool(connStr);
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")));

  @inject IDBSPTool _idbsp

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

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);
        Tuple<DataSet, Dictionary<string, object>, Exception> GetDataSet(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).