DEV Community: Maurizio8788

Testing in .NET Core: Unit test, integration test

Maurizio8788 — Fri, 20 Oct 2023 15:14:27 +0000

Hello everyone, today I would like to talk to you about a crucial phase during the development of a software application, framework, or product: Testing.

Implementing automated tests is essential to ensure the quality of our projects and to prevent any changes to our code from introducing unpleasant regressions.

We have at our disposal many types of unit tests, integration tests, and end-to-end (E2E) tests. In this article, we will examine the first two on this list and also mention a series of tools that can greatly assist you in creating your tests.

Test Pyramid

The Test Pyramid is a concept that visualizes different types of automated tests by categorizing them based on their complexity and execution speed. The higher a test is on the pyramid, the slower it will be in overall execution.

As you can see, starting from the bottom, we have Unit Tests at the base of the pyramid. They ensure that an isolated unit of code functions as it should and are the fastest tests in the pyramid.

At the second level of the pyramid, we encounter Integration Tests. These tests are used to verify if components interact correctly with each other to satisfy business rules. The main objective of these tests is to ensure that the involved components communicate without errors when combined.

These tests are much slower compared to Unit Tests and are typically used and triggered by a Continuous Integration (CI) system to ensure that the code being integrated does not conflict with other parts of the code.

At the top of our pyramid, we have End-to-End (E2E) tests, which verify the correct functioning of an application from the initial point to the final one, simulating a complete user journey. It is, of course, the slowest type of test in our pyramid.

Nunit for Unit Tests

For this article, we will be using the NUnit library in the context of .NET Core. The reason I choose to use it is because I prefer it over XUnit (another testing library in the .NET context). NUnit has specific attributes to manage the test lifecycle, making our tests clear and clean (of course, like all things in software development, this is my opinion and perspective).

To provide an overview of the test lifecycle, we need to talk about the de facto standard for developing a test, which is the 3A paradigm (Arrange, Act, Assert). When developing a test, the test method follows these three common phases:

Arrange: This is the phase in which we create all the objects we need to test classes, methods, or services that are under test.
Act: It invokes the class, method, or service being tested.
Assert: In this phase, we verify that the test has responded correctly using the Assert class's methods in NUnit (or in any testing library you are using).

Remember that as a good testing practice, it's essential to test only one element at a time, be it a class, a method, or a service.

Let's go through a small example of a Unit Test:

using NUnit.Framework;

public class Calculator
{
    public int Sum(int a, int b)
    {
        return a + b;
    }
}

[TestFixture]
public class CalculatorTests
{
    [Test]
    public void TestSum()
    {
        // Arrange (Preparation)
        Calculator calculator = new Calculator();
        int number1 = 5;
        int number2 = 7;

        // Act (execution)
        int result = calcolatrice.Sum(number1, number2);

        // Assert (check)
        Assert.AreEqual(12, result);
    }
}

Now, starting from the base of this example, we can see that in the Arrange phase, we instantiated the Calculator class to subject the Sum() method to a test. However, the instance of that class will be available only in the TestSum() test method. But what if we had more methods to test?

If we had a Multiply method to test, we would have to repeat the entire arrangement process. This is where the NUnit lifecycle attributes I mentioned at the beginning of the session come into play. In this article, we'll cover four of them:

OneTimeSetup: It activates only once at the beginning of all the tests in a class.
SetUp: It activates just before the start of each test method.
OneTimeTearDown: It activates only once at the end of all the tests in a class.
TearDown: It activates immediately after the end of each test method.

Returning to the previous example:


//file Calculator.cs
public class Calculator
{
    public int Sum(int a, int b)
    {
        return a + b;
    }
        public int Multiply(int a, int b)
        {
          return a * b;
        }
}
// file CalculatorTests.cs
using NUnit.Framework;

[TestFixture]
public class CalculatorTests
{
        private Calcolatrice calculator;
        private int number1;
        private int number2;

        [OneTimeSetUp]
        public void OneTimeSetup()
        {
            // Inizializzazione comune a tutti i test della classe
            calculator = new Calculator();
            numero1 = 5;
            numero2 = 7;
        }

    [Test]
    public void TestSum()
    {        
        // Act (execution)
        int result = calculator.Sum(number1, number2);

        // Assert (check)
        Assert.AreEqual(12, result);
    }

        [Test]
        public void TestMultiply()
        {
            // Act (execution)
            int result = calculator.Multiply(number1, number1);

            // Assert (check)
            Assert.AreEqual(35, result);
        }
}

As you've seen in this example, we've centralized the creation of the instance of the Calculator class, making it available throughout the test class. The reason for preferring NUnit over XUnit (unless, of course, I'm required to use it for work-related reasons) is precisely the cleanliness that comes from these practices. If I needed to inject some dependencies into the test class constructor now, the Arrange phase would remain isolated, keeping the two phases distinct.

One very useful element I want to draw your attention to is the TestCase attribute, which allows us to define multiple test scenarios by retrieving values as if they were parameters passed to the test method:

// file Calculator.cs
public class Calculator
{
    public int Sum(int a, int b)
    {
        return a + b;
    }
        public int Multiply(int a, int b)
        {
          return a * b;
        }
}

// file CalculatorTests.cs
using NUnit.Framework;

[TestFixture]
public class CalculatorTests
{
        private Calcolatrice calculator;
        private int number1;
        private int number2;

        [OneTimeSetUp]
        public void OneTimeSetup()
        {
            // Inizializzazione comune a tutti i test della classe
            calculator = new Calculator();
            numero1 = 5;
            numero2 = 7;
        }

    [Test]
    public void TestSum()
    {        
        // Act (execution)
        int result = calculator.Sum(number1, number2);

        // Assert (check)
        Assert.AreEqual(12, result);
    }

        [Test]
        public void TestMultiply()
        {
            // Act (execution)
            int result = calculator.Multiply(number1, number1);

            // Assert (check)
            Assert.AreEqual(35, result);
        }

        [Test]
    [TestCase(2, 3, 6)]
    [TestCase(4, 6, 24)]
    [TestCase(0, 5, 0)]
    public void TestMultiplyWithMultipleCases(int a, int b, int expectedResult)
    {
        // Act (execution)
        int result = calculator.Multiply(a, b);

        // Assert (check)
        Assert.AreEqual(expectedResult, result);
    }
}

In this example, you can see that thanks to this, we have three test cases for the TestMultiplyWithMultipleCases method, where the first number in the TestCase attribute corresponds to the 'a' parameter, the second to 'b', and the third to 'expectedResult'.

In the last tests we executed, we always used scalar values as both parameters and results, and the methods under test had no internal dependencies. This made our testing much easier. But what if we had methods with dependencies?

Let's start by understanding what a dependency is. We refer to a dependency as a class, property, method, or object linked to another class, property, method, or object and essential for its proper functioning. For example, in the Calculator class:

public class CalculatorScientific
{
    private ICalculator calculatorBase; // Calculator Dependence

    public CalculatorScientific(ICalculator calculator)
    {
        calculatorBase = calculator; // We initialize the dependency
    }

    public double Pow(int baseNumber, int sponent)
    {
        // We use the Basic Calculator to perform multiplication
        if (sponent== 0)
        {
            return 1;
        }
        else if (sponent> 0)
        {
            double result = 1;
            for (int i = 0; i < sponent; i++)
            {
                result = calculatorBase.Multiply(result, baseNumber);
            }
            return result;
        }
        else
        {
            throw new ArgumentException("The exponent must be a nonnegative number.");
        }
    }

    // More advanced methods here
}

Now, as you can see, the ICalculator interface has become a dependency of the CalculatorScientific class. So how do we test this class? Can we simply insert a Calculator object during the creation of an instance of CalculatorScientific? ****

using NUnit.Framework;

[TestFixture]
public class CalculatorScientificTests
{
    private CalculatorScientific calculatorScientific;

    [OneTimeSetUp]
    public void OneTimeSetup()
    {
        Calculator calculatorBase = new Calculator();
        calculatorScientific = new CalculatorScientific(calculatorBase);
    }

    [Test]
    public void TestPow()
    {
        // Act
        double risultato = calcolatriceScientifica.Pow(2, 3);

        // Assert
        Assert.AreEqual(8, risultato);
    }
}

Now, this may not be the best approach because it creates a tight coupling between Calculator and CalculatorScientific, making it difficult to change the implementation of Calculator in the future or use a different class. This would require significant changes to CalculatorScientific.

Secondly, injecting real dependencies during a test might require access to external resources or behave unpredictably, like database connections and web services.

This is where Moq comes into play. It's a fantastic library that allows us to mock dependencies during the testing phase, isolating them and creating simulated implementations. Thanks to this library, we can have complete control over the configuration of dependency behaviors, making them return specific values or even generate exceptions.

In light of all this, let's rewrite our test:

using Moq;
using NUnit.Framework;

[TestFixture]
public class CalculatorScientificTests
{
    private CalculatorScientific calculatorScientific;
    private Mock<Calculator> mockCalculator;

    [OneTimeSetUp]
    public void OneTimeSetup()
    {
        mockCalculator = new Mock<Calculator>();
        calculatorScientific = new CalculatorScientific(mockCalcolatrice.Object);
    }

    [Test]
    public void TestPotenza()
    {
        // Arrange
        mockCalculator.Setup(x => x.Multiply(It.IsAny<int>(), It.IsAny<int>())).Returns((int a, int b) => a * b);

        // Act
        double result = calculatorScientific.Pow(2, 3);

        // Assert
        Assert.AreEqual(8, result);
    }

    [Test]
    public void TestPotenza_ConEsponenteNegativo_DeveLanciareEccezione()
    {
        // Arrange
        mockCalculator.Setup(x => x.Multiply(It.IsAny<int>(), It.IsAny<int>())).Returns((int a, int b) => a * b);

        // Act & Assert
        Assert.Throws<ArgumentException>(() => calculatorScientific.Potenza(2, -3));
    }
}

In this example, we used Moq to create a mock of the Calculator class and configured its behavior using the Setup method to enable testing of the Pow method. The second method, on the other hand, checks that an ArgumentException is thrown when a negative exponent is used.

NUnit for Integration Test

Now that we have extensively covered the part related to unit tests, we can focus on the second level of our pyramid, which is integration tests.

As mentioned earlier, this type of test is done to ensure the overall application's proper functioning, evaluating how different parts of the software interact and communicate in a more realistic environment, involving, for example, databases or external web services.

For this example, we will test a repository that queries data using Entity Framework Core. If you're not familiar with how to configure Entity Framework, here's a link to a guide I wrote: The comprehensive guide to Entity Framework Core.

Let's create our entity:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Now let's define a repository for data access:

public interface IProductRepository
{
    Product GetById(int productId);
    void Add(Product product);
    void SaveChanges();
}

public class ProductRepository : IProductRepository
{
    private ProductContext context;

    public ProductRepository(ProductContext context)
    {
        this.context = context;
    }

    public Product GetById(int productId)
    {
        return context.Products.Find(productId);
    }

    public void Add(Product product)
    {
        context.Products.Add(product);
    }

    public void SaveChanges()
    {
        context.SaveChanges();
    }
}

And let's create the test for our ProductIntegrationTests class:

using System.Data.Entity;
using System.Linq;
using Moq;
using NUnit.Framework;

public class ProductIntegrationTests
{
    [Test]
    public void AddProductAndRetrieveFromDatabase()
    {
        // Arrange
        var product = new Product(){
                    Name = "Test Product",
                    Price = 10.00
                };

        var dbContextOptions = new DbContextOptionsBuilder<ProductContext>()
            .UseInMemoryDatabase(databaseName: "InMemoryDatabase")
            .Options;

        using (var context = new ProductContext(dbContextOptions))
        {
            var productRepository = new ProductRepository(context);
            productRepository.Add(product);
            productRepository.SaveChanges();
        }

        using (var context = new ProductContext(dbContextOptions))
        {
            var productRepository = new ProductRepository(context);

            // Act
            var retrievedProduct = productRepository.GetById(product.ProductId);

            // Assert
            Assert.IsNotNull(retrievedProduct);
            Assert.AreEqual(product.ProductId, retrievedProduct.ProductId);
            Assert.AreEqual(product.Name, retrievedProduct.Name);
            Assert.AreEqual(product.Price, retrievedProduct.Price);
        }
    }
}

In this example, we created a Product object, configured an InMemoryDatabase to simulate a database using Entity Framework Core, added the product to the database, and then retrieved it using the GetById method in the Act phase. We then created standard assertions, as we would in a typical Unit Test.

One last small piece of advice I would like to give you when performing integration tests is not to manually create your entity classes. Our example referred to a very small class with only two fields, but if you have much more complex objects, the task can become tedious and, moreover, it wouldn't allow us to focus on our true goal, which is testing.

For this purpose, I use a library called AutoFixture, which allows us to create our test objects easily. It also lets us configure our objects using a builder, indicating which properties to create, which ones not to, the format, custom values, and more.

If we had used AutoFixture, its implementation would look like this:

using System.Data.Entity;
using System.Linq;
using AutoFixture;
using Moq;
using NUnit.Framework;

public class ProductIntegrationTests
{
    [Test]
    public void AddProductAndRetrieveFromDatabase()
    {
        // Arrange
        var fixture = new Fixture();
        var product = fixture.Create<Product>();

        // other instructions...
    }
}

That's it! Isn't it fantastic?

Now, we've reached the end of this long article. I hope you enjoyed it and found it useful. If you did, please give it a like and share it as much as you can!

As always, I'd love to hear your suggestions and feedback to improve and delve deeper into these topics.

Happy Coding!

Dependency Injection in .NET Core

Maurizio8788 — Wed, 18 Oct 2023 16:24:55 +0000

Dependency Injection (from now on DI) is a design pattern that allows you to write modular and testable code. In .NET Core, we are fortunate to have a native library that helps us implement this pattern.

In this article, we will cover the basics needed to implement it in .NET Core 6 using the basic extension methods associated with the IServiceCollection interface, namely, AddSingleton, AddScoped, and AddTransient.

AddSingleton

Everyone working in our industry knows, or at least has heard of, the Singleton design pattern, which is the anti-pattern that ensures a class has one and only one instance throughout the application lifecycle, and that's precisely what the AddSingleton method does.

The classic implementation of a Singleton is as follows:

public class MySingleton : IMySingleton {
    public static MySingleton instance;
    private MySingleton(){}

    public void CreateInstance(){
        if(instance == null) {
            instance = new MySingleton();
        }
        return instance;
    }
    // other methods...
}

Now, when we implement it using .NET Core's DI, we simply use these instructions, and we can then inject it anywhere as needed:

// MySingleton.cs
public class MySingleton : IMySingleton {
    // other methods...
}

// Program.cs
builder.Service.AddSingleton<IMySingleton, MySingleton>();

// MyEndpointController.cs
[ApiController]
[Route("api/[controller]")]
public class MyEndpointController : ControllerBase {
    private readonly IMySingleton _mySingleton;
    public MyEndpointController(IMySingleton mySingleton){
        _mySingleton = mySingleton;
    }

    // other endpoints...
}

The entire process of creating an instance of the class at the application's startup is delegated to the .NET Core DI engine. Please note that Singleton classes are created and exist until the application is shut down or restarted, so use them sparingly.

AddTransient

These are services that are created on-demand by the service container. They are typically used for stateless services, and their usage is quite similar to Singleton.

// MyTransientService.cs
public class MyTransientService : IMyTransientService {
    public string MethodOfTransientService() {
        return "Hello from Transient Service!";
    }
}

// Program.cs
builder.Service.AddTransient<IMyTransientService, MyTransientService>();

// MyApplicationService.cs
public class MyApplicationService : IMyApplicationService {
    private readonly IMyTransientService _myTransient;
    public MyApplicationService(IMyTransientService myTransient) {
        _myTransient = myTransient;
    }

    public void MyAppServiceMethod() {
        var result = _myTransient.MethodOfTransientService();
        Console.WriteLine(result);
    }
}

AddScoped

It creates services once per client request, regardless of whether they are actually used or not. These services are typically used for registering services that need to be readily accessible, such as the AddDbContext in Entity Framework Core, which is a scoped service.

// Product.cs
public class Product {
    public int Id { get; set; }
    public string Name { get; set; }
}

// ApplicationDbContext.cs
public class ApplicationDbContext : DbContext {
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) {
    }
}

