Bio.cs

using Dapper;
using Npgsql;
using WorkoutTrackingWebApp.Models;

namespace WorkoutTrackingWebApp.Data
{
    public class BiometryDataProvider
    {
        private readonly string _dbConnectionString;
        
        public BiometryDataProvider()
        {
            _dbConnectionString = SettingsManager.GetDbConnectionString();
        }

        public List<BiometryTypes> GetBiometryTypes()
        {
            const string query = "SELECT id, type_name FROM public.biometry_types";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.Query<BiometryTypes>(query).ToList();
        }
        
        public BiometryTypes GetBiometryType(int id)
        {
            const string query = "SELECT id, type_name FROM public.biometry_types WHERE id = @id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.QuerySingle<BiometryTypes>(query, new { id });
        }

        public bool AddBiometryTypes(BiometryTypes request)
        {
            const string query = @"INSERT INTO public.biometry_types (type_name)
                                  VALUES(@BiometryType)";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, request);
            return affectedRows > 0;
        }

        public bool UpdateBiometryTypes(BiometryTypes request)
        {
            const string query = @"UPDATE public.biometry_types 
                                 SET type_name = @BiometryType 
                                 WHERE id = @Id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, request);
            return affectedRows > 0;
        }

        public bool DeleteBiometryTypes(int typeId)
        {
            const string query = "DELETE FROM public.biometry_types WHERE id = @typeId";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, new { typeId });
            return affectedRows > 0;
        }

/* весь ClientBiometrics вывешивается в 500

        public List<ClientBiometriсs> GetClientBiometriсs()
        {
            const string query = "SELECT id, client_id, date FROM public.client_biometry";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.Query<ClientBiometriсs>(query).ToList();
        }

        public ClientBiometriсs GetClientBiometry(int id)
        {
            const string query = "SELECT id, client_id, date FROM public.client_biometry WHERE id = @id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.QuerySingle<ClientBiometriсs>(query, new { id });
        }

        public bool AddClientBiometriсs(ClientBiometriсs request)
        {
            const string query = @"INSERT INTO public.client_biometry (client_id, date)
                                  VALUES(@ClientId, @Timestamp)";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            try
            {
                var affectedRows = connection.Execute(query, request);
                return affectedRows > 0;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateClientBiometriсs(ClientBiometriсs request)
        {
            const string query = @"UPDATE public.client_biometry 
                                 SET client_id = @ClientId,
                                     date = @Timestamp 
                                 WHERE id = @Id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, request);
            return affectedRows > 0;
        }

        public bool DeleteClientBiometriсs(int biometryId)
        {
            const string query = "DELETE FROM public.client_biometry WHERE id = @biometryId";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, new { biometryId });
            return affectedRows > 0;
        }
====================== */

        public List<ClientBiometryValues> GetClientBiometryValues()
        {
            const string query = @"SELECT id, client_biometry_id as clientBiometryId, 
                                 biometry_type_id as biometryTypeId, value 
                                 FROM public.client_biometry_values";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.Query<ClientBiometryValues>(query).ToList();
        }
        
        public ClientBiometryValues GetClientBiometryValue(int id)
        {
            const string query = @"SELECT id, client_biometry_id as clientBiometryId, 
                                 biometry_type_id as biometryTypeId, value 
                                 FROM public.client_biometry_values 
                                 WHERE id = @id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            return connection.QuerySingle<ClientBiometryValues>(query, new { id });
        }
        // и только здесь еще вываливается в out of range ex
        public bool AddClientBiometryValues(ClientBiometryValues request)
        {
            const string query = @"INSERT INTO public.client_biometry_values 
                                  (client_biometry_id, biometry_type_id, value)
                                  VALUES(@clientBiometryId, @biometryTypeId, @Value)";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            try
            {
                var affectedRows = connection.Execute(query, request);
                return affectedRows > 0;
            }
            catch
            {
                return false;
            }
        }
        
        public bool UpdateClientBiometryValues(ClientBiometryValues request)
        {
            const string query = @"UPDATE public.client_biometry_values 
                                 SET client_biometry_id = @clientBiometryId, 
                                     biometry_type_id = @biometryTypeId,
                                     value = @Value 
                                 WHERE id = @Id";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, request);
            return affectedRows > 0;
        }
        
        public bool DeleteClientBiometryValues(int valuesId)
        {
            const string query = "DELETE FROM public.client_biometry_values WHERE id = @valuesId";
            
            using var connection = new NpgsqlConnection(_dbConnectionString);
            var affectedRows = connection.Execute(query, new { valuesId });
            return affectedRows > 0;
        }
    }
}