Home

Intro

Following Youtube Course:
C# Application From Start to Finish: Tournament Tracker

VOD:
https://www.youtube.com/watch?v=HalXZUHfKLA&list=PLLWMQd6PeGY3t63w-8MMIjIyYS7MsFcCi&index=1

Stored Procedures (Some might find useful for Lesson 8):
https://www.youtube.com/watch?v=Sggdhot-MoM

Overview

Lesson 1 - Initial Planning
Lesson 2 - Overview Planning
Lesson 3 - Data Design
Lesson 4 - User Interface Design
Lesson 5 - Logic Planning
Lesson 6 - Class Library Creation
Lesson 7 - Form Building
Lesson 8 - SQL Database Design
Lesson 9 - Prize Form Wire Up

Visual Studio Shortcuts:

prop + tab + tab

tab to got to next property
enter to got to end of line

///

xml comment

cw + tab + tab

Console.WriteLine()

Lessons

Lesson 1 - Initial Planning

Scenario

Your friends come to you and ask you to create a tournament tracker.

They are always playing games and want to determine who is the best.

The idea is that you create a bracket tournament system where the computer will tell them who to play in a single-elimination style bracket.

At the end, the winner should be identified.

Their model is the NCAA Basketball tournament bracket for March Madness.

Requirements

Tracks games played and their outcome (who won).
Multiple competitors play in the tournament.
Creates a tournament plan (who plays in what order).
Schedules games.
A single loss eliminates a player.
The last player standing is the winner.

Questions

How many players will the tournament handle? Is it variable?
If a tournament has less than the full complement of players, how do we handle it?
Should the ordering of who plays each other be random or ordered by input order?
Should we schedule the game or are they just played whenever?
If the games are scheduled, how does the system know when to schedule games for?
If the games are played whenever, can a game from the second round be played before the first round is complete?
Does the system need to store a score of some kind or just who won?
What type of front-end should this system have (form, webpage, app, etc.)?
Where will the data be stored?
Will this system handle entry fees, prizes, or other payouts?
What type of reporting is needed?
Who can fill in the results of a game?
Are there varying levels of access?
Should this system contact users about upcoming games?
Is each player on their own or can teams use this tournament tracker?

Lesson 2 - Overview Planning

Answers

How many players will the tournament handle? Is it variable?
- The application should be able to handle a variable number of players in a tournament.
If a tournament has less than the full complement of players, how do we handle it?
- A tournament with less that the perfect number (a multiple of 2, so 4, 8, 16, 32, etc.) should add in "byes".
  - Basically, certain people selected at random get to skip the first round and act as if they won.
Should the ordering of who plays each other be random or ordered by input order?
- The ordering of the tournament should be random.
Should we schedule the game or are they just played whenever?
- The games should be played in whatever order and whenever the players want to play them.
If the games are scheduled, how does the system know when to schedule games for?
- They are not scheduled so we do not care.
If the games are played whenever, can a game from the second round be played before the first round is complete?
- No.
  - Each round should be fully completed before the next round is displayed.
Does the system need to store a score of some kind or just who won?
- Storing a simple score would be nice.
  - Just a number for each player.
    - That way, the tracker can be flexible enough to handle a checkers tournament (the winner would have a 1 and the loser a 0) or a basketball tournament.
What type of front-end should this system have (form, webpage, app, etc.)?
- The system should be a desktop system for now, but down the road we might want to turn it into an app or a website.
Where will the data be stored?
- Ideally, the data should be stored in a Microsoft SQL database but please put in an option to store to a text file instead.
Will this system handle entry fees, prizes, or other payouts?
- Yes.
  - The tournament should have the option of charging an entry fee.
    - Prizes should also be an option, where the tournament administrator chooses how much money to award a variable number of places.
      - The total cash amount should not exceed the income from the tournament.
        
        A percentage-based system would also be nice to specify.
