UWP. How do I pass data to my database then populate a grid?

Question

I'm having a touch of difficulty with UWP and wondered if anyone could help? Pretty new to C♯ here.

Basically I have a page for adding an employee, I think entered data logically should be sent to a class called "Person" and in turn be added to a database (but what do I know hahaha!). The database then needs to populate a grid on the main page.

So, the main two questions are, how do I make the data from the class Person populate the DB and in turn the grid on the main page? And how do I add to the class persons from a different page?

Here is where I am in my attempt:

This is what I have for my Person Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestApp2
{
    using static DB;
    class Person 
    {
            public int PersonId { get; set; }
            public int DepartmentId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string Position { get; set; }
            public string Address { get; set; }
            public double PayratePH { get; set; }
            public double Holiday { get; set; }
            public string TaxCode { get; set; }

        private List<String> Grab_Entries()
        {
            List<String> entries = new List<string>();
            using (SqliteConnection db = new SqliteConnection("Filename=sqliteSample.db"))
            {
                db.Open();
                SqliteCommand selectCommand = new SqliteCommand("SELECT First_Name from EmployeeTable", db);
                SqliteDataReader query;
                try
                {
                    query = selectCommand.ExecuteReader();
                }
                catch (SqliteException)
                {
                    //Handle error
                    return entries;
                }
                while (query.Read())
                {
                    entries.Add(query.GetString(0));
                    
                }
                db.Close();
            }
            return entries;
            
        }
        
    }

} 

Here is my Database Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.UI.Xaml;

namespace TestApp2
{
    public static class DB
    {
        private static SuspendingEventHandler App_Suspending;

        public static void database()
        {
            Application.Current.Suspending += new SuspendingEventHandler(App_Suspending);
            using (SqliteConnection db = new SqliteConnection("Filename=sqliteSample.db"))
            {
                //Creation of the database table
                db.Open();
                String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL , Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100)";
                
                SqliteCommand createTable = new SqliteCommand(tableCommand1, db);
                
                try
                {
                    createTable.ExecuteReader();
                }
                catch (SqliteException)
                {
                    //Do nothing
                }
            }
        }
    }
} 

And finally my Main Page. The commented out section was me testing that I could populate the xaml grid (I'm using the community development grid if that helps at all? https://learn.microsoft.com/en-us/windows/communitytoolkit/controls/datagrid_guidance/datagrid_basics)

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Popups;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;



// The Blank Page item template is documented at https://go.microsoft.com/fwlink/?LinkId=402352&clcid=0x409

namespace TestApp2
{
    

    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    }

    //public class Person
    //{
    //    public int PersonId { get; set; }
    //    public int DepartmentId { get; set; }
    //    public string FirstName { get; set; }
    //    public string LastName { get; set; }
    //    public string Position { get; set; }
    //    public string Address { get; set; }
    //    public double PayratePH { get; set; }
    //    public double Holiday { get; set; }
    //    public string TaxCode { get; set; }


    //}
    /// <summary>
    /// An empty page that can be used on its own or navigated to within a Frame.
    /// </summary>
    public sealed partial class MainPage : Page
    {

        public List<Department> Departments { get; set; }
        List<Person> persons;

        public MainPage()
        {
            this.InitializeComponent();
            persons = new List<Person>();

        //    Departments = new List<Department>
        //{
        //    new Department {DepartmentId = 1, DepartmentName = "R&D"},
        //    new Department {DepartmentId = 2, DepartmentName = "Finance"},
        //    new Department {DepartmentId = 3, DepartmentName = "IT"}
        //};



        //    persons = new List<Person>
        //{
        //    new Person
        //    {
        //        PersonId = 1, DepartmentId = 3, FirstName = "Ronald", LastName = "Rumple",
        //        Position = "Network Administrator", Address = "14 Malkie Avenue",  Holiday = 20,
        //        TaxCode = "LC455"
        //    },
        //    new Person
        //    {
        //        PersonId = 2, DepartmentId = 1, FirstName = "Brett", LastName = "Banner",
        //        Position = "Software Developer", Address = "4/L Long Terrace", PayratePH = 12.95, Holiday = 12.5,
        //        TaxCode = "LC455"
        //    },
        //    new Person
        //    {
        //        PersonId = 3, DepartmentId = 2, FirstName = "Alice", LastName = "Anderson",
        //        Position = "Accountant", Address = "56 Hemming Way", PayratePH = 10,  Holiday = 19.9,
        //        TaxCode = "LC455"
        //    }
        //};
        }

        private async void searchEmployee_Click(object sender, RoutedEventArgs e)
        {
            await new MessageDialog("Test").ShowAsync();

        }

        private void rota_Click(object sender, RoutedEventArgs e)
        {
            this.Frame.Navigate(typeof(Rota));
        }

        private void emailEmployee_Click(object sender, RoutedEventArgs e)
        {
            this.Frame.Navigate(typeof(email));
        }

        private void addEmployee_Click(object sender, RoutedEventArgs e)
        {
            this.Frame.Navigate(typeof(AddEmployee));
        }
    }
}

