C# – Management of a SQLite database

By | 06/11/2024

In this post, we will see how to create and manage a SQLite database using C#.
We will create a minimal API to use the database, implement a repository pattern to abstract database operations and finally, we will write unit tests to verify our code.
The SQLite database we will use is named TestDb and it contains a single table called TabUser, so defined:

CREATE TABLE TabUser (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Username TEXT(250),
    Password TEXT(20)
);


First of all, we create an empty project and then, we will install five packages via NuGet that we will use in the project:

  • Microsoft.AspNetCore.App: for building the minimal API
  • System.Data.SQLite: to interact with the SQLite database
  • xUnit: for unit testing.
  • Moq: for mocking dependencies during testing.
  • Microsoft.Extensions.Configuration: to handle configuration files

After the installation, we should have this result:


Then, we add a configuration file called appsettings.json, used to specify the path to the SQLite database file:
[APPSETTINGS.JSON]

1
2
3
4
5
{
  "DatabaseSettings": {
    "DatabasePath": "c:\\Tests\\TestDb.db"
  }
}

Now, we define the User class, the UserRepository interface and the UserRepository class:
[USER.CS]

1
2
3
4
5
6
7
8
namespace TestSQLite;
 
public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
}

[IUSERREPOSITORY.CS]

1
2
3
4
5
6
7
8
9
10
11
namespace TestSQLite;
 
public interface IUserRepository
{
    Task<List<User>> GetAllUsersAsync();
    Task<User> GetUserByIdAsync(int id);
    Task AddUserAsync(User user);
    Task UpdateUserAsync(User user);
    Task DeleteUserAsync(int id);
    Task EnsureDatabaseCreatedAsync();
}

[USERREPOSITORY.CS]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
using System.Data.SQLite;
 
namespace TestSQLite;
 
public class UserRepository: IUserRepository
{
        private readonly string _connectionString;
  
        // Constructor to initialize the connection string from configuration
        public UserRepository(IConfiguration configuration)
        {
            string databasePath = configuration["DatabaseSettings:DatabasePath"];
            _connectionString = $"Data Source={databasePath};Version=3;";
        }
  
