###### tags: `Back-end`,`Restful API`,`ASP.NET CORE`,'Titanium'
# Day 05 - Database, SQL, and .NET Integration
## Database and SQL Fundamentals
### What is a Database?
A database is an organized collection of data. Think of it as a place where information is stored and can be easily retrieved, updated, or deleted. Databases are essential for almost any application that needs to manage large amounts of data efficiently.
### What is SQL?
SQL (Structured Query Language) is the standard language used to communicate with databases. It allows us to perform actions like creating tables, inserting, updating, deleting data, and querying (searching) the database.

### Setting Up SQLite
Before we dive into creating tables and writing SQL queries, it's important to set up the right environment for practicing SQL commands. We will use SQLite, which is a lightweight, easy-to-use database engine. You don’t need a full database server to get started, and you can practice directly on your local machine.

**Why SQLite?**
Lightweight: It doesn’t require complex setup like some other databases (e.g., MySQL, PostgreSQL).
Portable: It stores the entire database in a single file.
Easy to use: You can use SQLite to practice SQL commands in minutes.

We will use SQLite Browser to interact with the database and execute SQL commands.

### Steps to Prepare SQL Environment
Install SQLite and SQLite Browser
Follow these steps to install SQLite Browser, which provides a graphical interface to manage SQLite databases:
* Download SQLite Browser:
* Visit the official website SQLite Browser.
* Download the version that matches your operating system (Windows, macOS, or Linux).
* Install SQLite Browser:
* For Windows, open the .exe file and follow the installation instructions.

Once installed, open the application. You’ll see an interface where you can create and manage databases.

### Create New Database
Now that you have SQLite Browser installed, let’s create your first SQLite database.

Open SQLite Browser.
Click on File → New Database.
Choose a location and name your database (e.g., TrainingDB.db).
Click Save. You now have an empty database ready to use!

### Executing SQL Commands in SQLite Browser
To start writing and executing SQL commands:

In SQLite Browser, click on the Execute SQL tab at the top.
You’ll see a large text area where you can write your SQL queries.
After writing your query, click the Execute button to run it. The results (if any) will appear below.

### Create Table Using SQL Commands
Now that you have set up your environment, let’s dive into SQL commands, starting with creating a table.

In a relational database like SQLite, data is stored in tables. Each table consists of columns (attributes) and rows (records).
Syntax for Creating a Table
To create a table, we use the CREATE TABLE command. Let’s look at an example.

Example:

CREATE TABLE Trainers (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Specialization TEXT
);

**Explanation:**
* Id: This is the unique identifier for each trainer. It’s of type INTEGER and is marked as the PRIMARY KEY, meaning it will be unique for every row.
* Name: This stores the name of the trainer. It is of type TEXT, and it’s marked as NOT NULL, meaning this field cannot be left empty.
* Specialization: This is another TEXT field that stores the area of expertise of the trainer (e.g., Fitness, Yoga).

In SQLite Browser, go to the Execute SQL tab.
Paste the above CREATE TABLE query into the text area.
Click Execute.

You have now successfully created a table named Trainers. You can view it by going to the Browse Data tab and selecting Trainers from the dropdown.

### Insert Data into a Table
Now that you have created the Trainers table, let’s insert some sample data into it. We use the INSERT INTO command for this.

INSERT INTO Trainers (Id, Name, Specialization, Salary)
VALUES (1, 'John Doe', 'Fitness', 3000.00);

**Explanation:**
* INSERT INTO Trainers: This specifies that we are inserting data into the Trainers table.
* (Id, Name, Specialization): These are the columns in which we are inserting values.
* VALUES (1, 'John Doe', 'Fitness'): These are the values that correspond to the columns. We are inserting a trainer with Id=1, Name='John Doe', Specialization='Fitness'

### View Data from a Table
the SELECT statement is used to retrieve data from the database. By default, it retrieves all the rows in the table. However, you often need to filter the data using the WHERE clause to get specific records based on certain conditions.

To view the data stored in a table, we use the SELECT statement.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

**Explanation:**
* SELECT *: This means we are selecting all columns (*) from the table.
* FROM Trainers: This specifies the table from which we want to retrieve the data.

**Example 1: Retrieve specific columns**
Let’s retrieve only the Name and Salary columns from the Trainers table:

SELECT Name, Salary
FROM Trainers;

Using the WHERE Clause
To filter results and retrieve only the trainers with a salary greater than 2500, you can use the WHERE clause:

SELECT Name, Salary
FROM Trainers
WHERE Salary > 2500;

**Explanation:**
SELECT Name, Salary: Retrieves only the Name and Salary columns.
WHERE Salary > 2500: Filters the results to show only rows where the Salary is greater than 2500.

