Make custom SQL Querys with Dapper #9235

unidata created 4 years ago

Good Morning

ASPNETZERO 8.4 / ANGULAR 8.3 / DOT NET CORE 3.1 / EF CORE 3.1 / POSTGRESQL DATBASE

I am making a manager to use create reports using SQL queries written by me, instead of leaving them to the FE for efficiency reasons. I already have dapper working properly for repositories, but I can't run an anonymous resoults query because dapper is asking me for a class to return the results to. I would use something like this in my reportsManager in the CORE layer, but I don't have a sql library in that layer, that is in the framework layer.


public class ReportsManager: VCloudDomainServiceBase,IReportsManager
{
    private readonly IDapperRepository _genericDapperRepository;

    public ReportsManager(IDapperRepository<GenericGroupDescription3Dto> genericDapperRepository)
    {
        _genericDapperRepository = genericDapperRepository;
    }

    public Task<IEnumerable> GetSalesByGroupRtdmAsync()
    {
        DateTime dateI = new DateTime(2020, 05, 01).ToUniversalTime();
        DateTime dateF = new DateTime(2020, 05, 30).ToUniversalTime();
        int restaurantId = 13;
        var sqlQuery = @"SELECT article_name Description, sum(quantity) Value1, sum(quantity * unit_price) Value2, sum(0) Value3
        FROM rtdm_orderitem OI JOIN rtdm_order O ON OI.order_id = O.id
        WHERE O.restaurant_id = {0} AND O.created_timestamp between {1} AND {2}
        GROUP BY article_name
        ORDER BY 2 desc
        LIMIT 10";

        var connectionString = ((JObject)(JObject.Parse(File.ReadAllText("appsettings.json")))["ConnectionStrings"]).Property("Default").Value.ToString();

        using (var connection = new NpgsqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
        {
            return connection.Query(sqlQuery, new {restaurantId, dateI,dateF});
        }
       }
}

So I create this generic class where to receive the results of the querys, but it doesn't work because I can't create it because I have to create the DbSet.


public class GenericGroupDescription3Dto : Entity
{
	public int Id { get; set; }
	public string Description { get; set; }
	public decimal Value1 { get; set; }
	public decimal Value2 { get; set; }
	public decimal Value3 { get; set; }
}

Is there a way to create a class that can be used by dapper to receive the results of a sql query? Is the approximation right or is the whole approach wrong?

Thanks!!

9 Answer(s)

maliming created 4 years ago

Support Team

You can consider creating a model for the GetSalesByGroupRtdmAsync method in the Domain layer. Then use it as the return value.

public class GetSalesByGroupRtdmModel
{
	public int Id { get; set; }
	public string Description { get; set; }
	public decimal Value1 { get; set; }
	public decimal Value2 { get; set; }
	public decimal Value3 { get; set; }
}

return connection.Query<GetSalesByGroupRtdmModel>(sqlQuery, new {restaurantId, dateI,dateF});

0
unidata created 4 years ago
hanks for your answer! That's what I had done and I know it works. In order to create the connection I'm reading the connectionstring like this:

var connectionString = ((JObject)(JObject.Parse(File.ReadAllText("appsettings.json")))["ConnectionStrings"]).Property("Default").Value.ToString();

Is there a better way that doesn't involve having to constantly read the appsettings file?

I tried with ConfigurationManager but I can't get it to work

Thank you!
0
unidata created 4 years ago
I managed to do it this way, which I think is more neat.

var connectionString2 = (new DefaultAppConfigurationAccessor()).Configuration.GetConnectionString("Default");

If there's a better one, I'll ask you to tell me, otherwise I'll close the question.

Thank you very much for the answers!
1
0

maliming created 4 years ago
Support Team

You can also use IConnectionStringResolver to get the relevant connection string.

https://github.com/aspnetboilerplate/aspnetboilerplate/blob/e0ded5d8702f389aa1f5947d3446f16aec845287/src/Abp/Domain/Uow/IConnectionStringResolver.cs

Actually when you use IDapperRepository you don't need to care about the connection string.

https://aspnetboilerplate.com/Pages/Documents/Dapper-Integration

unidata created 4 years ago

I understand, I managed to make it work using an IDapperRepository in this way:

        private readonly IDapperRepository<GenericNameQuantityDto> _dapperRepo;