// ProductService.cs
public class ProductService : IProductService {
    private readonly ApplicationDbContext dbContext;

    public ProductService(ApplicationDbContext context) {
        dbContext = context;
    }

    public void AddProduct(string productName) {
        var product = new Product { Name = productName };
        dbContext.Products.Add(product);
        dbContext.SaveChanges();
    }

    public List<Product> GetProducts() {
        return dbContext.Products.ToList();
    }
}

// Program.cs
builder.Service.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite("Data Source=products.db"));
builder.Service.AddScoped<IProductService, ProductService>();

// OrderService.cs 

public class OrderService : IOrderService {
    private readonly IProductService productService;

    public OrderService(IProductService productService) {
        this.productService = productService;
    }

    public void PlaceOrder(string productName) {
        productService.AddProduct(productName);
        Console.WriteLine($"Placed an order for {productName}");
    }
}

// MyApplicationService.cs
public class MyApplicationService : IMyApplicationService {
    private readonly IOrderService orderService;

    public MyApplicationService(IOrderService orderService) {
        this.orderService = orderService;
    }

    public void MyAppServiceMethod() {
        orderService.PlaceOrder("Widget");
        var products = orderService.GetProducts();
        Console.WriteLine($"Products in the order: {string.Join(", ", products)}");
    }
}

As you can see, the only difference in this case is the name of the operator used in the Program.cs file, but its usage is identical.

One last thing to keep in mind for this operator is not to inject a service registered with Scoped into a class registered with Singleton.

But why is that? If we think about it, it's logical. The Singleton service is created only once, while the Scoped service is disposed of at the end of the request. It's natural that if you inject a Scoped service into a Singleton, the Scoped service might remain active longer than it should and could be accessed by multiple threads, potentially creating thread-safety issues. This is a concern only when injecting Scoped services into classes registered as Transient and with other Scoped services.

I hope this brief introduction to Dependency Injection in .NET is helpful to all of you, and I look forward to receiving feedback on the article so that we can have a discussion.

Happy Coding!

The comprehensive guide to Entity Framework Core

Maurizio8788 — Mon, 16 Oct 2023 16:00:57 +0000

Hello everyone, in the previous article, we provided an overview of how to access data from our database through ADO.NET. Most of the time, we won't use ADO.NET in our applications; instead, we'll use an ORM (Object Relational Mapper), and in .NET Core, the most commonly used one is Entity Framework Core.

In this article, we won't be using the AdventureWorks2022 database we used previously. Instead, we will examine an example of a small TodoList. This choice will allow us to address topics like migrations and the Code First approach, which we will discuss in detail later.

Ecco il codice Markdown per creare la lista richiesta, con gli elementi principali in grassetto:

What is an ORM

An ORM (Object Relational Mapper) is a data access library that enables us to map each table in our databases to a corresponding class. It allows us to map each individual column and its corresponding data type, and it seeks to provide a more fluent way of handling data access through a global configuration. In the case of Entity Framework Core (EF Core), this configuration is represented by the DbContext, which we will delve into further later on.

Database First and Code First Approach

The EF Core team has provided us with two development approaches:

Database First: This approach starts with an existing database schema. It allows you to generate entity classes and a context based on the structure of the database. You work with the database schema as your starting point and generate code from it.
Code First: In contrast, the Code First approach begins with defining your entity classes and their relationships in code. From there, you can generate a database schema based on your code. This approach is particularly useful when you want to work primarily with code and let EF Core create and manage the database schema for you.

Database First

The Database First approach (or DB First) is used when we have an existing database schema (or decide to create the database schema first) and then create entity classes and the database context based on it manually or by using a process called scaffolding.

Scaffolding is a reverse engineering technique that allows us to create entity classes and a DbContext based on the schema of a database.

In EF Core, you can perform this operation by installing the NuGet package Microsoft.EntityFrameworkCore.Design in addition to the EF Core package of the database provider you are using.

Once these prerequisites are satisfied, you can run the Scaffold-DbContext command from the command line, providing it with the connection string of your database like this:


Scaffold-DbContext ‘Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TodoList’ Microsoft.EntityFrameworkCore.SqlServer

Alternatively, by including it in our appsettings.json file, we can retrieve it using this approach.


Scaffold-DbContext 'Name=ConnectionStrings:TodoContext' Microsoft.EntityFrameworkCore.SqlServer

Using these two simple commands, all the entities of our tables with their corresponding relationships will be created, and additionally, the DbContext class will be generated.

Code First

The Code First approach, on the other hand, allows us to create entity tables, their relationships, the mapping between various columns, and optionally, the generation of initial data using what are called "Migrations."

Migrations are a system for updating the database schema and keeping track of ongoing changes to it. In fact, after creating the first migration, you'll see a table named EFMigrationsHistory is created. This system allows us, in case of an error, to revert the changes.

To create a migration, you need to create the DbContext and the entities you wish to create (operations we will delve into later), install the NuGet package Microsoft.EntityFrameworkCore.Tools, and if you're using Visual Studio (recommended), you need to run the following command from the Package Manager Console:


Add-Migrations <nome della migration>

This command will create a 'Migrations' folder within the project, containing the following files:

