BathhouseManagment/Models/Database/DiscountRepository.cs

using System;
using System.Collections.Generic;
using BathHouseManagmet.Models;
using BathHouseManagmet.Models.Database;
using Microsoft.Extensions.Options;
using Microsoft.VisualBasic;
using MySqlConnector;

namespace BathHouseManagmet.Database;

public class DiscountRepository : BaseRepository<Discount>, IDisposable
{
    public DiscountRepository()
    {
        OpenConnection();
    }
    
    public override List<Discount> GetPage(int pageNumber, int pageSize)
    {
        List<Discount> result = new();
        string sql = "select * from `discount` limit @limit offset @offset";
        try
        {
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@limit", pageNumber);
            command.Parameters.AddWithValue("@offset", pageNumber * pageSize);
            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                result.Add(new Discount
                {
                    Id = reader.GetInt32("id"),
                    Name = reader.GetString("name"),
                    Description = reader.GetString("description"),
                    DiscountPercent =  reader.GetInt32("discount_percent"),
                    StartDate = reader.GetDateTime("start_date"),
                    EndDate = reader.GetDateTime("end_date")
                });
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        return result;
    }

    public override bool Add(Discount discount)
    {
        string sql = 
            "insert into `discount` (`id`, `name`, `description`, `discount_percent`, `start_date`, `end_date`) values (0, @name, @description, @discount_percent, @start_date, @end_date)";
        try
        {
            using (var command = new MySqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@id", discount.Id);
                command.Parameters.AddWithValue("@name", discount.Name);
                command.Parameters.AddWithValue("@description", discount.Description);
                command.Parameters.AddWithValue("@discount_percent", discount.DiscountPercent);
                command.Parameters.AddWithValue("@start_date", discount.StartDate);
                command.Parameters.AddWithValue("@end_date", discount.EndDate);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return false;
        }
        return false;
    }

    public override bool Update(Discount discount)
    {
        string sql = 
            "update `discount` set name = @name, description = @description, discount_percent = @discount_percent, start_date = @start_date, end_date = @end_date";
        try
        {
            using (var command = new MySqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@id", discount.Id);
                command.Parameters.AddWithValue("@name", discount.Name);
                command.Parameters.AddWithValue("@description", discount.Description);
                command.Parameters.AddWithValue("@discount_percent", discount.DiscountPercent);
                command.Parameters.AddWithValue("@start_date", discount.StartDate);
                command.Parameters.AddWithValue("@end_date", discount.EndDate);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return false;
        }
        return false;
    }

    public override bool Delete(Discount discount)
    {
        string sql = "delete from `discount` where `id` = " + discount.Id;
        try
        {
            using (var command = new MySqlCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return false;
        }
        return true;
    }

    public int GetRowsCount()
    {
        string sql = "select count(id) from `discount`";
        try
        {
            using var command = new MySqlCommand(sql, connection);
            return Convert.ToInt32(command.ExecuteScalar());
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return 0;
        }
    }

    public void Dispose()
    {
        CloseConnection();
        base.Dispose();
    }
}