title: ASP.Net Core 3.1 Razor Pages and CRUD operation with Dapper
body: <p>I am beginner to ASP.Net Core 3.1 and have to use Dapper in our project which need to be upgraded from webform to asp.net core 3.1 using Dapper as we use lot of store procedure and CTE queries on some page. We also have lot of data which is frequently queries.</p>
  
  <p>I am at a very beginner level with no experience in MVC so my focus is on ASP.Net Core 3.1 razor pages using dapper, Unfortunately i am not abel to find complete tutorials on net with using Razor pages &amp; dapper. Most o fthe example &amp; tutorials are for ASP.net core MVC &amp; EF based.</p>
  
  <p>I am trying to convert <a href="https://www.c-sharpcorner.com/article/create-a-blazor-server-spa-with-dapper/" rel="nofollow noreferrer">Blazor dapper example</a> into asp.net core razor pages and i am stuck on how to show data on employee.cshtml.cs  </p>
  
  <pre><code>      using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  using Microsoft.AspNetCore.Mvc;
  using Microsoft.AspNetCore.Mvc.RazorPages;
  using BookListRazor.Data;
  using BookListRazor.Model;
  
  namespace BookListRazor.Pages
  {
      public class EmployeeModel : PageModel
      {
  
          private readonly SqlConnectionConfiguration _configuration;
          public EmployeeModel(SqlConnectionConfiguration configuration)
          {
              _configuration = configuration;
          }
          public IEnumerable&lt;Employee&gt; EmployeeList{ get; set; }
  
          public IEmployeeService _employeeService;
  
  
          public async Task OnGet()
          {
  
              EmployeeList = await _employeeService.GetEmployees();
          }
      }
  }
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/KYu2w.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KYu2w.jpg" alt="enter image description here"></a>
  I am not able to make a call to <code>GetEmployees()</code> function in <code>EmployeeDapperService.cs</code> class. I need help so that i can list all employee and from there i will also work on Insert &amp; Update.</p>
  
  <p>Below is the complete code for project </p>
  
  <p>Folder Structure &amp; files</p>
  
  <pre><code>-Data
  --EmployeeDapperService.cs
  --IEmployeeService.cs
  --SqlConnectionConfiguration.cs
  
  -Model
  --Employee.cs
  
  -Pages
  --Index.cshtml
  --Employee.cshtml
  
  Startup.cs
  </code></pre>
  
  <p><strong>EmployeeDapperService.cs</strong></p>
  
  <pre><code>using Dapper;
  using Microsoft.Data.SqlClient;
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Linq;
  using System.Threading.Tasks;
  using BookListRazor.Model;
  using BookListRazor.Data;
  
  namespace BookListRazor.Data
  {
      public class EmployeeDapperService : IEmployeeService
      {
          private readonly SqlConnectionConfiguration _configuration;
          public EmployeeDapperService(SqlConnectionConfiguration configuration)
          {
              _configuration = configuration;
          }
          public async Task&lt;bool&gt; CreateEmployee(Employee employee)
          {
              using (var conn = new SqlConnection(_configuration.Value))
              {
                  const string query = @"insert into dbo.Employees (Id,Name,Department,Designation,Company,City) values(@Id,@Name,@Department,@Designation,@Company,@City)";
                  conn.Open();
                  try
                  {
                      await conn.ExecuteAsync(query, new { Id = Guid.NewGuid().ToString(), employee.Name, employee.Department, employee.Designation, employee.Company, employee.City }, commandType: CommandType.Text);
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      conn.Close();
                  }
              }
              return true;
          }
  
          public async Task&lt;bool&gt; DeleteEmployee(string id)
          {
              using (var conn = new SqlConnection(_configuration.Value))
              {
                  const string query = @"delete dbo.Employees where Id=@Id";
                  conn.Open();
                  try
                  {
                      await conn.ExecuteAsync(query, new { id }, commandType: CommandType.Text);
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      conn.Close();
                  }
              }
              return true;
          }
  
          public async Task&lt;bool&gt; EditEmployee(string id, Employee employee)
          {
              using (var conn = new SqlConnection(_configuration.Value))
              {
                  const string query = @"update dbo.Employees set Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, City = @City where Id=@Id";
                  conn.Open();
                  try
                  {
                      await conn.ExecuteAsync(query, new { employee.Name, employee.Department, employee.Designation, employee.Company, employee.City, id }, commandType: CommandType.Text);
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      conn.Close();
                  }
              }
              return true;
          }
  
          public async Task&lt;List&lt;Employee&gt;&gt; GetEmployees()
          {
              IEnumerable&lt;Employee&gt; employees;
              using (var conn = new SqlConnection(_configuration.Value))
              {
                  const string query = @"select * from dbo.Employees";
  
                  conn.Open();
                  try
                  {
                      employees = await conn.QueryAsync&lt;Employee&gt;(query, commandType: CommandType.Text);
  
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      conn.Close();
                  }
  
              }
              return employees.ToList();
          }
  
          public async Task&lt;Employee&gt; SingleEmployee(string id)
          {
              Employee employee = new Employee();
              using (var conn = new SqlConnection(_configuration.Value))
              {
                  const string query = @"select * from dbo.Employees where Id=@Id";
  
                  conn.Open();
                  try
                  {
                      employee = await conn.QueryFirstOrDefaultAsync&lt;Employee&gt;(query, new { id }, commandType: CommandType.Text);
  
                  }
                  catch (Exception ex)
                  {
                      throw ex;
                  }
                  finally
                  {
                      conn.Close();
                  }
  
              }
              return employee;
          }
      }
  }
  </code></pre>
  
  <p><strong>IEmployeeService.cs</strong></p>
  
  <pre><code>using BookListRazor.Model;
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  
  namespace BookListRazor.Data
  {
      public interface IEmployeeService
      {
          Task&lt;List&lt;Employee&gt;&gt; GetEmployees();
          Task&lt;bool&gt; CreateEmployee(Employee employee);
          Task&lt;bool&gt; EditEmployee(string id, Employee employee);
          Task&lt;Employee&gt; SingleEmployee(string id);
          Task&lt;bool&gt; DeleteEmployee(string id);
      }
  }
  </code></pre>
  
  <p><strong>SqlConnectionConfiguration.cs</strong></p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  
  namespace BookListRazor.Data
  {
      public class SqlConnectionConfiguration
      {
          public SqlConnectionConfiguration(string value) =&gt; Value = value;
          public string Value { get; }
      }
  }
  </code></pre>
  
  <p><strong>Employee.cs</strong></p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  
  namespace BookListRazor.Model
  {
      public class Employee
      {
          public string Id { get; set; }
          public string Name { get; set; }
          public string Department { get; set; }
          public string Designation { get; set; }
          public string Company { get; set; }
          public string City { get; set; }
  
      }
  }
  </code></pre>
  
  <p><strong>Startup.cs</strong> partial code</p>
  
  <pre><code> // This method gets called by the runtime. Use this method to add services to the container.
      public void ConfigureServices(IServiceCollection services)
      {
          services.AddDbContext&lt;ApplicationDbContext&gt;(option =&gt; option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
  
  
  services.AddTransient&lt;IEmployeeService, EmployeeDapperService&gt;();
  var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));
          services.AddSingleton(sqlConnectionConfiguration);
          services.AddControllersWithViews();
          services.AddRazorPages().AddRazorRuntimeCompilation();
  
      }
  </code></pre>
  
  <p><strong>Employee.cshtml.cs</strong></p>
  
  <pre><code>    using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  using Microsoft.AspNetCore.Mvc;
  using Microsoft.AspNetCore.Mvc.RazorPages;
  using BookListRazor.Data;
  using BookListRazor.Model;
  
  namespace BookListRazor.Pages
  {
      public class EmployeeModel : PageModel
      {
  
          //for Dapper
          private readonly SqlConnectionConfiguration _configuration;
          public IEnumerable&lt;Employee&gt; EmployeeList{ get; set; }
  
          public readonly IEmployeeService _employeeService;
  
          public async Task OnGet()
          {
              // Employees = await employees.
  
              // Employees = await 
  
              EmployeeList = await _employeeService.GetEmployees();
          }
      }
  }
  </code></pre>