C# How to populate list of objects from SQL database
I'm making this online store in WPF as part of one of my assignments in programming where I have a games page on which all the games displayed. I have used a DataGrid which also has a DataTemplate with RowDefinitions so that when you click on a row, it expands and shows you more info about the game you clicked on. I have mostly used textblocks to show to display the content in the front end. All the front end controls have a databind to a list of objects called "games" in my .cs file where I manually added the details for a few games in order to test it (it's the same game repeated for now, while I'm testing).
My problem with this is that I need to populate the content of this list of objects from a database and I have no idea of how to achieve this, the idea being that you can change the amount of games in the database and you will be able to see this in the datagrid on the games page too(i.e. let's say you run a query and add 2 more games in the DB table, you'll also have 2 more games displayed on the DataGrid). I've had a look at a few things to populate this list such as using a datatable or just making a giant for loop to do this for you but as far as I'm concerned, as long as I understand it and it works, any sort of solution is more than welcome.
This is what it currently looks like with the data I have stored in the list manually. In terms of how it looks, it is the desired outcome. I just need all that data it gets via the DataBinding from the list to be from the database instead of manually added by me in the .cs file. Don't worry too much about the image as it's loaded from a URL and I'm sure that once I figure out how to populate this list from the DB, I'll be able to add images from the DB too.
Here's the XAML code for the front-end.
<Page xmlns:xctk="http://schemas.xceed.com/wpf/xaml/toolkit" x:Class="RareMantis.GamesPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:local="clr-namespace:RareMantis"
mc:Ignorable="d"
d:DesignHeight="534" d:DesignWidth="1200"
Title="GamesPage"
xmlns:materialDesign="http://materialdesigninxaml.net/winfx/xaml/themes"
TextElement.Foreground="{DynamicResource MaterialDesignBody}"
TextElement.FontWeight="Regular"
TextElement.FontSize="13"
TextOptions.TextFormattingMode="Ideal"
TextOptions.TextRenderingMode="Auto"
Background="{DynamicResource MaterialDesignPaper}"
FontFamily="{DynamicResource MaterialDesignFont}">
<!--Main Grid used for setting blue background-->
<Grid Background="#374e70">
<!--Second grid with page content-->
<Grid>
<!--Declare Rows-->
<Grid.RowDefinitions>
<RowDefinition Height="50"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<!--Declare Columns-->
<Grid.ColumnDefinitions>
<ColumnDefinition Width="170"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<!--Grid containing Page Title-->
<Grid Grid.RowSpan="2">
<TextBlock Text="Games" HorizontalAlignment="Center" Margin="10" FontSize="28"/>
</Grid>
<!--Page content is placed withing a stackpanel (+scrollviewer)-->
<ScrollViewer Grid.Column="1" Grid.Row="1" Background="#FFF1F1F1">
<StackPanel>
<!--Catalogue Text-->
<TextBlock Text="Catalogue" Margin="10" FontSize="22" FontWeight="Medium"/>
<!--Datagrid containig the table with rows and columns-->
<DataGrid Name="dg_Games" AutoGenerateColumns="False">
<!--Declare the main columns that get displayed initially and bind data from "Game class"-->
<DataGrid.Columns>
<DataGridTextColumn Header="Title" Binding="{Binding Title}" />
<DataGridTextColumn Header="Genre" Binding="{Binding Genre}" />
<DataGridTextColumn Header="Release Date" Binding="{Binding Release}" />
<DataGridTextColumn Header="Price £" Binding="{Binding Price}" />
</DataGrid.Columns>
<!--Declare the details contained by the rows(when you click them and they expand)-->
<DataGrid.RowDetailsTemplate>
<DataTemplate>
<!--Dockpanel for expanding the row-->
<DockPanel Background="GhostWhite">
<Image DockPanel.Dock="Left" Source="{Binding ImageUrl}" Height="64" Margin="10" />
<!--Grid for row dockpanel content-->
<Grid Margin="0,10">
<!--Declare 2 Columns-->
<Grid.ColumnDefinitions>
<ColumnDefinition Width="Auto" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<!--Declare 4 Rows-->
<Grid.RowDefinitions>
<RowDefinition Height="Auto" />
<RowDefinition Height="Auto" />
<RowDefinition Height="Auto" />
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<!--Game Image from class-->
<TextBlock Text="" FontWeight="Bold" />
<TextBlock Text="{Binding GameImage}" Grid.Column="1" />
<!--Developer name from class-->
<TextBlock Text="Developer: " FontWeight="Bold" Grid.Row="1" />
<TextBlock Text="{Binding Developer}" Grid.Column="1" Grid.Row="1" />
<!--Description from class-->
<TextBlock Text="Description: " FontWeight="Bold" Grid.Row="2" />
<TextBlock Grid.Column="1" Grid.Row="3" Width="300" HorizontalAlignment="Left" Text="{Binding Description}" TextWrapping="WrapWithOverflow"/>
<!--Purchase button-->
<Button x:Name="btn_Purchase" Grid.Column="1" Grid.Row="3" Width="100" Click="btn_Purchase_Click" Content="Purchase" />
</Grid>
</DockPanel>
</DataTemplate>
</DataGrid.RowDetailsTemplate>
</DataGrid>
</StackPanel>
</ScrollViewer>
</Grid>
</Grid>
</Page>
This is the C# code from the .cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
//add namespace
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace RareMantis
{
/// <summary>
/// Interaction logic for GamesPage.xaml
/// </summary>
public partial class GamesPage : Page
{
public GamesPage()
{
InitializeComponent();
//Make List based on object items
List<Game> games = new List<Game>();
//Add games manally via "Games" class
games.Add(new Game() { GameID = 1, ImageUrl = "https://www.freepnglogos.com/uploads/gta-5-logo-png/grand-theft-auto-v-1.png", Title = "GTA 5", Genre = "Action-adventure, Open world", Description = "Grand Theft Auto V for PC offers players the option to explore the award-winning world of Los Santos and Blaine County in resolutions of up to 4k and beyond, as well as the chance to experience the game running at 60 frames per second.", Developer = "Rockstar North", Release = new DateTime(2013, 10, 4), Price = "59.99" });
games.Add(new Game() { GameID = 2, ImageUrl = "https://www.freepnglogos.com/uploads/gta-5-logo-png/grand-theft-auto-v-1.png", Title = "GTA 5", Genre = "Action-adventure, Open world", Description = "Grand Theft Auto V for PC offers players the option to explore the award-winning world of Los Santos and Blaine County in resolutions of up to 4k and beyond, as well as the chance to experience the game running at 60 frames per second.", Developer = "Rockstar North", Release = new DateTime(2013, 10, 4), Price = "59.99" });
games.Add(new Game() { GameID = 3, ImageUrl = "https://www.freepnglogos.com/uploads/gta-5-logo-png/grand-theft-auto-v-1.png", Title = "GTA 5", Genre = "Action-adventure, Open world", Description = "Grand Theft Auto V for PC offers players the option to explore the award-winning world of Los Santos and Blaine County in resolutions of up to 4k and beyond, as well as the chance to experience the game running at 60 frames per second.", Developer = "Rockstar North", Release = new DateTime(2013, 10, 4), Price = "59.99" });
games.Add(new Game() { GameID = 4, ImageUrl = "https://www.freepnglogos.com/uploads/gta-5-logo-png/grand-theft-auto-v-1.png", Title = "GTA 5", Genre = "Action-adventure, Open world", Description = "Grand Theft Auto V for PC offers players the option to explore the award-winning world of Los Santos and Blaine County in resolutions of up to 4k and beyond, as well as the chance to experience the game running at 60 frames per second.", Developer = "Rockstar North", Release = new DateTime(2013, 10, 4), Price = "59.99" });
games.Add(new Game() { GameID = 5, ImageUrl = "https://www.freepnglogos.com/uploads/gta-5-logo-png/grand-theft-auto-v-1.png", Title = "GTA 5", Genre = "Action-adventure, Open world", Description = "Grand Theft Auto V for PC offers players the option to explore the award-winning world of Los Santos and Blaine County in resolutions of up to 4k and beyond, as well as the chance to experience the game running at 60 frames per second.", Developer = "Rockstar North", Release = new DateTime(2013, 10, 4), Price = "59.99" });
//Add to datagrid from "games list"
dg_Games.ItemsSource = games;
}
//Game class for database content on games page
public class Game
{
public int GameID { get; set; }
//Not used anymore
//public Image GameImage { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public string Genre { get; set; }
public string Developer { get; set; }
public DateTime Release { get; set; }
public string Price { get; set; }
//Temporary, don't worry about this
public string ImageUrl { get; set; }
}
//Button purchase click
private void btn_Purchase_Click(object sender, RoutedEventArgs e)
{
}
}
}
And finally, here's what my table in SSMS is supposed to look like. I haven't stared filling it in yet as I don't even know how to populate the list in the first place.
EDIT : I forgot to add some detail about third party libraries, my only real restirction for this is the fact that I have to use Entity Framework but, besides that, I can use pretty much any 3rd party library as long as it's open source.
Solved it, once I got how Entity works, it was dead straight forward. It turns out I don't need that generic list at all, you can bind stuff from the DB directly to the textblocks. All I had to do was add this bit of code and it does all the rest for you:
public partial class GamesPage : Page
{
RareMantisEntities1 _db = new RareMantisEntities1();
public static DataGrid datagrid;
public GamesPage()
{
InitializeComponent();
Load();
}
private void Load()
{
dg_Games.ItemsSource = _db.Games.ToList();
datagrid = dg_Games;
}
}