XXXXXXXXXXXXXX_.cs, which contains the instructions for applying the migration.
ModelSnapshot.cs, which creates a "snapshot" of the current model. It is used to identify changes made during the implementation of the next migration.

If you wish to run the migration and update the schema of your database, you should execute the following command:


Update-Database

If you want to remove a migration, you can run the following command:


Remove-Migration

Apart from the instructions just explained, there are two tools that allow us to perform these operations much more easily, which I recommend you explore:

Entity Framework Core Command-Line Tools - dotnet ef

Ef Core Power Tools - github repository

The Context Class

The DbContext class is the primary class that allows us to query our data, manage the database connection, and ensure that the mapping is correct.

To create a DbContext class in EF Core, it is sufficient to make one of our classes inherit from the DbContext class, like this:


public class TodosContext : DbContext 
{

}

Of course, you can give your class any name you prefer. However, it's a common convention to name it after the database and add the "Context" suffix. This way, if you have multiple DbContext classes, you can easily distinguish them.

Another small step to follow is to use the default constructor of the DbContext class:


public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}
}

Now that we've created our DbContext class, we can finally create our entities. In this case, let's simulate a simple TodoList application by creating our two entities: Todo and TodoItem:


// file Todo.cs
public class Todo {
   public Todo() {
      TodoItems = new HashSet<TodoItem>();
   }
   public int Id { get;set;}
   public string Name { get;set; }
   public ICollection<TodoItem> TodoItems { get;set; }
}

// file TodoItem.cs
public class TodoItem {
   public int Id { get;set;}
   public string Description { get;set; }
   public bool IsCompleted { get;set; }
}

Once we have created our classes, we can add them to our DbContext as properties of type DbSet:


public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}

   public DbSet<Todo> Todos { get;set; }
   public DbSet<TodoItem> TodoItems { get;set; }
}

The DbSet class in EF Core represents a specific table or view in the database within the database context. It enables CRUD operations, LINQ queries, and provides a change tracking mechanism to simplify data management within the database entities.

Column Mapping

The DbContext has many methods that you can override, and one of the most important ones I'd like to mention is the OnModelCreating method. It allows you to perform fluent mapping of your entities using the modelBuilder parameter:


public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}

   ...entities classes created

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
    modelBuilder.Entity<Todo>( builder => {
        builder.HasKey(x => x.Id);

            builder.Property(t => t.Id)
                   .ValueGeneratedOnAdd();

        builder.Property(x => x.Name)
                   .HasColumnName("todo_name");

            builder.HasMany(x => x.TodoItems)
                   .WithOne(t => t.Todo)
                   .HasForeignKey(t => t.TodoId)
                   .OnDelete(DeleteBehavior.Cascade);
      });
   }
}

This is what we call Fluent Mapping of entity classes. Through the builder, we inform our DbContext about properties, the model, names (if different from the property name in the entity class), whether a property is required, and most importantly, we can map relationships between various entities.

A widely used mapping strategy in EF Core involves Data Annotations, which are attributes used to customize the mapping of entity classes to database tables. Data Annotations allow you to define column attributes and relationships in detail. An example illustrates this practice:


[Table("TodosItems")]
class TodoItems {
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int Id { get;set; }
   [MaxLength(150)]
   [Required]
   [Column("item_description")]
   public string Description { get;set; }
   [Column("item_si_completed")]
   public bool IsCompleted { get; set; }
   public int TodoId { get; set; }
   [ForeignKey(nameof(Todo))]
   public Todo? Todo { get; set; }
}

In the example above, we use Data Annotations to specify the table name ("TodoItems"), declare the primary key (Id), define the maximum length and requirement of the Description attribute, and specify the column name IsCompleted in the database. This provides detailed control over the mapping between the entity class and the database table.

Queries with Entity Framework

Now that everything is correctly configured, we can finally think about how to execute our first query with EF Core.

First of all, we need to register the DbContext class with the .NET Core dependency injection system and define the database provider we are using. In this case, we are using Sqlite. You can install the NuGet package Microsoft.EntityFrameworkCore.Sqlite for this purpose:


// file Program.cs

builder.Services.AddDbContext<TodosContext>(options => {
  options.UseSqlite(builder.Configuration.GetConnectionString("TodoContext"));
});

Let's add these instructions to the OnModelCreating method in our database context to have some sample data available immediately as an example:


modelBuilder.Entity<Todo>(builder =>
{
    // …other properties

    builder.HasData(
        new Todo
        {
            Id = 1,
            Name = "Project management",
        });
});


modelBuilder.Entity<TodoItem>(builder =>
{
    builder.HasData(
      new TodoItem { Id = 1, Description = "Create a Database Context", IsCompleted = false, TodoId = 1 },
      new TodoItem { Id = 2, Description = "Create Todo entity", IsCompleted = false, TodoId = 1 },
      new TodoItem { Id = 3, Description = "Create TodoItem entity", IsCompleted = false, TodoId = 1 }
    );
});

Retrieve Entities

Finally, let's focus on making our first query:


[Route("api/[controller]")]
[ApiController]
public class TodosController : ControllerBase
{
    private readonly TodosContext _context;

    public TodosController(TodosContext context)
    {
        _context = context;
    }