What type of reporting is needed?
- A simple report specifying the outcome of the games per round as well as a report that specifies who won and how much they won.
  - These can be just displayed on a form or they can be emailed to tournament competitors and the administrator.
Who can fill in the results of a game?
- Anyone using the application should be able to fill in the game scores.
Are there varying levels of access?
- No.
  - The only method of varied access is if the competitors are not allowed into the application and instead, they do everything via email.
Should this system contact users about upcoming games?
- Yes, the system should email users that they are due to play in a round as well as who they are scheduled to play.
Is each player on their own or can teams use this tournament tracker?
- The tournament tracker should be able to handle the addition of other members.
  - All members should be treated as equals in that they all get tournament emails.
    - Teams should also be able to name their team.

Big Picture Design

Structure: Windows Forms application and Class Library
Data: SQL and/or Text File
Users: One at a time on one application

Key Concepts

Email
SQL
Custom Events
Error Handling
Interfaces
Random Ordering
Texting

Lesson 3 - Data Design

Mapping the Data

Team

TeamMembers (List<Person>)
TeamName (string)

Person

FirstName (string)
LastName (string)
EmailAddress (string)
CellphoneNumber (string)

Tournament

TournamentName (string)
EntryFee (decimal)
EnteredTeams (List<Team>)
Prizes (List<Prize>)
Rounds (List<List<Matchup>>)

Prize

PlaceNumber (int)
PlaceName (string)
PrizeAmount (decimal)
PrizePercentage (double)

Matchup

Entries (List<MatchupEntry>)
Winner (Team)
MatchupRound (int)

Matchup Entry

TeamCompeting (Team)
Score (double)
ParentMatchup (Matchup)

Lesson 4 - User Interface Design

Album:
https://imgur.com/a/TYJneFU https://github.com/ShadowEnder/TournamentTracker/tree/master/wiki%20images/4%20-%20User%20Interface%20Design

Tournament Viewer

Create Tournament

Create Team

Create Prize

Tournament Dashboard

Lesson 5 - Logic Planning

Create Tournament

create new (link)

Creates a new team
Opens a new form
When finished, closes form, and new data is on create tournament form
Probably a good place to have an interface

Add Team (button)

Looks in Select Team Box and add to tournamentPlayersListBox
Remove from dropdown list it pulled it from
Refresh dropdown and listbox

Create Prize (button)

works like create new
open form
wait till prize is created
put in prizesListBox

Delete Selected (button)

delete item selected in listbox to its left
for teams, the team will go back into the Select Team dropdown box

Create Tournament (button)

Validate information
- Have Tournament Name
- Make sure Entry Fee isn't a negative number
- Make sure have at least 2 teams
- Create Schedule
  - Number of teams (ex: have 10 teams, then make a tournament of 16 teams)
    - Number of byes
  - Randomize order for first round

Create Team

Add Member (button)

Take existing Team Member from dropdown list and add to tournamentPlayersListBox on right
- Remove member from dropdown list and refresh both lists

Create Member (button)

Take 4 fields, make new Team Member, and add to tournamentPlayersListBox
- Clear out 4 fields

Create Team (button)

Validate Team and create them
Send info back to the caller

Missing Delete Player button (Delete Selected).

Create Prize

Create Prize (button)

Validate info and send info back to calling form and close form.

Tournament Dashboard

Has list of existing tournaments.
If select a tournament and click Load Tournament (button), is going to load selected Tournament in Tournament Viewer.

Create Tournament (button)

Opens Create Tournament form.
Capture created tournament and load into dropdown.

Tournament Viewer

Tournament: <name> will be updated when form is loaded.

Round (dropdown)

Will figure out how many rounds are in a rounds object.
If a tournament has 4 rounds -> Round 1, 2, 3, and 4.
Has to know which round to go to.
Will change what the matchupListBox shows.

Unplayed Only (checkbox)

