$\textcolor{green}{\textsf{S}}$ tandard $\textcolor{green}{\textsf{Q}}$ uery $\textcolor{green}{\textsf{L}}$ anguage Database Apps

Demo

Demo.mp4

---

1. Build a SQL database

2. Build a front end app

3. Connect DB to app

Staging Development Environment

1. Install MySQL server, make sure to include MySQL Workbench.

2. Install MAMP

3. Install Visual Studio 2022 Community Ed.

Create a database named music with table albums

Field 	Type 	Null 	Key 	Default 	Extra 	
ID 	int(11) 	NO 	PRI 	NULL 	auto_increment
ALBUM_TITLE 	varchar(100) 	NO 		NULL 	
ARTIST 	varchar(100) 	NO 		NULL 	
YEAR 	year(4) 	NO 		NULL 	
IMAGE_NAME 	varchar(500) 	NO 		NULL 	
DESCRIPTION 	text 	NO 		NULL 	
URL 	varchar(200) 	NO 		NULL 	

Synchronize Tables albums and tracks

-- MySQL Workbench Synchronization
-- Generated: 2025-03-08 15:48
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: markn

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `music`.`albums` 
ADD PRIMARY KEY (`ID`);
;

CREATE TABLE IF NOT EXISTS `music`.`tracks` (
  `ID` INT(11) NOT NULL,
  `track_title` VARCHAR(100) NULL DEFAULT NULL,
  `number` INT(11) NULL DEFAULT NULL,
  `video_url` VARCHAR(100) NULL DEFAULT NULL,
  `lyrics` TEXT(1000) NULL DEFAULT NULL,
  `albums_ID` INT(11) NOT NULL,
  PRIMARY KEY (`ID`),
  INDEX `fk_tracks_albums_idx` (`albums_ID` ASC),
  CONSTRAINT `fk_tracks_albums`
    FOREIGN KEY (`albums_ID`)
    REFERENCES `music`.`albums` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

EER Diagram

---

Front end app

Create a C# Windows Form Desktop application using Visual Studio 2022 with .NET 8.0 framework.

1. Create a windows form
2. Create an Albums class, and a Tracks class
3. Create an Albums Data Access Object class, and a Tracks DAO class
4. Build with Nuget extension System.Data.SQLite. Since we started with MySQL, the framework is similar. Replacing MySQL Data Access Objects with SQLite3 DAO is a simple task. However, there are little nuances along the way which is a great way to learn 3rd party integration.

---

Download Demo