In the previous article of this series
, I looked into different parameters that can be defined in a SQLite connection string. Detailed
map will serve when I’ll need behaviors that the default values cannot provide. In this article,
I will try to learn how to do CRUD operations on a SQLite database.
Let’s define a data context -I am not talking about DataContext keyword that is used in Entity Framework-. The database I need will hold todo data. I’ll imagine that I created an application and the user interactions are defined is the following:
A todo entry is defined by:
modification date ( at the moment of creation modification date is the same as creation date)
status (3 available status: to be done, postponed, done)
The user can add a new todo
The user can display all the todos or some todos by filtering on a date range
The user can delete a todo
The user can update a todo
by modifying the description
by changing its status
Given the information above, I can create my model class like the following
To create data access layer for my application, I’ll need following methods:
Note: You may say -Hey, why don’t you design generic methods so that you can use them in another project?-. This would be perfectly valid proposition but let’s not lose focus, the sole purpose of this series of articles is to learn how to manipulate SQLite.
The conception aims to guarantee backward compatibility and migration of the data to/from other
relational database management systems. To have a deeper understanding, please visit
SQLite official web site Let’s come back
to our subject and try to define data type mappings for our Todo class:
SQLite data type
I sense a smell in the mapping for the Status column. Maybe in the future, I’ll need to create a separate table and use id of this column in todo table to avoid hard coded conversions in the source code. Note that I simply expose my concerns about the limitations that the actual design may cause, I don’t try over-engineer to be able to stay in the context and finish the exercise. By translating mapping table above to sql script, I get:
Let’s implement CreateTodoTable method:
Please note that this isn’t an ideal way to do at all. Once I finish implementing CRUD methods,
I will review all the code snippets to spot weak points.
Inserting data into the database
Let’s get someresults from database.
Selecting a result set from database
When executed in a console application, this code snippets will give following result
Updating the database
Now that we inserted a todo row into the database, let’s change its status now:
Deleting data from the database
The last thing to learn now to move forward is how to delete a record from the database
What is wrong with the code snippets?
Now, let’s take a step back and criticize the code snippets -which all worked fine during my tests-:
The code needs to be reorganized into one class that’ll separate business logic from data access logic
Hard coded connection string is repeated in every method
Error handling is missing. -What happens if insert or update fails?-
Guard clauses are missing. -What happens if todo object that is received by insert; update or select methods is null ?-
In the next article, I’ll try to clean the code, group it into one reusable component
-do you see repository pattern smile?- and try to address fixes to the concerns I raised above.