IMSCustomFieldExtentions 1.0.3

Introduction

IMSCustomFieldExtention provides Interfaces, Models and Extension Methods to implement Custom Field for any given Entity.

Database Schema

CUSTOM_FIELD

The table CUSTOM_FIELD is used as master for Custom Field Configurations.

CREATE TABLE [dbo].[CUSTOM_FIELDS](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EntityName] [varchar](50) NOT NULL,
	[FieldName] [varchar](50) NOT NULL,
	[FieldDesc] [varchar](200) NULL,
	[DataType] [varchar](20) NOT NULL,
	[IsRequired] [bit] NOT NULL,
	[Regex] [varchar](100) NULL,
	[IsList] [bit] NOT NULL,
	[ListItems] [varchar](max) NULL,
	[ListSql] [varchar](max) NULL,
	[MinLength] [smallint] NULL,
	[MaxLength] [smallint] NULL,
	[Filter1] [varchar](100) NULL,
	[Filter2] [varchar](100) NULL,
	[Filter3] [varchar](100) NULL,
 CONSTRAINT [PK_CUSTOM_FIELDS] PRIMARY KEY (ID)
) 

CUSTOM_VALUES_{ENTITY}

Tables CUSTOM_VALUES_{ENTITY} are used to store the values of custom field in the database. Create individual tables with naming convention (as in header) for each model/entity you wish to add custom fields on.

CREATE TABLE [dbo].[CUSTOM_VALUES_{ENTITY}](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EntityId] [int] NOT NULL,
	[CustomFieldId] [int] NOT NULL,
	[CustomValue] [varchar](200) NULL,
	CONSTRAINT [PK_CUSTOM_VALUES_ROOM] PRIMARY KEY ([ID]),
	CONSTRAINT [FK_CUSTOM_VALUES_ROOM_CUSTOM_FIELDS] FOREIGN KEY([CustomFieldId]) 
	REFERENCES [dbo].[CUSTOM_FIELDS] ([ID]),
	CONSTRAINT [FK_CUSTOM_VALUES_ROOM_RENTAL_ROOM] FOREIGN KEY([EntityId]) 
	REFERENCES [dbo].[{ENTITY}] ([{PRIMARY_KEY_FIELD}]) ON DELETE CASCADE
 )

Project Integration

Implement IModelWithCustomField Interface

public class Room : IModelWithCustomField
{
    public Room() { }
    public Room(CustomFieldQueryDto model)
    {
        RoomId = Convert.ToInt32(model.EntityId);
        Floor = model.Filter1;
        Building = model.Filter2;
    }

    public int RoomId { get; set; }
    public string RoomNo { get; set; }
    public decimal RoomArea { get; set; }
    public string Floor { get; set; }
    public string Building { get; set; }
    public byte Status { get; set; }

    public object EntityId { get { return RoomId; } set { RoomId = (int)value; } }
    public string EntityName { get { return "Room"; } }
    public string TableName { get { return "CUSTOM_VALUES_ROOM"; } }
    public string Filter1 { get { return Floor; } }
    public string Filter2 { get { return Building; } }
    public string Filter3 { get { return default(string); } }
    public IList<CustomValue> CustomValues { get; set; }
}

Implement IDALWithCustomField<T> Interface

public class RoomDA : IDALWithCustomField<FunctionResponse>
{
	 public FunctionResponse GetCustomFields(CustomFieldQueryDto dto)
	 {
	     try
	     {
	         IModelWithCustomField model = GetModel(dto);
	         using (SqlConnection con = new SqlConnection(conString))
	         {
	             var customFields = model.GetCustomFields(con);
	             return new FunctionResponse() { status = "ok", result = customFields };
	         }
		 }
		 catch (Exception ex)
		 {
			 return new FunctionResponse() { status = "error", result = ex.GetBaseException().Message };
		 }
     }

     public FunctionResponse GetCustomValues(CustomFieldQueryDto dto)
     {
         try
         {
             IModelWithCustomField model = GetModel(dto);
             using (SqlConnection con = new SqlConnection(conString))
             {
                 var customValues = model.GetCustomValues(con);
                 return new FunctionResponse() { status = "ok", result = customValues };
             }
         }
         catch (Exception ex)
         {
             return new FunctionResponse() { status = "error", result = ex.GetBaseException().Message };
         }
     }

     public IModelWithCustomField GetModel(CustomFieldQueryDto model)
     {
         switch (model.EntityName.ToLower())
         {
             case "room":
                 return new Room(model);
             default:
                 return Activator.CreateInstance<IModelWithCustomField>();

         }
     }
}

Add Controller Endpoint for getting Fields & Values

 public class RoomController : ControllerBase
 {
     private LoggingHelper logger;
     private RoomDA roomDA;
     public RoomController(RoomDA _roomDA, LoggingHelper _logger)
     {
         roomDA = _roomDA;
         logger = _logger;
     }
     
	 [HttpPost]
     [Route("api/GetRoomCustomFields")]
     public IActionResult GetCustomFields([FromBody] CustomFieldQueryDto model)
     {
         try
         {
             FunctionResponse res = roomDA.GetCustomFields(model);
             if (res.status != "ok")
             {
                 return BadRequest(res);
             }
             return Ok(res);
         }
         catch (Exception ex)
         {
             return BadRequest(new FunctionResponse { status = "error", result = ex.Message });
         }
     }

     [HttpPost]
     [Route("api/GetRoomCustomValues")]
     public IActionResult GetCustomValues([FromBody] CustomFieldQueryDto model)
     {
         try
         {
             FunctionResponse res = roomDA.GetCustomValues(model);
             if (res.status != "ok")
             {
                 return BadRequest(res);
             }
             return Ok(res);
         }
         catch (Exception ex)
         {
             return BadRequest(new FunctionResponse { status = "error", result = ex.Message });
         }
     }
}

Save/Update Custom Values

Call the IModelWithCustomField extension method SaveCustomValues(IDbTransaction tran) to Save & Update the custom field values. No specific delete action is required as Foreign Key reference to entity table is added with Cascade on Delete

 using (SqlConnection con = new SqlConnection(conString))
 {
     con.Open();
     using (SqlTransaction tran = con.BeginTransaction())
     {
         if (roomdto.Mode == "New")
         {
             var Q1 = @"insert into RENTAL_ROOM(RoomNo,RoomArea, Floor,Building,Status)
			             values(@RoomNo,@RoomArea,@Floor,@Building,@Status)";
             await con.ExecuteAsync(Q1, room, tran);
             room.SaveCustomValues(tran);
         }
         else 
         {
	         var Q2 = @"update RENTAL_ROOM SET RoomNo = @RoomNo, RoomArea = @RoomArea, Floor = @Floor
			         , Building = @Building, Status = @Status WHERE Roomid = @roomid";
             await con.ExecuteAsync(Q2, room, tran);
             room.SaveCustomValues(tran);
         }
         tran.Commit();
         return new FunctionResponse { status = "ok", message = "Data saved successfully" };
    }