Click here to Skip to main content
15,867,939 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 26-Aug-20 4:57am
Comments
[no name] 26-Aug-20 10:47am    
This is what I would call a throw-away. There is very little code related to the actual task. Take a "working example" and start over with that.
Member 14616529 26-Aug-20 10:50am    
Thanks for responding, I am pretty new to this as you can probably tell. Is there honestly not a way for me to show the data from where I am?
[no name] 26-Aug-20 10:57am    
See below.
Member 14616529 26-Aug-20 10:58am    
Thank you for the link

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900