Add SQLite & Dapper

Introduction

We will be taking a 'database first' approach in this project. This means that we will create the database outside Visual Studio and the C# code. The tool I have selected to do this is DB Browser for SQLite, but 

YouTube Video

Tasks

Before we add the database we must create the folder location within our Visual Studio project to store the SQLite database file. Once we have added the database and the tables we need, we return to Visual Studio to include the database within the project.

The tasks to be undertaken are therefore:

  • Install DB Browser for SQLite
  • In Visual Studio add the Data folder and copy its path to the clipboard.
  • Using DB Browser for SQLite
    • add the CountriesAndCities database
    • add the Countries table
    • add the Cities table
    • add some test data to the Countries table
  • Return to Visual Studio to include the database in the project

Install DB Browser for SQLite

If you don't already have it installed, install DB Browser for SQLite from https://sqlitebrowser.org

Add the Data folder

Open Visual Studio and the project if not already open.

In Solution Explorer, right-click the Server project name and select:

  • Add > New Folder
  • Name it 'Data'
  • Right-click the new Data folder and select 'Copy Full Path'; this will copy the path to the clipboard (or better still, Notepad).

Add the database

Open DB Browser for SQLite

  • Click 'New Database' button on the toolbar
  • Paste the path copied from Visual Studio into the 'address bar' of the form that opens and press carriage return.
  • Enter a name for the database, e.g. 'CountriesAndCities', in the File name box and click 'Save'.

Add the Country table

As you save the database a form will open to allow the definition of a table.

  • Enter the name of the table, i.e. 'Country' in the Table name box at the top of the form.
  • On the 'Fields' tab click 'Add' to add the first field (column)
  • Enter the following:
    • Name: CountryId
    • Type: Integer
    • NN: Checked (Yes)
    • PK: Checked (Yes)
    • AI: Checked (Yes)
    • U: Checked (Yes)
  • Click 'Add' to add the next field
    • Name: CountryName
    • Type: Text
    • NN: Checked (Yes)
    • PK: Unchecked (No)
    • AI: Unchecked (No)
    • U: Unchecked (No)

Once all the fields have been added, click OK to save the table. Note that all fields have been set as 'Not Null', but I haven't provided Default values for any.

The abbreviations used mean:

  • NN: Not Null
  • PK: Primary Key
  • AI: Auto Increment
  • U: Unique

Add the City table

  • Click 'Create Table'
  • Enter the name of the table, i.e. 'City' in the Table name box at the top of the form.
  • On the 'Fields' tab click 'Add' to add the first field (column)
  • Enter the following:
    • Name: CityId
    • Type: Integer
    • NN: Checked (Yes)
    • PK: Checked (Yes)
    • AI: Checked (Yes)
    • U: Checked (Yes)
  • Click 'Add' to add the next field
    • Name: CityName
    • Type: Text
    • NN: Checked (Yes)
    • PK: Unchecked (No)
    • AI: Unchecked (No)
    • U: Unchecked (No)
  • Click 'Add' to add the next field
    • Name: CityPopulation
    • Type: Integer
    • NN: Checked (Yes)
    • PK: Unchecked (No)
    • AI: Unchecked (No)
    • U: Unchecked (No)
  • Click 'Add' to add the next field
    • Name: CountryId
    • Type: Integer
    • NN: Checked (Yes)
    • PK: Unchecked (No)
    • AI: Unchecked (No)
    • U: Unchecked (No)

These table definitions mirror the classes we created in Visual Studio for Country and City

For users familiar with Microsoft SQL Server there are a couple of peculiarities with the above.

Although I have defined a Primary Key, this isn't strictly necessary This is because, unless specified otherwise, a 'rowid' is automatically added to the table definition. This is an auto-incrementing integer. There is therefore no need to specify a separate primary key field. This is covered in this article in https://sqlitetutorial.net. However, to mimic the original Countries & Cities SQL Server database I have included 'CountryId' and 'CityId'.

Add Sample Data

Whilst we have DB Browser for SQLite open we can take the opportunity to enter some sample data; this will make testing our Blazor application a little easier.

