More
    Home Blog Page 2

    Example of full API code and Error/Exception Handling (Chapter 9)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    (1) Example of search a client list

    [Authorize(Role.Admin, Role.Operator)]
    [HttpGet("list")]
    public async Task<IActionResult> GetClientList([FromQuery] ClientSearchCommand command)
    {
        // Rewritten by Sammy Cheng, 2022-05-11
        //checkAdmin(Guid.Empty);
        var response = new GenericResponseModel<ClientListModel>();
        var clients = await _clientService.GetAllClientsAsync(command);
        response.Data = _clientFactory.PrepareClientListResponseModel(response.Data, clients);            
        return Ok(response);
    }
    namespace BlueSky.Service.Models.Client
    {
        // Rewritten by Sammy Cheng, 2022-05-10
        /// <summary>
        /// Represents search options for clients
        /// </summary>
        public partial record ClientSearchCommand : BasePageableModel
        {
            /// <summary>客戶 Guid, 如網上注冊,客戶 Guid 同跟用戶編號一樣</summary>
            public Guid? EntityGuid { get; set; }
            /// <summary>上級客戶編號</summary>
            public Guid? MasterClientGuid { get; set; }
            /// <summary>業務員編號或協銷聯盟編號</summary>
            public string SalesCode { get; set; }
            /// <summary>客戶分類, 網上注冊用戶為 N</summary>
            public string ClientTypeCode { get; set; }
            /// <summary>客戶名稱, 如公司客戶則公司名稱</summary>
            public string ClientName { get; set; }
            /// <summary>國家 (地址)</summary>
            public string CountryCode { get; set; }
            /// <summary>州/省 (地址)</summary>
            public string StateProvinceCode { get; set; }
            /// <summary>市/縣</summary>
            public string CountyCode { get; set; }
            /// <summary>城/鎮</summary>
            public string City { get; set; }
            /// <summary>地址</summary>
            public string Address { get; set; }
            /// <summary>電郵地址</summary>
            public string Email { get; set; }
            /// <summary>手提電話</summary>
            public string Phone { get; set; }
            /// <summary>狀況 0-新客戶, 1-活動, -1-暫停,-2-已刪除</summary>
            public short? Status { get; set; }
            /// <summary>Show Active Only</summary>
            public bool ActiveOnly { get; set; } = true;
            /// <summary>Include Deleted Records</summary>
            public bool IncludeDeleted { get; set; } = false;
            /// <summary>
            /// Order By :      0 - CreateTimeUtc
            ///                 1 - Client Code
            ///                 2 - Client Name
            /// </summary>
            public int? OrderBy { get; set; }
        }
    }
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    
    // Rewritten by Sammy Cheng, 2022-05-11
    /// <summary>
    /// Represents a Client List Response Model
    /// </summary>
    namespace BlueSky.Service.Models.Client
    {
        public class ClientListModel
        {
            public IList<ClientModel> Clients { get; set; }
    
            public ClientListModel()
            {
                Clients = new List<ClientModel>();
            }
        }
        
    }
    
    namespace BlueSky.Service.Factories
    {
        public class ClientFactory : IClientFactory
        {
            #region Properties
    
            private readonly DataContext _context;
            private readonly IMapper _mapper;
            
            #endregion
    
            #region Ctor
    
            public ClientFactory(
                DataContext context,
                IMapper mapper)
            {
                _context = context;
                _mapper = mapper;
            }
    
            #endregion
    
            #region Utilities
            #endregion
    
            #region Methods
    
            public Client PrepareClientFromUpdateModel(Client response, ClientUpdateRequest model)
            {
                _mapper.Map<ClientUpdateRequest, Client>(model, response);
                return response;
            }
    
            public ClientModel PrepareClientResponseModel(ClientModel response, Client client)
            {
                response = _mapper.Map<ClientModel>(client);
                return response;
            }
    
            public ClientListModel PrepareClientListResponseModel(ClientListModel response, IPagedList<Client> list)
            {
                if (response == null)
                    response = new ClientListModel();
    
                response.Clients = _mapper.Map<List<ClientModel>>(list);
                return response;
            }
    
            #endregion
        }
    }
    public async Task<IPagedList<Client>> GetAllClientsAsync(ClientSearchCommand command)
    {
        // Rewrite by Sammy Cheng, 2022-05-11
        var clients = await _clientRepository.GetAllPagedAsync(query =>
        {
            if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
                query = query.Where(g => g.EntityGuid == command.EntityGuid);
            if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
                query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);
            if (String.IsNullOrEmpty(command.SalesCode))
                query = query.Where(s => s.SalesCode == command.SalesCode);
            if (string.IsNullOrEmpty(command.ClientTypeCode))
                query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
            if (string.IsNullOrEmpty(command.ClientName))
                query = query.Where(c => c.ClientName.Contains(command.ClientName));
            if (string.IsNullOrEmpty(command.CountryCode))
                query = query.Where(c => c.CountryCode == command.CountryCode);
            if (string.IsNullOrEmpty(command.StateProvinceCode))
                query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
            if (string.IsNullOrEmpty(command.CountyCode))
                query = query.Where(c => c.CountyCode == command.CountyCode);
            if (string.IsNullOrEmpty(command.City))
                query = query.Where(c => c.City.Contains(command.City));
            if (string.IsNullOrEmpty(command.Address))
                query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
            if (string.IsNullOrEmpty(command.Email))
                query = query.Where(e => e.Email.Contains(command.Email));
            if (string.IsNullOrEmpty(command.Phone))
                query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) || p.Fax2.Contains(command.Phone));
            if (command.Status.HasValue)
                query = query.Where(s => s.Status == command.Status);
            switch (command.OrderBy)
            {
                case 1:
                    query = query.OrderBy(o => o.ClientCode);
                        break;
                    case 2:
                        query = query.OrderBy(o => o.ClientName);
                        break;
                    default:                    
                        query = query.OrderBy(o => o.CreateTimeUtc);
                        break;
                    }
    
    
                    return query;
           }, pageIndex: command.PageIndex, pageSize: command.PageSize, activeOnly: command.ActiveOnly, includeDeleted: command.IncludeDeleted );
    
           return clients;
    }

    This example has complete to demostrate how to create a API for search client and reture ClientListModel

    (2) Example of Updating a Client

    /// <summary>
    /// 更新當前客戶資料, 如客戶可使用 Cookie, 則不用指明 ClientGuid
    /// Update current client information record
    /// </summary> 
    /// <remarks>
    /// 
    /// 驗證 : Bearer Token + Cookie / Admin to update other client
    /// 
    /// 請求路由: api/client/update/{ClientGuid}
    /// 
    /// 請求方法: POST
    ///     
    /// </remarks>
    /// <response code="200">Ok Success</response>
    /// <response code="400">Error:304    Client record not found or UserGuid not correct. 客戶資料找不到, 可能是 UserGid 不正確</response>
    /// <response code="401">Unauthorized</response>
    [Authorize]
    [HttpPost("update/{ClientGuid}")]
    public async Task<IActionResult> UpdateClient([FromBody]ClientUpdateRequest model, Guid ClientGuid)
    {
        // Rewritten by Sammy Cheng, 2022-05-11
        var response = new GenericResponseModel<ClientModel>();
        // accept token from request body or cookie
        ClientGuid = getUserGuidCookie(ClientGuid);
    
        var client = await _clientService.UpdateClientFromModelAsync(model, ClientGuid);
        response.Data = _clientFactory.PrepareClientResponseModel(response.Data, client);
        return Ok(response);
    }
    namespace BlueSky.Service.Models.Client
    {
        public class ClientUpdateRequest
        {
            /// <summary>客戶 Guid, 如網上注冊,客戶 Guid 同跟用戶編號一樣</summary>
            public Guid EntityGuid { get; set; }
            /// <summary>上級客戶編號</summary>
            public Guid MasterClientGuid { get; set; }
            /// <summary>業務員編號或協銷聯盟編號</summary>
            public string SalesCode { get; set; }
            /// <summary>簡稱</summary>
            public string ShortName { get; set; }
            /// <summary>客戶分類, 網上注冊用戶為 N</summary>
            public string ClientTypeCode { get; set; }
            /// <summary>客戶名稱, 如公司客戶則公司名稱</summary>
            public string ClientName { get; set; }
            /// <summary>姓氏</summary>
            public string FirstName { get; set; }
            /// <summary>名子</summary>
            public string LastName { get; set; }
            /// <summary>國家 (地址)</summary>
            public string CountryCode { get; set; }
            /// <summary>州/省 (地址)</summary>
            public string StateProvinceCode { get; set; }
            /// <summary>市/縣</summary>
            public string CountyCode { get; set; }
            /// <summary>城/鎮</summary>
            public string City { get; set; }
            /// <summary>地址(一行)</summary>
            public string Add1 { get; set; }
            /// <summary>地址(二行)</summary>
            public string Add2 { get; set; }
            /// <summary>郵政編碼</summary>
            public string ZipCode { get; set; }
            /// <summary>預設帳單地址 ID, 跟 Addres ID</summary>
            public int? DefBillAddressId { get; set; }
            /// <summary>預設送貨地址 ID, 跟 Address ID</summary>
            public int? DefShipAddressId { get; set; }
            /// <summary>電郵地址</summary>
            public string Email { get; set; }
            /// <summary>電話 1</summary>
            public string Tel1 { get; set; }
            /// <summary>電話 2</summary>
            public string Tel2 { get; set; }
            /// <summary>傳真 1</summary>
            public string Fax1 { get; set; }
            /// <summary>傳真 2</summary>
            public string Fax2 { get; set; }
            /// <summary>手提電話</summary>
            public string Mobile { get; set; }
            /// <summary>貨幣</summary>
            public string CurrencyCode { get; set; }
            /// <summary>狀況 0-新客戶, 1-活動, -1-暫停,-2-已刪除</summary>
            public short? Status { get; set; }
            /// <summary>個人或公司網址</summary>
            public string WebPage { get; set; }
            /// <summary>備註</summary>
            public string Remarks { get; set; }
    
        }
    }
    namespace BlueSky.Service.Factories
    {
        public class ClientFactory : IClientFactory
        {
            #region Properties
    
            private readonly DataContext _context;
            private readonly IMapper _mapper;
            
            #endregion
    
            #region Ctor
    
            public ClientFactory(
                DataContext context,
                IMapper mapper)
            {
                _context = context;
                _mapper = mapper;
            }
    
            #endregion
    
            #region Utilities
            #endregion
    
            #region Methods
    
            public Client PrepareClientFromUpdateModel(Client response, ClientUpdateRequest model)
            {
                _mapper.Map<ClientUpdateRequest, Client>(model, response);
                return response;
            }
    
            public ClientModel PrepareClientResponseModel(ClientModel response, Client client)
            {
                response = _mapper.Map<ClientModel>(client);
                return response;
            }
    
            public ClientListModel PrepareClientListResponseModel(ClientListModel response, IPagedList<Client> list)
            {
                if (response == null)
                    response = new ClientListModel();
    
                response.Clients = _mapper.Map<List<ClientModel>>(list);
                return response;
            }
    
            #endregion
        }
    }
    private async Task ClientUpdateAsync(Client client)
    {
        try
        {
            await _clientRepository.UpdateAsync(client);
        }catch( Exception E)
        {
            throw new AppException(_context.errorList.Response(ErrorCode.DataUpdateError, _detail: E.Message).ToString());
    
        }
    }
    
    public async Task<Client> UpdateClientFromModelAsync(ClientUpdateRequest model, Guid ClientGuid)
    {
        // Rewrite by Sammy Cheng, 2022-05-11
        var client = await GetClientAsync(ClientGuid);
        client = _clientFactory.PrepareClientFromUpdateModel(client, model);
    
        await ClientUpdateAsync(client);
        return client;
    }

    For this example, it demonstrates how to use get the model and update to Database by repository.

    Error And Exception Handling

    Inside the Framework, to handle the error, we can easy by throwing a AppException to return a Bad Request response. The framework has created a “errorList” class that store list of most commonly used error code and description. To call the error, just simple use like this

    try
    {
        await _clientRepository.UpdateAsync(client);
    }catch( Exception E)
    {
        throw new AppException(_context.errorList.Response(ErrorCode.DataUpdateError, _detail: E.Message).ToString());
    }

    The errorList class current declared in the DataContent, but will move to as a new service in coming version. We will create more information once the error handle service completed.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Creating Controller Classes with API endpoint (Ch.8)

    Next : Chapter 10 : PostMan Documentation Guide

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Creating Controller Classes as API endpoint (Chapter 8)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    After built the services and factories, we will go to the final step, creating the API endpoint controller.

    Create the Controller

    In .NET Core, it is very easy to create the Controller class, the declaration should like this:

    using Microsoft.AspNetCore.Mvc;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using AutoMapper;
    using Microsoft.Extensions.Options;
    using Microsoft.AspNetCore.Authorization;
    using System.Text.Json;
    using BlueSky.Core.Configuration;
    using BlueSky.Service.Models.Client;
    using BlueSky.Core;
    using BlueSky.Service.Clients;
    using BlueSky.Api.Controllers;
    using BlueSky.Service.Factories;
    
    namespace BlueSky.Api.Controllers
    {
        [Route("api/clients")]
        [ApiController]
        public class ClientController : BaseController
        {
            #region Properties
    
            private readonly IClientService _clientService;
            private readonly IClientFactory _clientFactory;
            private readonly IMapper _mapper;
            private readonly AppSettings _appSettings;
    
            #endregion
    
            #region Ctor
    
            public ClientController(
                       IClientService clientService,
                       IClientFactory clientFactory,
                       IMapper mapper,
                       IOptions<AppSettings> appSettings)
            {
                _clientService = clientService;
                _clientFactory = clientFactory;
                _mapper = mapper;
                _appSettings = appSettings.Value;
            }
    
            #endregion
    
            #region Methods
            ..........
            #endregion
        }
    }

    First of all, use should declare the endpoint route path and inherit the BaseController class

    [Route("api/clients")]
    [ApiController]
    public class ClientController : BaseController
    {

    For this route, frontend should called this controller by “https://mysite.com/api/clients/…..”

    Then we write the first API function

            /// <summary>
            /// 更新當前客戶資料, 如客戶可使用 Cookie, 則不用指明 ClientGuid
            /// Update current client information record
            /// </summary> 
            /// <remarks>
            /// 
            /// Authorize : Bearer Token + Cookie / Admin to update other client
            /// 
            /// Route : api/client/update/{ClientGuid}
            /// 
            /// HTTP Method : POST
            ///     
            /// </remarks>
    
            /// <param name="model">Client Update Model</param>
            /// <param name="ClientGuid">Client Guid Identifier</parm>
            /// <response code="200">Ok Success</response>
            /// <response code="400">Error:304    Client record not found or UserGuid not correct. 客戶資料找不到, 可能是 UserGid 不正確</response>
            /// <response code="401">Unauthorized</response>
            [Authorize]
            [HttpPost("update/{ClientGuid}")]
            public async Task<IActionResult> UpdateClient([FromBody]ClientUpdateRequest model, Guid ClientGuid)
            {
                // Rewritten by Sammy Cheng, 2022-05-11
                var response = new GenericResponseModel<ClientModel>();
                // accept token from request body or cookie
                ClientGuid = getUserGuidCookie(ClientGuid);
    
                var client = await _clientService.UpdateClientFromModelAsync(model, ClientGuid);
                response.Data = _clientFactory.PrepareClientResponseModel(response.Data, client);
                return Ok(response);
            }

    (A) Swagger Comments

    For this Framework, we have integrated the Swagger documentation generator. So, the remark comments will directly generated as http document so that it is easy to work by frontend developers. Also, it reduces our workload on writting the development document. If you have added the comments as above, you will see if you run the project.

    For more information about using Swagger, please find in Swagger training materials.

    (B) API Endpoint

    [Authorize]
    [HttpPost("update/{ClientGuid}")]
    public async Task<IActionResult> UpdateClient([FromBody]ClientUpdateRequest model, Guid ClientGuid)

    For defining the endpoint name, just simply [HttpGet(“apipath&name”/”parameters)]. For http method, there are four method avaliable

    [HttpPost(” “)], [HttpGet(” “)], [HttpPut(” “)], [HttpDelete(” “)]

    But however, avoid using Put and Delete because it will conflict with server WebDAV service.

    Usually, we use Post method when user send data update request and collect data in JSon class or parameters. We use Get method when frontend retrieve data for display with parameters. For some frontend engine limitation, it may restricted than Get method can only send parameters by query but not support by JSon body.

    (C) Passing Parameters

    (1) Pass parameters by inline parameter in route: you can declare the parameter at the end of the endpoint name inside { } backet like this, the parameters will be pass to the function with the same name:

    [HttpGet("info/{ClientGuid}")]
    public async Task<IActionResult> GetClientInfo(Guid ClientGuid)

    (2) Pass parameters by query parameters : you can declare to accept parameters by using query like this.

    [HttpGet("list")]
    public async Task<IActionResult> GetClientList([FromQuery] ClientSearchCommand command)

    Even ClientSearchCommand is a Json class, add [FromQuery] tag to the parameters and you can use it as query parameters. To call this fucntion, it will be:

    https://ccoms.com/client/list?Name=ABCD&PageNumber=1&PageSize=10

    (2) Pass parameters by Json, normally using with Post by adding [FromBody]

    [Authorize]
    [HttpPost("update/{ClientGuid}")]
    public async Task<IActionResult> UpdateClient([FromBody]ClientUpdateRequest model, Guid ClientGuid)

    It is example, a ClientUpdateRequest Model structured Json is required to pass to the API function. You may use PostMan to pass the Json as follow screen:

    For .net core, it API controller will check the correctly of the JSon class automatically. Therefore, if the class is not matched, the API system will reject directly by Bad Request to frontend.

    The example also shows that mixed parameters with Json and inline parameters.

    (D) Authorization

    At the start of the declaration, we added a tag [Authorize]. This is the Framework permission filtering features, which let us to add simple security filtering for this function. There is some options here:

    [AllowAnonymous] – Allow any one to access the function without authenicate

    [Authorize] – Allow only authenicated users

    [Authorize(Role.Admin)] – Allow only authenicated users with Admin Role

    [Authorize(Role.Admin, Role.Operator)] – Allow only authenicated users with Opearator and Admin Role

    Currently, system defined four roles : User, ServiceProvider, Operator, Admin

    (E) Return Data Model

    By using the Framework, we use a universal response <IActionResult> for return model, which is a free format response. But the Framework has defined 2 common response models base class:

    (1) GenericResponseModel – Use it when you needed to return with a Model or a ListModel. The result Json will show as follow:

    {
        "Data": {
            "Email": "sammy@cpu88.com   ",
            "EmailToRevalidate": null,
            "CheckUsernameAvailabilityEnabled": false,
            "AllowUsersToChangeUsernames": false,
            "UsernamesEnabled": false,
            "Username": "sammy@cpu88.com",
            "GenderEnabled": true,
            "Gender": "M",
            "FirstName": "satest",
            "LastName": "32",
            "DateOfBirthEnabled": true,
            "DateOfBirthDay": null,
            "DateOfBirthMonth": null,
            "DateOfBirthYear": null,
            "DateOfBirthRequired": false,
            "CompanyEnabled": true,
            "CompanyRequired": false,
            "Company": null,
            "StreetAddressEnabled": false,
            "StreetAddressRequired": false,
            "StreetAddress": null,
            "StreetAddress2Enabled": false,
            "StreetAddress2Required": false,
            "StreetAddress2": null,
            "ZipPostalCodeEnabled": false,
            "ZipPostalCodeRequired": false,
            "ZipPostalCode": null,
            "CityEnabled": false,
            "CityRequired": false,
            "City": null,
            "CountyEnabled": false,
            "CountyRequired": false,
            "County": null,
            "CountryEnabled": false,
            "CountryRequired": false,
            "CountryId": 0,
            "AvailableCountries": [],
            "StateProvinceEnabled": false,
            "StateProvinceRequired": false,
            "StateProvinceId": 0,
            "AvailableStates": [],
            "PhoneEnabled": false,
            "PhoneRequired": false,
            "Phone": null,
            "FaxEnabled": false,
            "FaxRequired": false,
            "Fax": null,
            "NewsletterEnabled": true,
            "Newsletter": false,
            "SignatureEnabled": false,
            "Signature": null,
            "TimeZoneId": null,
            "AllowCustomersToSetTimeZone": true,
            "VatNumber": null,
            "VatNumberStatusNote": "Unknown",
            "DisplayVatNumber": false,
            "AssociatedExternalAuthRecords": [],
            "NumberOfExternalAuthenticationProviders": 0,
            "AllowCustomersToRemoveAssociations": true,
            "CustomerAttributes": [],
            "GdprConsents": [],
            "CustomProperties": {}
        },
        "Message": null,
        "ErrorList": []
    }

    Where { Data: } can contain any data model class. { Message: } and { ErrorList: } can used to show additional message or capture the errors.

    (2) SimpleResponseModel Use it when you do not needed to return a model class, just return string message or update count.

    public async Task<IActionResult> CreateClientFormUser(Guid UserGuid)
    {
        // Rewritten by Sammy Cheng, 2022-05-11
        // accept token from request body or cookie
        UserGuid = getUserGuidCookie(UserGuid);
        checkAdmin(UserGuid);
        var response = new SimpleResponseModel();
        await _clientService.CreateClientFromUser(UserGuid);
        response.SetSuccess(1, 0, "created from User");
        return Ok(response);
    }

    To result will return as

    1 record(s) created from user.

    You can define you own response model but remember that standardized response format can increase the development performance and enhance the code quality.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Creating and Registering Factory, Using of AutoMapper Service (Ch.7)

    Next : Example of full API code and Error/Exception Handling (Ch.9)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    PostMan Documentation Guide (Ch.10)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    After successful online deployment, write a API Document for frontend bridging. The most common tool is Postman.

    (A) Create Collection and declare Variables

    Variables, such as API URL, can be used recurring throughout the whole collection.

    (B) Create Folder and Request

    Create distinct folder for each Controller. For each Request, fill in Type(Get, Post, Put, Delete), Url, JSON body and Parameters.

    (C) Test the API and Save a success response as Example for each request

    Creating and Registering Factory, using of AutoMapper Service (Chapter 7)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    For better data manipulation, we suggest to a a Factory class working with service class to provide data mapping and data manipulation. Inside the framework, we always return the Entity Record from database which is the raw data from database. But we are not recommended to return the data structure directly to frontend view. In the actual working environment, the data input and output model are different when calling different functions, and some data we may not want to transfer to frontend because of data security. So, we suggest to create input and output data model instead of directly using data table (Entity).

    Creating Model Classes

    For Model Classes, we usually treated as 3 types

    • Data Input : Request Class, we always named as XXXXRequest. e.g. ClientUpdateRequest, UserApprovalRequest
    • Data Output : Response Class, we always named as XXXXXModel. e.g. ClientModel, ClientListModel, AuthenicateModel
    • Data Searching Parameters : Command Class, we always named as XXXXCommand. e.g. ClientSearchCommand

    There is some experience to be shared here:

    For Request Class:

    • For all bool, int, double, datetime fields, no matter with the Entity Class, I always to use bool?, int?, double?, datetime? instead. It is because when using the model as data update, the frontend may only transfer the modified fields only in the JSon. Therefore, we should avoiding overwriting the existing values with Null parameters.
    • My habit is using the same class of Update Model and Create Model in single model, I will use the mapper service for data copying from model to entity or from entity to model.
    • ‘Id’ key will absent from the Update Model because it avoid the mapper class to try to modify the ‘Id’ value when using UpdateAsync.
    • The request class should write as simple as possible that easy to get the request data from Frontend.

    For Response Class:

    • Can create multiple response class in different controller response, reduce the unnecessary data fields to minimize the network usage to enhance the system performance.
    • Add some calculated fields and prepare the value in factory to reduce the frontend to call API to retrieve more information. For example, if the table contains PictureId, we always as a PictureModel class into the model that include the URL of the Picture. Then we return the data model include the related PictureModel, so that the frontend do not needed to call another API to obtain the picture Url.
    • May different when using in Administrator backend panel or in user portal. Just data should not directly pass to frontend browse due with the system security and data piracy.

    For Command Class:

    • Always consider to return data with paging, to avoid accidentally huge data transfer
    • All Search parameters should be nullable, so we can easy to skip handling empty string and null data input options
    • Include ActiveOnly and IncludeDeleted options if applicable.
    • Always inherit basePageableModel
    public partial record AddressSearchCommand : BasePageableModel
        {
            /// <summary> Address Type Enums </summary>
            public string CRecType { get; set; }
            /// <summary> Address Type Enums </summary>
            public string CRecCode { get; set; }
            /// <summary> Address Type Enums </summary>
            public Guid? CRecGuid { get; set; }
            /// <summary>收貨公司名稱</summary>
            public string CompanyName { get; set; }
            /// <summary>收貨人姓名</summary>
            public string FullName { get; set; }
            /// <summary>收貨人電郵地址</summary>
            public string Email { get; set; }
            /// <summary>國家碼</summary>        
            public string CountryCode { get; set; }
            /// <summary>州/省碼</summary>
            public string StateProvinceCode { get; set; }
            /// <summary>市/縣</summary>
            public string CountyCode { get; set; }
            /// <summary>地/鎮</summary>
            public string City { get; set; }
            /// <summary>地址</summary>
            public string Address { get; set; }
            /// <summary> Phone, Tel, Fax </summary>
            public string Phone { get; set; }
            /// <summary> Is Approved Address </summary>
            public bool? IsApproved { get; set; }
            /// <summary> Is Billing Address </summary>
            public bool? IsBilling { get; set; }
            /// <summary> Show Active Only </summary>
            public bool? ActiveOnly { get; set; }
            /// <summary> Include Deleted </summary>
            public bool? IncludeDeleted { get; set; }
            /// <summary> 
            /// Order By :  0 - Order By Id
            ///             1 - Company Name
            ///             2 - Full Name
            /// </summary>
            public int? OrderBy { get; set; }
    
        }

    Create the Factory Class

    Why we use factory? In my experience, factory is a part of service but handle different data. From my design, services always deal with data provider or repository to retrieve or post. Factories will mainly to handle to the build correct model result and return to controller. Therefore, in the example of this Framework, we always to call service to retrieve a set of data and call factory to build the response model. For more information, please take a look with the sample code of the Framework.

    As the same way as Service, to create a Factory Class we needed to create a Factory Class and an Interface Class. The example like this:

    namespace BlueSky.Service.Factories
    {
        public class AddressFactory : IAddressFactory
        {
            #region Properties
    
            private readonly DataContext _context;
            private readonly IMapper _mapper;
    
            #endregion
    
            #region Ctor
            public AddressFactory(DataContext context,
                IMapper mapper)
            {
                _context = context;
                _mapper = mapper;
            }
            #endregion
    
            #region Utilities
            #endregion
    
            #region Methods
    
            public Address PrepareAddressFromUpdateModel(Address response, AddressUpdateRequest model)
            {
                response = _mapper.Map<Address>(model);
                return response;
            }
    
            public AddressModel PrepareAddressResponseModel(AddressModel response, Address address)
            {
                response = _mapper.Map<AddressModel>(address);
                return response;
            }
    
            public AddressListModel PrepareAddressListResponseModel(AddressListModel response, IPagedList<Address> list)
            {
                if (response == null)
                    response = new AddressListModel();
                response.Addresses = _mapper.Map<List<AddressModel>>(list);
    
                response.PageNumber = list.PageIndex + 1;
                response.PageSize = list.PageSize;
                response.HasNextPage = list.HasNextPage;
                response.HasPreviousPage = list.HasPreviousPage;
                response.TotalItems = list.TotalCount;
                response.TotalPages = list.TotalPages;
    
                return response;
            }
    
            public Address PrepareAddressApproveModel(Address response, AddressApproveRequest model)
            {
                if (response == null)
                    return response;
                
                response = _mapper.Map<Address>(model);
                response.IsApproved = true;
                response.IsRejected = false;
                response.ApproveDateUtc = DateTime.UtcNow;
    
                return response;
            }
    
            public Address PrepareAddressRejectModel(Address address)
            {
                if (address == null)
                    return address;
    
                address.IsApproved = false;
                address.IsRejected = true;
                address.ApproveDateUtc = DateTime.UtcNow;
    
                return address;
            }
    
            #endregion
    
        }
    }
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using BlueSky.Core;
    using BlueSky.Data;
    using BlueSky.Core.Domain.Common;
    using BlueSky.Core.Domain.Clients;
    using BlueSky.Service.Models.CommonData;
    using BlueSky.Service.Models.Client;
    
    namespace BlueSky.Service.Factories
    {
        public interface IAddressFactory
        {
            Address PrepareAddressFromUpdateModel(Address response, AddressUpdateRequest model);
            AddressModel PrepareAddressResponseModel(AddressModel response, Address address);
            AddressListModel PrepareAddressListResponseModel(AddressListModel response, IPagedList<Address> list);
            Address PrepareAddressApproveModel(Address response, AddressApproveRequest model);
            Address PrepareAddressRejectModel(Address address);
        }
    }
    

    Register the Factory Class

    The same as service, create a registration in ‘Infrastructure/DependencyRegistrar.cs’ as below

    
    namespace BlueSky.Service.Infrastructure
    {
        public class DependencyRegistrar 
        {
            public void Register(IServiceCollection services)
            {            
                services.AddScoped(typeof(IRepository<>), typeof(EntityRepository<>));
                services.AddScoped(typeof(IGuidRepository<>), typeof(GuidEntityRepository<>));
                services.AddScoped(typeof(ICustomKeyRepository<>), typeof(CustomKeyEntityRepository<>));
                
                services.AddScoped<IEmailService, EmailService>();
                services.AddScoped<ISiteService, SiteService>();
                services.AddScoped<IUserService, UserService>();
                services.AddScoped<ICommonDataService, CommonDataService>();
                services.AddScoped<ILanguageService, LanguageService>();
                services.AddScoped<IAddressService, AddressService>();
                services.AddScoped<IClientService, ClientService>();
    
                services.AddScoped<ISiteFactory, SiteFactory>();
                services.AddScoped<ICommonDataFactory, CommonDataFactory>();
                services.AddScoped<ILanguageFactory, LanguageFactory>();
                services.AddScoped<IUserFactory, UserFactory>();
                services.AddScoped<IAddressFactory, AddressFactory>();
                services.AddScoped<IClientFactory, ClientFactory>();
            }
        }
    }
    

    For using of Localization Service, it is best to work in the Factory so that we can easy response the correct locale result to client.

    Using of AutoMapper

    AutoMapper is a service which used to clone/fill data from Entity class to Model class or Model class to Entity class. It let you can copy hundreds of field by just few lines of code.

    // To overwrite Client Record from ClientUpdateRequest
    // Syntax :  _mapper.Map<SourceModel, DestModel>(Source, Destination)
    public Client PrepareClientFromUpdateModel(Client response, ClientUpdateRequest model)
    {
         _mapper.Map<ClientUpdateRequest, Client>(model, response);
         return response;
    }
    
    // To Create a new set of record without old data
    // Syntax : destination = _mapper.Map<DestModel>(SourceModel)
    public ClientModel PrepareClientResponseModel(ClientModel response, Client client)
    {
         response = _mapper.Map<ClientModel>(client);
         return response;
    }

    In the example, you can see the it is very easy to copy all the fields value from ClientUpdateRequest –> Client (Entity) and from Client (Entity) –> ClientModel

    To complete this feature, we must have correct declaration between the class and model mapping. Open the “/Helpers/AutoMapperProfile.cs”, add the entry

    CreateMap<Client, ClientModel>().ForMember(dest => dest.ClientGuid, opt => opt.MapFrom(src => src.guid));

    For this Example, it declared a entity class (Client) to a response model (ClientModel). Seen we defined we have change the field name “guid” to “ClientGuid” in the response model so that we add a option to change the mapping.

    CreateMap<ClientUpdateRequest, Client>()
                    .ForAllMembers(x => x.Condition(
                        (src, dest, prop) =>
                        {
                            // ignore null & empty string properties
                            if (prop == null) return false;
                            if (prop.GetType() == typeof(string) && string.IsNullOrEmpty((string)prop)) return false;
                            if (prop.GetType() == typeof(Guid) && (Guid)prop == Guid.Empty) return false;
    
                            return true;
                        }
                ));

    For this example, it declared an update model (ClientUpdateRequest) to map to the entity class (Client). For this case, to avoid unexpected empty data overwrite the existing data, we needed to add some condition for handling. In this example, we have skip null value, empty string and empty Guid to me update to destinated class.

    After adding this declaration, you can enjoy the service of AutoMapper, which is very code friendly for Model building. In later chapter, we will discuss how to use Localization service within the Factory Service and Models.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Creating and Registering of Service Classes (Ch.6)

    Next : Creating Controller Classes with API endpoint (Ch.8)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Using of SVN and Visual Studio (Ch.1)

    0

    For version control, we use Tortoise SVN together with Visual SVN for Visual Studio.

    Tortoise SVN

    (A) Installation

    The download link is https://tortoisesvn.net/downloads.html

    (B) Checkout

    After successful installation, there will be “SVN Checkout” and “TortoiseSVN” when you right click on File Explorer.

    To download the source code from the SVN server, click “SVN Checkout” and enter Url of repository and Checkout directory.

    VisualSVN for Visual Studio

    (A) Installation

    The download link is https://tortoisesvn.net/downloads.html

    (B) Update and Commit

    After successful installation, you can find the VisualSVN in Extensions.

    Everyday, when start editting the code, please update the repository first. At the end of the day, you commit your changes with some describing messages. Remember to press All so that no file will be missing.

    Creating and Registering of Service Classes (Chapter 6)

    0

    A service that handles user input and returns an instantiated data model class, then controller will output to frontend to rendered the view. This topics will discuss how we create and register service to the Framework.

    To create service, we needed to add a class and an interface class to the folder ‘/Services’

    Create a Service Class

    The interface class is worked as a header file, which contains the declaration of the public functions of the service class. For each service class, we will normally written with four regions. Here is the example of inside the framework:

    public class ClientParcelsService : IClientParcelsService
        {
            #region Properties
    
            private readonly ApiDataContext _context;
            private readonly IMapper _mapper;
            private readonly AppSettings _appSettings;
            private readonly IGuidRepository<ClientParcel> _clientParcelRepository;
            
            #endregion
    
            #region Ctor
    
            public ClientParcelsService(
                ApiDataContext context,
                IMapper mapper,
                IOptions<AppSettings> appSettings,
                IGuidRepository<ClientParcel> clientParcelRepository)
            {
                _context = context;
                _mapper = mapper;
                _appSettings = appSettings.Value;
                _clientParcelRepository = clientParcelRepository;
            }
    
            #endregion
    
            #region Utilities
            #endregion
    
            #region Methods
            public virtual async Task<IPagedList<ClientParcel>> GetClientParcelList(ClientParcelSearchCommand command)
            {
                var query = from p in _context.ClientParcels
                            select p;
    
                if (command.ClientGuid != Guid.Empty)
                    query = query.Where(g => g.ClientGuid == command.ClientGuid);
    
                if (string.IsNullOrWhiteSpace(command.ShipmentCode))
                    query = query.Where(s => s.SourceShipmentCode.Contains(command.ShipmentCode));

    #region Properties / #region Fields

    “Properties” regions place all definitions variable declaration using by the service, which include properties, repositories, services, context and any properties which required to access within the service.

    #region Ctor

    “Ctor” is the constructor function, which initial all the “Properties” and assign the correct initial values or service assignment.

    #region Utilities

    “Utilities” regions always place the private / protection helpers functions with commonly used within the service.

    #region Methods

    “Methods” regions contains the public functions which the service will provide to controller or other services.

    This is not a hard rule but it is a good habit to organize the code for future troubleshooting.

    Create the Interface

    To create the interface, just easy to add a Interface file and write like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using CCOMS.Api.Domain.ConsolidatedOrder;
    using CCOMS.Api.Models.ConsolidatedOrder;
    using BlueSky.Core;
    
    namespace CCOMS.Api.Services
    {
        public interface IClientParcelsService
        {
            Task<IPagedList<ClientParcel>> GetClientParcelList(ClientParcelSearchCommand command);        
        }
    }
    

    Get all the public functions inside the service and write the declaration into the interface. Remember to inherit the interface to the service class.

     public class ClientParcelsService : IClientParcelsService

    Registering Service to framework

    After the creation of service and interface, we needed to register the service to the framework so that we can call the service across the projects. To add registration, go and open ‘/infrastructure/DependencyRegistrar.cs’

    using CCOMS.Api.Services;
    using CCOMS.Api.Factories;
    
    
    namespace CCOMS.Api.Infrastructure
    {
        public class DependencyRegistrar 
        {
            public void Register(IServiceCollection services)
            {
                services.AddScoped<IClientParcelsService, ClientParcelsService>();
                services.AddScoped<IClientParcelsFactory, ClientParcelsFactory>();
            }
        }
    }
    

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Creating and Registering of Service Classes (Ch.5)

    Next : Creating and Registering Factory, using of AutoMapper Services (Ch.7)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Using of IRepository, IGuidRepository, ICustomKeyRepository (Chapter 5)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    The Repository Classes, which is developed for the Repository Pattern and Unit of Work in ASP.NET CORE. For more concept on using Repository Pattern Implementation, please see relative Microsoft Documentations or other training web site.

    It is recommended to use the Repository classes instead of directly access the DataContext to operate all database operation. It is because we can implement any database operation on different database providers, so that our API do not limit to any single database system. Also, we can implement many automated data handling at the repository layer. In this chapter, we will introduce the functions provided in the Repository Class.

    Using of Repository Class

    To use repository, first we must declare the data table class with inheritance of base classes:

    • baseEntity : The table using integer identity field “Id” as primary key.
    • baseGuidEntity : The table using Guid field “guid” as primary key.
    • baseCustomKeyEntity : The table using any field(s) as primary key combination.

    The definition should like this :

    namespace BlueSky.Core.Domain.Common
    {
        [Table("CountyDef")]
        public class County : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity ,ILocalizedEntity
        {
            [Column(TypeName = "varchar(2)")]
            public string CountryCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string StateProvinceCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string CountyCode { get; set; }
            public int? NopStateId { get; set; }
            [Column(TypeName = "varchar(100)")]
            public string CountyName { get; set; }        
            public int? DisplayOrder { get; set; }
    
            /// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
            [NotMapped]
            public override string CustomKey { get { return CountryCode + "," + StateProvinceCode + "," + CountyCode; } }
    
            /// <summary>Implementation of IActiveFilterEntity </summary>
            public bool Active { get; set; }
            /// <summary>Implementation of ISoftDeletedEntity </summary>
            public bool Deleted { get; set; }
    
            public County()
            {
                Active = true;
                Deleted = false;
            }
        }
    }

    each base class is required to use different repository class in pair

    public class MyTable : baseEntityIRepository<MyTable> _myTableRepository;
    public class MyTable2 : baseGuidEntityIGuidRepository<MyTable2> _myTable2Repository;
    public class MyTable3 : baseCustomKeyEntityICustomKeyRepository<MyTable3> _myTable3Repository;

    Declaration of Repository Class

    When we needed to access the data table, for example in a service, we should declare the repository object like this

    namespace CCOMS.Api.Services
    {
        public class ClientParcelsService : IClientParcelsService
        {
            #region Properties
            private readonly ApiDataContext _context;
            private readonly IMapper _mapper;
            private readonly AppSettings _appSettings;
            private readonly IGuidRepository<ClientParcel> _clientParcelRepository;
            #endregion
    
            #region Ctor
            public ClientParcelsService(
                ApiDataContext context,
                IMapper mapper,
                IOptions<AppSettings> appSettings,
                IGuidRepository<ClientParcel> clientParcelRepository)
            {
                _context = context;
                _mapper = mapper;
                _appSettings = appSettings.Value;
                _clientParcelRepository = clientParcelRepository;
            }
            #endregion
            ...............

    See this example, we have declare a “_clientParcelRepository” object, then we can easily to retrieve, query, update the database by the repository class.

    Functionility of Repository Class

    (1) Get a record by ID, Guid or Expression

    Task<TEntity> GetByIdAsync(int? id, bool includeDeleted = true, bool activeOnly = false);           // Provided by IRepository
    
    Task<TEntity> GetByIdAsync(Guid guid, bool includeDeleted = true, bool activeOnly = false);         // Provided by IGuidRepository
    
    Task<IList<TEntity>> GetByExpressionSingleOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = true, bool activeOnly = false);       // Provided by ICustomKeyRepository
    Input Parameters :
    int? idThe Id field lookup value
    Guid guidThe guid field lookup value
    Expression<Func<TEntity, bool>> predicateFor custom key table, we are not sure that what is the key field name and how many key fields there. Therefore, we needed to use Linq expression to search the record

    e.g _clientParcelRepository.GetByExpressionSingleOrDefaultAsync(p => p.ParcelCode == myParcelCode);
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)
    private async Task<Client> GetClientAsync(Guid ClientGuid)
    {
        var client = await _clientRepository.GetByIdAsync(ClientGuid, includeDelete: false, activeOnly: true);
        if (client == null)
            throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _detail: "Client Guid : " + ClientGuid.ToString() + " not found." ).ToString());
            return client;
    }

    (2) Get list of records by IDs, Guids or Expression

    Task<IList<TEntity>> GetByIdsAsync(IList<int> ids, bool includeDeleted = true, bool activeOnly = false);   // Provided by IRepository
    
    Task<IList<TEntity>> GetByIdsAsync(IList<Guid> guids, bool includeDeleted = true, bool activeOnly = false);    // Provided by IGuidRepository
    
    Task<IList<TEntity>> GetByExpressionAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = true, bool activeOnly = false);   // Provided by ICustomKeyRepository
    Input Parameters :
    IList<int> idsA List if Id field lookup value in IList
    IList<Guid> guidA list of guid field lookup value in IList
    Expression<Func<TEntity, bool>> predicateFor custom key table, we are not sure that what is the key field name and how many key fields there. Therefore, we needed to use Linq expression to search the record

    e.g _clientParcelRepository.GetByExpressionAsync(p => p.ParcelCode == myParcelCode);
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)

    (3) Get All Records (Search Records)

    Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, bool includeDeleted = true, bool activeOnly = false);  // Provided by IRepository
    
    Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, bool includeDeleted = true, bool activeOnly = false);  // Provided by IGuidRepository
    
    Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, Task<IQueryable<TEntity>>> func = null, bool includeDeleted = true, bool activeOnly = false)  // Provided by ICustomKeyRopository
    Input Parameters :
    Func<IQueryable<TEntity>, IQueryable<TEntity>> func = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)

    This features are usually used for data searching. In this example, we first to declare and search model (normally named as xxxxSearchCommand) in the model folder.

        public partial record ClientSearchCommand : BasePageableModel
        {
            /// <summary>客戶 Guid, 如網上注冊,客戶 Guid 同跟用戶編號一樣</summary>
            public Guid? EntityGuid { get; set; }
            /// <summary>上級客戶編號</summary>
            public Guid? MasterClientGuid { get; set; }
            /// <summary>業務員編號或協銷聯盟編號</summary>
            public string SalesCode { get; set; }
            /// <summary>客戶分類, 網上注冊用戶為 N</summary>
            public string ClientTypeCode { get; set; }
            /// <summary>客戶名稱, 如公司客戶則公司名稱</summary>
            public string ClientName { get; set; }
            /// <summary>國家 (地址)</summary>
            public string CountryCode { get; set; }
            /// <summary>州/省 (地址)</summary>
            public string StateProvinceCode { get; set; }
            /// <summary>市/縣</summary>
            public string CountyCode { get; set; }
            /// <summary>城/鎮</summary>
            public string City { get; set; }
            /// <summary>地址</summary>
            public string Address { get; set; }
            /// <summary>電郵地址</summary>
            public string Email { get; set; }
            /// <summary>手提電話</summary>
            public string Phone { get; set; }
            /// <summary>狀況 0-新客戶, 1-活動, -1-暫停,-2-已刪除</summary>
            public short? Status { get; set; }
            /// <summary>Show Active Only</summary>
            public bool? ActiveOnly { get; set; }
            /// <summary>Include Deleted Records</summary>
            public bool? IncludeDeleted { get; set; }
            /// <summary>
            /// Order By :      0 - CreateTimeUtc
            ///                 1 - Client Code
            ///                 2 - Client Name
            /// </summary>
            public int? OrderBy { get; set; }
        }

    Then we implement the code in ClientService to search record by input parameters

    var clients = await _clientRepository.GetAllPagedAsync(query =>
    {
         if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
             query = query.Where(g => g.EntityGuid == command.EntityGuid);
         if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
             query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);  
         if (String.IsNullOrEmpty(command.SalesCode))
             query = query.Where(s => s.SalesCode == command.SalesCode);
         if (string.IsNullOrEmpty(command.ClientTypeCode))
             query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
         if (string.IsNullOrEmpty(command.ClientName))
             query = query.Where(c => c.ClientName.Contains(command.ClientName));
         if (string.IsNullOrEmpty(command.CountryCode))
             query = query.Where(c => c.CountryCode == command.CountryCode);
         if (string.IsNullOrEmpty(command.StateProvinceCode))
             query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
         if (string.IsNullOrEmpty(command.CountyCode))
             query = query.Where(c => c.CountyCode == command.CountyCode);
         if (string.IsNullOrEmpty(command.City))
             query = query.Where(c => c.City.Contains(command.City));
         if (string.IsNullOrEmpty(command.Address))
             query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
         if (string.IsNullOrEmpty(command.Email))
             query = query.Where(e => e.Email.Contains(command.Email));
         if (string.IsNullOrEmpty(command.Phone))
             query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                        || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) || p.Fax2.Contains(command.Phone));
         if (command.Status.HasValue)
             query = query.Where(s => s.Status == command.Status);
         switch (command.OrderBy)
         {
             case 1:
                 query = query.OrderBy(o => o.ClientCode);
                 break;
             case 2:
                 query = query.OrderBy(o => o.ClientName);
                 break;
             default:                    
                 query = query.OrderBy(o => o.CreateTimeUtc);
                 break;
         }
    
         return query;
    }, activeOnly: (bool)command.ActiveOnly, includeDeleted: (bool)command.IncludeDeleted );
    
    return clients;

    (4) Get All Records with Paging (Search Records)

    Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null,
     int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by IRepository
    
    Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by IGuidRepository
    
    Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by ICustomKeyRepository
    
    Input Parameters :
    Func<IQueryable<TEntity>, IQueryable<TEntity>> func = nullUsing a IQueryable expression to query data , see example code
    pageIndexThe page requested, 0 – First Page
    pageSizeNo of records of this page
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)
    var clients = await _clientRepository.GetAllPagedAsync(query =>
    {
         if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
             query = query.Where(g => g.EntityGuid == command.EntityGuid);
         if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
             query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);  
         if (String.IsNullOrEmpty(command.SalesCode))
             query = query.Where(s => s.SalesCode == command.SalesCode);
         if (string.IsNullOrEmpty(command.ClientTypeCode))
             query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
         if (string.IsNullOrEmpty(command.ClientName))
             query = query.Where(c => c.ClientName.Contains(command.ClientName));
         if (string.IsNullOrEmpty(command.CountryCode))
             query = query.Where(c => c.CountryCode == command.CountryCode);
         if (string.IsNullOrEmpty(command.StateProvinceCode))
             query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
         if (string.IsNullOrEmpty(command.CountyCode))
             query = query.Where(c => c.CountyCode == command.CountyCode);
         if (string.IsNullOrEmpty(command.City))
             query = query.Where(c => c.City.Contains(command.City));
         if (string.IsNullOrEmpty(command.Address))
             query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
         if (string.IsNullOrEmpty(command.Email))
             query = query.Where(e => e.Email.Contains(command.Email));
         if (string.IsNullOrEmpty(command.Phone))
             query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                        || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) || p.Fax2.Contains(command.Phone));
         if (command.Status.HasValue)
             query = query.Where(s => s.Status == command.Status);
         switch (command.OrderBy)
         {
             case 1:
                 query = query.OrderBy(o => o.ClientCode);
                 break;
             case 2:
                 query = query.OrderBy(o => o.ClientName);
                 break;
             default:                    
                 query = query.OrderBy(o => o.CreateTimeUtc);
                 break;
         }
    
         return query;
    }, pageIndex: command.PageIndex, pageSize: command.PageSize, activeOnly: (bool)command.ActiveOnly, includeDeleted: (bool)command.IncludeDeleted );
    
    return clients;

    (5) Insert A Record / Insert A List of Records

    Task InsertAsync(TEntity entity, bool publishEvent = true);
    Task InsertAsync(IList<TEntity> entities, bool publishEvent = true);
    Input Parameters :
    EntityThe entity record
    IList<TEntity>List of entity records
    publishEventtrue / false – to indicate whether publish a event for the system, default = true
    private async Task ClientInsertAsync(Client client)
    {
        try
        {
            await _clientRepository.InsertAsync(client);
        } catch(Exception E)
        {
            throw new AppException(_context.errorList.Response(ErrorCode.DataInsertError, _detail: E.Message).ToString());
        }
    }

    (6) Upload A Record / Upload A List of Records

    Task UpdateAsync(TEntity entity, bool publishEvent = true);
    Task UpdateAsync(IList<TEntity> entities, bool publishEvent = true);
    Input Parameters :
    EntityThe entity record
    IList<TEntity>List of entity records
    publishEventtrue / false – to indicate whether publish a event for the system, default = true
    private async Task ClientUpdateAsync(Client client)
    {
        try
        {
            await _clientRepository.UpdateAsync(client);
        }catch( Exception E)
        {
            throw new AppException(_context.errorList.Response(ErrorCode.DataUpdateError, _detail: E.Message).ToString());
        }
    }

    (7) Delete/UnDelete A Record / Delete A List of Records

    Task DeleteAsync(TEntity entity, bool publishEvent = true, bool DeletePermanently = false);
    Task DeleteAsync(IList<TEntity> entities, bool publishEvent = true, bool DeletePermanently = false);
    Task UnDeleteAsync(TEntity entity, bool publishEvent = true);
    Input Parameters :
    EntityThe entity record
    IList<TEntity>List of entity records
    DeletePermanentlytrue / false – to indicate whether override the softdelete function and delete the record permanently
    publishEventtrue / false – to indicate whether publish a event for the system, default = true
    private async Task ClientDeleteAsync(Client client, bool HardDelete)
    {
        try
        {
            await _clientRepository.DeleteAsync(client, DeletePermanently: HardDelete);
        } catch (Exception E)
        {
            throw new AppException(_context.errorList.Response(ErrorCode.DataDeleteError, _detail: E.Message).ToString());
        }
    }

    (8) Directly get records of data by SQL statement

    Task<IList<TEntity>> EntityFromSqlAsync(string sql, params DataParameter[] parameters);
    Input Parameters :
    sqlSQL Query in string
    DataParameter[] parametersparameters list of the query
    IList<Client> clients = _clientRepository.EntityFromSqlAsync("select * from ClientMaster where ClientName like '%TEST%');

    Note : Be sure that the query return the record fields int the TEntity format, or otherwise, exception may occurs.

    (9) Check Expression records exist or not

    Task<bool> AnyAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = false, bool activeOnly = true);
    Input Parameters :
    Func<IQueryable<TEntity>, IQueryable<TEntity>> func = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)
    var user = 
    if (await _userRepository.AnyAsync(u => u.UserGuid == Guid.Empty))
       throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _validationErrors: "User Guid : " + UserGuid.ToString() ).ToString());

    (10) Get Single Record by Expression

    Task<TEntity> SingleOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = false, bool activeOnly = true);
    Input Parameters :
    Func<IQueryable<TEntity>, IQueryable<TEntity>> func = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / false – to indicate whether search include active Only or all records
    (for table interface with IActiveFilterEntity and IStatusControlEntity)
    var user = await _userRepository.SingleOrDefaultAsync(u => u.UserGuid == UserGuid);
    if (user == null)
          throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _validationErrors: "User Guid : " + UserGuid.ToString() ).ToString());

    We will add more repository features in future for rapid development.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Updating DataContent, Creation of Initial Data and Generate Database Migration Script (Ch. 4)

    Next : Creating and Registering of Service Classes (Ch.6)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Using DataContext, Creation of Initial Data and Generate Database Migration Script (Chapter 4)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    After created data table, we needed to define the table as DataSet in the DataContext.

    Adding “DbSet” to “ApiDataContext”

    To add the table to DataContext, go folder and open “\DataProviders\ApiDataContext.cs”

    namespace CCOMS.Api.DataProviders
    {
        public abstract class ApiDataContext : DbContext
        {
            protected readonly IConfiguration Configuration;
    
            public ApiDataContext(IConfiguration configuration)
            {
                Configuration = configuration;
            }
    
            public DbSet<DeliveryMethodType> DeliveryMethodTypes { get; set; }
            public DbSet<ParcelItemType> ParcelItemTypes { get; set; }
            public DbSet<RestrictedItem> RestrictedItems { get; set; }
            public DbSet<RestrictedItemGroup> RestrictedItemGroups { get; set; }
            public DbSet<ShippingGoodsType> ShippingGoodsTypes { get; set; }
            public DbSet<ShippingServiceType> ShippingServiceTypes { get; set; }
            public DbSet<ShippingVendor> ShippingVendors { get; set; }
            public DbSet<ShippingFee> ShippingFees { get; set; }
            public DbSet<ConsolidatedOrderMaster> ConsolidatedOrderMasters { get; set; }
            public DbSet<ClientParcel> ClientParcels { get; set; }
            public DbSet<TrackingEventType> TrackingEventTypes { get; set; }
            public DbSet<TrackingMaster> TrackingMasters { get; set; }
            public DbSet<TrackingTranscoding> TrackingTranscodings { get; set; }
            public DbSet<Warehouse> Warehouses { get; set; }
            public DbSet<WarehouseArea> WarehouseAreas { get; set; }
            public DbSet<WarehouseAreaType> WarehouseAreaTypes { get; set; }
            public DbSet<WarehouseCell> WarehouseCells { get; set; }
            public DbSet<WarehouseType> WarehouseTypes { get; set; }
        }
    }

    Adding Special Primary Key Definition to “MsSqlApiDbContext”

    All data tables should define an entry here as of above examples. Remember to configure the table with 2 or 3 field primary key tables if you have. Go to folder and open “\DataProviders\MsSqlApiDbContext.cs”

    namespace CCOMS.Api.DataProviders
    {    
        // To Add Migration : dotnet ef migrations add InitalCreate --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext --output-dir DataProviders\Migrations\MsSql
    
        public class MsSqlApiDbContext : ApiDataContext
        {
            public MsSqlApiDbContext(IConfiguration configuration) : base(configuration)
            {
    
            }
    
            protected override void OnConfiguring(DbContextOptionsBuilder options)
            {
                // connect to sql server database
                options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection"));
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.HasDefaultSchema("dbo");
    
                modelBuilder.Entity<WarehouseArea>().HasKey(u => new
                { 
                    u.WarehouseCode,
                    u.AreaCode
                });
    
                modelBuilder.Entity<WarehouseCell>().HasKey(u => new
                {
                    u.WarehouseCode,
                    u.AreaCode,
                    u.CellCode
                });
            }
        }
    }

    Creating Initial Data to new Data Table

    For some tables, we may want to create some initial data to the newly created database. To add some initial data, go to folder and open “\DataProviders\ApiDataInitializer.cs”

    namespace CCOMS.Api.DataProviders
    {
        public class ApiDataInitializer
        {
            public static void Initialize(ApiDataContext _context)
            {
                if (!_context.WarehouseAreaTypes.Any())
                {
                    var types = new WarehouseAreaType[]
                    {
                        new WarehouseAreaType{AreaTypeCode="SS", AreaTypeName="Storage Space", DefBorderColor=0, DefFillColor=16776960},
                        new WarehouseAreaType{AreaTypeCode="TS", AreaTypeName="Temporary Storage", DefBorderColor=0, DefFillColor=65280}                    
                    };
    
                    _context.AddRange(types);
                    _context.SaveChanges();
                }
    
                if (!_context.WarehouseTypes.Any())
                {
                    var warehouseTypes = new WarehouseType[]                
                    {
                        new WarehouseType{ WareHouseTypeCode="ST", WareHouseTypeName="Storage", DisplayOrder=1 },
                        new WarehouseType{ WareHouseTypeCode="RT", WareHouseTypeName="Retail", DisplayOrder=2 },
                        new WarehouseType{ WareHouseTypeCode="TT", WareHouseTypeName="Transit", DisplayOrder=3 },
                        new WarehouseType{ WareHouseTypeCode="PD", WareHouseTypeName="Product", DisplayOrder=4 },
                        new WarehouseType{ WareHouseTypeCode="MA", WareHouseTypeName="Material", DisplayOrder=5 },
                        new WarehouseType{ WareHouseTypeCode="RF", WareHouseTypeName="Refrigerated", DisplayOrder=6 },
                        new WarehouseType{ WareHouseTypeCode="TC", WareHouseTypeName="Temperautre Controlled", DisplayOrder=7 },
                        new WarehouseType{ WareHouseTypeCode="CS", WareHouseTypeName="Consolidation", DisplayOrder=8 },
                        new WarehouseType{ WareHouseTypeCode="OD", WareHouseTypeName="OutDoor", DisplayOrder=9 },
                        new WarehouseType{ WareHouseTypeCode="TS", WareHouseTypeName="Temporary Storage", DisplayOrder=10 }
                    };
    
                    _context.AddRange(warehouseTypes);
                    _context.SaveChanges();
                }
    
                if (!_context.Warehouses.Any())
                {
                    var warehouses = new Warehouse[]
                    {
                        new Warehouse{ WarehouseCode="WH01", WarehouseName="Default Warehouse", WarehouseTypeCode = "ST", Active = true, CreateTimeUtc = DateTime.UtcNow, UpdateTimeUtc = DateTime.UtcNow, UpdateMan = "DBInitial", UpdateZone = "APIDBCONTEXT"}
                    };
    
                    _context.AddRange(warehouses);
                    _context.SaveChanges();
                }
    
                if (!_context.WarehouseAreas.Any())
                {
                    var areas = new WarehouseArea[]
                    {
                        new WarehouseArea{ WarehouseCode="WH01", AreaCode="A", AreaTypeCode="SS", AreaLeft=1, AreaTop=1, AreaHeight = 100, AreaWidth = 100, AreaBorderColor = 0, AreaFillColor = 16776960, MaxCBM = 0, MaxLength = 0, MaxWeight = 0, MaxHeight = 0, MaxWidth = 0 }
                    };
    
                    _context.AddRange(areas);
                    _context.SaveChanges();
                }
            }
        }
    }
    

    Creating Initial Data to new Data Table

    Microsoft Entity Framework has the feature to auto database migration, which can create new database table or modify the changing of database structure. We can use this feature to create or update the tables automatically to ensure the table structure are 100% same as the definition. There are advantages when deployment to client system or update management of client system.

    If you want to use Entity Framework in Visual Studio 2019, open the terminal console of the project :

    Right-Click the project and select “Open in Terminal”, then you will see Developer Powershell window will open at the bottom of the IDE.

    Setting up Database Connection

    Open the “appsettings.json” files, and modify “ConnectionStrings : MsSqlConnection “

    Using Entity Framework to create database creation and migration script

    Make sure you are at location of the solution root folder, may be you just key in command “cd ..” to go to the root folder.

    If your Visual Studio do not have the Entity Framework Tools or if it is first time installation, you have to install the tools first

    dotnet tool install --global dotnet-ef

    When the installation of the ef tools completed, install the latest “Microsoft.EntityFrameworkCore.Design” package

    dotnet add package Microsoft.EntityFrameworkCore.Design

    After installed all the tools, you can enjoy to use the Entity Framework auto database migration feature.

    NOTE: Remember to ensure the database table definition is correct, or you can create table ONE by ONE to avoid incorrect table definition before creating the script

    To Generate the database creation script:

    dotnet ef migrations add "NameOfAction" --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext --output-dir DataProviders\Migrations\MsSql

    Noted: “NameOfAction” is the name you give the system for migration identifier.

    If you found any problem of this script, you can rollback to remove this migration:

    donet ef migrations remove --startup-project CCOMS.Api --project CCOMS.Api --context MsSqlApiDbContext

    Run the project and see if the SQL table create correctly.

    After you run the project, if you can see the Swagger API Document page, it means that your API started successfully. Go and create MSSQL Management Studio to check if the database tables has been created.

    Yeah!!! All the tables created with initial data.

    If you found some problems of table definition and cannot be rollback, try to fix it manually.

    Inside the database, you will found at table {username}._EFMigrationsHistory. When the framework migrated the database, it will add a record to this table. If you want to rollback to previous status, simply delete the latest record of this table. And you can manually modify the table back to original definition. You can also delete the EF generated script manually and regenerate new script manually also. Delete the file as below:

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Using of framework baseClass inheritance and functional interface

    Next : Using of IRepository, IGuidRepository, ICustomKeyRepository

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Using of framework baseClass inheritance and functional interface (Ch.3)

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    To implement more standardize features and data repository features, the framework defined 3 data “base class” and several “data interface” so that more data automation will be provided from data repository.

    Base Class

    To declare a data table in EF, you are required to inherit a base class so that you can use the IRepository

    (A) baseEntity

    “baseEntity” is used for the table which use integer identity field “Id” as primary key. The class will inherit the “Id” field from baseEntity class, therefore, developer is no needed to declare the “Id” field in their class structure.

    /// <summary>
        /// Represents a log record
        /// </summary>
        [Table("SystemLog")]
        public partial class SystemLog : BaseEntity
        {
            /// <summary>
            /// Gets or sets the log level identifier
            /// </summary>
            public int LogLevelId { get; set; }
    
            /// <summary>
            /// Gets or sets the short message
            /// </summary>
            [Column(TypeName = "nvarchar(100)")]
            public string ShortMessage { get; set; }
    
            /// <summary>
            /// Gets or sets the full exception
            /// </summary>
            [Column(TypeName = "nvarchar(max)")]
            public string FullMessage { get; set; }
    
            /// <summary>
            /// Gets or sets the IP address
            /// </summary>
            [Column(TypeName = "nvarchar(50)")]
            public string IpAddress { get; set; }
    
            /// <summary>
            /// Gets or sets the page URL
            /// </summary>
            [Column(TypeName = "nvarchar(500)")]
            public string PageUrl { get; set; }
    
            /// <summary>
            /// Gets or sets the referrer URL
            /// </summary>
            [Column(TypeName = "nvarchar(500)")]
            public string ReferrerUrl { get; set; }
    
            /// <summary>
            /// Gets or sets the referrer URL
            /// </summary>
            public bool IsRead { get; set; }
    
            // From Interface IUpdateTrackingEntity
            [Column(TypeName = "datetime")]
            public DateTime? CreateTimeUtc { get; set; }
            [Column(TypeName = "datetime")]
            public DateTime? UpdateTimeUtc { get; set; }
            [Column(TypeName = "datetime")]
            public DateTime? ImportTimeUtc { get; set; }
            [Column(TypeName = "varchar(50)")]
            public string UpdateMan { get; set; }
            [Column(TypeName = "varchar(50)")]
            public string UpdateZone { get; set; }
    
            /// <summary>
            /// Gets or sets the log level
            /// </summary>
            public LogLevel LogLevel
            {
                get => (LogLevel)LogLevelId;
                set => LogLevelId = (int)value;
            }
        }

    In this example, a “Id” identity key field will be inherit to data class and set key “Id” as primary key. The data class will map to “IRepository” class for data operation controls. For using of “IRepository” Class, it will discuss in later chapters.

    (B) baseGuidEntity

    “baseGuidEntity” is used for the data table using in “guid” field as primary key. In the following example, data table is a “guid” field in Guid data type is defined and inherit to data table.

        [Table("ClientMaster")]
        public partial class Client : BaseGuidEntity, IUpdateTrackingEntity, IStatusDeletedEntity, ILocalizedEntity
        {
            /// <summary> Organization / Entity Guid </summary>
            public Guid EntityGuid { get; set; }
            /// <summary> Client Code (for User Customizable) </summary>
            [Column(TypeName = "varchar(30)")]
            public string ClientCode { get; set; }
            /// <summary> Master Guid </summary>
            [Column(TypeName = "varchar(30)")]
            public Guid? MasterClientGuid { get; set; }
            /// <summary> For Nop Commerce Sychronization </summary>
            public int? NopClientId { get; set; }
            /// <summary> Sales Code </summary>
            [Column(TypeName = "varchar(30)")]
            public string SalesCode { get; set; }
            /// <summary> Short Name </summary>
            [Column(TypeName = "nvarchar(20)")]
            public string ShortName { get; set; }
            /// <summary> Client Type Code (Foriegn Key) </summary>
            [Column(TypeName = "varchar(2)")]
            public string ClientTypeCode { get; set; } = "I";
            ..............
        }

    For “baseGuidEntity” inherit class, we will map to use “IGuidRepository” for data operations. For more information of “IGuidRepostory”, we will discuss in later chapters

    (C) baseCustomKeyEntity

    “baseCustomKeyEntity” is used for the data table using in string code fields as primary key. The main different of this table definition developer is required to define the primary key in the class and the base class will not inherit the primary key field. The base class will only inherit a virtual field “CustomKeyCode” that is for a calculated field for the data class, which will use in Localization Service. Below is the class definition:

    public abstract partial class BaseCustomKeyEntity
    {
        /// <summary>
        /// Must declare Computed query defintion in OnModelCreating
        /// </summary>
        [NotMapped]
        [Column(TypeName = "varchar(200)")]
        public virtual string CustomKey { get; private set; }
    }

    For the data class, you should declare like this.

        [Table("CountyDef")]
        public class County : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity ,ILocalizedEntity
        {
            [Column(TypeName = "varchar(2)")]
            public string CountryCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string StateProvinceCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string CountyCode { get; set; }
            public int? NopStateId { get; set; }
            [Column(TypeName = "varchar(100)")]
            public string CountyName { get; set; }        
            public int? DisplayOrder { get; set; }
    
            /// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
            [NotMapped]
            public override string CustomKey { get { return CountryCode + "," + StateProvinceCode + "," + CountyCode; } }
    
            // From Interface IActiveFilterEntity
            public bool Active { get; set; }
            // From Interface ISoftDeletedEntity
            public bool Deleted { get; set; }
    
            public County()
            {
                Active = true;
                Deleted = false;
            }
        }
            [Column(TypeName = "varchar(2)")]
            public string CountryCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string StateProvinceCode { get; set; }
            [Column(TypeName = "varchar(5)")]
            public string CountyCode { get; set; }
            public int? NopStateId { get; set; }

    Add developer should declare a custom key value key class as below. The CustomKey field is used on Localization Service to locate a unique key value for each record of data in database.

            /// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
            [NotMapped]
            public override string CustomKey { get { return CountryCode + "," + StateProvinceCode + "," + CountyCode; } }
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {           
                modelBuilder.HasDefaultSchema("dbo");
    
                .......
                
                modelBuilder.Entity<County>().HasKey(u => new
                {
                    u.CountryCode,
                    u.StateProvinceCode,
                    u.CountyCode
                });
    
                
    .......
            }

    Same rule as before, “baseCustomKeyEntity” inherit class, we will map to use “ICustomKeyRepository” for data operations. For more information of “IbaseCustomRepository”, we will discuss in later chapters.

    Functional Interface Class

    The framework has designed several interface classes for functional inheritance. Each services provides different data filtering or functional implementation in Repository Classes, for example, Filtering “Active” record, using Localization Service, etc. To declare a data table to inherit the interface, see below:

        [Table("CountyDef")]
        public class County : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity ,ILocalizedEntity
        {
             ........
        }

    (A) IActiveFilterEntity

    This filter is used to implement a Boolean field “Active” to control the data display or hidden on frontend or backend. Normally, with Active = true, the record will be included and shows to users. And it will be filter out if Active = false. To attach this interface, developer is required to add the implementation as follows to the table class.

    /// <summary>Implementation of IActiveFilterEntity </summary>
    public bool Active { get; set; }
            

    When you use the member function of IRepository, you are always allow to add the “actionOnly:” options to confirm the filter of the records.

    public async Task RecoverPageAsync(string PageCode)
    {
        // Rewritted by Sammy Cheng, 2022-05-08
        var rec = await _sitePageRepository.GetByExpressionFirstOrDefaultAsync(o => o.PageCode == PageCode, includeDeleted: true, activeOnly: false);
        if (rec == null)
        {    
            throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound).ToString());
        }
                
        try
        {
            await _sitePageRepository.UnDeleteAsync(rec);
        }
        catch (Exception E)
        {
            throw new AppException(_context.errorList.Response(ErrorCode.DataUpdateError, E.Message).ToString());
        }
    }

    In the “IRepository”, “IGuidRepository”, “ICustomKeyRepository”, all function for data retrieving support “activeOnly:” parameters. A function of SetActiveAsync(bool) provided for standard routine for setting Active or Inactive for the records.

    (B) ISoftDeletedEntity

    This filter implement soft-deleted logic by setting a record “Deleted” to indicate the record is deleted or not, so developer can easy to “undelete” the record through repository classes. To interface with this interface, developer should include the current implementation:

    /// <summary> Implementation of Interface ISoftDeletedEntity </summary>
    public bool Deleted { get; set; } = false;

    In the Repository classes, all function for data retrieving support “includeDeleted:” parameters. They provides also “DeleteAsync()” and “UnDeleteAsync()” function to handle the soft delete operations. But if the class don’t inherit “ISoftDeletedEntity”, the record will be deleted directly with Delete(Async). “DeletePermentantly:” parameter is also provided in “DeleteAsync()” so that developer can confirm to delete the record instead of setting “Deleted” to true;

    if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
        query = query.Where(g => g.EntityGuid == command.EntityGuid);
        if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
            query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);   
        if (String.IsNullOrEmpty(command.SalesCode))
            query = query.Where(s => s.SalesCode == command.SalesCode);
        if (string.IsNullOrEmpty(command.ClientTypeCode))
            query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
        if (string.IsNullOrEmpty(command.ClientName))
            query = query.Where(c => c.ClientName.Contains(command.ClientName));
        if (string.IsNullOrEmpty(command.CountryCode))
            query = query.Where(c => c.CountryCode == command.CountryCode);
        if (string.IsNullOrEmpty(command.StateProvinceCode))
            query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
        if (string.IsNullOrEmpty(command.CountyCode))
            query = query.Where(c => c.CountyCode == command.CountyCode);
        if (string.IsNullOrEmpty(command.City))
            query = query.Where(c => c.City.Contains(command.City));
        if (string.IsNullOrEmpty(command.Address))
            query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
        if (string.IsNullOrEmpty(command.Email))
            query = query.Where(e => e.Email.Contains(command.Email));
        if (string.IsNullOrEmpty(command.Phone))
            query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                    || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) ||                  
                    p.Fax2.Contains(command.Phone));
        if (command.Status.HasValue)
            query = query.Where(s => s.Status == command.Status);
            switch (command.OrderBy)
            {
                  case 1:
                       query = query.OrderBy(o => o.ClientCode);
                       break;
                  case 2:
                       query = query.OrderBy(o => o.ClientName);
                       break;
                  default:                    
                       query = query.OrderBy(o => o.CreateTimeUtc);
                       break;
           }
    
    
           return query;
    }, pageIndex: command.PageIndex, pageSize: command.PageSize, activeOnly: (bool)command.ActiveOnly, includeDeleted: (bool)command.IncludeDeleted );
    

    (C) IStatusControlEntity

    This Filter is using a “Status” to control the active status or delete status for the data records. The most often like “User” Class, we always defined that the user in different status like this

    -1 - Deleted
    0  - New User
    1  - Activated User
    2  - Certified User

    For this filter, it looks like a merging of “IActiveFilterEntity” and “ISoftDeletedEntity” and with more extra status defined by developers. The “IRepository” classes will perform in the same way with “IActiveFilterEntity” and “ISoftDeletedEntity” to filter with “activeOnly:” and “includedDeleted”. To implement this filter, please add the following code to the data class:

    /// <summary> Implementation of Interface IStatusDeletedEntity </summary>
    public short Status { get; set; } = 1;

    (D) IUpdateTrackingEntity

    For this filter, the framework add some fields to record the man and time of creation, update, import. This is a compatible way to let system to handle with cross platform data synchronization or import/export operations. The “IRepository” classes will automatically update the fields in real time.

    /// <summary> Implementation of Interface IUpdateTrackingEntity </summary>
    [Column(TypeName = "datetime")]
    public DateTime? CreateTimeUtc { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime? UpdateTimeUtc { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime? ImportTimeUtc { get; set; }
    [Column(TypeName = "varchar(50)")]
    public string UpdateMan { get; set; }
    [Column(TypeName = "varchar(50)")]
    public string UpdateZone { get; set; }

    You are advised to add this filter to table that if the data table may have chance to export or synchronize data with other system. But there is not necessary to add this filter to all tables. For example, if you store a “ORDER” in 4 tables :

    • OrderMaster
    • OrderDetails
    • OrderPacking
    • OrderCostItems

    If the main table is “OrderMaster” and the others table is sub-table of “OrderMaster”, then you just at this filter to “OrderMaster” is enough. It is because all 4 table records with the same “OrderCode” (Key) is treated as single set of table when processing a transaction.

    (E) ILocalizedEntity

    For this Filter, it is used to indicate the data records contains multi-language fields and required to be handled by Localization Service. If you don’t Inherit this class, you cannot work with Localization Service to support multi-locale. There are not required to add any implementation fields and will discuss how to use Localization Service in later chapter.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Definition of Domain (Entities) of Database Tables (Ch. 2 )

    Next : Updating DataContent, Creation of Initial Data and Generate Database Migration Script (Ch. 4)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Definition of Domain (Entities) of Database Tables (Chapter 2 )

    0

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    By using Microsoft Entity Framework, it is advised that developers design the database can support multiple database providers, like Microsoft SQL, MySQL, PostgreSQL, MariaDB or ANY common database engines. The framework has already had an implementation of “Data Provider” layer so that we can program our project with Database Engine independently.

    Defining a data table

    Recommended that all data tables (Entities) shall place inside the folder “/domain/{modules}”. To declare a table, here is an example:

    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace BlueSky.Core.Domain.Common
    {
        // Rewrittedn by Sammy Cheng, 2022-05-08
        /// <summary>
        /// Represents a record of Country
        /// </summary>
        [Table("CountryDef")]
        public class Country : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity, ILocalizedEntity
        {
            /// <summary> Country Code </summary>
            [Key]
            [Column(TypeName = "varchar(2)")]
            public string CountryCode { get; set; }
            /// <summary> NopCommerce Mapping Id</summary>
            public int? NopCountryId { get; set; }
            /// <summary> Country Name </summary>
            [Column(TypeName = "nvarchar(100)")]
            public string CountryName { get; set; }
            /// <summary> Is Allow billing </summary>
            public bool? AllowBilling { get; set; }
            /// <summary> Is Allow Shipping </summary>
            public bool? AllowShipping { get; set; }
            /// <summary> ISO Code (3 char) </summary>
            [Column(TypeName = "varchar(3)")]
            public string IsoCode3 { get; set; }
            /// <summary> Num ISO Code </summary>
            public int? NumIsoCode { get; set; }
            /// <summary> Is VAT </summary>
            public bool? IsVAT { get; set; }
            /// <summary> Display Order </summary>
            public int? DisplayOrder { get; set; }
    
            /// <summary> Implement for IActiveFilterEntity </summary>
            public bool Active { get; set; } = true;
            /// <summary> Implement for ISoftDeletedEntity </summary>
            public bool Deleted { get; set; } = false;
    
            /// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
            [NotMapped]
            public override string CustomKey { get { return CountryCode; } }
    
    
            [ForeignKey("CountryCode")]
            public ICollection<Address> Addresses { get; set; }
    
            [ForeignKey("CountryCode")]
            public ICollection<StateProvince> StateProvinces { get; set; }        
        }
    }

    Here is an example to create a class of a data table. Remember to include these library namespace:

    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    For these classes, which provide an easy declaration of the data fields types, key and more attributes of all definitions. For most of the developers behavior, they will use an “Identity (Auto Increment)” field “Id” as a primary key. This may be a simply and convenient way to handle the data rows and voiding key violation, but this is not good for big system and not considerable to manage for cross platform data synchronization.

    Think about “Country” table below. If we use “Id” as primary key, we have data rows in system A like these:

    Id         Name       DisplayOrder   .....
    ------------------------------------------------------
    1           China            0
    2           Hong Kong        1
    3           Japan            2
    4           Korea            3

    But if you have another “Country” table with these system B:

    -----------------------------------------------------
    1           China            0
    2           Hong Kong        1
    3           USA              2
    4           Germany          3

    So, if we want to let both system to be synchronized, or if you are required to post some data from system A to system B. It is terrible to handle the different Id of each value. It is unclear to define the data and increases the issue of data integrity, and it is dangerous to cause system bugs and hard to maintain the data for big database. Therefore, we have some understandings on how to be well-selecting the [Key] fields and type for better data structure.

    From my suggestion, I would define the key fields in some rules:

    (1) Using character code (Non-Unicode recommended, varchar) for any type code fields

    [Key]
    [Column(TypeName = "varchar(2)")]
    public string CountryCode { get; set; }

    For these table, I use a two character fields as “CountryCode” and use as primary key, which can be easy to use for foreign keys and manage the data with import and export, export to CSV/Excel and data troubleshooting in any case. The only consideration is when we insert a new record, we may have a concern whether any old record code with the same key code and cause the “Key violent” exception. On the other words, this can avoid duplicate records by mishandling.

    However, if we cannot use single key code as primary key, we may use two code fields or three code fields as primary key. Here is an example of “StateProvince” and “County” tables which define 2 and 3 key fields.

    // Key fields of StateProvince
    [Column(TypeName = "varchar(2)")]
    public string CountryCode { get; set; }        
    [Column(TypeName = "varchar(5)")]
    public string StateProvinceCode { get; set; }
    // 3 key fields for County
    [Column(TypeName = "varchar(2)")]
    public string CountryCode { get; set; }
    [Column(TypeName = "varchar(5)")]
    public string StateProvinceCode { get; set; }
    [Column(TypeName = "varchar(5)")]
    public string CountyCode { get; set; }

    Why we do like this, it is more applicable for managing a large set of data in most of the cases.

    So, you can see, this is advantageous to manage in any cases and it won’t cause any problems during the process of data exchange between different systems.

    For the size of the fields, it is not restricted but remember “enough is enough”. What that means? Just consider if the size use as minimal size as you need. Please be careful if you use a big field, it might increase the situation of mishandling of the field data. Normally, varchar(5), varchar(10), varchar(20), varchar(30) is commonly used for most of the cases.

    Why we use non-Unicode field for Key? First, all code should be restricted in use of ASCII code and number code, best in capital letters for all the times. Avoiding to use other locales as code naming. The reason of unicode string fields is some systems calculate the string length are different between languages in unicode environment. For a chinese character, if we use non-Unicode fields, one chinese character will count as 2 characters but in unicode fields, some system count as 1 characters and some count as 2. It is a serious bug source if we do some code division calculation or any memory streaming operations.

    One more things that Entity Framework (EF) cannot define 2 or 3 fields as primary key simple using [Key] tag, we needed to add code to DataProvider class of model creating like this:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {           
         modelBuilder.HasDefaultSchema("dbo");
    
         modelBuilder.Entity<StateProvince>().HasKey(u => new
         {
              u.StateProvinceCode,
              u.CountryCode
         });
                
         modelBuilder.Entity<County>().HasKey(u => new
         {
               u.CountryCode,
               u.StateProvinceCode,
               u.CountyCode
         });
                
         modelBuilder.Entity<GlobalSubRegion>().HasKey(u => new 
         { 
               u.GlobalRegionCode, 
               u.GlobalSubRegionCode 
         });
    }

    (2) Using Guid field for Primary Key if the records do not easily to define as code

    For example, if you declare a “Client” table or “Vendor” table, user may want to create a code for each record but the identifier is so important to identify the record in unique key. Using Guid is a better choice for developers.

    [Table("ClientMaster")]
    public partial class Client : BaseGuidEntity, IUpdateTrackingEntity, IStatusDeletedEntity, ILocalizedEntity
    {
            /// <summary> Organization / Entity Guid </summary>
            public Guid EntityGuid { get; set; }
            /// <summary> Client Code (for User Customizable) </summary>
            [Column(TypeName = "varchar(30)")]
            public string ClientCode { get; set; }
            /// <summary> Master Guid </summary>
            [Column(TypeName = "varchar(30)")]
            public Guid? MasterClientGuid { get; set; }
            /// <summary> For Nop Commerce Sychronization </summary>
            public int? NopClientId { get; set; }
            ......

    When using Guid, remember to create NewGuid() when inserting the records. Guid is very good for cross platform data identifying but it cannot use as sorting because it generated randomly. So, when querying data for the table using Guid primary key field, better to add “OrderBy” to handle the data sorting.

    (3) Using String Code or Guid field as Primary Key if using in master table (data in multi-table)

    For example, if you store an order data, you must required to use several table to store a set of order data. If is recommended to use a string code field or Guid field as primary key. How to consider using string or Guid? If the system can generate a sequential code when creating the order, and the code should be unique. It is recommended that using string code because it is good for sorting with the code. But however, if the order may not be easy to generate an unique string code when creating the order, I suggest to use Guid instead.

    (4) Using identity Id fields as Primary Key if it is a sub-table or slave table and the key fields are not so important

    For example, a detail table of the order which store the items of the order. Like following example, we will get the order detail data by “Where OrderCode = ‘Order000166’ “, therefore the key is not so important for any cases. And we can also manage the sorting by adding a “DisplayOrder” fields so that user can easy to adjust the sorting of each records without relating with the Id key.

    Sometimes, when we use some Type Definition tables but the type is not so important for cross platform. We can use Identity “Id” field as primary key also.

    For more information about how to define a Table Class, please refer to Microsoft documents about Entity Framework.

    Overview : Developer Guide for BlueSky .NETCORE API Framework

    Previous : Using of SVN in Visual Studio as source control (Ch.1)

    Next : Using of framework baseClass Inheritance and functional interface (Ch.3)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.