Answer 1

how do I make the data from the class Person populate the DB and in turn the grid on the main page

The tableCommand1 string you created is not incomplete, you need to modify it like this: String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL, Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100))";

The complete code for creating a database is:

public async static void database()
{
    await ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", CreationCollisionOption.OpenIfExists);
    string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
    using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
    {
        //Creation of the database table
        db.Open();
        String tableCommand1 = "CREATE TABLE IF NOT EXISTS EmployeeTable (Employee_ID INTEGER PRIMARY KEY AUTOINCREMENT, First_Name NVARCHAR(20) NULL, Last_Name NVARCHAR(40) NULL, Address NVARCHAR(50) NULL, Position NVARCHAR(20) NULL, Pay_Rate DOUBLE NULL, Tax_Code NVARCHAR(10) NULL, Sex NVARCHAR(20), NI NVACHAR(10), Emergency_Details NVARCHAR(100))";

        SqliteCommand createTable = new SqliteCommand(tableCommand1, db);

        try
        {
            createTable.ExecuteReader();
        }
        catch (SqliteException ee){}
    }
}

When you want to insert the data of Person into DataBase, I take first_name as an example:

private void InsertData(object sender, RoutedEventArgs e)
{
   string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
    using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
    {
        db.Open();

        SqliteCommand insertCommand = new SqliteCommand();
        insertCommand.Connection = db;

        // Use parameterized query to prevent SQL injection attacks
        insertCommand.CommandText = "INSERT INTO EmployeeTable VALUES (NULL, @First_Name);";
        insertCommand.Parameters.AddWithValue("@First_Name", "Hello");

        insertCommand.ExecuteReader();
        db.Close();
    }
}

For more details about how to use sqlite, you could refer to this document.

In addition, when you want to display the new data to the Grid, it's recommended to use ObservableCollection class instead of List, when you insert or remove data from this class, it will automatically update the UI.

ObservableCollection<Person> persons;

public MainPage()
{
    this.InitializeComponent();
    persons = new ObservableCollection<Person>();
    ......
}

how do I add to the class persons from a different page

You could declare a public static property to represent MainPage instance and then you could directly call a public method to add a new Person class. You could set the NavigationCacheMode as Enabled, in this case, when you back to the MainPage, the data will be cached. For example:

MainPage.cs:

public MainPage()
{
    this.InitializeComponent();
    ......
    this.NavigationCacheMode = NavigationCacheMode.Enabled;

    Current = this;
}

public static MainPage Current;

public void addMethod(Person p) 
{
    persons.Add(p);
}

SecondPage.cs:

private void AddNewData(object sender, RoutedEventArgs e)
{
    //First add data to database like mainpage does
    MainPage.Current.addMethod(person);
}