C# WPF DataGrid connecting to a SQLite database

Question

I'm new to C# and WPF so I would appreciate your help.

I can't get a WPF grid to connect to a SQLite database and display its data. I just get the Grid displayed without any data.

Blank DataGrid

The Datagrid code on Home.xaml:

    <DataGrid Name="DataGridHome" Margin="10,66,242,185" Grid.Row="2" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False"
          CanUserDeleteRows="False" CanUserReorderColumns="False" CanUserResizeColumns="False"
          CanUserResizeRows="False" CanUserSortColumns="False" SelectionMode="Single" SelectionChanged="DataGrid_SelectionChanged">
        <DataGrid.Columns>
            <DataGridTextColumn Header="id" Visibility="Hidden"/>
            <DataGridTextColumn Header="Surname" FontFamily="Arial"/>
            <DataGridTextColumn Header="First Name" FontFamily="Arial" />
            <DataGridTextColumn Header="DoorNum" FontFamily="Arial"/>
            <DataGridTextColumn Header="StreetName" FontFamily="Arial"/>
            <DataGridTextColumn Header="StreetName2" FontFamily="Arial"/>
            <DataGridTextColumn Header="City" FontFamily="Arial"/>
            <DataGridTextColumn Header="PostCode" FontFamily="Arial"/>
            <DataGridTextColumn Header="Belt" FontFamily="Arial"/>
            <DataGridTextColumn Header="AccountType" FontFamily="Arial"/>
            <DataGridTextColumn Header="JoinDate" FontFamily="Arial"/>
            <DataGridTextColumn Header="MembershipActive" FontFamily="Arial"/>
        </DataGrid.Columns>
    </DataGrid>
</Grid>

The App.config code (my database doesn't have any username or password):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
  <add connectionString="Data Source=MemberDB.db; Initial Catalog=Pubs;" name="ConString"/>
  </connectionStrings>
 </configuration>

The C# code Home.xaml.cs :

using System;
using System.Windows;
using System.Windows.Controls;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;

namespace KarateClub
{
/// <summary>
/// Interaction logic for Home.xaml
/// </summary>
public partial class Home : Page
{
    public Home()
    {
        InitializeComponent();
        DataBaseConnection();
    }

    private void DataBaseConnection()
    {
        try
        {
            SQLiteConnection sqlite = new SQLiteConnection("Data Source=MemberDB.db; Version=3;");

            SQLiteCommand sqlcmd;
            sqlite.Open();
            sqlcmd = sqlite.CreateCommand();
            string query = "SELECT * FROM Members";
            sqlcmd.CommandText = query;
            SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlcmd);
            DataTable dt = new DataTable("Members");
            sda.Fill(dt);
            DataGridHome.ItemsSource = dt.DefaultView;
            sqlite.Close();

        }
        catch(SQLiteException ex)
        {
            Console.WriteLine("SQLite DataBase Error!");
        }

    }
    ....

I'm thinking there must be something wrong with App.config or my connection to the database in the C# code. I don't know how name="ConString" in App.config is supposed to be used with the C# code to access SQLite.

This has been VERY hard to research online - some people using Dapper, others using ADO.NET, the ones who have similar code to me are not using a DataGrid but a simple list view.

Thanks :)

Answer 1

A datagrid connects to a list -> This is what you need to do:

1. Make a class which represents the objects in your database (e.g. Person)
2. Make a list (List Persons)
3. Make a database connection (I suggest Dapper)
4. Fil the List (e.g. People) with Objects (Persons)
5. Bind Datagrid to list

A good tutorial: https://www.c-sharpcorner.com/UploadFile/009464/how-to-bind-datagrid-in-wpf-using-C-Sharp/