question

Brian-7574 avatar image
0 Votes"
Brian-7574 asked ZhiLv-MSFT edited

Populate <select asp-items> using dapper in the BLL

I have a razor page with a select tag:

 <select asp-for="Curriculum" asp-items="Model.Curricula"></select>


In my page model I can populate the values using EF and LINQ:


 public List<SelectListItem> Curricula => (from cur in _db.Curriculum
                                                   join c in _db.Courses on cur.CurriculumID equals c.CurriculumID 
                                                   into temp
                                                   from q in temp.DefaultIfEmpty()
                                                   where cur.CurriculumStatusID == 1 && !(q.OnDemand == true)
                                                   orderby cur.Curriculum
                                                   select new SelectListItem { Value = cur.CurriculumID.ToString(), Text = cur.Curriculum }
                                                  ).ToList();



This works but two things I want to change are:

  1. I'd like to use Dapper instead of the long LINQ statement

  2. I'd like to put the SQL Query in my Service layer instead of in my UI layer

Any suggestions for how to do this?



dotnet-aspnet-core-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZhiLv-MSFT avatar image
1 Vote"
ZhiLv-MSFT answered ZhiLv-MSFT edited

Hi @Brian-7574,

This works but two things I want to change are:

I'd like to use Dapper instead of the long LINQ statement

I'd like to put the SQL Query in my Service layer instead of in my UI layer

Any suggestions for how to do this?

To use Dapper to execute the SQL Query instead of the EF core, you have to install the Dapper Package first. Then, in the service layer you could create a service to execute the sql query statement and return the required properties (CurriculumID and Curriculum), here you need to convert the LINQ query statement to SQL Query statement. In the UI layer, you can call the relate service and get the required properties and then based on the result to create a list of SelectListItem. Finally render the data in the view page.

Here is sample you could refer it:

  1. Based on the query result create a CurriculumViewModel

      public class CurriculumViewModel
         {
             public int CurriculumID { get; set; }
             public string Curriculum { get; set; }
         }
    

  2. Create a CurriculumService with the following code:

      public interface ICurriculumService
         {
             Task<IEnumerable<CurriculumViewModel>> GetCurriculumAsync();
         }
    
         public class CurriculumService : ICurriculumService
         {
             private readonly SqlServerConnectionProvider _provider;
             public CurriculumService(SqlServerConnectionProvider provider)
             {
                 _provider = provider;
             }
             public async Task<IEnumerable<CurriculumViewModel>> GetCurriculumAsync()
             {
                 using (var connection = _provider.GetDbConnection())
                 {
                     var querystr = "SELECT cur.CurriculumID, cur.Curriculum FROM Curriculum as cur " +
                            " LEFT JOIN Courses as c ON cur.CurriculumID = c.CurriculumID " +
                            "Where cur.CurriculumStatusID = 1 and c.OnDemand != '0' order by cur.Curriculum";
                    
                     return await connection.QueryAsync<CurriculumViewModel>(querystr, null);
                 }
             }
         }
    

    The SqlServerConnectionProvider class as below:

      public class SqlServerConnectionProvider
         {
             private readonly IConfiguration _config;
             private string Connectionstring = "DefaultConnection";
             public SqlServerConnectionProvider(IConfiguration configuration)
             {
                 _config = configuration;
             }
    
             public IDbConnection GetDbConnection()
             {
                 return new SqlConnection(_config.GetConnectionString(Connectionstring));
             }
         }
    

  3. Register the service in the ConfigureServices method:

              //Register dapper in scope    
                 services.AddScoped<SqlServerConnectionProvider>();
                 services.AddScoped<ICurriculumService, CurriculumService>();
    

  4. Call the service in the Index Razor page:

    Index.cshtml.cs:

      public class IndexModel : PageModel
         {
             private readonly ILogger<IndexModel> _logger;
             private readonly ICurriculumService _repository;
             public IndexModel(ILogger<IndexModel> logger, ICurriculumService datatRepository)
             {
                 _logger = logger;
                 _repository = datatRepository;
             }
             [BindProperty]
             public List<SelectListItem> Curricula { get; set; }
    
             [BindProperty]
             public string Curriculum { get; set; }
             public void OnGet()
             {
                 Curricula = _repository.GetCurriculumAsync().Result.ToList().Select(c => new SelectListItem { Value = c.CurriculumID.ToString(), Text = c.Curriculum }).ToList();
             }
         }
    

    Index.cshtml:

      @page
         @model IndexModel 
    
         <select asp-for="Curriculum" class="form-control" asp-items="Model.Curricula"></select>
    

    The screeshort as below:

    144923-1.gif


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion


1.gif (351.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.