# Creating database and tables

## Creating a database from C#

There are two main ways to create and initialize a database

1. using C# code
1. using DBMS tools

We will use C# code.

<!--
## Typical database structure

A database consists of one or more tables and a table one or more columns.

:::{mermaid}
classDiagram
    direction LR
    Database o-- Table
    Table o-- Column
:::
-->

## Making objects storable

Using the Entity Framework Core, we can create database tables from a collection of objects, and vice-versa. For example:

::::{grid} 3
:::{grid-item-card}
:columns: auto
```cs
new Item()
{
    Id = "M3 screw",
    PricePerUnit = 1,
    InventoryLocation = 1
},
new Item()
{
    Id = "pen",
    PricePerUnit = 1,
    InventoryLocation = 3
}
```
:::
:::{grid-item}
:columns: auto
:child-align: center
↔️
:::
:::{grid-item-card}
:columns: auto
```{list-table}
:header-rows: 1
* - Id
  - PricePerUnit
  - InventoryLocation
* - M3 screw
  - 1.0
  - 1
* - pen
  - 1.0
  - 3
```
:::
::::

Before we can store objects of our classes, we have to pay attention to the requirements:

1. Each object needs a field called `Id`, which helps the database to differentiate between *row*s.

2. Each field that must be stored must be stored in the database must be represented as a property with getters and setters, i.e., `{get; set;}`

```cs
public class Item
{
    public string Id { get; set; }
    public decimal PricePerUnit { get; set; }
    public uint InventoryLocation { get; set; }
}
```

:::{note}
We modified the `Name` field to `Id` to make `Item` storable in the database.
:::

<!--
better other item
:::{activity} Making `Item` storable
1. Create a console project named `DatabaseExample`.
1. Copy your `Item` class from your previous project.
1. Modify the `Item` class so that its objects are storable in the database
:::
-->

## Creating a database with a table

:::{commons-figure} https://commons.wikimedia.org/wiki/File:Hand_in_filing_cabinet.jpg
:figwidth: 35%
:align: right
If the whole filing cabinet represents a database, then a drawer is a *table* and each *card* is a *row* in the database.
:::
You can use the following code for creating a database for the first time:

```cs
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

var db = new InventorySystemDbContext();
db.Database.EnsureCreated();

public class InventorySystemDbContext : DbContext
{
    public DbSet<Item> Items { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=../../../inventory.sqlite");
    }
}

public class Item
{
    public string Id { get; set; }
    public decimal PricePerUnit { get; set; }
    public uint InventoryLocation { get; set; }
}
```

:::{figure} ../img/inventory.sqlite-in-solution-pane.png
:name: inventory.sqlite
:figwidth: 35%
:align: right
`inventory.sqlite` on the solution pane after creating the database.
:::

After running, you should see `inventory.sqlite` in your project folder as shown in {numref}`inventory.sqlite`.


Explanation:
1. For database interaction, we create a class (`InventorySystemDbContext` above) that inherits from `DbContext`.

   `DbContext` represents the interaction session with the database.

1. We override the method `DbContext.OnConfiguring()`, so that we can set the options.

   In the options, we set the path of our database file `inventory.sqlite`. `../../../` means three folders up in the hierarchy so that we land in our project folder. We do this for convenience so that we can easily delete or open this file direct from our solution. This file would be typically saved on the same level of the program binary, i.e., in the folder `./bin/Debug/net*/`, however.
   
1. Our class contains `DbSet`s for each table we want to store, e.g., `DbSet<Item> Items`.

1. `DbContext.Database.EnsureCreated()` creates the database file and/or tables if the database does not contain any tables. Returns `True` if a new database was created.

   This function is useful for initializing a database.
   
:::{tip}
If you want to create the database again, then you can simply delete `inventory.sqlite` in the solution pane or use `db.Database.EnsureDeleted()`.
:::
:::{note}
Database currently does not contain any data.
:::
:::{warning}
In a GUI application, use `EnsureCreatedAsync()` instead of `EnsureCreated()`. Otherwise GUI may freeze.
:::

## Appendix

- <mslearn:ef/core> about EF in general: database context, querying and saving data
- <mslearn:ef/core/miscellaneous/connection-strings#universal-windows-platform-uwp> about connecting to a SQLite database
- <mslearn:ef/core/modeling/keys> about mapping database keys