Note This is a simple example
Warning This code must not go to PRODUCTION
Install Nuget Packages
In this project we will develop a simple CRUD using .Net 6, Dapper and SQL Server. Let's go 🚀!
After creating the .NET 6 project, we need to add the nuget package to use Dapper.
Install package using the command below:
Install-Package Dapper -Version 2.0.123We also need to install package to connect the SQL Server. Install package using the command below:
Install-Package System.Data.SqlClient -Version 4.8.3Configure SQL Server (Docker)
To run SQL locally we will use Docker. Note the command below:
- We accept the terms of use: "ACCEPT_EULA=1"
- We set a password for SA: @Bruno@123
- We define the SA user
- Port Bind to port 1433 of the container to port 1433 of our computer
docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=Bruno@123" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sqlserver mcr.microsoft.com/ azure-sql-edge After running the command successfully, we can use a client of our choice to test the connection.Database creation
Run the command below to create the Database and Table
GO CREATE DATABASE Factory GO use factory CREATE TABLE Car ( Id int primary key identity, Name varchar(max), int year, Created at date and time ) GO > .Net 6 and Dapper Finally, we will start development using C#. 😂
We need to add our ConnectionString in appSettings:
{ "ConnectionStrings": { "Factory": "Data Source=::1;Database=Factory;User ID=sa;Password=Bruno@123;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*" }Let's create a folder called Entities and inside that folder we create a class called "Car". Basically this class should be a mirror of our Database.
public class car { public int Id { get; to define; } public string Name { get; to define; } public int Year { get; to define; } public DateTime CreatedAt { get; to define; } } The next step is the creation of our Controller Car. dependencies using Dapper; using DapperSQLServer.Entities; using Microsoft.AspNetCore.Mvc; using System.Data.SqlClient; Class namespace DapperSQLServer.Controllers { [Route("api/[controller]")] [ApiController] public class CarController: ControllerBase { private readonly IConfiguration _configuration; private read-only string _connectionString; public CarController (IConfiguration configuration) { _configuration = configuration; _connectionString = _configuration.GetConnectionString("Factory"); } [HttpGet] Public async Task<IActionResult> GetCars() { using var connection = new SqlConnection(_connectionString); var cars = await connection.QueryAsync<Car>("select * from Car"); return Ok(cars); } [HttpGet("{id}")] Public async Task<IActionResult> GetCarById(int id) { using var connection = new SqlConnection(_connectionString); var car = await connection.QueryFirstAsync<Car>("select * from Car where id = @Id", new { Id = id }); return Ok(car); } [HttpPost] Public async Task<IActionResult> CreateCar(Car request) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("enter values Car (name, year, createdAt) (@name, @year, @createdAt)", new { Name = request.Name, Year = order.Year, CreatedAt = DateTime.UtcNow }); returnOk(); } [HttpPut] Public async Task<IActionResult> UpdateCar(Car request) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("update Car set name = @Name, year = @Year", new { Name = request.Name, Year = order.Year }); return Ok(GetCarById(request.Id)); } [HttpDelete("{id}")] Public async Task<IActionResult> DeleteCarById(int id) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("delete Car where id = @Id", new { Id = id }); return Ok(GetCars()); } } }Our simple example is ready ::trophy::
Note Trata-se de um simples exemplo
Warning Este código não deve ir para PRODUÇÃO
> Instalando pacotes do Nuget
Neste projeto nós vamos desenvolver um simples CRUD usando .Net 6, Dapper e SQL Server. Vamos lá 🚀!
Depois de criando o projeto de API Web .Net 6, precisamos adicionar o pacote do nuget para usar o Dapper.
Instale o pacote usando o comando abaixo:
Install-Package Dapper -Version 2.0.123Precisamos também instalar o pacote para conectar com o SQL Server.ver. Instale o pacote usando o comando abaixo:
Install-Package System.Data.SqlClient -Version 4.8.3Configurando SQL Server (Docker)
Para rodar o SQL localmente utilizaremos Docker. Observe o comando abaixo.
- Aceitamos os termos de uso: "ACCEPT_EULA=1"
- Definimos uma senha para SA: "Bruno@123"
- Definimos o PID para Developer
- Definimos o usuário SA
- Porta bind para porta 1433 do container para a porta 1433 do nosso computador.
docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=Bruno@123" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sqlserver mcr.microsoft.com/azure-sql-edgeAfter executar o comando com sucesso, podemos usar um client de nossa preferência para testar a conexão.
Criação Bando de Dados
Execute o comando abaixo para criação do Banco de Dados e Tabela.
GO CREATE DATABASE Factory GO Use Factory CREATE TABLE Car ( Id int primary key identity, Name varchar(max), Year int, CreatedAt Datetime ) GO.Net 6 e Dapper
Finalmente iniciaremos o desenvolvimento usando C#. 😂
Precisamos adicionar nossa ConnectionString no appSettings:
{ "ConnectionStrings": { "Factory": "Data Source=::1;Database=Factory;User ID=sa;Password=Bruno@123;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*" }Vamos criar uma pasta chamada Entities e dentro dessa pasta criamos uma classe chamada "Car". Basicamente essa classe deve ser espelho do nosso Banco de Dados.
public class Car { public int Id { get; set; } public string Name { get; set; } public int Year { get; set; } public DateTime CreatedAt { get; set; } }Próximo passo é a criação da nossa Controller Car.
- Dependências
using Dapper; using DapperSQLServer.Entities; using Microsoft.AspNetCore.Mvc; using System.Data.SqlClient;- Classe
namespace DapperSQLServer.Controllers { [Route("api/[controller]")] [ApiController] public class CarController : ControllerBase { private readonly IConfiguration _configuration; private readonly string _connectionString; public CarController(IConfiguration configuration) { _configuration = configuration; _connectionString = _configuration.GetConnectionString("Factory"); } [HttpGet] public async Task<IActionResult> GetCars() { using var connection = new SqlConnection(_connectionString); var cars = await connection.QueryAsync<Car>("select * from Car"); return Ok(cars); } [HttpGet("{id}")] public async Task<IActionResult> GetCarById(int id) { using var connection = new SqlConnection(_connectionString); var car = await connection.QueryFirstAsync<Car>("select * from Car where id = @Id", new { Id = id }); return Ok(car); } [HttpPost] public async Task<IActionResult> CreateCar(Car request) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("insert into Car (name, year, createdAt) values (@name, @year, @createdAt)", new { Name = request.Name, Year = request.Year, CreatedAt = DateTime.UtcNow }); return Ok(); } [HttpPut] public async Task<IActionResult> UpdateCar(Car request) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("update Car set name = @Name, year = @Year", new { Name = request.Name, Year = request.Year }); return Ok(GetCarById(request.Id)); } [HttpDelete("{id}")] public async Task<IActionResult> DeleteCarById(int id) { using var connection = new SqlConnection(_connectionString); await connection.ExecuteAsync("delete Car where id = @Id", new { Id = id }); return Ok(GetCars()); } } }Pronto, nosso simples exemplo está pronto. ::trophy::