In my previous article Simple Way To Create Sqlite Database, I talked about the choice of the database and I focused on the simplest one which is Sqlite. But even if we choose the right one, we still need to work on the design and on its integration in the application side.
The old way of doing things was to use a database driver in your application, define the connexion and then execute the SQL command and queries from your code. When you get the data from the query execution, you need to parse them and then insert them to your objects. And this was a heavy task.
Later in time appeared Object-Relational Mapping. The ORM.
The ORM is the object-relational Mapping Tool. To make it simple, it's a tool that helps you to translate your database tables to a class Model in your application and vice versa. It also provides you with the methods to perform all the CRUD operation
The approach of creating the database then generating the class model is called Database First. The approach of creating the model in your application and then generating the database is called Code First.
In this article, we will focus on the database first part.
Entity Framework
Entity framework is the most commonly used ORM in .NET projects. It doesn't only allow you to access the database from your application. But also it also simplifies your life by giving you advanced possibilities like managing the concurrency and configuring your model. It has also the advantage of the use of the Language Integrated Query LINQ which is a powerful tool to perform your queries and filters.
Step 1: Nuget packages
Create a new console project in visual studio.
Open the commad line: Go to Tools => NuGet Package Manager => Package Manager Console and then you will get the command line displayed in your editor.
Install the required libraries by executing the following commands :
PM> Install-Package Microsoft.EntityFrameworkCore.Tools
PM> install-Package Microsoft.EntityFrameworkCore.Sqlite
Once you installed all packages you should have the following references in your solution
Step 2: Model generation
To generate the model, you need to use also the command line
You need to copy the needed database to your project folder and choose the right mode of copy to output directory:
Now you can generate your classes using the following command. You need to specify the output path, in our case it will be the folder Models
Scaffold-DbContext "Data Source=.\Database\products.db" Microsoft.EntityFrameworkCore.Sqlite -OutputDir Models
if everything is ok, you should get the classes generated under the folder Models and the following message in the package manager console should appear
The warning is there because the generator hardcoded the database's connection string in the code. You need to consider moving it in a protected way to a configuration file. But let's ignore it because it's not the main topic of this article.
Step 3: Check your generated classes
Now you should have the Context that contains the model creation and for each table you will get a class. In our case we have only one entity.
The Context class,
using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
namespace EntityFrameworkDbFirst.Models;
public partial class ProductsContext: DbContext {
public ProductsContext() {}
public ProductsContext(DbContextOptions < ProductsContext > options): base(options) {}
public virtual DbSet < Item > Items {
get;
set;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code.You can avoid scaffolding the connection string by using the Name = syntax to read it from configuration - see https: //go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
=> optionsBuilder.UseSqlite("Data Source=.\\Database\\products.db");
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.Entity < Item > (entity => {
entity.HasKey(e => e.Barcode);
entity.Property(e => e.Barcode).HasColumnType("VARCHAR");
entity.Property(e => e.Brand).HasColumnType("VARCHAR");
entity.Property(e => e.Name).HasColumnType("VARCHAR");
entity.Property(e => e.PruchasePrice).HasColumnType("DOUBLE");
entity.Property(e => e.SellingPrice).HasColumnType("DOUBLE");
});
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
The class Item,
using System;
using System.Collections.Generic;
namespace EntityFrameworkDbFirst.Models;
public partial class Item {
public string Barcode {
get;
set;
} = null!;
public string ? Name {
get;
set;
}
public string ? Brand {
get;
set;
}
public double ? PruchasePrice {
get;
set;
}
public double ? SellingPrice {
get;
set;
}
}
Step 4: Write your cruds
Once you have created your model, you can now start performing operations on it. The following code in program.cs allows to add / update or delete an item.
You can test it and then see the results in the database directly.
It's a simple call to every method of the crud. You can play with it by changing the data and adding more controls.
For example if you call the AddItem twice without modifying it you will get an exception related to the constraint of the primary key
// See https://aka.ms/new-console-template for more information
using EntityFrameworkDbFirst.Models;
using System.Runtime.CompilerServices;
using static System.Net.Mime.MediaTypeNames;
using System.Xml.Linq;
try {
Item item = new Item() {
Barcode = "B12345678",
Brand = "Milky Way",
Name = "Milk",
PruchasePrice = 20.5,
SellingPrice = 25.5
};
// Uncomment this line if you want to test the delete method
//DeleteItem("B12345678");
AddItem(item);
////Uncomment this line if you want to test the update method
//item.SellingPrice = 30;
//UpdateItem(item);
Console.WriteLine("Everything is Ok !");
} catch (Exception e) {
Console.WriteLine(e.Message);
if (e.InnerException != null) Console.WriteLine(e.InnerException.Message);
}
void AddItem(Item item) {
using(var db = new ProductsContext()) {
db.Items.Add(item);
db.SaveChanges();
}
}
//Delete the item
void DeleteItem(string Barcode) {
using(var db = new ProductsContext()) {
var item = db.Items.Find(Barcode);
if (item == null) return;
db.Items.Remove(item);
db.SaveChanges();
}
}
//Update the price of the item
void UpdateItem(Item item) {
using(var db = new ProductsContext()) {
db.Items.Update(item);
db.SaveChanges();
}
}
Step 4: And finally check your database
If you remember, we have set the property of the database in the solution explorer to "Copy if newer" so the file products.db is copied to the binary folder.
Open the database and browse your file then you will see the inserted item. Ensure that you left at least one product after testing the add and the delete
