In the previous article of this series
I learnt how to do CRUD operations on a SQLite database. I have also raised a few issues for the code snippets.
In this article, I’ll try to reorganize the code and address fixes to these issues.
Issue n° 0: Organizing the code
To organize all the code I have written in previous article.
This is a quite simple pattern that consist of regrouping all data access logic into one class named generally
xxxxRepository, xxx being the data context in which you are. The main purpose here is to separate business
logic from data access logic. If, in a few months, I decide to transform the application so that it works with
LocalDb, there will be only one layer to write/modify. In the context of persisting Todo objects into SQLite
database, I created the following interface with its concrete implementation named TodoRepository.
Note: Whether creating a table should be part of this repository or not can be discussed.
Furthermore, since I know that I’ll use configuration file -especially to address a fix to next issue-,
I created a ConfigurationWrapper class that can be injected as dependency thus I will avoid writing code
like the below in my classes.
ConfigurationWrapper class is as simple as:
Database Connection Factory
I know there are out there people who won’t agree with me because of the indirection level - this is
synonym to difficulty for some fellow - which this class will add. So why did I add it? The answer is:
To be able to test repository class. Let’s look at the following code:
How can you test the case: “When there is a problem with the connection, log the error message” ?
With the code above, you cannot because of the new SQLiteConnection(connectionString) . That’s why
I added DatabaseConnectionFactory which generates SQL connection.
This one along with the others are injected into TodoRepository class:
And the below test lets me verify the behavior of the method in case there is a problem while opening
This code snippet gives also a clue regarding the issue n°2: error handling which you’ll
read in a minute.
To see the problem, I have asked myself “What happens if I change the connection
1. I’ll need to change it in every method ⇒ code duplication
2. I’ll have to re-compile the code. In production environments it means deployment. That is not
something you would want for a simple change as connection string.
The solution is to add an app.config in the solution and place the connection string into
<connectionstrings></connectionstrings> section. Right click to the solution, add new item, search
for config and choose Application Configuration File
After having inserted <connectionstrings></connectionstrings> section, app.config looks like:
Please note the usage of |DataDirectory|.
Resolves to a relative path to a mapping and metadata files. This is the value that is set through the
AppDomain.SetData(“DataDirectory”, objValue) method. The DataDirectory substitution string must be
surrounded by the pipe characters and there cannot be any whitespace between its name and the pipe
characters. The DataDirectory name is not case-sensitive. If a physical directory named “DataDirectory”
has to be passed as a member of the list of metadata paths, add whitespace should on either side or
both sides of the name, for example:
An ASP.NET application resolves
to the “/app_data" folder.
Issue n° 2 : Error handling
In the previous article,
I have written methods that realize CRUD operations but errors weren’t handled properly. To refresh memories:
The method above has a direct dependence to SQLiteConnection. It opens the connection, it creates a
command and it executes this command. What happens if one of these operations fails? The code above is
unable to take proper actions -like, if something fails, log an error- I can think of 2 approaches:
Wrap every code blocks that deal with SQLiteConnection with try catch
Let the exception climb up to the top level in the code where it should be handled. The downside
to that is that at the top level, probably there will be a catch using a generic exception.
I have gone with the first one and below the result:
Note: Why did I use throw instead of throw exception or throw new Exception(...) ?
Issue n° 3: Missing guard clauses
The final issue I raised on previous article
was about missing guard clauses. Let’s look at the following method as it was coded:
What happens if I execute this code block?
The moment execution flow hits the line
the code will throw a NullReferenceException . In this particular case, I may have two choices:
Adding a guard clause in the beginning of the method to check if todo received as argument is null or not.
Add an additional catch block for NullReferenceException.
In my opinion, the advantage of the first is that you can define the direction you want the code to take.
If the argument expected by the method is null, isn’t it better to throw ArgumentNullException instead
of the underlying exception which gives a more mysterious message? Here is the redesigned code:
Have you noticed that instead of using updateCommand.Parameters.AddWithValue(...,...) method I am
now using the following block ?:
That’s is because AddWithValue is in the default SQLite .Net library I installed via nuget in the
first article. I transformed type of the command variable from SQLiteCommand to IDbCommand to
break the dependency of the code with SQLite library. What happens if I plug SQL server to the
Repository class? Would it work - except CreateTodoTable() method -? :) That has been said,
I also created an extension method that’ll gather 3 lines per parameter in to one method to have
a more readable code :