Now we need to call our SqliteDataAccess class when the program starts and when the user clicks the user interface buttons. When the program starts, right after InitializeComponent, we call LoadPeopleList(). We’ve got to re-write some of our code so that we go out to the database instead of just working with our List of people in the memory. We need to be able to permanently save our list of people in the database and retrieve it each time the program starts.

MainWindow

Below is the re-write of the code behind of the MainWindow. The original code is in the part 1 of this series.

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()
        {   // read db into memory and display list on GUI
            people = SqliteDataAccess.LoadPeople();
            WireUpPeopleList();
        }
        private void WireUpPeopleList()
        {
            // attach the in-memory list (people) to the ListBox for display
            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;
            if (!string.IsNullOrEmpty(txtbFirstName.Text.Trim()) &&
                !string.IsNullOrEmpty(txtbLastName.Text.Trim()))
            {
                SqliteDataAccess.SavePerson(p);
                txtbFirstName.Text = "";
                txtbLastName.Text = "";
            }
        }
        private void BtnRefreshList_Click(object sender, RoutedEventArgs e)
        {
            LoadPeopleList();
        }
    }
}

The Database File

THe SQLite database file that at the root of our project is empty. The other database file that’s in the bin\debug directory will contain people that your added when you were testing it in debug mode. We’ll call it the debug database. As soon as you switch to release mode, the database file in the root of the project, which is empty, is copied to the bin\release folder if this is he first time you’ve run it in release mode. This is good because when you are ready to release the program, you can start with a clean database. Look at the Tim Corey YouTube video at 33:10 for an explanation.

The “root” database gets re-deployed (copied) from the root folder every time it’s not there, or every time it gets changed.

What if you need to quickly clean out your debug database because it’s full of people and you want to start fresh? Just delete the database in the bin\debug folder. Another database file will be copied from the root folder to the debug folder the next time you run the project in Debug mode.