locked
Mixing WebAPI and Dapper - how to set up? RRS feed

  • Question

  • User379720387 posted

    WebAPI + EF core  is kind of on the slow side for presenting information, if I compare this to straight Dapper.

    Previously some of you said you have Dapper for quickly presenting data, and would use EF Core for CRUD.

    I tried to implement this by putting all the Dapper code inside the Controller project, and that killed WebAPI that had worked previously. Not sure if my implementation was lacking. Took all the Dappers stuff out and now what I had works again.

    My next move will be to create a separate project, but was wondering what your suggestions would on what the best way is of mixing EF Core and Dapper.

    Advice?

    Friday, February 12, 2021 2:42 PM

All replies

  • User-474980206 posted

    I would not mix. Pick one or the other. EF raw sql should perform as well as dapper. The main disadvantage of ef over dapper, is the context needs a dbset for every raw sql entity. But this makes the context more formal.

    note: I use dapper instead of EF, but still would not recommend a switch to dapper for some of your queries if ef is the ORM.

    Friday, February 12, 2021 3:39 PM
  • User1120430333 posted

    It should have been no problem implementing Dapper with it implemented in a classlib project like a DAL, the DAO pattern was used and the DAO in the DAL dependency injected into the controller, which would be separation of concerns.

    You could have tested all CRUD aspects of the DAO(s) in the DAL that are using Dapper using  integration tests using MSTest and Nunit or some other UT framework without even involving the WebAPI to  make sure everything worked and them implemented Dapper into the WebAPI.

    Now I have incentive to watch the Blazor tutorials on Pluralsight and do some Blazor with SoC. :) 

    Tuesday, February 23, 2021 12:14 PM
  • User-821857111 posted

    that killed WebAPI that had worked previously
    What were the symptoms? Were there error messages?

    Tuesday, February 23, 2021 6:24 PM
  • User379720387 posted

    I had removed most of the Dapper related folders and settings, so I had to retrace my steps.

    My WebAPI project folder has:

    Controllers

    DapperData

    Model

    Program, Startup, appsettings.json

    In DapperData I have SqlDataAccess and ISqlDataAccess:

    using Dapper;
    using Microsoft.Extensions.Configuration;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace BtApi.Controllers
    {
        public class SqlDataAccess : ISqlDataAccess
        {
            private readonly IConfiguration _config;
    
            public string ConnectionStringName { get; set; } = "Default";
    
            public SqlDataAccess(IConfiguration config)
            {
                _config = config;
            }
    
            public async Task<List<T>> LoadData<T, U>(string sql, U parameters)
            {
                //string connectionString = _config.GetConnectionString("ConnectionStringName");  
                string connectionString = _config["ConnectionString:Default"];
    
                using (IDbConnection connection = new SqlConnection(connectionString))   //docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?view=aspnetcore-5.0
                {
                    var data = await connection.QueryAsync<T>(sql, parameters);
    
                    return data.ToList();
                }
    
            }
    
            public async Task SaveData<T>(string sql, T parameters)
            {
                string connectionString = _config.GetConnectionString(ConnectionStringName);
    
                using (IDbConnection connection = new SqlConnection(connectionString))
                {
                    await connection.ExecuteAsync(sql, parameters);
                }
            }
        }
    }

    and SchedEquineData.cs

    using BtApiEf5.Model;
    using BtApiEf5.Model.Custom;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace BtApiEf5.DapperData
    {
        public class SchedEquineData : ISchedEquineData
        {
            private readonly BtApi.Controllers.ISqlDataAccess _db;
    
            public SchedEquineData(BtApi.Controllers.ISqlDataAccess db)
            {
                _db = db;
            }
    
            public Task<List<SchedEquineGrid>> GetSchedEquine()
            {
                string sql = @"
                    SELECT * FROM
                    (
                        //long sql query
                    ) As todos
    		        ORDER BY Sort DESC, CName ASC";
    
                return _db.LoadData<SchedEquineGrid, dynamic>(sql, new { });
            }
        }
    }

    In Model.Custom I have SchedEquineGrid.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace BtApiEf5.Model.Custom
    {
        public class SchedEquineGrid
        {
            public int ClientId { get; set; }
    
            public string Location { get; set; }
    
            public string CName { get; set; }
    
            public int LastServiceWeekNo { get; set; }
    
            public int AgeInWeeks { get; set; }
    
            public int NextService { get; set; }
    
            public int Sort { get; set; }
        }
    }

    In Startup.cs in the ConfigureServices method I had added:

    services.AddTransient<ISqlDataAccess, SqlDataAccess>();
    services.AddTransient<ISchedEquineData, SchedEquineData>();

    Squiggles say I cannot find the two ISql..., so when I delete the one I had copied in that same folder and try to recreate them it does so, or attempts to do so in Controllers.

    That is apparently not allowed and I get an Exception from VS.

    Wednesday, February 24, 2021 2:09 AM
  • User379720387 posted

    @DA924  that is what I am doing with Open API and Swagger on NET5.0. Created a few controllers once I got those working (with help from you all), I was able to see they were returning the data as expected

    Here is a YT channel I follow: https://www.youtube.com/channel/UCS4yRfjjXhjlbx_NiaJB26g/videos and here is the link to Blazor Crud https://www.youtube.com/watch?v=_qjR25bEsWs

    And the new NET5 WebAPI explained: https://www.youtube.com/watch?v=nY-w9wPFEuY

    @mikesdotnetting.... This is how far I got this time, cannot remember exactly where I ended up the first time I tried this.

    Wednesday, February 24, 2021 2:32 AM
  • User-821857111 posted

    Squiggles say I cannot find the two ISql..., so when I delete the one I had copied in that same folder and try to recreate them it does so, or attempts to do so in Controllers.

    That is apparently not allowed and I get an Exception from VS.

    I'm no clearer. It sounds like you simply needed to add the correct using directives to include the namespace of the ISqlDataAccess class, no?  You can do that by clicking on the squiggled word and hitting  Ctrl+Shift+F10. Otherwise, what was the exception?

    Wednesday, February 24, 2021 7:34 AM
  • User379720387 posted

    Got the Interfaces sorted out and there are no longer any complaints so I can run the projects. And WebAPI stuff is still working too.

    Now, when loading the razor page SchedEquinesDapper (which has the call to the Dapper code) generates an exception.  

    blazor.server.js:19 [2021-02-24T11:19:47.411Z] Error: System.InvalidOperationException: Cannot provide a value for property '_db' on type 'BtServer.Pages.SchedEquinesDapper'. There is no registered service of type 'BtApiEf5.DapperData.ISchedEquineData'.

    Guess it is complaining about this at the top of the Razor page,

    @using BtApiEf5
    @using BtApiEf5.Model
    @using BtApiEf5.DapperData
    @inject BtApiEf5.DapperData.ISchedEquineData _db

    Well, there is:

    public void ConfigureServices(IServiceCollection services)
            {
                services.AddTransient<ISqlDataAccess, SqlDataAccess>();
                
                services.AddTransient<BtApiEf5.DapperData.ISchedEquineData, SchedEquineData>();
                
                
                services.AddControllers();
                services.AddSwaggerGen(c =>
                {
                    c.SwaggerDoc("v1", new OpenApiInfo { Title = "BtApiEf5", Version = "v1" });
                });
                services.AddDbContext<Model.BTContext>(options => options.UseSqlServer(Configuration["ConnectionString:Default"]));
            }

    More later.

    Wednesday, February 24, 2021 11:43 AM
  • User-474980206 posted

    You need to register it in the blazor server app to use it in the blazor code. But I thought your blazor code was calling the webapi service. You use httpclient to call webapi.

    Thursday, February 25, 2021 4:18 PM
  • User379720387 posted

    I was expecting the Blazor Server app to call either the Web API or Dapper, living happily together in a Web API project. 

    Since the Web API project already has the EF Model in it.

    The Blazor Server project  already has a project reference to the Web API project.

    Sunday, February 28, 2021 2:55 PM