Build a Complete App with ASP.Net, C#, SQL Lite – Part 3

This is part 3 of our complete application in ASP.NET with C# and SQL Lite. In this part we would do the following

• Create the SQL Lite database connection
• Install Entity Framework
• Setup the Data Context
• Generate Tables Using Migration

 

1. Create the SQL Lite Connection

First create a folder named Data.

Open the  the Server Explorer Windows (Views > Server Explorer). Shown below

Click on the SQL Lite/SQL Server Compact Toolbox icon to open

Then click on Add New SQL Lite Connection

The Create New SQL Lite connection window is opened as shown below. So you can define new SQL Lite Connection

 

Click on Create

Navigate the the Data folder you created earlier and give a name to your database.

Provide a name for your database (I call it HospitalDB)

Finally, click on Test Connection. If the test is successful, you will notice that the database file is created in the Data folder.

You can then close the window.

 

2. Install Entity Framework

Now, what is Entity Framework? This is an ORM(Object Relational Mapping) tool that helps you map between database tables and C# classes in your application.

To install EF, open Package Manager console (Views > Other Windows > Package Manager)

Run the following two commands one after the other

Install-Package Microsoft.EntityFrameworkCore -Version 3.1.6

 

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 3.1.6

 

Install-Package Microsoft.EntityFrameworkCore.Sqlite -Version 3.1.6

Note that the version numbers have to be the same

 

3. Create the Data Context

A data context is used to communicate with the database and perform various operation included Create, Read, Update and Delete( CRUD). Follow the steps below:

Step 1 – Create a class in the Data folder. Name it HospitalContext. This class should inherit from DBContext.

Step 2 – Add the following code into the class:

public class HospitalContext : DbContext
{
    public HospitalContext(DbContextOptions<HospitalContext> options)
    : base(options)
    {
    }
    public virtual DbSet<Patient> Patients {get; set;}
    public virtual DbSet<Appointment> Appointments { get; set; }
    public virtual DbSet<Room> Rooms { get; set; }
    public virtual DbSet<Admission> Admissions { get; set; }
    public virtual DbSet<Discharge> Discharges { get; set; }
    public virtual DbSet<Physician> Physicians { get; set; }
    public virtual DbSet<Specialty> Specialties { get; set; }
    public virtual DbSet<Country> Countries { get; set; }
}

 

4. Generate the Tables Using Migration

Now we would use migrations  to generate the tables in the database. Follow the steps below:

Step 1 – Add a connection string to the appsettings.json file.

  "ConnectionStrings": {
    "HospitalConnectionString": "Data Source=Data/HospitalDB.db"
  }

 

Step 2 – Also add the connectionstring to the Startup.cs file, in the ConfigureServices method as shown below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
                services.AddDbContext<HospitalContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("HospitalConnectionString")));
}

 

Step 3 – Finally run to code below to add migrations Initialize(you can change the name if you want). This would simply generate the script that would create the database object for you

add-migration Initialize

 

If this code executes successfully, you will notice that a folder is create called Migrations. This folder contains two files: the migration file and the snapshot

Step 4 – At this point, I recommend you run the program the make sure nothing is broken.

Step 5 – Run the following command to actually generate your tables

Update-Database

 

If this command executes successfully, you will notice that your database tables is created. You can expand the SQL Lite data connection in the server explorer to check this as shown below: