CREATION OF A SQL WEB API SERVICE THAT READS DATA FROM THE DATABASE

In today's digital environment, web API services are becoming crucial for developing flexible and scalable applications. Throughout this page, we will guide you through the process of creating a SQL Web API service that reads data from a database using ASP.NET Core. This application will allow you to understand how data from the database is converted into JSON format that can be used by client applications.
Why Use These Tools and Packages?
In order to build an efficient and functional web API application, it is necessary to use certain tools and packages. Each of them has a specific role in the development process and allows you to:
Required Software and Packages Before starting development, make sure you have the following tools and packages installed:
​Before starting the creation of the web api service, it is necessary to check whether the following is installed:In order to be able to establish a connection to the database, it is necessary to install the following additional packages:ASP.NET core web API server - client call
Figure 1: ASP.NET core web API server - client call
In addition to the web application that we are creating, we need to have one database, for example a sql server database that contains some data, e.g.  product table. The goal of a web API application is to respond to a client's request for a specific product.
When a GET request is made, for a specific product for example with id = 15:
https://localhost:5001/products/15
​the application will try to extract the appropriate product from the database and return it to the application (picture 2). Using the additional package "Dapper", which we installed with the application, the corresponding row of the table will be converted into a corresponding C# object that has the same structure as the table in the database. You can see an example of the Product class in Figure 3.
Further, the web api converts the data into a JSON object and then the object is returned to the browser (see Figure 4).​

Video 1: A simple web API server that reads data from a database - part 1

​Video 2: A simple web API server that reads data from a database - part 2

A more detailed explanation of the Web API architecture:

​
This architecture makes it easy to maintain, test and scale the application.Safety recommendations:
 Performance optimization: ASP.NET core web API server - GET request for a product from the database
Figure 2: ASP.NET core web API server - GET request for a product from the database
The application should have a model class whose fields correspond to the fields found in the database so that record can be converted into a c# object so that the application can create an object that will connect to the database in code a client for the database must be created , and therefore the additional package "Microsoft.Data.SqlClient" is needed.
ASP.NET core web API server - Product class modelT core web API server - model klasa Product
Figure 3: ASP.NET core web API server - Product class model
 ASP.NET core web API server - returning a JSON object to the client (web browser)
Figure 4: ASP.NET core web API server - returning a JSON object to the client (web browser)

Creating a restful web api server

In order to create a new web api application in the Command Prompt within the previously created root folder, type
dotnet new api
as can be seen in Figure 5. 		Dotnet WEB API - Creating a new application in the CommandPrompt
Figure 5: dotnet WEB API - Creating a new application in the CommandPrompt
​If we type in the command prompt​

code .
VS Code will launch and open the project you just created.SimpleWebApiWidthDatabase application - class Program.cs
Figure 6: SimpleWebApiWidthDatabase application - class Program.cs
Now on the left side in the explorer you can see the created initial files of the just created application. In the right part, you can see the content of the initial Program.cs file in which the host object of the application is configured. Since a database is required for products, a database should be created, in this case a SQL Server database, which will be called "database_product" in this example. The database and the "Products" table in it were created using the Microsoft SQL Server Management Studio tool. The design of the created table can be seen in the image below:Sql Server Database: Database
Figure 7: Sql Server Database: Database "database_products", for products
In order for the application to connect to the database, the packages mentioned above must be installed:The connection is established by creating an object that will represent the client for the database, and in order to create it, the information provided by the ConnectionString is needed. 
We will create a ConnectionString inside the appsettings.json fileSee in the following video how the Dapper library is used to read data from the database and convert it into C# objects. The video shows the use of appropriate queries for that purpose. 
A useful tutorial on using Dapper:​ dapper-tutorial.net/

If you want to try Dapper queries: dotnetfiddle.net/

The Dapper Library

​Dapper is a lightweight data access library in .NET applications that enables efficient and simple mapping of database data to C# objects. It was developed to provide speed and simplicity compared to other ORM (Object-Relational Mapping) libraries like Entity Framework, while maintaining flexibility and control.

Here are some key points about the Dapper library:
 Dapper is a great choice when you need speed and simplicity, but want to have full control over the SQL queries you use.SimpleWebApiWidthDatabase application - ConnectionString defined in appsettings.json file
Figure 8: SimpleWebApiWidthDatabase application - ConnectionString defined in appsettings.json file
Within the startup file, within the configuration method, we will create an object with which a connection to the database is established. To create that object of the SqlConnection class, we use the information written in the connection string defined in the appsettings.json file, as can be seen in the following image:Web API Server: Controller class
Figure 9: Web API Server: Controller class "ProductController"
.
Within this method, it injects dependencies between the classes in the application (DependencyInjection)  and the services and objects they need. Within this method, services are created that are added to the collection of services (IServiceCollection), which will be available in classes within the application via parameters in the constructor. This concept is called IoC Inversion of Control and see more about it in the video: IoC Inversion of Controlin .Net Applications. Also, controllers should be added to this collection.

​Configuration Instructions

​When working with static pages in an ASP.NET Core application, it is important to properly configure the server to serve them. This is accomplished by adding specific lines of code to the Startup.cs file, which is key to configuring and initializing the application.public class Startup
{
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
// Use default files such as index.html if no other file is specified
        app.UseDefaultFiles();

// Enables serving static files from the wwwroot directory
        app.UseStaticFiles();

 // Other middleware and configurations
    }
}What these lines of code do:
​
app.UseDefaultFiles(); app.UseStaticFiles();

