YGADO 1.4.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

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

IDBSPTool Interface:

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

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