Checked by default.
If checked, will filter matchupListBox further by if a game is played or not (not just round #).

Scores

Display, update, and change scores.
Depends on what's selected in matchupListBox
- Update Team Names
- If have scores, put scores in score boxes

Score (button)

Change that matchup's scores.
The matchup is over, this is who won.
Last unplayed game in the round, triggers next round.
- email, end of tournament, assignment of prizes, results, etc.
Can we mark played games with a new score?
- Yes, as long as still in current round.

Other Pieces

Data Access
Data Storage
How deal with 2 different data sources?
How email out information?
- What triggers that?
What triggers knowing who plays the next matchup?

Lesson 6 - Class Library Creation

TrackerLibrary
Solution: Tournament Tracker

Shortcuts:
prop + tab + tab

tab to got to next property
enter to got to end of line

///

xml comment

cw + tab + tab

Console.WriteLine()

Code

Team Model.cs

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

namespace TrackerLibrary
{
    public class TeamModel
    {
        public List<PersonModel> TeamMembers { get; set; } = new List<PersonModel>();
        public string TeamName { get; set; }
    }
}

Person Model.cs

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

namespace TrackerLibrary
{
    public class PersonModel
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailAddress { get; set; }
        public string CellphoneNumber { get; set; }
    }
}

TournamentModel.cs

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

namespace TrackerLibrary
{
    public class TournamentModel
    {
        public string TournamentName { get; set; }
        public decimal EntryFee { get; set; }
        public List<TeamModel> EnteredTeams { get; set; } = new List<TeamModel>();
        public List<PrizeModel> Prizes { get; set; } = new List<PrizeModel>();
        public List<List<MatchupModel>> Rounds { get; set; } = new List<List<MatchupModel>>();
    }
}

PrizeModel.cs

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

namespace TrackerLibrary
{
    public class PrizeModel
    {
        public int PlaceNumber { get; set; }
        public string PlaceName { get; set; }
        public decimal PrizeAmount { get; set; }
        public double PrizePercentage { get; set; }
    }
}

MatchupModel.cs

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

namespace TrackerLibrary
{
    public class MatchupModel
    {
        public List<MatchupEntryModel> matchupEntries { get; set; } = new List<MatchupEntryModel>();
        public TeamModel Winner { get; set; }
        public int MatchupRound { get; set; }
    }
}

MatchupEntryModel.cs

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

namespace TrackerLibrary
{
    public class MatchupEntryModel
    {
        /// <summary>
        /// Represents one team in the matchup.
        /// </summary>
        public TeamModel TeamCompeting { get; set; }

        /// <summary>
        /// Represents the score for this particular team.
        /// </summary>
        public double Score { get; set; }

        /// <summary>
        /// Represents the matchup that this team came 
        /// from as the winner.
        /// </summary>
        public MatchupModel ParentMatchup { get; set; }
    }
}

Lesson 7 - Form Building

Created Icon in Syncfusion Metro Studio.
Size: 16 x 16
Padding: 0 (Important!)
Export: TournamentViewer.ico

TournamentViewerForm.cs Tournament Viewer Form

CreateTournamentForm.cs Create Tournament Form

CreateTeamForm.cs Create Team Form

CreatePrizeForm.cs
Create Prize Form

TournamentDashboard.cs
Tournament Dashboard Form

Lesson 8 - SQL Database Design

Database Diagram

Microsoft SQL Server Management Studio (SSMS)
Version: 18.0 (RC1) (17.9.1 would not install on Windows 10 with Visual Studio 2017, believe it needs Visual Studio 2015)
Link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#ssms-180-rc1
Docs: https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-2017

SQL Server 2017 - Developer Edition
Link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Setup: Basic

In SSMS Connect to: localhost
Databases folder -> right-click -> New Database
Database Name: Tournaments

A DBA (Database Administrator) would normally put Data file on one drive and the Log file on another drive.

OR

Click New Query (button)

create database Tournaments;

Click Execute (button)

Right-click Tables folder -> New Table