    [HttpGet]
    [Route(nameof(GetAllTodos))]
    public async Task<ActionResult<List<Todo>>> GetAllTodos()
    {
        try
        {
            List<Todo> todos = await 
               _context.Todos.AsNoTracking().ToListAsync();
            return Ok(todos);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

As you can see, we've injected the context class into the controller's constructor and then used it in our endpoint to query the database with LINQ and retrieve the data created earlier:

Operators like ToList, ToListAsync, ToArray, ToDictionary, and similar methods are extension methods in EF Core used to actually execute LINQ queries and materialize the resulting data from the database. These methods are essential because they allow deferring the execution of queries until the actual results are requested. Additionally, they convert the results into convenient data structures such as lists, dictionaries, or arrays that can be easily used in the application. This approach provides better control over when queries are executed and yields optimized results for further processing.

Another important EF Core extension method that requires explanation is AsNoTracking(). This method is used to inform the framework not to track entities retrieved from the database. This choice significantly improves performance when fetching data from a query.

When using AsNoTracking(), the database context will not maintain an internal state of the retrieved entities. This means there will be no tracking of changes made to these entities, making the data retrieval process faster and more efficient.

However, it's important to note that when using AsNoTracking(), you won't have the ability to make direct changes to the retrieved entities and save them to the database without additional steps. Therefore, it's crucial to use this method carefully, reserving it for situations where you only need to retrieve data and not modify entities.

If, on the other hand, we want to retrieve the Todos along with their respective TodoItems, we should:


[HttpGet]
[Route(nameof(GetAllTodoWithItems))]
public async Task<ActionResult<List<Todo>>> GetAllTodoWithItems()
{
    try
    {
        List<Todo> todos = await _context.Todos.Include(x => x.TodoItems )
                                               .AsNoTracking()
                                               .ToListAsync();
        return Ok(todos);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

That Include statement will perform a left join on the TodoItems table and retrieve, of course if they exist, all the TodoItems for each Todo item.

Add a New Entity

Inserting a new entity into our database is very simple:


[HttpPost]
[Route(nameof(CreateTodo))]
public async Task<ActionResult<Todo>> CreateTodo([FromBody] Todo todo)
{
   try
   {
      _context.Todos.Add(todo);
      await _context.SaveChangesAsync();

      return CreatedAtRoute(nameof(GetTodo), new { todoId = todo.Id }, todo);
    }
    catch (Exception ex)
    {
       throw ex;
    }
}

It's important to note that the simple Add operation won't immediately insert our new entity into the database. Instead, it adds the entity to the ChangeTracker of the database context, setting it to an Add state. The actual insertion into the database will occur only when we call the SaveChangesAsync() method. This operation is crucial for confirming and making the changes permanent in the database. So, remember that Add is just the first step, and SaveChangesAsync() is what performs the final database insertion action.

Update an Entity

To update an entity in our database, we can retrieve our TodoItem by its ID and then make changes to the object. It's important to ensure that you save the changes to the database using SaveChanges or SaveChangesAsync after making the modifications to the object:


[HttpPut("{todoItemId:int}")]
public async Task<ActionResult> UpdateTodoItem(
    int todoItemId, [FromBody] TodoItem todoItemUpdated) {
  ArgumentNullException.ThrowIfNull(todoItemId, nameof(todoItemId));
  try {
    TodoItem? todoItemFromDatabase =
        await _context.TodoItems.FirstOrDefaultAsync(x => x.Id == todoItemId);
    if (todoItemFromDatabase == null)
      return NotFound();

    todoItemFromDatabase.IsCompleted = todoItemUpdated.IsCompleted;
    todoItemFromDatabase.Description = todoItemUpdated.Description;

    _context.TodoItems.Update(todoItemFromDatabase);
    await _context.SaveChangesAsync();

    return NoContent();
  } catch (Exception ex) {
    throw ex;
  }
}

In this controller, we retrieve the existing TodoItem object from the database based on the provided ID. This is done using await _context.TodoItems.FirstOrDefaultAsync(x => x.Id == todoItemId);. Subsequently, the properties of the todoItemFromDatabase object are updated with the new data provided. Finally, the todoItemFromDatabase object is marked as modified using _context.TodoItems.Update(todoItemFromDatabase);, and the changes are applied to the database with await _context.SaveChangesAsync();.

Delete an Entity

To delete a Todo item from the database, we can use the .Remove() operator. EF Core will automatically handle the deletion of associated TodoItem properties belonging to this entity based on the relationship configuration. This simplifies the management of cascading deletions when configured correctly:


[HttpDelete("{todoId}")]
public async Task<ActionResult> Delete(int todoId) {
  try {
    Todo? todo = await _context.Todos.FirstOrDefaultAsync(y => y.Id == todoId);
    if (todo is null)
      return NotFound();

    _context.Todos.Remove(todo);
    await _context.SaveChangesAsync();

    return NoContent();
  } catch (Exception ex) {
    throw;
  }
}

If we now make a call to the endpoint GetAllTodoWithItems we will see that the entity and its respective items have been deleted from our database.

Once you have understood how to set up the basic configuration and performed the main CRUD operations, you can confidently say that you have covered the fundamental knowledge needed to start working with EF Core. You will find all the code used in this article and the previous one available in this GitHub repository:

Maurizio8788 / ArticleAdoDotNetAndEFCore

EF Core TodoList Application

This repository contains a simple TodoList application built using Entity Framework Core (EF Core). In this application, we explore the fundamental concepts of setting up and using EF Core to interact with a database.

Getting Started

Prerequisites

Before you start, make sure you have the following installed:

.NET Core SDK
Visual Studio or your preferred code editor
SQLite for database development

Installation

Clone this repository to your local machine.
Open the solution in Visual Studio or your code editor of choice.
Build the solution to restore dependencies.

Usage

Set up your database connection in the appsettings.json file.
Create the database schema using EF Core migrations:
```
dotnet ef database update
```
Run the application to start managing your TodoList.

Key Concepts Covered

Database First and Code First approaches
Fluent Mapping of Entity Classes
Performing CRUD operations with EF Core
Using Data Annotations for configuration
Handling Relationships in EF…

View on GitHub

Link to the repo

I hope you've enjoyed this introduction to data processing in .NET, and I hope it proves to be helpful in your studies. If you liked the article, please give it a thumbs up, and I hope you're willing to leave a comment to share knowledge and exchange opinions.

Happy Coding!

From ADO.NET to Entity Framework Core: ADO.NET beginner guide

Maurizio8788 — Sat, 14 Oct 2023 12:15:06 +0000

Hello everyone, today we will take a journey through data processing in the .NET world. In particular, we will focus on two topics: ADO.NET and Entity Framework Core.

This aims to be a good basic guide to using these libraries. Before delving into technical aspects, I'd like to share my opinion on the learning process and recommend what, of course, I believe to be the best path.

You should not dismiss the idea of using ADO.NET. I understand the allure of using an ORM (Object Relational Mapping), but having a deep understanding of ADO.NET provides a better foundation for transitioning to Entity Framework. In analogy, it would be like trying to learn CSS (Cascading Style Sheets) using Bootstrap.

Overview of Libraries

ADO.NET and Entity Framework Core (from now on, EF Core) are two libraries available in .NET with the purpose of reading and processing data in our databases. Despite their common purpose, they achieve it in different ways.

The first is a native library in .NET, available in both .NET Framework and .NET Core, and it has a manual system for opening and closing the database connection. It receives queries in string format.

The second is an Object-Relational Mapper (ORM) that interfaces with our database(s) through a Database Context and precise mapping of entities and relationships using classes. Various database operations can be performed using LINQ extensions or, alternatively, by writing queries in string format.

Let's Begin with ADO.NET

As we mentioned, ADO.NET is a data access library that natively supports SQL Server but also offers extensions for other database providers. It is used to modify, create, and read data in our databases, and the results of data retrieval operations are then placed in an object called a DataSet, which serves as an in-memory cache of the freshly retrieved data.

Configuring ADO.NET can be a bit verbose, but what it certainly doesn't lack are:

High Performance
Complete control and management of SQL operations

Now, let's set up a classic Console application in .NET and use the AdventureWorks2022 database as our data source, which we configured in a previous article on stored procedures that you can find here.

Then, let's configure a very simple appsettings.json file. If you're not sure how to do it, no problem; I've written a guide for this as well Options and Configuration in .NET6. Finally, install the ADO.NET provider for MSSQL, Microsoft.Data.SqlClient.

Now, let's create a basic connection to our database:


using SqlConnection sqlConnection = new(connectionString);

For educational purposes, we won't centralize the management of various connection opening or closing operations. However, as a best practice, I recommend using this kind of approach. We use a 'using' statement to free up memory at the end of operations.

Now, in order to execute our queries on the database, it is necessary to create a SqlCommand. This can be done in two ways:


string query = "SELECT TOP 100 Name, ProductNumber FROM [Production].[Product]";

SqlCommand commandFluent = sqlConnection.CreateCommand();
commandFluent.CommandText = query;

/* **************************** otherwise *********************************/

SqlCommand command = new SqlCommand(query, sqlConnection);

Now, these two statements can be used to execute any SQL statement, from stored procedures to regular queries, whether they are simple or complex.

Next, let's open the actual connection to the database. To do this, we'll use this paradigm. We'll use a try/catch/finally block, where the first statement in the try block will open the database connection with:


try {

    sqlConnection.Open();

And as the last statement in the finally block:


finally {
    sqlConnection.Close();
}

This will ensure that we close the database connection even in the event of an error.

Note: Always close the database connections, or you risk blocking any subsequent connection attempts!

Once all of this is done, we are ready to execute our queries.

Exploring Execute Commands

To execute our query on the database under examination, we can use a series of commands that begin with the Execute construct. Primarily, we have the following commands:

ExecuteNonQuery: Returns the number of rows affected by our query, mainly used for insert, update, and delete operations.
ExecuteReader: Allows you to retrieve and read the query's results using a Data Reader.
ExecuteScalar: Returns the first column of the first row of the results.

Each of the commands just mentioned has an asynchronous counterpart, and of course, whether you use one or the other command will vary depending on your needs. In this specific example, you will need to use the ExecuteReader command.

Let's see a small example of what was just mentioned:


using SqlDataReader reader = command.ExecuteReader();

Since the SqlDataReader class implements the IDisposable interface, it is possible and, I would say, necessary to use the 'using' statement here as well in order to release resources once the operation is executed.

Now, to read the result of our reader, we need to use the Read() method included in the SqlDataReader class, and with a simple 'while' loop, we can perform the reading (or mapping into an entity class) like this:


while (reader.Read())
{
   Console.WriteLine($"{reader["Name"]} - {reader["ProductNumber"]}");
}

This will allow us to display the properties we've retrieved from our database on the screen.

Working with Parameters

Now, in the previous case, we saw how to retrieve data from our tables with a simple SELECT query on the database. But what if we need to insert, update, or remove data from our database? I would say the process is almost identical but with one small but crucial difference: SqlParameter.

The SqlParameter class allows us to write and execute all queries that require one or more parameters to be passed securely (preventing SQL injection attacks) and, in my opinion, it makes the code much more readable.

Now, let's set up the basic code:


query = $@"

INSERT INTO [Production].[Product] ([Name],
      [ProductNumber],
      [MakeFlag],
      [FinishedGoodsFlag],
      [Color],
      [SafetyStockLevel],
      [ReorderPoint],
      [StandardCost],
      [ListPrice],
      [Size],
      [SizeUnitMeasureCode],
      [WeightUnitMeasureCode],
      [Weight],
      [DaysToManufacture],
      [ProductLine],
      [Class],
      [Style],
      [ProductSubcategoryID],
      [ProductModelID],
      [SellStartDate],
      [SellEndDate],
      [DiscontinuedDate],
      [rowguid],
      [ModifiedDate]
)
VALUES (@Name, @ProductNumber, @MakeFlag, @FinishedGoodsFlag, @Color, @SafetyStockLevel, @ReorderPoint, @StandardCost, @ListPrice, @Size, @SizeUnitMeasureCode, @WeightUnitMeasureCode, @Weight, @DaysToManufacture, @ProductLine, @Class, @Style, @ProductSubcategoryID, @ProductModelID, @SellStartDate, @SellEndDate, @DiscontinuedDate, @rowguid, @ModifiedDate);
";

SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);


sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50)).Value = "Nuovo Prodotto";
sqlCommand.Parameters.Add(new SqlParameter("@ProductNumber", SqlDbType.NVarChar, 25)).Value = "N12345";
sqlCommand.Parameters.Add(new SqlParameter("@MakeFlag", SqlDbType.Bit)).Value = true;
sqlCommand.Parameters.Add(new SqlParameter("@FinishedGoodsFlag", SqlDbType.Bit)).Value = true;
... Others query parameter

try
{
    sqlConnection.Open();
    sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
    throw;
}
finally
{
    sqlConnection.Close();
}

As you can see, we have used the SqlParameter class to parameterize our query, and we have also added other values in addition to the parameter name, such as the type, size, and value.

Mapping Techniques with ADO.NET

Now that we have a solid foundation in ADO.NET, we can address the last remaining point regarding entity mapping. If you recall, in the first example, we read properties from a data reader using indexing, like 'reader["property name"]'. Is there a different way to achieve this? Let's explore it together!

We can do this using implicit or explicit operators:


public class Employee
{
    public int BusinessEntityID { get; set; }
    public string Title { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string Suffix { get; set; }
    public string JobTitle { get; set; }
    public string PhoneNumber { get; set; }
    public string PhoneNumberType { get; set; }
    public string EmailAddress { get; set; }
    public int EmailPromotion { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string StateProvinceName { get; set; }
    public string PostalCode { get; set; }
    public string CountryRegionName { get; set; }
    public string AdditionalContactInfo { get; set; }

    public static implicit operator Employee(SqlDataReader reader)
    {
        Employee employee = new Employee();
        if(reader.Read())
        {
            employee.BusinessEntityID = (int)reader["BusinessEntityID"];
            employee.Title = reader["Title"] is DBNull ? string.Empty : (string)reader["Title"];
            employee.FirstName = reader["FirstName"] is DBNull ? string.Empty : (string)reader["FirstName"];
            employee.MiddleName = reader["MiddleName"] is DBNull ? string.Empty : (string)reader["MiddleName"];
            employee.LastName = reader["LastName"] is DBNull ? string.Empty : (string)reader["LastName"];
            employee.Suffix = reader["Suffix"] is DBNull ? string.Empty : (string)reader["Suffix"];
            employee.JobTitle = reader["JobTitle"] is DBNull ? string.Empty : (string)reader["JobTitle"];
            employee.PhoneNumber = reader["PhoneNumber"] is DBNull ? string.Empty : (string)reader["PhoneNumber"];
            employee.PhoneNumberType = reader["PhoneNumberType"] is DBNull ? string.Empty : (string)reader["PhoneNumberType"];
            employee.EmailAddress = reader["EmailAddress"] is DBNull ? string.Empty : (string)reader["EmailAddress"];
            employee.EmailPromotion = (int)reader["EmailPromotion"];
            employee.AddressLine1 = reader["AddressLine1"] is DBNull ? string.Empty : (string)reader["AddressLine1"];
            employee.AddressLine2 = reader["AddressLine2"] is DBNull ? string.Empty : (string)reader["AddressLine2"];
            employee.City = reader["City"] is DBNull ? string.Empty : (string)reader["City"];
            employee.StateProvinceName = reader["StateProvinceName"] is DBNull ? string.Empty : (string)reader["StateProvinceName"];
            employee.PostalCode = reader["PostalCode"] is DBNull ? string.Empty : (string)reader["PostalCode"];
            employee.CountryRegionName = reader["CountryRegionName"] is DBNull ? string.Empty : (string)reader["CountryRegionName"];
            employee.AdditionalContactInfo = reader["AdditionalContactInfo"] is DBNull ? string.Empty : (string)reader["AdditionalContactInfo"];
        }
        return employee;
    }

    public override string ToString()
    {
        return $"{Title} - {FirstName} - {LastName} - {Suffix} - {JobTitle} - {PhoneNumber} - {PhoneNumberType} - {EmailAddress} - {EmailPromotion} - {AddressLine1} - {AddressLine2} - {City} - {StateProvinceName} - {PostalCode} - {CountryRegionName} - {AdditionalContactInfo}.";
    }
}

Or, alternatively, using explicit operators:


public class Employee
{
  // all properties
public static explicit operator Employee(SqlDataReader reader) {       Employee employee = new Employee(); 

  if (reader.Read()) { employee.BusinessEntityID = (int)reader["BusinessEntityID"]; 
employee.Title = reader["Title"] is DBNull ? string.Empty : (string)reader["Title"]; 
employee.FirstName = reader["FirstName"] is DBNull ? string.Empty : (string)reader["FirstName"]; 
employee.MiddleName = reader["MiddleName"] is DBNull ? string.Empty : (string)reader["MiddleName"]; 
employee.LastName = reader["LastName"] is DBNull ? string.Empty : (string)reader["LastName"]; 
employee.Suffix = reader["Suffix"] is DBNull ? string.Empty : (string)reader["Suffix"]; 
employee.JobTitle = reader["JobTitle"] is DBNull ? string.Empty : (string)reader["JobTitle"]; 
employee.PhoneNumber = reader["PhoneNumber"] is DBNull ? string.Empty : (string)reader["PhoneNumber"]; 
employee.PhoneNumberType = reader["PhoneNumberType"] is DBNull ? string.Empty : (string)reader["PhoneNumberType"]; 
employee.EmailAddress = reader["EmailAddress"] is DBNull ? string.Empty : (string)reader["EmailAddress"]; 
employee.EmailPromotion = (int)reader["EmailPromotion"]; employee.AddressLine1 = reader["AddressLine1"] is DBNull ? string.Empty : (string)reader["AddressLine1"]; 
employee.AddressLine2 = reader["AddressLine2"] is DBNull ? string.Empty : (string)reader["AddressLine2"]; 
employee.City = reader["City"] is DBNull ? string.Empty : (string)reader["City"]; 
employee.StateProvinceName = reader["StateProvinceName"] is DBNull ? string.Empty : (string)reader["StateProvinceName"]; 
employee.PostalCode = reader["PostalCode"] is DBNull ? string.Empty : (string)reader["PostalCode"]; 
employee.CountryRegionName = reader["CountryRegionName"] is DBNull ? string.Empty : (string)reader["CountryRegionName"]; employee.AdditionalContactInfo = reader["AdditionalContactInfo"] is DBNull ? string.Empty : (string)reader["AdditionalContactInfo"]; } return employee; }

}

And then use them, respectively, like this:


Employee employee = reader;

// Or like this...

Employee employee = (Employee)reader;

Alternatively, we could create a generic extension method that allows us to convert the data reader into an entity object. Let's create the SqlDataReaderExtensions class:


public static class SqlDataReaderExtension {

}

And insert these two new methods:


public static T ConvertToEntities<T>(this SqlDataReader reader) where T : class, new()
{
   ArgumentNullException.ThrowIfNull(nameof(reader));
   Type type = typeof(T);
   BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Public;
   PropertyInfo[] propertyInfos = type.GetProperties(bindingFlags);
   T entity = new T();

   while(reader.Read())
   {
       foreach(PropertyInfo propertyInfo in propertyInfos)
       {
          if (reader.HasColumn(propertyInfo.Name))
          {
                    object value = reader[propertyInfo.Name];
             if (value != DBNull.Value)
             {
                propertyInfo.SetValue(entity, value);
             }
          }
       }
   }

   return entity;
}

public static bool HasColumn(this SqlDataReader reader, string name)
{
   for (int index = 0; index < reader.FieldCount; index++)
   {
       if (reader.GetName(index).Equals(name, StringComparison.InvariantCultureIgnoreCase))
       {
          return true;
       }
    }
    return false;
}

What we are doing here is quite simple. Firstly, we check that the reader is not null. Then, we use reflection to retrieve the generic type used and obtain its public properties. We instantiate the generic entity with a constraint that it must have a constructor and then loop through the reader and properties. We check if that property is present in our reader and, if it is, we verify that its value is not of type DBNull.

These are, of course, basic examples. In our work, we have many ways to achieve the same thing, and it's up to us to understand which one best suits our project's needs.

With this, we conclude our first journey into data processing in .NET. In the next article, I will show you how Entity Framework Core eliminates all this "complexity," how we can map our elements to the database and obtain our entities, removing all the mapping code we saw at the end of this article. I will also explain how to use LINQ to execute our database queries and how it manages database connections.

If you've made it this far in the article, I sincerely thank you and hope you can give it a like and share it. Of course, I also hope you can share your experiences with me and contribute interesting points of discussion.

Happy Coding!!

Create a stored procedure with Sql Server

Maurizio8788 — Sat, 07 Oct 2023 20:44:51 +0000

Hello everyone, today I would like to delve into a feature present in all databases and used by anyone who needs to approach server-side development: Stored Procedures.

In particular, this article will be focused on SQL Server databases, so while many concepts can be applied to other Relational Database Management Systems (RDBMS), many others will be specific to the T-SQL language.

What are Stored Procedures

Stored Procedures (hereafter referred to as SP) are more or less complex instructions, depending on the problem they need to solve. They are saved in the cache of our databases, in this case, SQL Server, and can be reused as needed.

They are used for various purposes, but they typically come into play when we need certain operations to be executed quickly. Usually, the operations involved are either Data Manipulation Language (DML) operations or heavy data retrieval operations.

But what are the advantages of using them?

It avoids rewriting complex queries, allowing the developer to reuse the code at any time.
Greater query efficiency thanks to the fact that SQL Server can create increasingly efficient query execution plans.
They reduce the amount of network traffic, for example:
- Imagine having to create a new item in our table, wait for confirmation of the new item's creation, and then retrieve the newly created item. Instead of performing these operations sequentially, we could simply put the code into a SP and execute both the INSERT and SELECT operations all at once.

Are there any disadvantages? In this case, rather than disadvantages, I would call them best practices. In other words, you can do many things, but some things are better to avoid, for example:

Returning an excessively large result set, i.e., too much information from a single Stored Procedure.
Excessive use of cursors should be avoided, and it's preferable to use the Merge statement.

Setting up the Environment

First of all, make sure to download SQL Server 2022 (from now on referred to as MSSQL) and SQL Server Management Studio (from now on referred to as SSMS). I'm providing the links for you to download both:

SQL Server:

Download SQL Server HERE

Make sure to download the Developer edition and follow the installation wizard. Once you have completed the process, you can download SSMS:

Download Sql Server Management Studio

You'll also need to wait for the installation to be completed. In the meantime, you can start downloading the database that we will use for the experiments in this article from the AdventureWorks repository provided by Microsoft:

Download .bak file from here

Once you have downloaded the file and completed the installation, let's proceed to restore the database. Open SSMS and log in using Windows authentication:

Next, right-click on the 'Database' folder and then select 'Restore Database...':

In the 'General' page, you can choose the source as 'Device' and by clicking the 'Add' button, locate the .bak file that you downloaded earlier. Complete the operation by selecting the 'OK' buttons in succession, which will perform the database restore. If everything goes well, you should have this situation:

Starting with the Basics

The main commands for creating stored procedures are three:

CREATE PROCEDURE to create a new procedure.
ALTER PROCEDURE* to modify an existing procedure.
DROP PROCEDURE to delete a procedure.

Essentially, the basic schema for creating a new stored procedure is as follows:


CREATE PROCEDURE <name of procedure>
- - Optional parameters.
AS
BEGIN

- -  Body of the stored procedure.

END;

As we can see, it's possible to assign parameters to our SP, just as we would do in any function or method of a class in any programming language. The words AS, BEGIN, and END are mandatory.

The names of SPs must be unique. In addition, I like to use a specific naming convention, which involves prefixing the name with "sp_" (of course, this should follow the project and team guidelines you are working with). Another thing that can help you in naming your SPs is assigning them a schema different from the typical "dbo." For those who may not know, a schema is similar to a namespace. This way, you can create SPs with the same name.

Modifying an SP is similar to creating one, with the only difference being that instead of CREATE, you will use the keyword ALTER:


ALTER PROCEDURE <name of procedure>
- - Optional parameters.
AS
BEGIN

- - Body of the stored procedure.

END;

For its deletion, instead:


DROP PROCEDURE <name of procedure>

Now, if we open our database and then navigate to the folders Programmability > StoredProcedures, we will already find some stored procedures there. Let's ignore them for now and create our custom ones (you can come back to them later). To get started, at the top, you'll find the 'New Query' button:

Click on the button to create a new page and insert the following query:


USE [AdventureWorks2022];
GO

SELECT * FROM [Person].[Person]

Using the 'Execute' button at the top or pressing the F5 key, we will execute the query, which will return a result set:

Now, if we want to create our first stored procedure based on this simple query, all we have to do is write these instructions:


USE [AdventureWorks2022];
GO

CREATE PROCEDURE sp_SelectPersons
AS
BEGIN
 SELECT * FROM [Person].[Person]
END;

and you will receive a message confirming the successful creation of our stored procedure. You will find it in the path Programmability > StoredProcedures. If you were to try running the same statement again, you would, of course, encounter an error because our SP already exists. Now, if you want to use it, it's quite simple; just execute the statement:


EXECUTE sp_SelectPersons;

and you will obtain the same result screen as before (link to the previous image).

Note that it is rarely necessary (but not impossible) to return all the columns of a table. Make it a good practice to select only the columns you need.

Based on this suggestion, let's try modifying our stored procedure in the following way:


USE [AdventureWorks2022];
GO

ALTER PROCEDURE sp_SelectPersons
AS
BEGIN
 SELECT 
    [FirstName],
    [MiddleName],
    [LastName]
 FROM [Person].[Person]
END;

se lanciamo nuovamente la query recupereremo solo quei tre campi.

One last basic piece of advice: if SSMS marks your SPs with a red error line, don't worry. Go to the top and click on the path:

Edit > Intellisense > Refresh Local Cache

The issue will be automatically resolved.

Stored Procedure Parameters

Now that we've covered the basics, let's take the next step, which is passing parameters to our SPs. There are three types of parameters:

INPUT: These determine the values to pass to the SP.
OUTPUT: Output parameters are useful when you want to obtain additional data or information from the database, in addition to the result set. They can be used to return calculated values, automatically generated identifiers, or other information.
CUSTOM TYPE: Creating a custom data type.

If you recall the previous explanation, after naming them, you can also pass parameters in the following way:


CREATE PROCEDURE sp_InsertPerson
(
    @PersonType NCHAR(2), 
    @Title NVARCHAR(8), 
    @FirstName NVARCHAR(50),
    @MiddleName NVARCHAR(50),
    @LastName NVARCHAR(10), 
    @Suffix NVARCHAR(10), 
    @EmailPromotion INT, 
    @rowguid UNIQUEIDENTIFIER,
    @ModifiedDate DATETIME
)
AS
BEGIN

DECLARE @BusinnessEntityId INT;

INSERT INTO [Person].[BusinessEntity] ([rowguid], [ModifiedDate])
VALUES (@rowguid, @ModifiedDate)

SELECT @BusinnessEntityId = SCOPE_IDENTITY();

INSERT INTO [Person].[Person] ([BusinessEntityID], [PersonType], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [rowguid], [ModifiedDate])
VALUES (@BusinnessEntityId, @PersonType, @Title, @FirstName, @MiddleName, @LastName, @Suffix, @EmailPromotion, @rowguid, @ModifiedDate)

END;

The stored procedure executes two INSERT statements, one in the [Person].[BusinessEntity] table and one in the [Person].[Person] table. As you can see, we have created a variable (thank you, T-SQL) that, thanks to the SCOPE_IDENTITY() function, retrieves the Identity value from the first INSERT operation dynamically. This allows us to use that value to create a new person entity.

As you can see, we have enclosed all the various properties in parentheses and assigned them the correct data type to send. But now, how can we use this SP and pass the parameters it needs to execute? It's quite simple; the previous statement will be more than sufficient:


EXECUTE [dbo].[sp_InsertPerson] 'EM', NULL, 'Bruce', NULL, 'WAYNE', NULL, 1, '8A2E08B6-341E-488F-8BFF-C32477F4E626', '2023-10-07 14:51:10.540';

Now, let's execute a SELECT statement on the [Person].[BusinessEntity] and [Person].[Person] tables, and we will see that the fields have been correctly inserted:

If, on the other hand, you want to use OUTPUT parameters to retrieve, for example, the person with the longest name and the total count of people in our [Person].[Person] table, you would need to write an SP like this:


CREATE PROCEDURE sp_GetCustomerInformation
   @NumeroTotaleClienti INT OUTPUT,
   @ClienteNomePiuLungo NVARCHAR(50) OUTPUT
AS
BEGIN
   -- We calculate the total number of customers.
   SELECT @NumeroTotaleClienti = COUNT(*) FROM [Person].[Person];

   -- We find the customer with the longest name.
   SELECT TOP 1 @ClienteNomePiuLungo = FirstName
   FROM [Person].[Person]
   ORDER BY LEN(FirstName) DESC;
END

And then, you can execute it in the following way:

DECLARE @TotalClients INT;
DECLARE @LongestClientName NVARCHAR(50);

EXEC sp_GetCustomerInformation @TotalClients OUTPUT, @LongestClientName OUTPUT;

PRINT @TotalClients;
PRINT @LongestClientName;

The last case concerns the possibility of creating Custom data types that help us have a cleaner parameter management. As developers, what would we do in a common object-oriented language when we have too many parameters in our method? If the answer is that you would create an object to use as a parameter for the method, then not only have you answered correctly, but you have also grasped the concept.

The creation of a new type in MSSQL is possible thanks to the CREATE TYPE <name of type> statement, so now, let's create a new SP that will insert a new product into our table. First, let's create our custom data type 'Product' which will handle passing the parameters to our SP:


CREATE TYPE Product
AS TABLE 
(
    [Name] NVARCHAR(50) NOT NULL,
    [ProductNumber] NVARCHAR(25) NOT NULL,
    [MakeFlag] BIT NOT NULL,
    [FinishedGoodsFlag] BIT NOT NULL,
    [Color] NVARCHAR(15) NULL,
    [SafetyStockLevel] SMALLINT NOT NULL, 
    [ReorderPoint] SMALLINT NOT NULL,
    [StandardCost] MONEY NOT NULL,
    [ListPrice] MONEY NOT NULL,
    [Size] NVARCHAR(5) NULL, 
    [SizeUnitMeasureCode] NCHAR(3) NULL,
    [WeightUnitMeasureCode] NCHAR(3) NULL,
    [Weight] DECIMAL(8,2) NULL,
    [DaysToManufacture] INT NOT NULL,
    [ProductLine] NCHAR(2) NULL,
    [Class] NCHAR(2) NULL,
    [Style] NCHAR(2) NULL, 
    [ProductSubcategoryID] INT NULL, 
    [ProductModelID] INT NULL, 
    [SellStartDate] DATETIME NOT NULL, 
    [SellEndDate] DATETIME NULL, 
    [DiscontinuedDate] DATETIME NULL,
    [rowguid] UNIQUEIDENTIFIER NOT NULL, 
    [ModifiedDate] DATETIME NOT NULL
);

Let's EXECUTE it from our SSMS, and now our new data type will be available and accessible in the path:

Programmability > Types > User-Defined Table Types

Now, let's create our SP:


CREATE PROCEDURE sp_CreateNewProduct
(
 @product dbo.Product READONLY
)
AS
BEGIN

INSERT INTO [Production].[Product] 
([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], 
[ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], 
[SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate])
SELECT
      [Name],
      [ProductNumber],
      [MakeFlag],
      [FinishedGoodsFlag],
      [Color],
      [SafetyStockLevel],
      [ReorderPoint],
      [StandardCost],
      [ListPrice],
      [Size],
      [SizeUnitMeasureCode],
      [WeightUnitMeasureCode],
      [Weight],
      [DaysToManufacture],
      [ProductLine],
      [Class],
      [Style],
      [ProductSubcategoryID],
      [ProductModelID],
      [SellStartDate],
      [SellEndDate],
      [DiscontinuedDate],
      [rowguid],
      [ModifiedDate]
   FROM @product;
END;

As you can see, passing parameters is much cleaner, and we can execute the INSERT using the SELECT statement rather than passing individual data to the statement as we would have done with input parameters. Now, let's try our new SP:


DECLARE @tempProduct dbo.Product;

INSERT INTO @tempProduct
VALUES 
('Prodotto3', '21543', 1, 1, 'Rosso', 10, 5, 10.00, 19.99, 'M', 'CM', 'KG', 2.5, 5, 'S', 'M', 'U', 1, 1, '2023-01-01', NULL, NULL, NEWID(), GETDATE()),
('Prodotto4', '87609', 0, 1, 'Blu', 12, 6, 8.50, 15.99, 'L', 'IN', 'LB', 3.0, 7, 'S', 'M', 'W', 2, 2, '2023-02-01', NULL, NULL, NEWID(), GETDATE());

EXECUTE sp_CreateNewProduct @product = @tempProduct;

Best Practices

Now, if you've been running our SPs, you might have noticed that in the terminal below the query execution window, we had messages notifying us of successful operations.

In a development environment, this isn't an issue, but in a production environment, there's no need for these messages. Is there a way to eliminate them? Absolutely! You can use MSSQL's SET Options, specifically the SET NOCOUNT option.

Let's modify our SP in the following way:


ALTER PROCEDURE sp_CreateNewProduct
(
  @product dbo.Product READONLY
)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO [Production].[Product] 
([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], 
[SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], 
[SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight],  [DaysToManufacture], 
[ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], 
[SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate])
SELECT
      [Name],
      [ProductNumber],
      [MakeFlag],
      [FinishedGoodsFlag],
      [Color],
      [SafetyStockLevel],
      [ReorderPoint],
      [StandardCost],
      [ListPrice],
      [Size],
      [SizeUnitMeasureCode],
      [WeightUnitMeasureCode],
      [Weight],
      [DaysToManufacture],
      [ProductLine],
      [Class],
      [Style],
      [ProductSubcategoryID],
      [ProductModelID],
      [SellStartDate],
      [SellEndDate],
      [DiscontinuedDate],
      [rowguid],
      [ModifiedDate]
FROM @product;

SET NOCOUNT OFF;

END;

If you now run our SP again, perhaps changing the product name, you will see that the messages are no longer displayed. You can find additional options here.

Another important best practice to use in our SPs is to use TRY/CATCH to handle errors. Let's modify our SP once again in the following way:


ALTER PROCEDURE sp_CreateNewProduct
(
   @product dbo.Product READONLY
)
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRY

   INSERT INTO [Production].[Product] 
   ([Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], 
   [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], 
   [ListPrice], [Size], [SizeUnitMeasureCode], 
   [WeightUnitMeasureCode], [Weight], [DaysToManufacture], 
   [ProductLine], [Class], [Style], [ProductSubcategoryID], 
   [ProductModelID], [SellStartDate], [SellEndDate], 
   [DiscontinuedDate], [rowguid], [ModifiedDate])
   SELECT
    [Name],
    [ProductNumber],
    [MakeFlag],
    [FinishedGoodsFlag],
    [Color],
    [SafetyStockLevel],
        [ReorderPoint],
    [StandardCost],
    [ListPrice],
    [Size],
    [SizeUnitMeasureCode],
    [WeightUnitMeasureCode],
    [Weight],
    [DaysToManufacture],
    [ProductLine],
    [Class],
    [Style],
    [ProductSubcategoryID],
    [ProductModelID],
    [SellStartDate],
    [SellEndDate],
    [DiscontinuedDate],
    [rowguid],
    [ModifiedDate]
  FROM @product;

END TRY
BEGIN CATCH
   PRINT 'Error in ' + ERROR_PROCEDURE() + '' + ERROR_MESSAGE();
   RETURN -1;
END CATCH;

SET NOCOUNT OFF;

END;

This implementation allows us to handle errors correctly.

Well, we've reached the end of this long journey, and I hope it has given you a good overview of what can be done with Stored Procedures in SQL Server databases.

If you enjoyed my article, which I sincerely hope you did, please give it a thumbs up. And if you have anything to share with me, please feel free to do so in the comments. I'm always looking to learn new things and discover new optimizations!

Happy Coding!

OPTIONS PATTERN IN .NET 6

Maurizio8788 — Tue, 03 Oct 2023 20:34:20 +0000

Hello everyone, in a previous article, we saw how to retrieve the configurations of our applications through the IConfiguration interface. Now, we will move on to using a method that, in my opinion, is certainly cleaner and more responsive: the Options Pattern.

The Options Pattern

The Options Pattern provides us with strongly typed access to our configurations, thanks to the use of classes, and centralized configuration through the use of configuration in the Program.cs file. Configuration retrieval is achieved through the use of the three main interfaces that this paradigm offers, namely:

IOptions
IOptionsSnapshot
IOptionsMonitor

Let's try to create a simple basic configuration using the Options Pattern. First, let's add a fictitious configuration to our appsettings.json file for educational purposes:


"OptionsConfigurationBase": {
  "Test1": "It's a test configuration"
}

Next, we will create our class with properties that match the ones in the previously configured appsettings section:


public class OptionsConfigurationBase
{
    public const string ConfigurationName = nameof(OptionsConfigurationBase);
    public string Test1 { get; set; }
}

Once done, let's configure our class in the Program.cs file using the extension method .Configure


builder.Services.Configure<OptionsConfigurationBase>(
    builder.Configuration
        .GetSection(OptionsConfigurationBase.ConfigurationName)
);

so that it can be subsequently injected through the Dependency Injection of .NET Core.


[ApiController]
[Route("[controller]")]
public class WeatherForecastController : ControllerBase
{
    //.... other variable

    private readonly ILogger<WeatherForecastController> _logger;
    private readonly OptionsConfigurationBase _options;

    public WeatherForecastController(ILogger<WeatherForecastController> logger, IOptions<OptionsConfigurationBase> options)
    {
        _logger = logger;
        _options = options.Value ?? throw new ArgumentNullException(nameof(options));
    }

    // ...other methods

    [HttpGet]
    [Route("GetOptionsTest")]
    public string GetOptionsTest()
    {
        return _options.Test1;
    }
}

Now, this is certainly a great system; the only issue is that this configuration does not allow for reading changes at runtime. Therefore, any modifications made after the application starts will not be considered. To achieve this type of behavior, we'll need to use the IOptionsSnapshot interface.

Configuration with IOptionsSnapshot

This configuration is very useful when options need to be recalculated for each request. Essentially, this is what the IOptionsSnapshot interface does: it is registered as a Scoped service and caches the configurations for each request.

There are no changes in registration compared to the IOptions interface, except for the fact that in the constructor of our controller, we change IOptions to IOptionsSnapshot.


public WeatherForecastController(ILogger<WeatherForecastController> logger, IOptionsSnapshot<OptionsConfigurationBase> options)
{
        _logger = logger;
        _options = options.Value ?? throw new ArgumentNullException(nameof(options));
}

If, after the application is started, we change the value of the "Test1" parameter from:

"Test1": "It's a test configuration"

to:

"Test1": "It's a test configuration updated"

And if we make the request again after changing the "Test1" parameter to "It's a test configuration" we will see that the returned result will be "It's a test configuration updated".

This configuration is certainly useful, but it should be used with caution because it could potentially lead to performance issues since, as a Scoped service, it is recalculated for every request.

Configuration with IOptionsMonitor

Last but not least, the use of the IOptionsMonitor interface is certainly important. This service is configured as a Singleton service and will retrieve updated values of our configurations at any time. One key difference is found in the constructor when retrieving the injected service, instead of using the .Value property, we will use the .CurrentValue property:


public WeatherForecastController(ILogger<WeatherForecastController> logger, IOptionsMonitor<OptionsConfigurationBase> options)
{
        _logger = logger;
        _options = options.CurrentValue ?? throw new 
ArgumentNullException(nameof(options));
}

Exactly as before, when making the initial call, we will receive the response with the value "It's a test configuration updated" However, if we update the property to "I'm a test configuration updated and retrieved with IOptionsMonitor" this value will be retrieved from our service.

Configuring Named Options

Named options are useful when we need multiple sections within a single configuration object.

Let's modify the appsettings.json file as follows:


"OptionsConfigurationBase": {
  "NamedOptions1": {
    "Test1": "I am the Named Option 1 configuration"
  },
  "NamedOptions2": {
    "Test1": "I am the Named Option 2 configuration"
  }
}

let's make a small modification to our class:


public class OptionsConfigurationBase
{
    public const string NamedOptions1 = "NamedOptions1";
    public const string NamedOptions2 = "NamedOptions2";

    public string Test1 { get; set; }
}

finally, let's modify the Program.cs to support this new configuration option:


builder.Services.Configure<OptionsConfigurationBase>(
    OptionsConfigurationBase.NamedOptions1,
    builder.Configuration.GetSection($"{nameof(OptionsConfigurationBase)}:{OptionsConfigurationBase.NamedOptions1}")
);

builder.Services.Configure<OptionsConfigurationBase>(
    OptionsConfigurationBase.NamedOptions2,
    builder.Configuration.GetSection($"{nameof(OptionsConfigurationBase)}:{OptionsConfigurationBase.NamedOptions2}")
);

How do we retrieve configurations in the controller? By using the Get method associated with the IOptionsSnapshot interface, like this:


private readonly  OptionsConfigurationBase _namedOptions1;
private readonly  OptionsConfigurationBase _namedOptions1;

public WeatherForecastController(ILogger<WeatherForecastController> logger, IOptionsSnapshot<OptionsConfigurationBase> options)
{
        _logger = logger;
        _namedOptions1 = options.Value.Get(OptionsConfigurationBase.NamedOptions1)?? throw new ArgumentNullException(nameof(options));
        _namedOptions1 = options.Value.Get(OptionsConfigurationBase.NamedOptions2)?? throw new ArgumentNullException(nameof(options));
}

Choosing Between Configurations

As we've seen, each of these interfaces has its own characteristics, and choosing between them is not always straightforward. However, we can do a quick review to help you choose the right interface based on your needs:

IOptions

It doesn't support runtime reloading of configuration values.
It's registered as a Singleton service in the .NET Core Dependency Injection container.
It can be injected into any service, whether registered as Singleton, Scoped, or Transient.
It doesn't support named options.

IOptionsSnapshot

Useful when configurations need to be reconfigured for each request.
Registered as Scoped, which means it cannot be injected into a Singleton service and might have performance issues.
Supports named options.

IOptionsMonitor

Registered as a Singleton service in the .NET Core Dependency Injection container.
Supports named options.
Dynamic changes reloaded for each request.

And with that, our overview of the Options Pattern in .Net Core comes to an end. I hope this article helps you choose the right interface and understand how this pattern makes configuration retrieval in our applications easy and dynamic.

You can find all the code used in the article (it's not much) in this repository:

Maurizio8788 / OptionsPatternArticle

If you enjoyed the article, please share it and give it a like to make it visible to others. I look forward to reading your comments where you share your experiences with this feature.

Happy Coding!

ANGULAR INJECT FUNCTION

Maurizio8788 — Mon, 02 Oct 2023 14:57:32 +0000

Hello everyone,

in today's article, I wanted to briefly illustrate one of the features of Angular that has left me pleasantly satisfied: the Inject function.

Note: All the code used in this article can be found here.

Introduction to Inheritance in TypeScript

Before delving into the explanation of the Inject function, I wanted to briefly touch on inheritance in TypeScript. Inheritance is a process in OOP that allows us to extend our classes by inheriting methods and fields from other classes. In TypeScript, you can achieve this using the extends keyword, like this:


interface IAnimal {
  dateOfBirth: any;
}


interface IDog extends IAnimal {
  breed: any;
}


class Animal {
  resident: IAnimal ;
  constructor(animal: IAnimal) {
    this.resident = animal;
  }
}


class Dog extends Animal {
  constructor(dog: IDog, private parameter2: number) {
    super(dog);
  }
}

As you can see in the constructor of the Dog class, if we want to inject or add additional dependencies in the constructor of the child class, we must necessarily use the super function and inject the inherited dependencies from the base class into it. Of course, this means that if the Animal class had 10 dependencies, we would need to inject all 10 dependencies in the super call.

The Inject function

Once we have a good understanding of inheritance in TypeScript, we can introduce the first benefit of using the new Inject feature in Angular, which precisely relates to not having to use the super function to inherit dependencies from a parent class. Let's take the example of a basic service:

// base-service.service.ts
export abstract class BaseService {
  http: HttpClient = inject(HttpClient);
}


// user.service.ts
@Injectable({
  providedIn: 'root',
})
export class UserService extends BaseService {
  getAll() {
    return this.http.get<User[]>('/users');
  }
}

as you can see, instead of inserting dependencies inside the constructor, we have simply created a variable of type protected, which will then be inherited by child classes.

Another benefit also relates to the use of inject outside of a component or service class. It could be used, for example, to create a function that dynamically retrieves a router param like this:


import { inject } from '@angular/core';
import { ActivatedRoute } from '@angular/router';


export function getRouteParameter(param: string) {
  const router = inject(ActivatedRoute);
  return router.snapshot.paramMap.get(param);
}

to then be used in


import { CommonModule } from '@angular/common';
import { Component, inject, OnInit } from '@angular/core';
import { RouterLink } from '@angular/router';
import { getRouteParameter } from '../../utility';
import { User } from '../user.model';
import { UserService } from '../user.service';
import { Observable } from 'rxjs';
import { UserAddressPipe } from '../../user-address.pipe';


@Component({
  selector: 'app-user-detail',
  standalone: true,
  imports: [CommonModule, RouterLink, UserAddressPipe],
  providers: [UserService],
  templateUrl: './user-detail.component.html',
})
export class UserDetailComponent implements OnInit {
  userId: string | null = '';
  user$!: Observable<User>;
  private userService: UserService = inject(UserService);
  constructor() {
    this.userId = getRouteParameter('id');
  }
  ngOnInit(): void {
    if (this.userId != null)
      this.user$ = this.userService.getById(+this.userId);
  }
}

making the HTTP call from the service in this way.

And with that, we can conclude our brief overview of the inject function and its uses. If you found this article helpful, please consider sharing it and leaving a review ❤️.

Happy Coding!!

Options and Configuration In .NET6

Maurizio8788 — Mon, 07 Aug 2023 21:51:52 +0000

Hi everyone, today I wanted to talk to you about one of the fundamental aspects in .NET development: namely retrieving configuration information. With the advent of .NET 6 it has become even easier to manage our applications by retrieving configuration options based on the development environment we are in using in this first article the IConfiguration interface and in a second article the Options pattern easily defining configuration options and retrieving them in a clean and structured way. This allows us to separate configuration logic from business logic, making our code more modular, maintainable and flexible.

SUBJECT INDEX:

Retrieving information via IConfiguration.
Configure new appsettings
The File Hierarchy
The methods of IConfiguration
Binding of property with custom class
Final Considerations

Information retrieval by IConfiguration

In a .NET application we have the ability to set our configurations via .json or .xml files normally visible at the root of our projects, depending on the project chosen these files can correspond to the name of:

appsetting.json when working with WebApi or MVC
App.config when we work in a WPF application instead.

Configure new appsettings

In other projects such as a console application these files are not present and you have to create and register them at startup through the Microsoft.Extensions.Configuration.json library by configuring the newly created file so that at build time it can be placed in the project bin, this through these few lines of code:

public class Program
{
  private static void Main(string[] args)
  {
        var configuration = new ConfigurationBuilder()
             .SetBasePath(AppContext.BaseDirectory)                                   
             .AddJsonFile("appsettings.json",optional:true)
             .Build();
       var conn = configuration
                  .GetConnectionString("AppDbContext");
  }
}

and of course creating the appsettings file in the root directory and setting the 'Copy to output Directory' option to on Copy Always (right click > Properties):

at this time we have a configuration file created ad hoc for our application, we set the base path where the compiler will fetch the file through the SetBasePath method and then added the AddJsonFile method of the builder entered the name of the file, and set this file as non-optional and thus generating an error in case the file is removed, next we used the GetConnectionString method to test the correct setting and retrieval of the value present in our configuration file.

The File Hierarchy

Now let's take a small step back and imagine that we have to develop a trivial application in .NET 6 MVC, within this type of project, as we pointed out earlier, we find our appsettings.json file and another file namely appsettings.development.json. The configuration files follow a real hierarchy based on the specific environment in which we are working, it should be noted that the properties of the '.development.json' file will override the main appsettings.json file.

The methods of IConfiguration

Previously we used the GetConnectionString method to retrieve from our appsettings file the property that contains our database connection string, this method is present in the IConfiguration interface, this interface we can inject it anywhere in our application through dependency injection:

public class HomeController : Controller
{
   private readonly ILogger<HomeController> _logger;
   private readonly ApplicationDbContext _context;
   private readonly IConfiguration _configuration;

   public HomeController(
      ILogger<HomeController> logger,
      IConfiguration configuration
   )
   {
     _logger = logger;
     _configuration = configuration;
   }
}

now in order to retrieve the property within the configuration .json file, we will use two of the most commonly used methods in this interface namely:

GetSection
GetValue

GetSection will retrieve entire sections of our configuration file, GetValue instead individual values, to be clear:

{
   “Section”: {
     “Single value”:”test”
   }
}

now suppose I have within my appsettings file the following configuration:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    },
  },
  "Apis": {
     "BookApi": "https://localhost:7260/api/"
  }
  "AllowedHosts": "*"
}

If I wanted to retrieve the entire section of the outer bees, I should use the following form:

var apiSection = _configuration.GetSection(“Apis”);

this way I could retrieve in my method the individual values from the api section to create a configuration for the http client in this way:

builder.Services.AddHttpClient<BookService>(options => {
    options.BaseAddress = new Uri(apiSection["BookApi"]);
});

now we have configured an Http client that we could use in individual services. It is not necessary to retrieve the whole "Apis" section to access our remote url, we can also modify our code as follows:

builder.Services.AddHttpClient<BookService>(options => {
    options.BaseAddress = new Uri(_configuration.GetValue<string>("Apis:BookApi");
});

the code above other reproduces the same result, following the pattern "section:properties" we can retrieve our endpoint and initialize our view.

This is all great but you have to realize that hand-writing the various properties can lead to misspellings, you also have to take into account that if we change the names of the properties or sections, we would be forced to change all the points in our code that uses those properties or those sections, and that's not very practical especially in case of large applications and that's where .NET Core comes to our rescue.

Binding of properties with custom class

Our IConfiguration interface has another method that can help us make the retrieval of our configurations cleaner, the Bind method this method simply binds a section of our appsettings to the properties of a class we created and passed as a parameter to the method.

NB For the binding to work properly, the properties must be public and the names perfectly identical to those entered in the appsettings file.

First we create our class, which we will call ApisConfiguration:

public class ApisConfiguration
{
    public string BookApi { get; set; }
}

next we return to the Program.cs class of our project, and only then can we retrieve our settings through the Bind method:

ApisConfiguration apiConfig = new ApisConfiguration();
builder.Configuration.Bind("Apis", apiConfig);

and we will get the same result as before but using the property of the class which at this point will be valued m we will have a cleaner method available:

builder.Services.AddHttpClient<IBookService, BookService>(options => {
  options.BaseAddress = new Uri(apiConfig.BookApi);
});

Final Considerations

We have come to the end of our first article covered several topics we talked about the configuration hierarchy, we saw how to configure an appsettings file completely de zero and how to retrieve information using the methods provided by the IConfiguration interface, and then we used binding to connect a class we created to a particular section of our appsettings.

Now it might seem like this method of information retrieval might be more than fine, but this method is definitely very verbose we need to inject the IConfiguration and then bind the properties with our classes, the .NET Core provides us with a pattern that allows us to centralize the retrieval of our configurations at the Startup level and then inject these configurations into the classes where we need them, this is called Pattern Options.

If you liked this article of mine put a like and give me your impression on this topic, contact me on Linkedin or write me on Twitter for a discussion.

From Doubts to Dreams: My Path to Becoming a Developer

Maurizio8788 — Thu, 03 Aug 2023 16:59:50 +0000

Hi everyone, my name is Maurizio Strazzullo, and I am a software developer. I want to start writing posts to share what I learn during my working days and my studies with all of you.

I was initially undecided about the topic for my first post, but recently, I came across several questions on Facebook like:

can I become a software developer without having a degree?
can I become a software developer at 30/32/36 years old?

Since I have faced similar doubts and fears in the past, not only during my job search but also due to a lack of confidence in my abilities, it became clear that my first post should address this topic. So, let's proceed in order, and I'll explain the steps that convinced me to embark on this challenging journey.

During my school years, I was never highly regarded, not because I lacked abilities, but simply because I was a very lively and active person. Unfortunately, in Italy, if a student is labeled in a certain way, they are often left to their own devices.

At the age of 19, I started working in the fish department of a wholesale supermarket through a cooperative. Though the salary was adequate for the hours I worked, I had no benefits like sick leave or vacation. I continued this way for two years before being hired on a part-time contract.

Despite my efforts, I couldn't advance my career, and I remained part-time for ten years. When I got married, I realized that I needed to make a change, as it was no longer just about me. I had someone else depending on me, and I had vowed to take care of my family. However, there was a problem: I didn't have a degree. This led me to ponder what I could do to improve my situation.

I had two options: I could either pity myself or take action and make use of the one thing I had in abundance – my willpower. I firmly believe that willpower can move mountains and enable us to achieve success in actions that others may consider foolish or destined to fail. In the words of Liam Neeson from one of my favorite movies:

The first step I took was to complete at least high school, so I returned to school as a working student. In 2010, I finally obtained my diploma, bringing happiness not only to myself but also to my family. Now it was time to decide what I wanted to do in life. One day, I realized that my passion for computers and computer science could become a fulfilling career. However, not being able to attend university, I turned to the internet for resources.

With limited means, I found free resources like SoloLearn and W3Schools to learn programming. My first language was Python, as I read that it was suitable for first-time programmers. In hindsight, I would recommend starting with C++ as it may be challenging at first, but it will give you a strong foundation.

The combination of these resources made learning exciting for me. I also used HackerRank to practice programming and problem-solving. Despite having to get up at 3:15 in the morning for work, I dedicated my evenings to study and contribute to married life.

Later, I discovered Freecodecamp, where I delved into client-side web development, learning:

HTML
CSS
SCSS
JavaScript
React

Over time, I ventured into PHP **and started creating websites using **WordPress. I also attended meetups to enhance my skills. Through hard work and determination, I got my first job as a DEVELOPER!! 🥳

Though my experience was modest, I knew it wasn't enough. I recognized my shortcomings and was eager to improve. I continued my studies and joined Linkedin to connect with professionals and learn from others.

My early experiences with LinkedIn were challenging, as I received many rejections. However, I refused to be demoralized. I had a goal, and after all the hard work I put in, giving up was not an option. As I studied more, I became hungrier for knowledge and self-improvement. I explored the web and discovered tips and tricks to make my profile more attractive to potential employers. I included some amateur projects to showcase my skills and soon caught the attention of a software development company in Turin.

I attended the interview with a mix of excitement and anxiety. I would be interacting with experienced engineers, many with degrees in mathematics, statistics, and computer science. I felt like going to war armed only with a plastic machine gun, and my impostor syndrome was wearing down my morale. However, I managed to break the ice by answering cognitive questions, and the interview gradually shifted to technical topics, where I confidently discussed the technologies I had used and the websites I had developed. Eventually, they offered me a probationary position.

Working there for six months was intense, as I was still managing my part-time job in the mornings. However, I was fascinated by the technologies I encountered, such as C# and .NET Core 3.1, Sql Server, and Angular with Angular Material. I also dived into REST API development and Microservices architectures. I loved what I was doing, and I felt happy, fulfilled, and proud of my work. It was just the beginning, and there was much more to learn.

Those six months were challenging, but my team leader understood my eagerness to learn, and he taught me many useful skills that I still use today, not only on the coding side but also in managing projects and soft skills. I approached him humbly, followed his guidance, and tried to learn from him. "Stealing the craft" was not easy, but I wanted to be an asset to my team, not a burden. I studied and worked relentlessly, and it wouldn't have been possible without the support of my wife, who helped me with my schedule and supported my study and rest.

After successfully completing the probationary period, they offered me a permanent contract, and I finally became a software developer on paper!

Final Thoughts

Over the next two years, working for a large consulting company, I gained experience in various projects. I continue to learn every day, both at work and on my own. I believe that continuous learning and self-improvement are the keys to success, whether you have a degree or not. If you want to become a developer without a degree and at an advanced age, the answer is "YES, it is possible!" But it requires belief in yourself, willingness to sacrifice, and the determination to put in the effort.

I hope my story can be inspirational and helpful to some of you. Remember that finding a job you love and pursuing your passion is possible, even if it's not as a developer. Feel free to connect with me on Linkedin or Twitter if you liked this post or have any questions.

DEV Community: Maurizio8788

Testing in .NET Core: Unit test, integration test

Table of Contents:

Test Pyramid

Nunit for Unit Tests

NUnit for Integration Test

Dependency Injection in .NET Core

Table of Contents:

AddSingleton

AddTransient

AddScoped

The comprehensive guide to Entity Framework Core

Table of Contents:

What is an ORM

Database First and Code First Approach

Database First

Code First

The Context Class

Column Mapping

Queries with Entity Framework

Retrieve Entities

Add a New Entity

Update an Entity

Delete an Entity

Maurizio8788 / ArticleAdoDotNetAndEFCore

EF Core TodoList Application

Getting Started

Prerequisites

Installation

Usage

Key Concepts Covered

From ADO.NET to Entity Framework Core: ADO.NET beginner guide

Table of Contents:

Overview of Libraries

Let's Begin with ADO.NET

Exploring Execute Commands

Working with Parameters

Mapping Techniques with ADO.NET

Create a stored procedure with Sql Server

Table of Contents:

What are Stored Procedures

Setting up the Environment

Starting with the Basics

Stored Procedure Parameters

Best Practices

OPTIONS PATTERN IN .NET 6

Table of Contents:

The Options Pattern

Configuration with IOptionsSnapshot

Configuration with IOptionsMonitor

Configuring Named Options

Choosing Between Configurations

IOptions

IOptionsSnapshot

IOptionsMonitor

Maurizio8788 / OptionsPatternArticle

ANGULAR INJECT FUNCTION

TABLE OF CONTENTS:

Introduction to Inheritance in TypeScript

The Inject function

Options and Configuration In .NET6

SUBJECT INDEX:

Information retrieval by IConfiguration

Configure new appsettings

The File Hierarchy

The methods of IConfiguration

Binding of properties with custom class

Final Considerations

From Doubts to Dreams: My Path to Becoming a Developer

Final Thoughts