# Working with SQLite on .NET-3

Published On

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.

# Data Context

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:
• unique id
• description
• creation date
• 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.

# Creating Todo table

When you create a table , let’s say in SQL Server, you have a multitude of choice for column types that will hold the data and it is quite easy to make correspondence between the type of a field/property of your model class and the type of the column. However SQLite uses a dynamic type system which consists of

• defining storage classes
• defining type affinity rules
• map data types to a certain type affinity

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:

Property Property type SQLite data type
Id Int32 int
Description String nvarchar
CreatedOn DateTime datetime
ModifiedOn DateTime datetime
Status Enum int

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 ?-

# What’s next?

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.

Tags:   .net   c#   database   decompiling   sqlite   programming