Column Name - Data Type - Allow Nulls

id - int - [] - Primary key
-> Column Properties -> Identity Speculation -> (Is Identity) -> Yes, -> Identity Increment -> 1, -> Identity Seed -> 1

PlaceNumber - int - [] PlaceName - nvarchar(50) - []
-> nvarchar = n (stores unicode characters), var (variable length, can hold any number of characters up to the max number of characters), char (character), 50 (max number of characters it can hold, max of ~4000)
-> varchar stores ASCII characters -> unicode takes up twice the space but allows for other languages' characters -> nvarchar(MAX) is stored differently. For when user is typing a book or other types of data. Ex: storing JSON string.

PrizeAmount - money - [] PrizePercentage - float - []

Tables and Fields

Prizes
Prizes

Tournament Prizes

Tournaments

TournamentEntries

Teams
Teams

TeamMembers

People
People

Matchups

MatchupEntries

Relationships

Database Table Relationships

Primary key table - Foreign key table

field - field

Prizes - TournamentPrizes
id - PrizeId

Tournaments - TournamentPrizes
id - TournamentId

Tournaments - TournamentEntries
id - TournamentId

Teams - TournamentEntries
id - TeamId

Teams - TeamMembers
id - TeamId

People - TeamMembers
id - PersonId

Teams - Matchups
id - WinnerId

Teams - MatchupEntries
id - TeamCompetingId

Matchups - MatchupEntries
id - MatchupId

Matchups - MatchupEntries
id - ParentMatchupId

Video Mistake

There was apparently a mistake in the video and Database Diagram.

Can edit the table without having to delete and recreate it by following directions here:
https://stackoverflow.com/questions/9870968/cant-change-table-design-in-sql-server-2008

Make sure to re-enable it after you finish updating the table.

Matchups should have another field: Tournament Id
TournamentId is gotten from the Tournaments table.

Relationship
Primary key - Foreign key
Tournamnets - Matchups
id - TournamentId

Matchups new Table

Updated Relationships Diagram

Other Info

Email Address - Max length 2000 characters but nearly no one has so limit to 200 characters, nvarchar(200)
Phone Number - 20 characters, allow null
Create Data - datetime2(7) - Column Properties -> Default Value or Binding -> getdate() - gets the system date and time, or -> getutcdate() - gets date time with modification of 0, so it's the same time no matter where in the world, won't have to worry about timezones.

date is just the date and time datetime is the old standard for storing date and time datetime2 is the new standard

Select top 1000 rows from TestPerson table.

select top 1000 *
from TestPerson
where LastName = 'Corey'

Select top 1000 rows from TestPerson table where their last name is Corey.
(Not case sensitive, ex: could search for 'corey' and would get same results.)

select top 1000 *
from TestPerson
where LastName = 'Corey'

Select top 1000 rows from TestPerson table where their last name starts with Cor followed by any other characters.

select top 1000 *
from TestPerson
where LastName like 'Cor%'

Select top 1000 rows from TestPerson table and order them by their first name.
Two dashes, -- , makes the line a comment.

select top 1000 *
from TestPerson
--where LastName = 'Corey'
order by FirstName

Selects only the first name and last name columns where last name is Corey and orders by their first name.

select FirstName, LastName
from TestPerson
where LastName = 'Corey'
order by FirstName

Selects only the first name and last name columns, renaming the columns displayed from FirstName to First Name and LastName to Surnname, where last name is Corey and orders by their first name.

select FirstName as 'First Name', LastName as 'Surname'
from TestPerson
where LastName = 'Corey'
order by FirstName

Stored Procedures

CTRL+SHIFT+R -> Refreshes Editor (so new procedures, tables, etc. are not underlined in red)

NOTE: Whatever you have highlighted is what get executed, unless nothing is highlighted, in which case everything is run.