### UPDATE Data in a Table
The UPDATE statement is used to modify existing records in a table. You can update specific rows using the WHERE clause.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let’s update the salary of a trainer with Id = 1:

UPDATE Trainers
SET Salary = 3500
WHERE Id = 1;

**Explanation:**
UPDATE Trainers: Specifies the table where the data should be updated.
SET Salary = 3500: Modifies the Salary column to 3500.
WHERE Id = 1: Ensures that only the record with Id = 1 is updated.

After executing, you can verify the update by selecting the data again:

SELECT * FROM Trainers;

### DELETE Data from a Table
The DELETE statement is used to remove rows from a table. Be cautious when using DELETE, especially without the WHERE clause, as it will remove all records from the table.

DELETE FROM table_name
WHERE condition;

Let’s remove a trainer from the Trainers table where the Id = 1:

DELETE FROM Trainers
WHERE Id = 1;

**Explanation:**
DELETE FROM Trainers: Specifies the table where the data should be deleted.
WHERE Id = 1: Ensures that only the record with Id = 1 is deleted.

To confirm that the row was deleted, you can select the remaining data from the Trainers table:

SELECT * FROM Trainers;

### Using the LIKE Operator
The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. The patterns often use wildcard characters:

%: Represents zero or more characters.

Let’s find all trainers whose names start with the letter "J":

SELECT *
FROM Trainers
WHERE Name LIKE 'J%';

### Understanding Relationships Between Tables
In relational databases, data is stored in multiple tables that are connected via relationships. These relationships typically follow three main types:

#### One-to-One Relationship
In a one-to-one relationship, a record in one table corresponds to exactly one record in another table. This type of relationship is less common but is useful when you need to split a table for organizational or security reasons.

Consider two tables: Users and UserProfiles. Each user in the Users table has exactly one corresponding profile in the UserProfiles table.

Tables:

Users: Stores general information about users.
UserProfiles: Stores additional profile information for each user.

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Username TEXT,
    Email TEXT
);

CREATE TABLE UserProfiles (
    UserId INTEGER,
    ProfilePicture TEXT,
    Bio TEXT,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

#### One-to-Many Relationship
In a one-to-many relationship, a record in one table (the "one" side) can relate to multiple records in another table (the "many" side). This is the most common type of relationship.

Example:
Consider a Trainers table and a Courses table where each trainer can teach multiple courses. This creates a one-to-many relationship between Trainers and Courses.

Tables:

Trainers: Stores information about trainers.
Courses: Stores information about the courses they teach.

CREATE TABLE Trainers (
    Id INTEGER PRIMARY KEY,
    Name TEXT,
    Salary REAL
);

CREATE TABLE Courses (
    Id INTEGER PRIMARY KEY,
    CourseName TEXT,
    TrainerId INTEGER,
    FOREIGN KEY (TrainerId) REFERENCES Trainers(Id)
);

In this case, each trainer in the Trainers table can be linked to multiple courses in the Courses table through the TrainerId foreign key.

#### Many-to-Many Relationship
In a many-to-many relationship, multiple records in one table are associated with multiple records in another table. This type of relationship requires a junction table (also called a bridge table) to link the two tables.

Example:
Consider a scenario where multiple students can enroll in multiple courses. This creates a many-to-many relationship between the Students and Courses tables. To model this relationship, we use a junction table called Enrollments.

Tables:

Students: Stores information about students.
Courses: Stores information about the courses.
Enrollments: A junction table that links Students and Courses.

CREATE TABLE Students (
    Id INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Courses (
    Id INTEGER PRIMARY KEY,
    CourseName TEXT
);

CREATE TABLE Enrollments (
    StudentId INTEGER,
    CourseId INTEGER,
    FOREIGN KEY (StudentId) REFERENCES Students(Id),
    FOREIGN KEY (CourseId) REFERENCES Courses(Id)
);

Here, the Enrollments table contains two foreign keys: StudentId and CourseId. This table allows each student to enroll in multiple courses, and each course to have multiple students.

**Why are Relationships Important?**
* Data Organization: Relationships help organize data efficiently by breaking it into smaller, logically-related tables.
* Avoiding Redundancy: By using foreign keys to reference other tables, you avoid duplicating data, which keeps your database smaller and more efficient.
* Data Integrity: Relationships enforce constraints such as ensuring that a record in one table corresponds to a valid record in another (e.g., a course must have a valid trainer).

### Foreign Keys: How Relationships are Defined
A foreign key is a column or group of columns in one table that uniquely identifies a row in another table. It’s used to create a link between the two tables and enforce referential integrity.

Example: In the Courses table, TrainerId is a foreign key that references the Id column in the Trainers table. This ensures that every course is associated with a valid trainer.

CREATE TABLE Courses (
    Id INTEGER PRIMARY KEY,
    CourseName TEXT,
    TrainerId INTEGER,
    FOREIGN KEY (TrainerId) REFERENCES Trainers(Id)
);

The foreign key constraint enforces the relationship between the two tables, ensuring that any TrainerId in the Courses table exists in the Trainers table.

### Join Clauses
a JOIN is used to combine rows from two or more tables based on a related column.

Types of Joins:
INNER JOIN: Returns only the rows where there is a match in both tables.
LEFT JOIN: Returns all the rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side.

Assume you have another table called Certifications:

CREATE TABLE Certifications (
    TrainerId INTEGER,
    CertificationName TEXT,
    FOREIGN KEY (TrainerId) REFERENCES Trainers(Id)
);

Now, to retrieve the names of trainers along with their certifications, you can use an INNER JOIN:

SELECT Trainers.Name, Certifications.CertificationName
FROM Trainers
INNER JOIN Certifications
ON Trainers.Id = Certifications.TrainerId;

**Explanation:**
INNER JOIN Certifications ON Trainers.Id = Certifications.TrainerId: Joins the two tables on the related column TrainerId.

## .NET Core 8 and SQLite Integration
This section introduces .NET Core 8 and its integration with SQLite, a lightweight, file-based relational database system. SQLite is a great choice for beginner projects or small-scale applications, especially for learning purposes.

we’ll discuss how to integrate SQLite into a .NET Core 8 project as the data tier, which will manage the interaction between the application and the database.

### Packages Installation
Entity Framework Core: A popular ORM (Object-Relational Mapper) to interact with the SQLite database in a .NET Core project.

### DbContext and Connection Setup
The DbContext is the primary class responsible for interacting with the database in Entity Framework Core. It allows you to query, update, and manage data using LINQ (Language Integrated Query).

#### Setting Up the DbContext
The DbContext class is at the core of EF Core's database interactions. It represents a session with the database and is used to query and save data.

#### Creating a Connection String
The connection string tells your application how to connect to the SQLite database. In the case of SQLite, it’s usually just the path to the database file.

public class AppDbContext : DbContext
{
    public DbSet<Trainer> Trainers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=app.db");
    }
}

In this example, the SQLite database is stored in a file called app.db in the project’s root folder.

#### Registering DbContext in Dependency Injection (DI)
In .NET Core, we use Dependency Injection (DI) to manage services and dependencies like DbContext. To configure AppDbContext in DI, add it in the Program.cs file:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));

