 |
Sphyrnidae Common Library
2.0.1
Shared Utilities/Library
|
|
Sphyrnidae Common Library
2.0.1
Shared Utilities/Library
|
The Data Access Layer (DAL) is how you go about retrieving data from a repository. For the purposes of these classes, a DAL is how you interact with a database. This will commonly be referred to as the repository/repo layer.
All methods that will interact with a database will inherit from the BaseRepo class. This class provides all the abstraction you will need to perform almost any database operation. Behind the covers, the class utilizes Dapper to actually interact with the database. Additionally, this class handles logging and connection management.
Base class: BaseRepo
Inherited classes:
Where used:
All methods are asynchronous (no synchronous option). All methods can take either direct SQL, or a stored procedure. All methods have a "TrappingExceptions" option (see below for more information)
It is a best practice to have a repository class that roughly maps to a single database table. As such, your repo class should inherit from either SqlServerRepo or MySqlRepo. Your inherited class must implement the following abstract methods:
Optionally, you can override PreCall() or PostCall() methods. The PreCall() is where the database connection is initiated (base class does this). If there is anything you might wish to alter in your command or the connection, this is where you can do that. The PostCall() is where you can setup additional handling for after the database call has completed.
A common scenario is where you are performing a non-Idempotent call (eg. Insert/Update/Delete). These calls might fail for a variety of reasons (eg. Foreign Key constraint, other contraints, unique index, etc.) When these calls fail, they will throw a SQLException. You may wish to "Trap" these exceptions instead of letting your repository layer throw the exception out to other layers in your architecture. You do this by utilizing the "TrapExceptions" method available for all of the calls. If you have trapped an exception, you can directly access this protected member in your repo class (Ex). Additionally, you may wish to implement the "HandleException" method. By implementing this method, this allows the layer in your code which made the repo call to retrieve the exception as a result object. Eg. Can return an HTTP 404 if it wasn't found, or a 409 if this is in conflict/duplicate.
The Transaction class provides the ability to execute multiple SQL statements within a transaction. If any of the statements fail, the transaction will be rolled back. If all of the statements complete successfully, the transaction will commit. So this is an all-or-nothing scenario.
Most transactions will be against a single database. If this is your scenario, then you will execute the "Run" static method. This wrapper method will call your method with the newly created transaction which you should pass along in all repository calls. There is no need for the internal sql statements to run synchronously... they can be all be run at once (asynchronously). This is accomplished by altering the connection string to include ";MultipleActiveResultSets=True" (a performance hit is incurred).
If your transaction is going to span multiple databases, you should utilize the "Distributed" static method. This method is similar, except that you don't pass in the connection string, and you don't receive a transaction to your method. Instead, it creates a TransactionScope for all repository calls within your method. Inside your transactional method, you will need to specify the connection string instead of the transaction. Please note that your transactional method will need to handle the possibly of multiple active result sets.
Your transactional method will return a TransactionResponse - either "Commit()" or "Rollback()". If your method throws an exception, the wrapper will catch this, call your exception handling method, and rollback the transaction.
Because the Transaction is generic, you can call this with any type that inherits from DbConnection. If you are connecting to SqlServer, then you can instead use the static class SqlTransaction. If you are connection to MySql, then you can instead use the static class MySqlTransaction.
| Class | Description |
|---|---|
| DatabaseLogger | If you are using the built-in database logger, this will connect to your logging database for all logging calls. |
public class MyRepo : SqlServerRepo, IMyRepo
{
public MyRepo(ILogger logger) : base(logger) { }
public override string CnnStr => "My Connection String - possibly lookup from Environmental Settings";
protected override string CnnName => "Widgets";
public Task<int?> InsertWidget(string name, double cost)
{
var parameters = new
{
Name = name,
Cost = cost
};
return InsertTrappingExceptionsAsync("insert into Widgets([Name], [Cost]) values (@Name, @Cost)", parameters);
}
public override ApiResponseStandard HandleException()
{
var msg = Ex.GetFullMessage().ToLower();
if (msg.Contains("unique constraint"))
return ApiResponse.Duplicate();
return ApiResponse.InternalServerError(Ex);
}
public Task<bool> TransactionTestWidget(int id, string name, double cost)
{
var parameters = new
{
Id = id,
Name = name,
Cost = cost
};
return WriteSQLAsBoolAsync("update Widgets set [Name]=@Name, [Cost]=@Cost where [Id]=@Id", parameters);
}
}
var id = await myRepo.InsertWidget("widget1", 0.99);
if (!id.HasValue)
return myRepo.HandleException();
return ApiResponse.Success(id.Value);
var success = await SqlTransaction.Run(
logger,
myRepo.CnnStr,
async transaction =>
{
// 1st call
if (!await myRepo.TransactionTestWidget(id.Value, "widget1a", 1.99))
return TransactionResponse.Rollback(); // Could optionally return a value
// 2nd call
if (!await myRepo.TransactionTestWidget(id.Value, "widget1b", 2.99))
return TransactionResponse.Rollback(); // Could optionally return a value
return TransactionResponse.Commit(); // Could optionally return a value
});
// You may wish to handle failures/exceptions differently
return success ? ApiResponse.Success(id.Value) : ApiResponse.NotFound("Widget", id.Value.ToString());
1.9.1