ASP.NET Core display all rows in database on page

I am trying to display every row from a database on a website with ASP.NET Core MVC, but I cannot find any source on how to do it.. This is what Ive tried to do but I got stuck:

public IActionResult Index()
        {
            connection.Open();
            command.Connection = connection;

            command.CommandText = "SELECT COUNT(*) FROM Users;";
            var rows = Convert.ToInt32(command.ExecuteReader());
            command.Dispose();

            List<UserModel> users = new List<UserModel>();

            for(int i = 0; i <= rows; i++)
            {
                users.Add(new UserModel(ID, "", ""));
            }

            command.CommandText = "SELECT * FROM Users";
            dataReader = command.ExecuteReader();




            return View();
            
        }

My Database is structured like this: Id, Username, Password, PasswordHash, but I only want to display Username to begin with.

If you have any sources or ideas it would be very appriciated! Thanks beforehand!

Best Regards Max

Answer

I recommend you to use the biggest ORM for .NET, Entity Framework.

Create this

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options): base(options)
    {
    }

    public DbSet<UserModel> Users { get; set; }
}

Add to the ConfigureServices method in Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(
        options => options.UseSqlServer({"your_connection_string"}));
}

On your controller

public class YourController : Controller
{
    private ApplicationDbContext ApplicationDbContext { get; }

    public YourController(ApplicationDbContext applicationDbContext)
    {
        ApplicationDbContext = applicationDbContext;
    }

    public async Task<IActionResult> Index()
    {
        var users = await ApplicationDbContext.Users.ToListAsync();
        return View(users);
    }
}

Then, on your view

@model List<YourNamespace.UserModel>

<table>
    <thead>
        <tr>
            <th>Name</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var user in Model)
        {
            <tr>
                <th>@user.Name</th>
            </tr>
        }
    </tbody>
</table>

References https://learn.microsoft.com/pt-br/ef/core/dbcontext-configuration/