var app = builder.Build();

This registers the AppDbContext in the DI container so that it can be injected into controllers or services.

#### Creating Models and First Code Migrations
Models in Entity Framework Core represent tables in the database. Each property in a model corresponds to a column in the table.

Let’s create a simple model for a Trainer, which we will store in the SQLite database.

public class Trainer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Specialization { get; set; }
    public decimal Salary { get; set; }
}

This class represents a table with columns Id, Name, Specialization, and Salary.

#### Applying Migrations
Migrations in Entity Framework allow you to create or update database tables based on your model classes.

For developers who are using VSCode, install the Entity Framework tools to manage migrations:

dotnet tool install --global dotnet-ef

Next, create an initial migration:

dotnet ef migrations add InitialCreate

or if you are using Visual Studio Management IDE, use `Package Manager Console` to apply below commands:

Add-Migration InitialMig

#### Updating the Database

dotnet ef database update

or for Visual Studio:

Update-Database

This command applies the migration and creates the necessary tables in your SQLite database.

### Exploring SQLite Data Using SQLite Browser
After creating the database and tables, you can use SQLite Browser to view and manipulate the data.

Viewing the Database
* Open SQLite Browser.
* Load the database file (e.g., app.db).
* Use the Browse Data tab to inspect the contents of tables like Trainers.

## Task 05
Let's practice Integration .NET Core application with SQLITE

- Install DB Browser for SQLITE abd verify installation
- Download the SQLite DB Browser from SQLite Browser Official Website.
- Install the software on your machine.
- Launch the SQLite DB Browser and verify that it opens successfully.

- Install the required packages to integrate SQLite with your .NET Core project.
- pen your .NET Core 8 project in Visual Studio, VS Code, or any IDE.
* Open a terminal in the project directory.
* Install the SQLite Entity Framework Core package.

- Set Up the Connection String and Add DbContext to Dependency Injection
- Open the Program.cs file in your project.
- register DbContext with a connection string pointing to an SQLite database file:

- Create DbContext and DbSet for at Least One Model
- Create a class AppDbContext that inherits from DbContext.
- Create a model class representing an entity, for example, a Trainer class:

- Apply the Migration to Create the Database and Table