How to Web API .Net Core Basics to Advanced Part3 Data Access Layer

Welcome back!

Now I wont be surprised if smart people like you have already figured out what a mess single tier application can be as the image below shows.

If you look at the image above, we have a presentation logic, business logic and data access problems rolled in one single tier. Imagine, we need to make changes to build new features as demand on our software increases. Now any changes we make will require major code change because it will affect all three layers.

To solve these problems we use "separation of concerns" as shown in the image below. We are isolating our presentation data (Controllers) from our business layer (Service Layer) and our data layer (Repository). This allows us to easily make changes to all three layers for example, business layer has no knowledge about data layer and how it handles data and so by introducing separation of concerns gives us a lot of flexibility. If you will like to read more about Importance Of Data Access Layer

Let us now get down to business and implement our Data Access Layer. We should create a folder called Repository and add a class file CompanyRepository.cs inside the folder. I am going to create another folder inside Repository folder and call it Contract, where we going to add my interface class file and I am going to name it ICompanyRepository.cs surprise surprise :). If you will like to read more about Repository Pattern.

We can now go head and add some code to our interface to define the methods we want in our Data layer.

ICompanyRepository.cs

public interface ICompanyRepository
    {

        /// <summary>
        /// Return all companies including records marked as deleted and disabled
        /// </summary>
        /// <returns>Entites.Company</returns>
        Task<ICollection<Entities.Company>> GetAllCompaniesAsync();
        /// <summary>
        /// Return list of companies which are not marked as deleted.
        /// </summary>
        /// <returns>Entites.Company</returns>
        Task<ICollection<Entities.Company>> GetCompaniesAsync();
        /// <summary>
        /// Return list of companies which are marked as deleted
        /// </summary>
        /// <returns>Entites.Company</returns>
        Task<ICollection<Entities.Company>> GetDeletedCompaniesAsync();
        /// <summary>
        /// Return list of companies which are marked as disabled
        /// </summary>
        /// <returns>Entites.Company</returns>
        Task<ICollection<Entities.Company>> GetDisabledCompaniesAsync();
        /// <summary>
        /// Return a company record
        /// </summary>
        /// <param name="CompanyId"></param>
        /// <returns>Entites.Company</returns>
        Task<Entities.Company> GetCompanyByIDAsync(int CompanyId);
        /// <summary>
        /// Return a company record
        /// </summary>
        /// <param name="CompanyGUID"></param>
        /// <returns>Entites.Company</returns>
        Task<Entities.Company> GetCompanyByGUIDAsync(Guid CompanyGUID);
        /// <summary>
        /// Return True/False if record exist
        /// </summary>
        /// <param name="CompanyName"></param>
        /// <returns>bool</returns>
        Task<bool> CompanyExistAsync(string CompanyName);
        /// <summary>
        /// Return True/False if record exist
        /// </summary>
        /// <param name="Id"></param>
        /// <returns>bool</returns>
        Task<bool> CompanyExistAsync(int Id);
        /// <summary>
        /// Return bool if record exist
        /// </summary>
        /// <param name="CompanyGUID"></param>
        /// <returns>bool</returns>
        Task<bool> CompanyExistAsync(Guid CompanyGUID);
        /// <summary>
        /// Add a new record for company
        /// </summary>
        /// <param name="company"></param>
        /// <returns>bool</returns>
        Task<bool> CreateCompanyAsync(Entities.Company company);
        /// <summary>
        /// Update a record in db
        /// </summary>
        /// <param name="company"></param>
        /// <returns>bool</returns>
        Task<bool> UpdateCompanyAsync(Entities.Company company);
        /// <summary>
        /// Update a record as Deleted=True
        /// </summary>
        /// <param name="CompanyGUID"></param>
        /// <returns>bool</returns>
        Task<bool> SoftDeleteCompanyAsync(Guid CompanyGUID);
        /// <summary>
        /// Permanently remove a record from db
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        Task<bool> HardDeleteCompanyAsync(Entities.Company company);

    }

This is our CompanyRepository.cs which is interacting with our Database server and separating our Presentation and business logic.

public class CompanyRepository : ICompanyRepository
    {
        private readonly DataContext _dataContext;

        public CompanyRepository(DataContext dataContext)
        {
            this._dataContext = dataContext;
        }
        public async Task<bool> CompanyExistAsync(string CompanyName)
        {
            return await _dataContext.Companies.AnyAsync(Comp => Comp.CompanyName.Contains(CompanyName));
        }

        public async Task<bool> CompanyExistAsync(int Id)
        {
            return await _dataContext.Companies.AnyAsync(Comp => Comp.Id == Id);
        }

        public async Task<bool> CompanyExistAsync(Guid CompanyGUID)
        {
            return await _dataContext.Companies.AnyAsync(Comp => Comp.GUID == CompanyGUID);
        }

        public async Task<bool> CreateCompanyAsync(Company company)
        {
            await _dataContext.Companies.AddAsync(company);
            return await Save();
        }
        public async Task<bool> UpdateCompanyAsync(Company company)
        {
            _dataContext.Companies.Update(company);
            return await Save();
        }

        public async Task<ICollection<Company>> GetAllCompaniesAsync()
        {
            return await _dataContext.Companies.ToListAsync();
        }

        public async Task<ICollection<Company>> GetCompaniesAsync()
        {
            return await _dataContext.Companies.Where(Comp => Comp.IsDeleted == false).ToListAsync();
        }

        public async Task<Company> GetCompanyByGUIDAsync(Guid CompanyGUID)
        {
            return await _dataContext.Companies.FirstOrDefaultAsync(Comp => Comp.GUID == CompanyGUID);
        }

        public async Task<Company> GetCompanyByIDAsync(int CompanyId)
        {
            return await _dataContext.Companies.FirstOrDefaultAsync(Comp => Comp.Id == CompanyId);
        }

        public async Task<ICollection<Company>> GetDeletedCompaniesAsync()
        {
            return await _dataContext.Companies.Where(Comp => Comp.IsDeleted == true).ToListAsync();
        }

        public async Task<ICollection<Company>> GetDisabledCompaniesAsync()
        {
            return await _dataContext.Companies.Where(Comp => Comp.IsEnabled == false).ToListAsync();
        }

        public async Task<bool> HardDeleteCompanyAsync(Company company)
        {
            _dataContext.Remove(company);
            return await Save();
        }

        public async Task<bool> SoftDeleteCompanyAsync(Guid CompanyGUID)
        {
            var _exisitngCompany = await GetCompanyByGUIDAsync(CompanyGUID);

            if (_exisitngCompany != null)
            {
                _exisitngCompany.IsDeleted = true;
                return await Save();
            }
            return false;
        }

        private async Task<bool> Save()
        {
            return await _dataContext.SaveChangesAsync() >= 0 ? true : false;
        }

    }