I found adding data not very intuitive, but this procedure seems to work:

  • Once the table has been added, right-click on the table name and select 'Browse Table'.
    • Alternatively highlight the table and click the 'Browse Data' tab.
  • If we had any data I think it would be displayed at this point.
  • To add data, in the toolbar above the grid, click the tiny button under 'Browse Data' with the downward pointing arrow. It has the tooltip 'Insert a new record into the current table'. (Make sure the whole dialog box is wide enough to display all items on the toolbar above the datagrid.)
  • To save data, it appears that you can either click the 'Refresh data' button on the datagrid toolbar, or click 'Write Changes' on the toolbar at the top of the form.
  • To make doubly sure I selected File > Save All before closing DB Browser for SQLite by selecting File > Exit. (I did not save as a project.)
  • To make it easier at the testing stage to determine whether data was being returned from the SQLite database (as opposed to the hard-coded data I originally added to the class file) I deliberately entered different countries and cities.

Add Database to Project

Return to Visual Studio and in the Solution Explorer open the Data folder. If CountriesAndCities.db is not displayed:

  • Highlight the Data folder and right-click
  • Select 'Existing Item...'
  • Select 'CountriesAndCities.db' when the file browser opens and click 'Add'
  • CountriesAndCities.db will now be shown in the Data folder.

Before we leave CountriesAndCities.db in Solution Explorer, we can set two properties of the database so that when we publish the application the database is included in the package.

  • Right-click it and select 'Properties'
    • Select 'Content' for 'Build Action'
    • Select 'Copy if newer' for 'Copy to Output Directory'.

These setting can be changed later if a different behaviour is required, but selecting these to start with will ensure that the database is published as part of the project.

Define Connection String

To link the application to the database we need to add a 'ConnectionString' to 'appsettings.json'. Open appsettings.json in the Server project and add (or edit) the following (you will probably have to add a comma to the line above where this is to be inserted):

  "ConnectionStrings": {
    "Default": "Data Source=.\\Data\\countriesandcities.db;Version=3;"
  }

This is the relative path to the CountriesAndCities database from the root of the Server project.  Note: it looks as if it would be possible to have appsettings.json in the wwwroot folder in the Client project - don't do that as appsettings.json in the Client project could be read by anyone who knew what they were doing!

Install Dapper

We need to install Dapper and its supporting package, System.Data.SQLite.core.  These need to be installed in the Server project.

  • Dapper
  • System.Data.SQLite.Core

Select

  • Tools > NuGet Package Manager > Manage NuGet Packages for Solution
  • Enter 'Dapper' in the browse box
  • Select 'Dapper' from the list
  • Check the Server project in the right hand pane
  • Click 'Install'
  • Repeat, for 'System.Data.SQLite.Core.

We are 'only' changing the data source from a list of data declared in the CountryController to reading the data from the SQLite database.  We therefore do not need to make any changes to 'CountryListPage.razor' nor 'CountryService' nor 'ICountryService'.  The only file that needs modifying is the controller, CountryController.cs.

CountryController.cs

  • At the top of the file add the using statements for Dapper and System.Data.SQLite.
  • Delete the code that added the static list of countries.
  • We need to read appsettings.json to get the connection string.  To do this add the 'IConfiguration' section.
  • Declare the variables required:
    • A string for 'connectionId'
    • A string for the 'sqlCommand' we will be passing to SQLite
    • An IEnumerable of type 'country' called 'countries'.  This is an IEnumerable, rather than a List, as it seems this is required by Dapper/SQLite.
  • Modify the 'GetCountries' method
    • The variable 'sqlCommand' is set to a simple select statement
    • A connection to the database is made using IDbConnection using the connection string read using IConfiguration from 'appsettings.json'
    • The countries variable is populated by passing the 'sqlCommand' to 'QueryAsync'
    • And the result, 'countries', is returned.
using Dapper;
using System.Data;
using System.Data.SQLite;
private readonly IConfiguration _config;

public CountryController(IConfiguration config)
{
    _config = config;
}
public string connectionId = "Default";
public string sqlCommand = "";
IEnumerable<Country>? countries; 
sqlCommand = "Select * From Country";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
    countries = await conn.QueryAsync<Country>(sqlCommand);
}
return Ok(countries);

(Out of curiosity I tried replacing 'using IDbConnection' with 'using var'; it still worked. I'm not sure what this is telling me...)

Save all files and test

References

The code changes for this post can be found here: Code

Powered by Publii