Now this is also avaliable for All Databases NuGet Package

To make full use of web APIs, exclude the installation of extra packages by just installing DapperDBHelper.


Create an object of DapperDBHelper and pass your connection string through the constructor of the DBHelper class.

Here's how you can use DapperDBHelper.

Below is an example:

public class SetupsController
{
private readonly DBHelpers _dBHelpers;

public SetupsController()
{
_dBHelpers = new DBHelpers(ConectionString.connectionString);
}

public async Task<List<YourViewModelClass>> GetSingleListAsync()
{
var result = await _dBHelpers.QueryAsyncList<YourViewModelClass>("SELECT * FROM table");
return result;
}

public YourViewModelClass GetSingleRecord()
{
var result = _dBHelpers.Query<YourViewModelClass>("SELECT TOP 1 * FROM table");
return result;
}

public async Task<Dictionary<string, IEnumerable<object>>> GetMultipleTablesAsync(int id)
{
// Make a list of table names that your stored procedure returns
List<string> tableNames = new List<string>
{
"Table1",
"Table2",
"Table3"
};

var result = await _dBHelpers.QueryMultipleAsync("EXEC Store_Procedure @ID", new { ID = id }, tableNames);
return result;
}

// Execute Method is used for CRUD operations
// After execution of your stored procedure, returning a single row of current execution is a best practice
// ExecuteAsync call depends upon your application nature
public async Task<int> InsertDataAsync(Employees model)
{
var result = await _dBHelpers.ExecuteAsync("EXEC Store_Procedure @FirstName, @LastName, @Address", new { FirstName = model.FirstName, LastName = model.LastName, Address = model.Address });
return result;
}
}




For .NET 8, you can use the following code:


// Register your service here
builder.Services.AddTransient<DBHelpersForAllDatabases>();

// Register your database connection
builder.Services.AddTransient<IDbConnection>(_ => new SqlConnection(Configuration.GetConnectionString("DefaultConnection")));

public class MyController : ControllerBase
{
private readonly DBHelpersForAllDatabases _dbHelpers;

public MyController(DBHelpersForAllDatabases dbHelpers)
{
_dbHelpers = dbHelpers;
}

[HttpGet]
public async Task<IActionResult> Get()
{
// Use DBHelpers to execute queries
var result = await _dbHelpers.QueryAsyncList<MyModel>("SELECT * FROM MyTable");
return Ok(result);
}
}


In this setup:

GetSingleListAsync: Asynchronously retrieves a list of records from a table.
GetSingleRecord: Retrieves a single record from a table.
GetMultipleTablesAsync: Executes a stored procedure that returns multiple tables and maps them to a dictionary.
InsertDataAsync: Executes a stored procedure to insert data into a table.
Make sure to adjust the method signatures and implementations to match your specific use case and requirements. This setup allows you to effectively manage database interactions using DapperDBHelper in a streamlined manner.