???File Path ========== ***...\Src\Travel\MasterGrouping.cs ???Check the points ================== // ??? IMPORT:- Add the items top - Duplcation Avoid using System; using System.Data; using System.Collections.Generic; using Npgsql; using NpgsqlTypes; using TAAS.Db; using TAAS.Models; using TAAS.Models.Travel; using TAAS.Helpers; // ??? CLASS ATTRIBUTES:- The code past top of the class(Attributes) - Duplcation Avoid public GlobalFunctions insGlobalFunctions = new GlobalFunctions(); DbService insDbService = new(); public List FnGetAllMasterGroupingBaseData() { List lstMdlMasterGroupingBaseIntId = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_master_grouping()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlBaseIntId mdlBaseIntId = new MdlBaseIntId(); mdlBaseIntId.binPkId = (DBNull.Value != objRecord["pk_master_grouping_id"]) ? (long)Convert.ToUInt64(objRecord["pk_master_grouping_id"]) : mdlBaseIntId.binPkId; mdlBaseIntId.strCode = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strCode; mdlBaseIntId.strName = (DBNull.Value != objRecord["vhr_grouping_name"]) ? Convert.ToString(objRecord["vhr_grouping_name"]) : mdlBaseIntId.strName; lstMdlMasterGroupingBaseIntId.Add(mdlBaseIntId); } } return lstMdlMasterGroupingBaseIntId; } public List FnGetAllMasterGroupingWithActionUser() { List lstMdlMasterGrouping = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_master_grouping_with_action_user()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlMasterGrouping mdlMasterGrouping = new MdlMasterGrouping(); mdlMasterGrouping.binMasterGroupingId = (DBNull.Value != objRecord["pk_master_grouping_id"]) ? (long)Convert.ToInt64(objRecord["pk_master_grouping_id"]) : mdlMasterGrouping.binMasterGroupingId; mdlMasterGrouping.strGroupingName = (DBNull.Value != objRecord["vhr_grouping_name"]) ? Convert.ToString(objRecord["vhr_grouping_name"]) : mdlMasterGrouping.strGroupingName; mdlMasterGrouping.sinGroupingSysType = (DBNull.Value != objRecord["sin_grouping_sys_type"]) ? (short)Convert.ToInt16(objRecord["sin_grouping_sys_type"]) : mdlMasterGrouping.sinGroupingSysType; mdlMasterGrouping.sinGroupingGui = (DBNull.Value != objRecord["sin_grouping_gui"]) ? (short)Convert.ToInt16(objRecord["sin_grouping_gui"]) : mdlMasterGrouping.sinGroupingGui; mdlMasterGrouping.strRemarks = (DBNull.Value != objRecord["txt_remarks"]) ? Convert.ToString(objRecord["txt_remarks"]) : mdlMasterGrouping.strRemarks; mdlMasterGrouping.sinRecordStatus = (DBNull.Value != objRecord["sin_record_status"]) ? (short)Convert.ToInt16(objRecord["sin_record_status"]) : mdlMasterGrouping.sinRecordStatus; mdlMasterGrouping.sinOrder = (DBNull.Value != objRecord["sin_order"]) ? (short)Convert.ToInt16(objRecord["sin_order"]) : mdlMasterGrouping.sinOrder; mdlMasterGrouping.intSysActionId = (DBNull.Value != objRecord["int_sys_action_id"]) ? (int)Convert.ToInt32(objRecord["int_sys_action_id"]) : mdlMasterGrouping.intSysActionId; mdlMasterGrouping.binCreatedUserId = (DBNull.Value != objRecord["fk_created_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_created_user_id"]) : mdlMasterGrouping.binCreatedUserId; mdlMasterGrouping.strCreatedUser = this.insDbService.FnGetValue(objRecord["vhr_created_user"], mdlMasterGrouping.strCreatedUser); mdlMasterGrouping.dtmCreated = (DBNull.Value != objRecord["dtm_created"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_created"]) : mdlMasterGrouping.dtmCreated; mdlMasterGrouping.binLastModifiedUserId = (DBNull.Value != objRecord["fk_last_modified_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_last_modified_user_id"]) : mdlMasterGrouping.binLastModifiedUserId; mdlMasterGrouping.strLastModifiedUser = this.insDbService.FnGetValue(objRecord["vhr_last_modified_user"], mdlMasterGrouping.strLastModifiedUser); mdlMasterGrouping.dtmLastModified = (DBNull.Value != objRecord["dtm_last_modified"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_last_modified"]) : mdlMasterGrouping.dtmLastModified; mdlMasterGrouping.binDeletedUserId = (DBNull.Value != objRecord["fk_deleted_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_deleted_user_id"]) : mdlMasterGrouping.binDeletedUserId; mdlMasterGrouping.strDeletedUser = this.insDbService.FnGetValue(objRecord["vhr_deleted_user"], mdlMasterGrouping.strDeletedUser); mdlMasterGrouping.dtmDeleted = (DBNull.Value != objRecord["dtm_deleted"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_deleted"]) : mdlMasterGrouping.dtmDeleted; lstMdlMasterGrouping.Add(mdlMasterGrouping); } } return lstMdlMasterGrouping; } public MdlResMasterGrouping FnInsertOrUpdateMasterGrouping(MdlReqMasterGrouping mdlReqMasterGrouping) { // *** Input Validation INSERT and UPDATE Case[not duplicate model validation] // ??? Duplication Checking in DB FUCTION(Insert and Update) - CODE, NAME [, other unique fields] Already exists // ??? Count Exced or other checking License related in DB FUCTION(Insert) // ??? UPDATE Check already update another users in DB FUCTION(Update and Delete) // Set Create or modified User in DB FUCTION(Insert, Update and Delete) if (mdlReqMasterGrouping.mdlMasterGrouping.binMasterGroupingId == 0) { mdlReqMasterGrouping.mdlMasterGrouping.intSysActionId = 0; mdlReqMasterGrouping.mdlMasterGrouping.binCreatedUserId = mdlReqMasterGrouping.linLoginUserId; mdlReqMasterGrouping.mdlMasterGrouping.strCreatedUser = mdlReqMasterGrouping.strLoginUserName; mdlReqMasterGrouping.mdlMasterGrouping.dtmCreated = mdlReqMasterGrouping.dtmCurrentDateTime; } else { mdlReqMasterGrouping.mdlMasterGrouping.binLastModifiedUserId = mdlReqMasterGrouping.linLoginUserId; mdlReqMasterGrouping.mdlMasterGrouping.strLastModifiedUser = mdlReqMasterGrouping.strLoginUserName; mdlReqMasterGrouping.mdlMasterGrouping.dtmLastModified = mdlReqMasterGrouping.dtmCurrentDateTime; } // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("mdlMasterGrouping", NpgsqlDbType.Jsonb) { Value = mdlReqMasterGrouping.mdlMasterGrouping, DataTypeName = "jsnMasterGrouping"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_insert_or_update_masterGrouping(:mdlMasterGrouping, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#002", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#003", mdlDbResponse.strSqlSysMessage))); } } // Saved MdlResMasterGrouping mdlResMasterGrouping = new MdlResMasterGrouping(); mdlResMasterGrouping.mdlMasterGrouping = mdlMasterGroupingReq.mdlMasterGrouping; if (mdlReqMasterGrouping.mdlMasterGrouping.binMasterGroupingId == 0) { mdlResMasterGrouping.mdlMasterGrouping.binMasterGroupingId = mdlDbResponse.binSqlPk ?? 0; mdlResMasterGrouping.mdlMasterGrouping.dtmCreated = mdlDbResponse.dtmCurrent; } else { mdlResMasterGrouping.mdlMasterGrouping.intSysActionId += 1; mdlResMasterGrouping.mdlMasterGrouping.dtmLastModified = mdlDbResponse.dtmCurrent; } mdlResMasterGrouping.sinStatus = 1; mdlResMasterGrouping.strStatusCode = "SUCCESS"; mdlResMasterGrouping.strMessage = "MASTERGROUPING has been Saved."; return mdlResMasterGrouping; } public MdlResBaseIntId FnDeleteMasterGrouping(MdlReqBaseIntId mdlReqMasterGroupingBaseIntId) { // *** Input Validation DELETE Case[not duplicate model validation] // ??? FOREIGN KEY REFERENCES Ching in DB FUCTION(Delete) // ??? UPDATE Check already update another users in DB FUCTION(Update and Delete) // ??? Set Create or modified User in DB FUCTION(Insert, Update and Delete) // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("binMasterGroupingId", NpgsqlDbType.Bigint) { Value = mdlReqMasterGroupingBaseIntId.mdlBaseIntId.binPkId, DataTypeName = "binMasterGroupingId"}, new NpgsqlParameter("intSysActionId", NpgsqlDbType.Integer) { Value = mdlReqMasterGroupingBaseIntId.mdlBaseIntId.binCustomValue, DataTypeName = "intSysActionId"}, new NpgsqlParameter("binDeletedUserId", NpgsqlDbType.Bigint) { Value = mdlReqMasterGroupingBaseIntId.linLoginUserId, DataTypeName = "binDeletedUserId"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_delete_masterGrouping(:binMasterGroupingId, :intSysActionId, :binDeletedUserId, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#005", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "MASTERGROUPING.CS#006", mdlDbResponse.strSqlSysMessage))); } } // Deleted MdlResBaseIntId mdlResMasterGroupingIntId = new MdlResBaseIntId(); mdlResMasterGroupingIntId.mdlBaseIntId = mdlReqMasterGroupingBaseIntId.mdlBaseIntId; mdlResMasterGroupingIntId.sinStatus = 1; mdlResMasterGroupingIntId.strStatusCode = "SUCCESS"; mdlResMasterGroupingIntId.strMessage = "MASTERGROUPING has been deleted."; return mdlResMasterGroupingIntId; }