In our second instalment of comprESSE I, ESSE I's column dedicated to coding (you can find the first "episode" here), we want to talk about things that are sometimes taken for granted but really shouldn't be: relational databases, and how to use them quickly and practically in the Microsoft ecosystem with the latest technology.
First of all, have you ever wondered why connecting to a relational database is so important in coding?
We have, and we think there are at least eight reasons:
- the persistence of data, which remains available even after the software has run or been closed. This persistence is essential for keeping important information over the long term;
- efficient access to data thanks to SQL (Structured Query Language), which lets you retrieve, update, insert or delete data quickly and precisely;
- maintaining the integrity of data thanks to "integrity rules" that guarantee its consistency and validity;
- sharing data between users and applications, useful in scenarios where several users need to work with the same data at the same time;
- a flexible data structure based on tables, which offers a versatile way to organise data and relate it, allowing more precise modelling that adapts to the application's needs;
- data security with access controls based on roles and permissions: this makes it possible to protect sensitive data and limit access to authorised users only;
- scalability to handle growing workloads thanks to technologies and approaches that let you distribute databases across server clusters or use the cloud to increase performance and capacity when needed;
- transactional consistency for complex operations, crucial in financial applications or in any context where operations must be atomic and consistent.
In short, although there are types of database such as "NoSQL" that may suit certain scenarios, relational databases remain a popular and fundamental choice in many software development contexts, because they provide a solid foundation for applications that require reliable, efficient and secure data management.
Contents:
- Microsoft Entity Framework Core
- Let's get to work: how do you use it?
- Our code
- Microsoft Entity Framework Core: what's next
- Conclusions
1) Microsoft Entity Framework Core
Now let's turn to the most modern solution Microsoft offers for managing them: EF Core, in full Microsoft Entity Framework Core, an open source object-relational mapping (ORM) framework that lets .NET developers access relational data using objects.
In this article we will try not only to describe it, but also to guide you through using it in practice.
To begin with, EF Core simplifies the management of relational data by providing an object-oriented data model that can be used to represent relational data, and it works with a variety of relational databases, including SQL Server, MySQL, PostgreSQL and Oracle.
EF Core is designed to be lightweight and scalable. Today it is an ideal choice for developing web, mobile and desktop applications.
What are the key features and benefits of EF Core?
There are at least five:
- automatic mapping: EF Core automatically maps .NET entities to database tables, removing the need for developers to write custom mapping code;
- querying: EF Core provides a set of query APIs that let developers query relational data in a natural and concise way;
- data manipulation: EF Core provides a set of APIs that let developers insert, update and delete relational data efficiently;
- relationships: EF Core lets developers define relationships between .NET entities, making it easier to manage complex relational data;
- migrations: EF Core provides a mechanism for applying changes to the data model, making it easier to keep applications up to date.
EF Core is a powerful tool that can simplify the development of applications that access relational data, and it can be used in many contexts. Typical cases include: a web application that stores user data in a relational database; a mobile application that stores product data; a desktop application that stores order data. In all cases, EF Core lets you not only save data but also manage it through SQL calls.
The benefits of using EF Core are many. The most common ones were listed above in relation to relational databases; here we would like to focus specifically on these four:
- simplicity: EF Core simplifies the management of relational data by providing an object-oriented data model that can be used to represent relational data;
- efficiency: EF Core is designed to be efficient, in terms of both performance and memory;
- portability: EF Core can be used with different types of relational database, making it an ideal choice for cross-platform application development;
- scalability: EF Core is designed to be scalable, which makes it an ideal choice for large applications.
2) Let's get to work: how do you use it?
Since with this column we want to get our hands a little dirty this time too, we thought we would build a simple project with Visual Studio Community that uses this framework to display the objects contained in a table of a SQL Server database.
To share the code we will use our GitHub repository: if you have an account, we recommend following it so you don't miss our news on this important social coding platform (a topic to which, incidentally, we have also devoted this article).
Obviously, to run the code we are assuming that you have installed SQL Server, configured it to accept incoming connections, created a user with the right privileges and loaded some data into the table (generated with the second block of code provided).
Remember that you can carry out all these tasks (as we did) with the excellent SQL Server Management Studio.
3) Our code
As well as below, the code is available here on ESSE I's GitHub profile.
C# source code:
using System;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
// Create a connection string with the parameters needed to connect to SQL Server
// You can change these parameters to match your server, database, user and password
string connectionString = "Server=127.0.0.1;Database=myDataBase;User Id=myUser;Password=myPassword;TrustServerCertificate=True";
// Create an instance of DbContextOptionsBuilder, used to configure the options for the database context
var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
// Use the UseSqlServer method to specify the database provider and the connection string
optionsBuilder.UseSqlServer(connectionString);
// Create an instance of MyDbContext, which represents the database context, using the configured options
using (var context = new MyDbContext(optionsBuilder.Options))
{
// Here you can run whatever operations you like on the database, such as queries or commands, using the entity classes defined in the context
// For example, you can use the LINQ ToList method to get all the rows of the Prodotti (Products) table
var prodotti = context.Prodotti.ToList();
// Print the number of items returned by the query
Console.WriteLine("The query returned {0} items.", prodotti.Count);
// Print the details of each item returned by the query
foreach (var prodotto in prodotti)
{
Console.WriteLine("Id: {0}, Name: {1}, Price: {2}", prodotto.Id, prodotto.Nome, prodotto.Prezzo);
}
Thread.Sleep(10000);
}
}
}
// Defines the MyDbContext class, which inherits from DbContext and represents the database context
public class MyDbContext : DbContext
{
// Defines the class constructor, which takes the context options as a parameter and passes them to the base constructor
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
// Defines the Prodotti property, which represents the Prodotti table in the database and uses the Prodotto class as its entity type
public DbSet<Prodotto> Prodotti { get; set; }
}
// Defines the Prodotto (Product) class, which represents an entity of the Prodotti table in the database
public class Prodotto
{
// Defines the Id property, which represents the primary key of the Prodotti table
public int Id { get; set; }
// Defines the Nome (Name) property, which represents a column of the Prodotti table
public string Nome { get; set; }
// Defines the Prezzo (Price) property, which represents a column of the Prodotti table
public decimal Prezzo { get; set; }
}
}
This C# code uses Entity Framework Core to connect to a SQL Server database, run a query to get data from the "Prodotti" (Products) table and print the results. We think it is a basic example of how to use an ORM (Object-Relational Mapping) to interact with a relational database using C#. The class and property names are kept in Italian so that they match the code in our GitHub repository.
SQL Server source code:
USE [myDataBase]
GO
/****** Object: Table [dbo].[Prodotti] Script Date: 26/09/2023 14:11:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Prodotti](
[Id] [int] NOT NULL,
[Nome] [text] NOT NULL,
[Prezzo] [decimal](18, 2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Overall, this script creates a simple table called "Prodotti" with three columns: "Id", "Nome" (name) and "Prezzo" (price). The table is designed to hold product information, and each column has constraints to ensure data integrity. Bear in mind that using the "text" data type for the "Nome" column could be problematic in some situations, since "text" data types are deprecated in SQL Server and may be replaced by other data types such as "nvarchar" or "varchar". Make sure this choice suits your specific needs.
4) Microsoft Entity Framework Core: what's next
Soon, as the .NET framework evolves (version 8 is expected – we talked about it in this social post), there will be some welcome improvements in performance.
Above all, the fact that it is available as FOSS (Free and Open Source Software) not only on Windows but also on Linux will keep it an attractive framework for developers who, while not wanting to leave the Microsoft ecosystem, are drawn to the savings offered by open source platforms.
5) Conclusions
We too are looking forward to the next "developments in development" (forgive the pun!) of software, relational databases included. While the aforementioned "NoSQL" (Not Only SQL) systems – a category of data management systems designed for storing and retrieving data that does not follow the tabular model of traditional relational databases – keep gaining ground, we believe that, for now, relational data remains the most cost-effective, fastest and most popular route.
What do you think?
Let us know by contacting us or leaving a comment on our social channels.