        public ReportsManager( IDapperRepository<GenericNameQuantityDto> dapperRepo)
        {
            _dapperRepo = dapperRepo;
        }

         public Task<List<GenericNameQuantityDto>> GetSalesByGroupRtdmAsync()
         {
                     int restaurantId = 13;
                     var sqlQuery = @"SELECT article_name as Name, sum(quantity) as Quantity, 
                               sum(quantity * unit_price) as Value 
                            FROM rtdm_orderitem  OI JOIN rtdm_order O ON OI.order_id = O.id 
                            WHERE O.restaurant_id = @restaurantId
                            GROUP BY article_name 
                            ORDER BY 2 desc
                            LIMIT 10 ";

            var query = _dapperRepo.Query(sqlQuery, new { restaurantId = restaurantId });
         }

Only one last detail is missing, how can I register in the depency injector the class GenericNameQuantityDto ?

    public class GenericNameQuantityDto : Entity
    {
        public string Name { get; set; }
        public decimal Quantity { get; set; }
        public decimal Percentage { get; set; }
    }

0

maliming created 4 years ago
Support Team

GenericNameQuantityDto should not inherit Entity.

how can I register in the depency injector the class GenericNameQuantityDto ?

Why? Can you share the use case? It's just a model class.

unidata created 4 years ago

You says "GenericNameQuantityDto should not inherit Entity.": If i not inherit class from Entity, the proyect dosen't compile. This line gives error:

Error CS0311 The type 'VCloud.VCloudCustom.Reports.GenericNameQuantityDto' cannot be used as type parameter 'TEntity' in the generic type or method 'IDapperRepository'.

There is no implicit reference conversion from 'VCloud.VCloudCustom.Reports.GenericNameQuantityDto' to 'Abp.Domain.Entities.IEntity'. VCloud.Core D:\Source\Vinson\VCloud\VCloud-ws\src\VCloud.Core\VCloudCustom\Reports\ReportsManager.cs

  private readonly IDapperRepository<GenericNameQuantityDto> _dapperRepo;

The only way i found to compile is to make it inherit from Entity.

Could you please correct in my sample code the correct way to do it? :

 private readonly IDapperRepository<GenericNameQuantityDto> _dapperRepo;
 
public ReportsManager(IDapperRepository<GenericNameQuantityDto> dapperRepo)
        {
             _dapperRepo = dapperRepo;
        }
        
private readonly IDapperRepository<GenericNameQuantityDto> _dapperRepo;

        public ReportsManager( IDapperRepository<GenericNameQuantityDto> dapperRepo)
        {
            _dapperRepo = dapperRepo;
        }

         public Task<List<GenericNameQuantityDto>> GetSalesByGroupRtdmAsync()
         {
                     int restaurantId = 13;
                     var sqlQuery = @"SELECT article_name as Name, sum(quantity) as Quantity, 
                               sum(quantity * unit_price) as Value 
                            FROM rtdm_orderitem  OI JOIN rtdm_order O ON OI.order_id = O.id 
                            WHERE O.restaurant_id = @restaurantId
                            GROUP BY article_name 
                            ORDER BY 2 desc
                            LIMIT 10 ";

            var query = _dapperRepo.Query(sqlQuery, new { restaurantId = restaurantId });
         }

    public class GenericNameQuantityDto : Entity
    {
        public string Name { get; set; }
        public decimal Quantity { get; set; }
        public decimal Percentage { get; set; }
    }

0
unidata created 4 years ago
228/5000

I already managed to solve it!

To my example code I simply modify the constructor to create an IDapperRepository of an entity that already exists in the database

private readonly IDapperRepository<RtdmOrder> _dapperRepo;

public ReportsManager( IDapperRepository<RtdmOrder> dapperRepo) { _dapperRepo = dapperRepo; }

and only change to the DTO type when I run the query.

var query = _dapperRepo.Query<GenericNameQuantityDto>(sqlQuery, new { restaurantId = restaurantId });

Thanks so much for all the help and patience!
0

ISTeam created 4 years ago

Thank you. Your answer helped me to get the connection string in appservice to use raw SQL using dapper!

Have an answer to this question? Log in and write your answer.

9 Answer(s)

I already managed to solve it!

Thanks so much for all the help and patience!