LevelcodeLicenseAPP/DataAccess/DashboardDAL.cs

using System.Data.SqlClient;
using LevelCode.License.DataAccess;

namespace Levelcode_licence.DataAccess
{
    public class DashboardDAL
    {
        private readonly DbConexao _db;

        public DashboardDAL(DbConexao db)
        {
            _db = db;
        }

        public int TotalLicencas()
        {
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) FROM Licencas",
                _db.Conexao);

            _db.Abrir();
            int total = (int)cmd.ExecuteScalar();
            _db.Fechar();

            return total;
        }

        public int LicencasAtivas()
        {
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) FROM Licencas WHERE Status = 1",
                _db.Conexao);

            _db.Abrir();
            int total = (int)cmd.ExecuteScalar();
            _db.Fechar();

            return total;
        }

        public int LicencasInativas()
        {
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) FROM Licencas WHERE Status = 0",
                _db.Conexao);

            _db.Abrir();
            int total = (int)cmd.ExecuteScalar();
            _db.Fechar();

            return total;
        }

        public int MaquinasAtivas()
        {
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) FROM Licencas_Maquinas WHERE Status = 1",
                _db.Conexao);

            _db.Abrir();
            int total = (int)cmd.ExecuteScalar();
            _db.Fechar();

            return total;
        }

        public int LicencasNoLimite()
        {
            SqlCommand cmd = new SqlCommand(
                @"
                SELECT COUNT(*)
                FROM Licencas L
                WHERE (
                    SELECT COUNT(*)
                    FROM Licencas_Maquinas M
                    WHERE M.LicencaId = L.IdLicenca
                      AND M.Status = 1
                ) >= L.LimiteMaquinas",
                _db.Conexao);

            _db.Abrir();
            int total = (int)cmd.ExecuteScalar();
            _db.Fechar();

            return total;
        }
    }
}