# Working with SQLite on .NET-4

Published On

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

## Repository Pattern

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.

## Configuration Wrapper

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 the connection:

This code snippet gives also a clue regarding the issue n°2: error handling which you’ll read in a minute.

# Issue n° 1: Hard coded connection strings

In the previous article, there were hard coded connection strings in every method:

To see the problem, I have asked myself “What happens if I change the connection string”: 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|. From MSDN:

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 DataDirectory 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:

1. Wrap every code blocks that deal with SQLiteConnection with try catch
2. 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:

1. Adding a guard clause in the beginning of the method to check if todo received as argument is null or not.
2. 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 :

## Code Samples

Code samples are available on GitHub

## What is next?

In the next - and final - article, I’ll share some aspects and important links.

Tags:   .net   c#   database   programming   repository pattern   sqlite   t.d.d   unit test