        // Method to ensure the database and tables are created if they do not exist
        public async Task EnsureDatabaseCreatedAsync()
        {
            try
            {
                var builder = new SQLiteConnectionStringBuilder(_connectionString);
                string databaseFile = builder.DataSource;
  
                // Check if the database file exists, and create it if necessary
                if (!File.Exists(databaseFile))
                {
                    SQLiteConnection.CreateFile(databaseFile);
                }
  
                // Open the connection and create the 'TabUser' table if it doesn't exist
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string createTableQuery = @"
                    CREATE TABLE IF NOT EXISTS TabUser (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Username TEXT(250),
                        Password TEXT(20)
                    );";
  
                await using var command = new SQLiteCommand(createTableQuery, connection);
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
  
        // Method to get all users from the 'TabUser' table
        public async Task<List<User>> GetAllUsersAsync()
        {
            try
            {
                var users = new List<User>();
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string query = "SELECT * FROM TabUser";
                await using var command = new SQLiteCommand(query, connection);
                await using var reader = await command.ExecuteReaderAsync();
                // Read each row and add to the user list
                while (await reader.ReadAsync())
                {
                    users.Add(new User
                    {
                        Id = reader.GetInt32(0),
                        Username = reader.GetString(1),
                        Password = reader.GetString(2)
                    });
                }
  
                return users;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
              
        }
  
        // Method to get a specific user by ID
        public async Task<User> GetUserByIdAsync(int id)
        {
            try
            {
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string query = "SELECT * FROM TabUser WHERE Id = @id";
                await using var command = new SQLiteCommand(query, connection);
                command.Parameters.AddWithValue("@id", id);
                await using var reader = await command.ExecuteReaderAsync();
                // Return the user if found
                if (await reader.ReadAsync())
                {
                    return new User
                    {
                        Id = reader.GetInt32(0),
                        Username = reader.GetString(1),
                        Password = reader.GetString(2)
                    };
                }
  
                return null; // Return null if user is not found
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
  
        // Method to add a new user to the 'TabUser' table
        public async Task AddUserAsync(User user)
        {
            try
            {
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string query = "INSERT INTO TabUser (Username, Password) VALUES (@username, @password)";
                await using var command = new SQLiteCommand(query, connection);
                // Set parameter values to prevent SQL injection
                command.Parameters.AddWithValue("@username", user.Username);
                command.Parameters.AddWithValue("@password", user.Password);
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
  
        // Method to update an existing user's details
        public async Task UpdateUserAsync(User user)
        {
            try
            {
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string query = "UPDATE TabUser SET Username = @username, Password = @password WHERE Id = @id";
                await using var command = new SQLiteCommand(query, connection);
                // Set parameter values for the update
                command.Parameters.AddWithValue("@username", user.Username);
                command.Parameters.AddWithValue("@password", user.Password);
                command.Parameters.AddWithValue("@id", user.Id);
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
              
        }
  
        // Method to delete a user by ID
        public async Task DeleteUserAsync(int id)
        {
            try
            {
                await using var connection = new SQLiteConnection(_connectionString);
                await connection.OpenAsync();
                string query = "DELETE FROM TabUser WHERE Id = @id";
                await using var command = new SQLiteCommand(query, connection);
                // Set the ID parameter for deletion
                command.Parameters.AddWithValue("@id", id);
                await command.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
}


Finally, in the Program.cs file, we define the Minimal API:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
using TestSQLite;
 
var builder = WebApplication.CreateBuilder(args);
 
// Add configuration
builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
 
// Add services
builder.Services.AddSingleton<IUserRepository, UserRepository>();
 
var app = builder.Build();
 
// Ensure database and tables are created
using (var scope = app.Services.CreateScope())
{
    var userRepository = scope.ServiceProvider.GetRequiredService<IUserRepository>();
    await userRepository.EnsureDatabaseCreatedAsync();
}
 
// Map endpoints
app.MapGet("/users", async (IUserRepository userRepository) =>
{
    // Endpoint to get all users
    return await userRepository.GetAllUsersAsync();
});
 
app.MapGet("/users/{id}", async (int id, IUserRepository userRepository) =>
{
    // Endpoint to get a user by ID
    return await userRepository.GetUserByIdAsync(id);
});
 
app.MapPost("/users", async (User user, IUserRepository userRepository) =>
{
    // Endpoint to add a new user
    await userRepository.AddUserAsync(user);
    return Results.Created($"/users/{user.Id}", user);
});
 
app.MapPut("/users/{id}", async (int id, User user, IUserRepository userRepository) =>
{
    // Endpoint to update an existing user
    user.Id = id;
    await userRepository.UpdateUserAsync(user);
    return Results.NoContent();
});
 
app.MapDelete("/users/{id}", async (int id, IUserRepository userRepository) =>
{
    // Endpoint to delete a user by ID
    await userRepository.DeleteUserAsync(id);
    return Results.NoContent();
});
 
app.Run();


The last class that we have to define is the UserRepositoryTests class that, we will use to write all unit tests for UserRepository class:

[USERREPOSITORYTESTS.CS]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
using Moq;
using Xunit;
 
namespace TestSQLite;
 
public class UserRepositoryTests
{
    private readonly Mock<IUserRepository> _mockRepo;
    public UserRepositoryTests()
    {
        _mockRepo = new Mock<IUserRepository>();
    }   
 
    [Fact]
    public async Task GetAllUsersAsync_ShouldReturnListOfUsers()
    {
        // Arrange
        _mockRepo.Setup(repo => repo.GetAllUsersAsync()).ReturnsAsync(new List<User>
        {
            new User { Id = 1, Username = "Alice", Password = "password1" },
            new User { Id = 2, Username = "Bob", Password = "password2" }
        });
 
        // Act
        var users = await _mockRepo.Object.GetAllUsersAsync();
 
        // Assert
        Assert.NotNull(users);
        Assert.Equal(2, users.Count);
    }
      
    [Fact]
    public async Task GetUserByIdAsync_ShouldReturnUser_WhenUserExists()
    {
        // Arrange
        var user = new User { Id = 1, Username = "Alice", Password = "password1" };
        _mockRepo.Setup(repo => repo.GetUserByIdAsync(1)).ReturnsAsync(user);
 
        // Act
        var result = await _mockRepo.Object.GetUserByIdAsync(1);
 
        // Assert
        Assert.NotNull(result);
        Assert.Equal("Alice", result.Username);
    }
 
    [Fact]
    public async Task GetUserByIdAsync_ShouldReturnNull_WhenUserDoesNotExist()
    {
        // Arrange
        _mockRepo.Setup(repo => repo.GetUserByIdAsync(99)).ReturnsAsync((User)null);
 
        // Act
        var result = await _mockRepo.Object.GetUserByIdAsync(99);
 
        // Assert
        Assert.Null(result);
    }
        
    [Fact]
    public async Task AddUserAsync_ShouldAddUser()
    {
        // Arrange
        var user = new User { Username = "Alice", Password = "password1" };
        _mockRepo.Setup(repo => repo.AddUserAsync(user)).Returns(Task.CompletedTask);
 
        // Act
        await _mockRepo.Object.AddUserAsync(user);
 
        // Assert
        _mockRepo.Verify(repo => repo.AddUserAsync(user), Times.Once);
    }
 
    [Fact]
    public async Task UpdateUserAsync_ShouldUpdateUser()
    {
        // Arrange
        var user = new User { Id = 1, Username = "Alice", Password = "password1" };
        _mockRepo.Setup(repo => repo.UpdateUserAsync(user)).Returns(Task.CompletedTask);
 
        // Act
        await _mockRepo.Object.UpdateUserAsync(user);
 
        // Assert
        _mockRepo.Verify(repo => repo.UpdateUserAsync(user), Times.Once);
    }
 
    [Fact]
    public async Task DeleteUserAsync_ShouldDeleteUser()
    {
        // Arrange
        var userId = 1;
        _mockRepo.Setup(repo => repo.DeleteUserAsync(userId)).Returns(Task.CompletedTask);
 
        // Act
        await _mockRepo.Object.DeleteUserAsync(userId);
 
        // Assert
        _mockRepo.Verify(repo => repo.DeleteUserAsync(userId), Times.Once);
    }
}

If we run the Unit Tests, this will be the result:


Finally, using a API client as Insomnia, we will check if all CRUD operations work fine:
INSERT:


SELECT:


UPDATE:


DELETE:




Category: C# Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *