How do I handle sql connection and data handling in razor pages with mysql

Question

What is the "Correct" way of handling database connection and getting data from table when working in razor pages?

This setup is working just fine but what happens when a lot of users are on the site, will it get a sql connection error?, I have added the AddTransient to both the manager and sql classes and from what I can understand AddTransient creates a new instance of that object everytime its needed and then destroys it again

So is there a better way of handling sql connection and data handling with razor pages?

Currently I'm having this setup:

SqlDataAccess

 private readonly IConfiguration _config;
        MySqlConnection conn;
        MySqlCommand cmd;
        MySqlDataReader reader;
        public string ConnString { get; private set; }

        public SqlDataAccess(IConfiguration config)
        {
            _config = config;
            ConnString = _config.GetConnectionString("Default");
        }
        public List<Product> GetProducts()
        {
            try
            {
                List<Product> products = new List<Product>();
                conn = new MySqlConnection(ConnString);
                conn.Open();
                cmd = new MySqlCommand("query", conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    products.Add(new Product((string)reader["PartNumber"], (string)reader["Name"], (int)reader["Stock"], (decimal)reader["Price"]));
                }
                conn.Close();
                return products;
            }
            catch (Exception)
            {
                throw new Exception();
            }
        }

ProductManager

 private readonly ISqlDataAccess _db;
         public ProductManager(ISqlDataAccess db)
 {
     _db = db;
 }
 public List<Product> GetProducts()
 {
     return _db.GetProducts();
 }

Program.cs

public static void Main(string[] args)
 {
     var builder = WebApplication.CreateBuilder(args);

     // Add services to the container.
     builder.Services.AddRazorPages();
     builder.Services.AddTransient<ISqlDataAccess, SqlDataAccess>();
     builder.Services.AddTransient<IProductManager, ProductManager>();
     var app = builder.Build();

     // Configure the HTTP request pipeline.
     if (!app.Environment.IsDevelopment())
     {
         app.UseExceptionHandler("/Error");
         // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
         app.UseHsts();
     }

     app.UseHttpsRedirection();
     app.UseStaticFiles();

     app.UseRouting();

     app.UseAuthorization();

     app.MapRazorPages();

     app.Run();
}

Answer 1

You close the connection each time so you won't get any memory leaks. In fact, the connection will get returned to a connection pool for reuse to save the cost of instantiating it from new each time. So from a performance point of view, there is no issue with your approach.

As to "better" ways to manage data access, that's a matter of opinion (I would recommend use of an ORM, myself) and is likely to get your question closed ;-)