Configuration of the wwwroot directory: ​

These configuration lines enable efficient serving of static content and allow you to easily extend your application with static pages as needed.Within the Configure method, the required "Middleware" should be defined and the order in which they should be executed. Read more about it in the article:​ MiddlewareWeb API server: Startup class,
Figure 10: Web API server: Startup class, "Configure" method
This application will have classes belonging to the model, as well as one controller class. As a model, we will create the Product class, which will be a class model for objects that represent data about a specific product and will have the same structure as a record in the database, so it will have the same fields. Figure 12 shows the Product classWeb API server:
Figure 11: Web API server: "Product" class

Inversion of Control (IoC) in .net applications

​Inversion of Control (IoC) is a design principle in software development that enables greater flexibility and testability of applications by reducing dependencies between components. In the context of .NET applications, IoC is usually used with DI (Dependency Injection), which is one of the most popular ways to implement IoC principles.

Key Concepts of IoC This can be done in several ways:


Advantages of IoC and DI

IoC Containers in .NET In the .NET ecosystem, there are various IoC containers that you can use:

Microsoft.Extensions.DependencyInjection: This is the core IoC container that comes with .NET Core and .NET 5/6+. It provides basic DI functionality and is easily integrated into .NET applications.
Autofac: A more advanced IoC container with additional capabilities and configuration.
Ninject: Another popular IoC container known for its flexibility and ease of use.
Unity: Microsoft's IoC container that is part of the Enterprise Library.​How it's used in .NET applications When using IoC in .NET applications, you'll typically register your services with the IoC container during application initialization, and then the IoC container will automatically provide the necessary dependencies for your classes. For example, in ASP.NET Core applications, service registration is done in the Startup.cs file:public void ConfigureServices(IServiceCollection services)
{
services.AddTransient<IMyService, MyService>();
// Add other services
}​And then, when you use IMyService in your controllers or other classes, .NET will automatically provide an instance of MyService:public class MyController : Controller
{
private readonly IMyService _myService;

public MyController(IMyService myService)
    {
        _myService = myService;
    }
}​​In short, IoC and DI are key to modern .NET applications because they help maintain clean, flexible, and testable code.

Performance optimization:

  1. Query Optimization: Indexes should be used to speed up data access and reduce the processing time of complex SQL queries. It is also necessary to analyze queries using SQL Server tools like Query Execution Plan to identify and improve slower queries.
  2. Caching: Data caching needs to be implemented at the application or database level. Use Redis or Memcached to store frequently used data, which will reduce the frequency of access to the database and speed up the application.
These techniques significantly increase the efficiency of Web API services.

Safety recommendations

Security is crucial for any web API service to protect data and prevent unauthorized access. Here are some key security recommendations and how to implement them in an ASP.NET Core application:
Authentication Authentication is the process of identifying the user accessing your API. It is recommended to use JSON Web Tokens (JWT) for secure authentication.
How to Implement JWT:
  1. Adding a NuGet Package: Add the Microsoft.AspNetCore.Authentication.JwtBearer package to your project:
dotnet add package Microsoft.AspNetCore.Authentication.JwtBearer
2. Configure the JWT in Startup.cs: In the ConfigureServices method, add the configuration for the JWT:public voidConfigureServices(IServiceCollection services)
{
    services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
        .AddJwtBearer(options =>
        {
            options.TokenValidationParameters = new TokenValidationParameters
            {
                ValidateIssuer = true,
                ValidateAudience = true,
                ValidateLifetime = true,
                ValidateIssuerSigningKey = true,
                ValidIssuer = "yourissuer",
                ValidAudience = "youraudience",
                IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes("your_secret_key"))
            };
        });
    services.AddControllers();
}3. Dodajte Autentifikaciju u Configure Metodu:public voidConfigure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }
else
    {
        app.UseExceptionHandler("/Home/Error");
        app.UseHsts();
    }
    app.UseHttpsRedirection();
    app.UseStaticFiles();
    app.UseRouting();
    app.UseAuthentication();
    app.UseAuthorization();
    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
}Authorization controls access to resources based on user roles or policies.
How to Implement Authorization:
Define Policies: In Startup.cs, in the ConfigureServices method, add:services.AddAuthorization(options =>
{
    options.AddPolicy("AdminOnly", policy => policy.RequireRole("Admin"));
});Use Policies in Controllers:​[Authorize(Policy = "AdminOnly")]
[ApiController]
[Route("[controller]")]
public classAdminController : ControllerBase
{
// Actions
}3. Data Protection. To protect data, use SSL/TLS encryption for secure data transmission and protection against attacks such as SQL injections or Cross-Site Scripting (XSS).

How to Configure SSL/TLS:
  1. Binding SSL/TLS in appsettings.json:
"Kestrel": {
"Endpoints": {
"Https": {
"Url": "https://localhost:5001",
"Certificate": {
 "Path": "path_to_your_certificate.pfx",
"KeyPassword": "your_certificate_password"
      }
    }
  }
}Binding HTTPS in Startup.cs:public voidConfigure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseHttpsRedirection();
// other middleware
}

Additional Resources

For further learning and exploration, here are some useful resources and documentation:
  1. ASP.NET Core Documentation
  2. JWT Authentication
  3. SSL/TLS Configuration
  4. Dapper Documentation
  5. Performance Optimization

​These resources will help you advance your knowledge and skills in creating secure and efficient web API services.
Previous
​|<Servicing static web pages using a web server		 ​Next
​​​​​​​​​Creating a controller in an asp.net web API application>|