Creating database and tables

Creating database and tables#

Creating a database from C##

There are two main ways to create and initialize a database

  1. using C# code

  2. using DBMS tools

We will use C# code.

Making objects storable#

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

new Item()
{
    Id = "M3 screw",
    PricePerUnit = 1,
    InventoryLocation = 1
},
new Item()
{
    Id = "pen",
    PricePerUnit = 1,
    InventoryLocation = 3
}

↔️

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 rows.

  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;}

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.

Creating a database with a table#

https://upload.wikimedia.org/wikipedia/commons/a/a8/Hand_in_filing_cabinet.jpg

Fig. 31 If the whole filing cabinet represents a database, then a drawer is a table and each card is a row in the database.
CC BY-SA 4.0. By Watty62. Source: Wikimedia Commons#

You can use the following code for creating a database for the first time:

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; }
}
../_images/inventory.sqlite-in-solution-pane.png

Fig. 32 inventory.sqlite on the solution pane after creating the database.#

After running, you should see inventory.sqlite in your project folder as shown in Fig. 32.

Explanation:

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

    DbContext represents the interaction session with the database.

  2. 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.

  3. Our class contains DbSets for each table we want to store, e.g., DbSet<Item> Items.

  4. 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 could try to

Appendix#

Contents