Query user in Sql server

Question

As I do to return instead of a query through a fixed user, the code was in the database to query the users and return it. I use Sql Server with aspnet core 3.1 application

Someone who can help me in this bank matter?

public static usuario Get(string Email, string Senha)
    {
        var users = new List<usuario>();
        users.Add(new usuario { id = 1, Nome = "Cachorro", Email = "[email protected]", Senha = "123456", Role = "manager" });
        users.Add(new usuario { id = 2, Nome = "Gato", Email = "[email protected]", Senha = "654321", Role = "employee" });
        return users.Where(x => x.Email.ToLower() == Email.ToLower() && x.Senha == x.Senha).FirstOrDefault();
    }

This code I already bring the user and password in the application, but I would like it to search the database?

Answer 1

For you to have access to SQL SERVER set the connection in appsettings.json

"ConnectionStrings": {
"DefaultConnection": "server=mssql.servidor.com.br;user=usuario;password=senha;database=banco;Persist Security Info=True"

},

Creates the user class template in the Models folder

public class Usuario
{
    public int Id {get; set;}
    public string Nome {get; set;}
    public string UsuarioNome {get; set;}
    public string Senha {get; set;}
    public string Email {get; set;}
}

Create a context. By default the visual studio template creates the Applicationdbcontext

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    //aqui você instancia as classes que representam o modelo da sua aplicação, no caso o usuario e senha
    public DbSet<Usuario> Usuarios {get; set;}
} 

And finally in the startup.Cs class, use the context you created as a service

services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(
                Configuration.GetConnectionString("DefaultConnection")));

Call the Nuget manager to create the migration that will create the database.

The first command:

Add-Migration Inicial -Verbose

If everything is right, run the second:

Update-Database -Verbose

In the controller do the Independence Injection to have access to the class connected to the database

public class HomeController : Controller
{
    
    private readonly ApplicationDbContext _context;
    public HomeController(ApplicationDbContext context)
    {
        _context = context;
    } 

    public IActionResult Index()
    {

    }
    
    [HttpPost]
    [ValidateAntiForgeryToken]
    public IActionResult Index(string usuario, string senha)
    {
        var result = _context.Usuarios
            .Where(x=>x.UsuarioNome == usuario)
            .Where(x=>x.Senha == senha)
            .Count() // ou .FirstOrDefault(), depende do que você queira fazer.
    }
    ...
}

Remembering that this is a pimp way to do. See microsoft documentation, has everything there for you to know how to implement in the right way what you need and go beyond.