dbo.spPrizes_GetByTournament

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spPrizes_GetByTournament]    Script Date: 4/8/2019 11:43:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Gets all the prizes for a given tournament
ALTER PROCEDURE [dbo].[spPrizes_GetByTournament] -- CREATE PROCEDURE (on first run). Method
	-- Add the parameters for the stored procedure here
	@TournamentId int -- Parameter name & Parameter type
AS
BEGIN -- Everything for stored procedure between BEGIN and END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON; -- doesn't send how many rows back to caller

	select p.* -- select all from dbo.Prizes table (renamed to p)
	from dbo.Prizes p -- Give me all info from Prizes table, dbo.Prizes table renamed to p
	inner join dbo.TournamentPrizes t on p.id = t.PrizeId -- inner join connects two different tables, connects based on Prize tables id equal (=) to TournamentPrizes id (renamed to t, so t.PrizeId)
	where t.TournamentId = @TournamentId; -- where TournamentPrizes.TournamentId equals (=) @TournamentId (@TournamentId is the id you enter into parameter when you call method)
END

Call by:

exec dbo.spPrizes_GetByTournament '5'

dbo.spMatchups_GetByTournament
Just a note, initial stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.spMatchups_GetByTournament
	@TournamentId int
AS
BEGIN
	SET NOCOUNT ON;

	select mu.*
	from matchups mu
	where mu.TournamentId = @TournamentId
	order by mu.MatchupRound
END
GO

After above script is executed, if you modify the stored procedure you'll see:

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spMatchups_GetByTournament]    Script Date: 4/9/2019 12:42:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spMatchups_GetByTournament]
	@TournamentId int
AS
BEGIN
	SET NOCOUNT ON;

	select mu.*
	from matchups mu
	where mu.TournamentId = @TournamentId
	order by mu.MatchupRound
END

Rest of scripts will show the final script (ALTER not CREATE), so keep that in mind.

dbo.spMatchupEntries_GetByMatchup

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spMatchupEntries_GetByMatchup]    Script Date: 4/9/2019 1:03:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spMatchupEntries_GetByMatchup]
	@MatchupId int
AS
BEGIN
	SET NOCOUNT ON;

	select mue.*
	from MatchupEntries mue
	inner join dbo.MatchupEntries mu on mue.MatchupId = mu.id
	where mu.id = @MatchupId
END

dbo.spPeople_GetAll

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spPeople_GetAll]    Script Date: 4/9/2019 12:50:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spPeople_GetAll]

AS
BEGIN
	SET NOCOUNT ON;

	select ppl.*
	from People ppl
	order by LastName
END

dbo.spTeam_GetByTournament

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spTeam_GetByTournament]    Script Date: 4/9/2019 1:08:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spTeam_GetByTournament]
	@TournamentId int
AS
BEGIN
	SET NOCOUNT ON;

	select te.*
	from TournamentEntries te
	inner join dbo.Tournaments t on te.TournamentId = t.id
	where t.id = @TournamentId
END

dbo.spTeamMembers_GetByTeam

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spTeamMembers_GetByTeam]    Script Date: 4/9/2019 12:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spTeamMembers_GetByTeam] 
	@TeamId int
AS
BEGIN
	SET NOCOUNT ON;

    select tm.*
	from TeamMembers tm
	inner join dbo.Teams t on tm.TeamId = t.id
	where t.id = @TeamId
END

dbo.spTournaments_GetAll

USE [Tournaments]
GO
/****** Object:  StoredProcedure [dbo].[spTournaments_GetAll]    Script Date: 4/9/2019 12:53:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spTournaments_GetAll]

AS
BEGIN
	SET NOCOUNT ON;

    select t.*
	from Tournaments t
END

dbo.spTestPerson_GetByLastName (example procedure)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.spTestPerson_GetByLastName
	@LastName nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

	select *
	from dbo.TestPerson
	where LastName = @LastName;
END

exec dbo.spTestPerson_GetByLastName 'Smith'

Provide feedback

Saved searches

Use saved searches to filter your results more quickly