UWP data binding issue

See more:

C#

SQLite

UWP

Hi!

Im having a bit of difficulty figuring out how to bind my SQLite database to my datagrid. Ive tried a few different ways as I'm sure you will see in the code below, but, seem to have just lost my way with it.

Can anybody see where I am going wrong? Any help at all would be greatly appreciated :)



So, here is my database class:

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

        public async static void InitializeDatabase()
        {
            

            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) { }
            }
        }
    

        //public static List<Person> Grab_Entries()
        //{
        //    List<Person> entries = new List<Person>();
            
        //    using (SqliteConnection db = new SqliteConnection("Filename=sqliteSample.db"))
        //    {
        //        db.Open();
        //        SqliteCommand selectCommand = new SqliteCommand("SELECT * 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;

This should all be working grand.

This is my MainPage:

namespace TestApp2
{
     
    public sealed partial class MainPage : Page
    {
        

        
        List<Person> persons;

        public MainPage()
        {
            this.InitializeComponent();
            DB.InitializeDatabase();


        }


        private void App_Suspending(object sender, SuspendingEventArgs e)
        {
            throw new NotImplementedException();
        }

        

        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));
        }

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

            //    string query = "select * from EmployeeTable";
            //    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(query, dbpath);

            //    DataSet _Bind = new DataSet();
            //    sqlDataAdap.Fill(_Bind, "MyDataBinding");

            //    EmployeeGrid.DataContext = _Bind;

            //    db.Close();
                //DB.InitializeDatabase();   
                //DB.Grab_Entries(); 
           // }

        }

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

My Person Class:

namespace TestApp2
{
    using static DB;
    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 string Sex { get; set; }
            public string TaxCode { get; set; }
            public string EmergencyDetails { get; set; }
            public string Email { get; set; }


        public 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;

        }

    }

}

and finally the MainPage xaml that hosts my grid:

<controls:DataGrid x:Name="EmployeeGrid" Margin="170,55,35,35"
                  ItemsSource="{x:Bind persons}"
                  CanUserSortColumns="True"
                  AutoGenerateColumns="False" Background="Black" Loaded="EmployeeGrid_Loaded">
            <controls:DataGrid.Columns>
                <controls:DataGridTextColumn Header="Employee ID"
                                             Binding="{Binding PersonId}"/>
                <controls:DataGridTextColumn Header="First Name"
                                             Binding="{Binding FirstName}"/>
                <controls:DataGridTextColumn Header="Last Name"
                                             Binding="{Binding LastName}"/>
                <controls:DataGridTextColumn Header="Address"
                                             Binding="{Binding Address}"/>
                <controls:DataGridTextColumn Header="Position"
                                             Binding="{Binding Position}"/>
                <controls:DataGridTextColumn Header="Pay Rate (ph)"
                                             Binding="{Binding PayratePH}"/>
                <controls:DataGridTextColumn Header="Sex"
                                             Binding="{Binding Sex}"/>
                <controls:DataGridTextColumn Header="TaxCode"
                                             Binding="{Binding TaxCode}"/>
                <controls:DataGridTextColumn Header="Email"
                                             Binding="{Binding Email}"/>
                <controls:DataGridTextColumn Header="Emergency Contact"
                                             Binding="{Binding EmergencyDetails}"/>
                <controls:DataGridCheckBoxColumn Header="Selected" 
                                                    />

            </controls:DataGrid.Columns>

Ah! lastly I am aware that my entries arent parameterized, that will be fixed once I can see the grid finally populating.

What I have tried:

I have tried creating a grab_entries method in the database and linking to it via the loaded event for the grid, and, I have tried use the database from the same method with a different implementation.