In my opinion, it is good way to make a separate layer between the database and the program logic. If I changed the database, it would be easier to make changes. Besides on this way, I can be assumed that each of my program treat the database on allowed ways. None of them will use or manipulate the data in database freely. It increase the robust of this application.
This article is part of a series. Other already public articles:
Home Ticketing #1 – What my back(end) at home!
Home Ticketing #2 – High level overview
Home Ticketing #3 – Tables and relationships
In the previous article, it was written what data I store. I need to write a class, what further solutions can use. This class should cover the following items.
- Models: need models to represent data what was read or written onto database. These models should be available for those solutions which will use this class.
- Method to handle data: these methods are not so advanced methods. They are just some basic method for query, insert and update information in database tables. Further server side applications can use these methods to interact with data
Entity framework setup
For database connectivity and interaction, I choose the Entity Framework (from now just EF). More specially the NpgSQL nuget package I use. Entity framework is basically a database mapper framework, which support LINQ, tracking changes and updates, etc. Long story short, it is a very good stuff. Most of the modern databases are supported by EF, for example: PostgreSQL, MySQL, MariaDB, MSSQL, Db2, etc.
EF models were generated by Scaffold-DbContext function. This function is able to extract database and setup automatically the EF settings. Although later changes, I had to update manually, but this function looks very good as base of my code. Generated members are in DataModel directory. Some settings was added later to those members. (e.g.: JSON attributes and Equals methods)
Following classes were generated (each for every table): User, Category, System, Usercategory, Ticket and Log. Last file in that folder is the TIcketDatabase. It is an implementation of DbContext with override for OnModelCreation method. That method contains the constraints and relationships among classes (tables).
Constraints implemented onto EF setup is very important. Because EF can return with a lot of kind of “internal database error” when it is forgotten to setup. It is because when we do some action with entities, framework will not ask database in every instruction like “Can we do that?”, instead it will check what is setup for this. From this view, we need to define constraints and relationships twice (once in database, once in EF), but at the beginning Scaffold-DbContext can be very useful.
It is also possible to implement converters onto the OnModelCreating method. By implementing them, framework can do the conversion, for example, between an enum and a text. These converters are not generated automatically, but not a big deal to setup them. In the following my user table entity can be seen:
modelBuilder.Entity<User>(entity =>
{
entity.ToTable("users");
entity.Property(e => e.Id).HasColumnName("id");
entity.Property(e => e.Email)
.HasMaxLength(120)
.HasColumnName("email");
entity.Property(e => e.Password)
.IsRequired()
.HasMaxLength(512)
.HasColumnName("password");
entity.Property(e => e.Username)
.IsRequired()
.HasMaxLength(50)
.HasColumnName("username");
/*--------------------------------*/
/* Here is the interestion part : */
/*--------------------------------*/
entity.Property(e => e.Role)
.HasMaxLength(10)
.HasColumnName("role")
.HasConversion
(
v => v.ToString(),
v => (UserRole)Enum.Parse(typeof(UserRole), v)
);
});
As it can be seen, role column’s entity has a conversion. First parameter executed when we deliver data to the service provider. In this case we just simply convert enum to string. But when we read data from the service provider (database in our case), that parse expression will run and convert simple text zo enum. I made the following enum to make logical programming part easier and prevent that some kind of “strange roles” could appear in case of a program error.
public enum UserRole
{
Admin,
User
}
About DbHandler
Entity framework object (TicketDatabase) is called from another class constructor: DbHandler. This DbHandler interface will be available for the further solutions. It has an interface, it can be used with dependency injection too. Connections string for database is got via constructor parameter.
private string _connectionString;
private TicketDatabase _context;
/// <summary>
/// Constructor for creating TicketHandler object. This object can be used for any pre-defined action with database.
/// </summary>
/// <param name="connectionString">Database connection string, used by EF</param>
public DbHandler(string connectionString)
{
// Read input string and try to establish a connection
if(connectionString == "")
{
throw new NullReferenceException("Connection string is missing in the constructor");
}
_connectionString = connectionString;
var optionsBuilder = new DbContextOptionsBuilder<TicketDatabase>();
optionsBuilder.UseNpgsql(connectionString);
_context = new TicketDatabase(optionsBuilder.Options);
// Validate the connection
try
{
_context.Database.OpenConnection();
_context.Database.CloseConnection();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
DbHandler is a huge class. It is implementation of IDbHandler interface. I will not run through on every single function, it is using a lot of LINQ for more readable format, but I can write what are those functions what it provides. With other words: what the rest of the application can do with the database.
- User table related instructions:
- Register new user
- Delete user
- Change user properties (email and password)
- Get user information
- Get password hash of a user
- Ticket table related instructions:
- List ticket
- List ticket with filters
- Get ticket meta data
- Get ticket details (meta data + logs)
- Create ticket
- Close ticket
- Category table related instructions:
- List categories
- List categories with filters
- Add new category
- Rename category
- Delete category
- Get info about a selected category
- System table related instructions:
- Add new system
- Get info about system
- Delete system
- Other mixed table related instructions:
- Assign user to category
- Unassign user from category
- Assign user to ticket
- Unassign user from ticket
- Change user role
- Non database related functions:
- Get connections string back
- Health check functions
This object limiting the data manipulation and access on database, rest of the application can only reach database via these functions.
Example call for entity framework in DbHandler class
Although, this article is not a EF tutorial, but to be whole, I write an example how that “_context” can be used, which was created in the constructor. I show a system delete function step-by-step. At the very beginning, we have a method which includes a try-catch. Function return with an Message object. It contains a message text and type. Type can be OK and NOK (Not OK). Text can give clue back to the caller.
public async Task<Message> RemoveSystemAsync(string sysname)
{
// Create object which will return
Message respond = new Message();
try
{
return respond;
}
catch (Exception ex)
{
respond.MessageType = MessageType.NOK;
respond.MessageText = $"Internal error: {ex.Message}";
return respond;
throw;
}
}
Transactions can be useful (but sometimes can be annoying too). Generally speaking, transaction can provide feature to us in databases for more complex instructions which are not just one query, update or insert. Or with other words, not atomic instructions. After a transaction has been started, it must be commit or rollback. In case of commit, every changes which was done from transactions tart, will affect database. Rollback can be use if something is failed and we need to undo the changes.
I am using transaction to avoid multiple remove request to the same system. I choose RepetableRead isolation levels which means: “Locks are placed on all data that is used in a query, preventing other users from updating the data.” After implementing it, function look like this:
public async Task<Message> RemoveSystemAsync(string sysname)
{
// Create object which will return
Message respond = new Message();
var transaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
try
{
// It run OK
transaction.Commit();
respond.MessageType = MessageType.OK;
respond.MessageText = $"System ({sysname}) has been removed";
return respond;
}
catch (Exception ex)
{
// Something bad happened
transaction.Rollback();
respond.MessageType = MessageType.NOK;
respond.MessageText = $"Internal error: {ex.Message}";
return respond;
throw;
}
}
Only one thing is left and that is the main part of this function, where we will remove record from database. Method of this remove is the following:
- Looking for record where the specified system name is located. As there is a unique constraint for system name in database, only one record is possible with the specified name
- If no record was found, then return with missing error message (which can lead for a 404 return on REST API site)
- Remove the found record from context
- Save changes in context
At the end, the system remove function looks:
public async Task<Message> RemoveSystemAsync(string sysname)
{
// Create object which will return
Message respond = new Message();
var transaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
try
{
// Check that system exist
var record = await _context.Systems.SingleOrDefaultAsync(s => s.Name == sysname);
if(record == null)
{
// No exist, thus cannot be deleted
transaction.Rollback();
respond.MessageType = MessageType.NOK;
respond.MessageText = $"System not defined into database";
return respond;
}
// Remove from database
_context.Systems.Remove(record);
_context.SaveChanges();
// It run OK
transaction.Commit();
respond.MessageType = MessageType.OK;
respond.MessageText = $"System ({sysname}) has been removed";
return respond;
}
catch (Exception ex)
{
// Something bad happened
transaction.Rollback();
respond.MessageType = MessageType.NOK;
respond.MessageText = $"Internal error: {ex.Message}";
return respond;
throw;
}
}
Final words
In this article I wrote about the data handler class which reach database via configure EF. Rest of the application can reach or manipulate database via provided functions by this implementation. On this way the “unwanted surprises”, e.g.: logically corrupted tables, can be avoid because this class prevent to do anything with data but only allowed functions.
