Custom Queries and Executing stored procedure

[AbuHuraira537]

Hi @hassanhabib
As I read The Standard In read about Services
-> foundation services (Basic Purpose is to validate and abstract)
-> Processing service (Processing about entity is done here)
and so on.
These Services and Brokers are purely related to Entity, but in many application to get the better performance developer create stored procedures where multiple entities are involved and we fetch the data which is not for any specific entity.
Where we should handle these scenarios. Do we need to create sperate brokers and services for it or is there any other way suggested by The Standard community.
Thanks to this community who gives newbies a chance to learn from most competent Engineers of current era.

[arnabcog]

I think you need to call the stored procedure and convert the output to a custom model with some kind of ORM or manually in the broker layer.

[LBoullosa]

Inside the broker can´t exist a mapper. That is a service responsability, the broker just call the stored procedure, the broker is an abstraction layer which doesn´t deal with any kind of logic.

[Arnab-Developer]

I agree with the fact that broker should not have any logic in it.

But if a stored procedure has been called with ADO.NET then we will get the data as DataSet and then we need to map the DataSet to custom objects so that we can return that object to the service layer.

But if we use something like Entity Framework then no extra mapping work needed because EF will automatically return data as objects which will be ready to return to the service layer.

[LBoullosa]

The mapping from Dataset to your local model should be done in a service. The Foundation Service is the only part where you could use an external model and a local model, so your broker just returns the Dataset and inside the service you could do the mapping to build the local model from the dataset.

[Arnab-Developer]

I have provided an example code to get data with SP and EF. The EF is automatically maps the data coming from the SP to custom objects. So we don't need to return dataset from the broker and then manually map that data to custom object in the foundation service layer. That is an advantage of EF.

[Arnab-Developer]

An example of getting data with stored procedure and entity framework.

Program.cs

using Microsoft.EntityFrameworkCore;

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddTransient<IStudentService, StudentService>();
builder.Services.AddTransient<IStudentBroker, StudentBroker>();

string conString = builder.Configuration.GetConnectionString("StudentDbConStr") ??
    throw new InvalidOperationException("Invalid con str");

builder.Services.AddSqlServer<StudentContext>(conString);

WebApplication app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.MapGet("/weatherforecast", (IStudentService service) =>
{
    IEnumerable<Student> students = service.GetStudents();
    return students;
})
.WithName("GetWeatherForecast")
.WithOpenApi();

app.Run();

Model

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Service layer

public interface IStudentService
{
    public IEnumerable<Student> GetStudents();
}

public class StudentService : IStudentService
{
    private readonly IStudentBroker broker;

    public StudentService(IStudentBroker broker)
    {
        this.broker = broker
    }

    public IEnumerable<Student> GetStudents()
    {
        return this.broker.GetStudents();
    }
}

Broker layer

public interface IStudentBroker
{
    public IEnumerable<Student> GetStudents();
}

public class StudentBroker : IStudentBroker
{
    private readonly StudentContext context;

    public StudentBroker(StudentContext context)
    {
        this.context = context
    }

    public IEnumerable<Student> GetStudents()
    {
        IEnumerable<Student> students = this.context.Database.SqlQueryRaw<Student>("GetStudents");
        return students;
    }
}

public class StudentContext : DbContext
{
    public DbSet<Student> Students { get; set; }

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

The stored procedure:

CREATE PROCEDURE GetStudents
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name
    FROM Students
    ORDER BY Name;
END

You need to put the connection string of the database in the appsettings.json file.

You need to install Microsoft.EntityFrameworkCore.SqlServer NuGet package.

[LBoullosa]

That´s a way.

Two considerations,

1. Stored procedures can´t be test driven, just to know it.
2. Model should be agnostic. The string.Empty initialization is not standarized.

Other than that, ok.

[Arnab-Developer]

Stored procedures can´t be test driven, just to know it.

Yes, and that is why I don't like stored procedures. Since this discussion topic is about stored procedures, that is why I have used it in the example code.

Model should be agnostic. The string.Empty initialization is not standarized.

Removed from the example code.

[Arnab-Developer]

These Services and Brokers are purely related to Entity, but in many application to get the better performance developer create stored procedures where multiple entities are involved and we fetch the data which is not for any specific entity.
Where we should handle these scenarios. Do we need to create sperate brokers and services for it or is there any other way suggested by The Standard community.

Let's say you have a student entity and a teacher entity. Now you need to get a collection of data which have some properties of student and some properties of teacher.

You have two options to achieve this.

In the first option, you can create a custom entity which have a combination of properties from student and teacher and then create a stored procedure to fetch that data with its own foundation service and broker.

In the second option, you can get the student data from its foundation service and broker and you can also get the teacher data from its foundation service and broker. Then you can combine them in a higher level of aggregation service layer and return a combination of student and teacher data.

You need to measure the performance between these two architectures. If the stored procedures architecture is not giving you a big performance advantage, then you can go with the second architecture because that is more readable and testable.