This series of posts shows us how to build a WPF project using SQLite and Dapper that allows the user to perform Create and Read. The other two functions, Update and Delete (of CRUD) will be shown in another post. This is a longer than normal post but provides a foundation for getting started in other projects. SQLite is a full ACID-compliant database that is housed in a single file that you can link into the exe as you distribute it to your users. With Dapper, running SQL commands against the database is easier to code, as long as you don’t mind writing the SQL commands yourself.

Tim Corey’s YouTube Video

This article is based on a YouTube video called Using SQLite in C# – Building Simple, Powerful, Portable Databases for Your Application by Tim Corey. There are a few differences, however. In Tim’s video he creates a WinForms project in Visual Studio. In this project, I will create a WPF project. I will provide the source code for the project there in this post. There is an order that you would roughly follow when you build any small project with a database back end. Here is a list of some of the steps to building this project on your own.

WPF Project: PeopleListAddSqlite

This post covers the first three steps of creating the project PeopleListAddSqlite. The other steps are covered by the next posts.

1. Data – design and create the C# models (People.cs in this case).
2. User Interface – what can the user do?
3. Use in-memory data so you can test it.
4. In the right location, create your SQLite database with SQLite Browser (sqlitebrowser.org).
5. Include the database in the project (in Solution Explorer) with a Build Action set to Content and Copy to Output set to Copy if newer. (first, Show All Files)
6. Set up connection string (15:30) connectionstrings.com – Part 3
7. Manage NuGet Packages – add SQLite and Dapper.
8. Add a class (SqliteDataAccess) to store our connections to the database (22:10) by writing your methods (Load, Add etc.).
9. Write code in UI to use this class (31:50) and test it. Part 4
10. Go to Release mode, run it but do not add people and notice an empty database.
11. Go back to Debug mode and continue testing and improving your project.

PersonModel.cs

Create a new C# class file at the root of the project for our Person data model. The Id will be auto-incremented by the upcoming database.

namespace PeopleListAddSqlite
{
    public class PersonModel
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string FullName
        {
            get { return $"{FirstName} {LastName}"; }
        }
    }
}

MainWindow.xaml

Create the user interface. A few of the important things to notice here are that the buttons and the listbox have names. The two buttons have Click events that are implemented in the code-behind. The important part is the Binding of the FullName and the Data Template. Note that the code is {Binding Path=FullName} but since Path is the default, it could have been just {Binding FullName}. The template has two TextBlocks. It really only needs one, but for illustration purposes I included a bracket to show that you can add other elements here. Maybe we could even add Buttons.

<Window x:Class="PeopleListAddSqlite.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:PeopleListAddSqlite"
        mc:Ignorable="d"
        WindowStartupLocation="CenterScreen"
        Title="MainWindow" Height="340" Width="500">
    <StackPanel Margin="2,2,2,2" Background="Beige">
        <TextBlock TextWrapping="Wrap"  Margin="6,6,6,6">This is a WPF program that has a SQLite back-end. 
            We are using the micro-ORM Dapper. The data model is Person, which has an Id, FirstName, and LastName.
        </TextBlock>
        <Grid>
            <Grid.ColumnDefinitions>
                <ColumnDefinition/>
                <ColumnDefinition/>
            </Grid.ColumnDefinitions>
            <StackPanel Grid.Column="0">
                <Label Margin="20,4,4,4" FontWeight="Bold" FontSize="20">Add People</Label>
                <Label Margin="20,4,4,4">First Name</Label>
                <TextBox Name="txtbFirstName" Width="120" Height="22"></TextBox>
                <Label Margin="20,4,4,4">LastName</Label>
                <TextBox Name="txtbLastName" Width="120" Height="22"></TextBox>
                <Button Margin="6,16,6,6" Name="BtnAddPerson" Width="100" Height="26" Click="BtnAddPerson_Click">Add Person</Button>
                <Button Margin="6,6,6,6" Name="BtnRefreshList" Width="100" Height="26" Click="BtnRefreshList_Click">Refresh List</Button>
            </StackPanel>
            <StackPanel Grid.Column="1">
                <Label Margin="20,4,4,4" FontWeight="Bold" FontSize="20">List People</Label>
                <ListBox x:Name="listPeopleListBox" Width="160" Height="200">
                    <ListBox.ItemTemplate>
                        <DataTemplate DataType="{x:Type local:PersonModel}">
                            <StackPanel Orientation="Horizontal">
                                <TextBlock Text="> "/>
                                <TextBlock Text="{Binding Path=FullName}"/>
                            </StackPanel>
                        </DataTemplate>
                    </ListBox.ItemTemplate>
                </ListBox>
            </StackPanel>
        </Grid>
    </StackPanel>
</Window>

C# Code-Behind for the MainWindow

Here we store our in-memory List. We also load the list with the method called LoadPeopleList. Shortly we’ll change this to get the data from the SQLite database instead of being hard-coded here. That code in in part 4. Here we attach our list to the GUI’s ListBox. We also handle the two Click events. The code below is just to get us started.

using System.Collections.Generic;
using System.Windows;
namespace PeopleListAddSqlite
{
    public partial class MainWindow : Window
    {
        List<PersonModel> people = new List<PersonModel>();
        // instead of a list we could use ObservableCollection
        public MainWindow()
        {
            InitializeComponent();
            LoadPeopleList();
        }
        private void LoadPeopleList()
        {
            people.Add(new PersonModel { FirstName = "Bob", LastName = "Roberts" });
            WireUpPeopleList();
        }
        private void WireUpPeopleList()
        {
            // attach the in-memory list to the ListBox
            listPeopleListBox.ItemsSource = null; // important to first make null
            listPeopleListBox.ItemsSource = people;
        }
        private void BtnAddPerson_Click(object sender, RoutedEventArgs e)
        {
            PersonModel p = new PersonModel();
            p.FirstName = txtbFirstName.Text;
            p.LastName = txtbLastName.Text;
            people.Add(p); // add to in-memory list
        }
        private void BtnRefreshList_Click(object sender, RoutedEventArgs e)
        {
            WireUpPeopleList();
        }
    }
}

Results

Here is what the project looks like so far. It works, but it’s just an in-memory list. Each time we run the program we only get Bob Roberts. The people we add to the list appear, but do not get saved to a database when the program closes because we have not yet set up our database.

You will see in the screenshot that after the person is added to the list and the list is refreshed so that the new person appears on the list, the two text boxes are not wiped clear of the user’s text. The name remains. Just add the following code to fix that. Make these three lines of code below the last three lines of code in the BtnAddPerson_Click event.

            txtbFirstName.Text = "";
            txtbLastName.Text = "";
            WireUpPeopleList();