???File Path ========== ***...\Src\Travel\GdsCustomFormat.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 FnGetAllGdsCustomFormatBaseData() { List lstMdlGdsCustomFormatBaseIntId = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_gds_custom_format()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlBaseIntId mdlBaseIntId = new MdlBaseIntId(); mdlBaseIntId.binPkId = (DBNull.Value != objRecord["pk_gds_custom_format_id"]) ? (long)Convert.ToUInt64(objRecord["pk_gds_custom_format_id"]) : mdlBaseIntId.binPkId; mdlBaseIntId.strCode = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strCode; mdlBaseIntId.strName = (DBNull.Value != objRecord["vhr_sys_attribute_name"]) ? Convert.ToString(objRecord["vhr_sys_attribute_name"]) : mdlBaseIntId.strName; lstMdlGdsCustomFormatBaseIntId.Add(mdlBaseIntId); } } return lstMdlGdsCustomFormatBaseIntId; } public List FnGetAllGdsCustomFormatWithActionUser() { List lstMdlGdsCustomFormat = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_gds_custom_format_with_action_user()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlGdsCustomFormat mdlGdsCustomFormat = new MdlGdsCustomFormat(); mdlGdsCustomFormat.binGdsCustomFormatId = (DBNull.Value != objRecord["pk_gds_custom_format_id"]) ? (long)Convert.ToInt64(objRecord["pk_gds_custom_format_id"]) : mdlGdsCustomFormat.binGdsCustomFormatId; mdlGdsCustomFormat.sinCategory = (DBNull.Value != objRecord["sin_category"]) ? (short)Convert.ToInt16(objRecord["sin_category"]) : mdlGdsCustomFormat.sinCategory; mdlGdsCustomFormat.sinSubCategory = (DBNull.Value != objRecord["sin_sub_category"]) ? (short)Convert.ToInt16(objRecord["sin_sub_category"]) : mdlGdsCustomFormat.sinSubCategory; mdlGdsCustomFormat.strSysAttributeName = (DBNull.Value != objRecord["vhr_sys_attribute_name"]) ? Convert.ToString(objRecord["vhr_sys_attribute_name"]) : mdlGdsCustomFormat.strSysAttributeName; mdlGdsCustomFormat.binCorporateCustomDataId = (DBNull.Value != objRecord["fk_corporate_custom_data_id"]) ? (long)Convert.ToInt64(objRecord["fk_corporate_custom_data_id"]) : mdlGdsCustomFormat.binCorporateCustomDataId; mdlGdsCustomFormat.binServiceCustomDataId = (DBNull.Value != objRecord["fk_service_custom_data_id"]) ? (long)Convert.ToInt64(objRecord["fk_service_custom_data_id"]) : mdlGdsCustomFormat.binServiceCustomDataId; mdlGdsCustomFormat.strAmadeusFormat = (DBNull.Value != objRecord["vhr_amadeus_format"]) ? Convert.ToString(objRecord["vhr_amadeus_format"]) : mdlGdsCustomFormat.strAmadeusFormat; mdlGdsCustomFormat.strGalileoFormat = (DBNull.Value != objRecord["vhr_galileo_format"]) ? Convert.ToString(objRecord["vhr_galileo_format"]) : mdlGdsCustomFormat.strGalileoFormat; mdlGdsCustomFormat.strSabreFormat = (DBNull.Value != objRecord["vhr_sabre_format"]) ? Convert.ToString(objRecord["vhr_sabre_format"]) : mdlGdsCustomFormat.strSabreFormat; mdlGdsCustomFormat.strWorldspanFormat = (DBNull.Value != objRecord["vhr_worldspan_format"]) ? Convert.ToString(objRecord["vhr_worldspan_format"]) : mdlGdsCustomFormat.strWorldspanFormat; mdlGdsCustomFormat.strFarelogixFormat = (DBNull.Value != objRecord["vhr_farelogix_format"]) ? Convert.ToString(objRecord["vhr_farelogix_format"]) : mdlGdsCustomFormat.strFarelogixFormat; mdlGdsCustomFormat.strAbacusFormat = (DBNull.Value != objRecord["vhr_abacus_format"]) ? Convert.ToString(objRecord["vhr_abacus_format"]) : mdlGdsCustomFormat.strAbacusFormat; mdlGdsCustomFormat.strTravelportFormat = (DBNull.Value != objRecord["vhr_travelport_format"]) ? Convert.ToString(objRecord["vhr_travelport_format"]) : mdlGdsCustomFormat.strTravelportFormat; mdlGdsCustomFormat.intSysActionId = (DBNull.Value != objRecord["int_sys_action_id"]) ? (int)Convert.ToInt32(objRecord["int_sys_action_id"]) : mdlGdsCustomFormat.intSysActionId; mdlGdsCustomFormat.binCreatedUserId = (DBNull.Value != objRecord["fk_created_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_created_user_id"]) : mdlGdsCustomFormat.binCreatedUserId; mdlGdsCustomFormat.strCreatedUser = this.insDbService.FnGetValue(objRecord["vhr_created_user"], mdlGdsCustomFormat.strCreatedUser); mdlGdsCustomFormat.dtmCreated = (DBNull.Value != objRecord["dtm_created"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_created"]) : mdlGdsCustomFormat.dtmCreated; mdlGdsCustomFormat.binLastModifiedUserId = (DBNull.Value != objRecord["fk_last_modified_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_last_modified_user_id"]) : mdlGdsCustomFormat.binLastModifiedUserId; mdlGdsCustomFormat.strLastModifiedUser = this.insDbService.FnGetValue(objRecord["vhr_last_modified_user"], mdlGdsCustomFormat.strLastModifiedUser); mdlGdsCustomFormat.dtmLastModified = (DBNull.Value != objRecord["dtm_last_modified"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_last_modified"]) : mdlGdsCustomFormat.dtmLastModified; mdlGdsCustomFormat.binDeletedUserId = (DBNull.Value != objRecord["fk_deleted_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_deleted_user_id"]) : mdlGdsCustomFormat.binDeletedUserId; mdlGdsCustomFormat.strDeletedUser = this.insDbService.FnGetValue(objRecord["vhr_deleted_user"], mdlGdsCustomFormat.strDeletedUser); mdlGdsCustomFormat.dtmDeleted = (DBNull.Value != objRecord["dtm_deleted"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_deleted"]) : mdlGdsCustomFormat.dtmDeleted; lstMdlGdsCustomFormat.Add(mdlGdsCustomFormat); } } return lstMdlGdsCustomFormat; } public MdlResGdsCustomFormat FnInsertOrUpdateGdsCustomFormat(MdlReqGdsCustomFormat mdlReqGdsCustomFormat) { // *** 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 (mdlReqGdsCustomFormat.mdlGdsCustomFormat.binGdsCustomFormatId == 0) { mdlReqGdsCustomFormat.mdlGdsCustomFormat.intSysActionId = 0; mdlReqGdsCustomFormat.mdlGdsCustomFormat.binCreatedUserId = mdlReqGdsCustomFormat.linLoginUserId; mdlReqGdsCustomFormat.mdlGdsCustomFormat.strCreatedUser = mdlReqGdsCustomFormat.strLoginUserName; mdlReqGdsCustomFormat.mdlGdsCustomFormat.dtmCreated = mdlReqGdsCustomFormat.dtmCurrentDateTime; } else { mdlReqGdsCustomFormat.mdlGdsCustomFormat.binLastModifiedUserId = mdlReqGdsCustomFormat.linLoginUserId; mdlReqGdsCustomFormat.mdlGdsCustomFormat.strLastModifiedUser = mdlReqGdsCustomFormat.strLoginUserName; mdlReqGdsCustomFormat.mdlGdsCustomFormat.dtmLastModified = mdlReqGdsCustomFormat.dtmCurrentDateTime; } // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("mdlGdsCustomFormat", NpgsqlDbType.Jsonb) { Value = mdlReqGdsCustomFormat.mdlGdsCustomFormat, DataTypeName = "jsnGdsCustomFormat"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_insert_or_update_gdsCustomFormat(:mdlGdsCustomFormat, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#002", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#003", mdlDbResponse.strSqlSysMessage))); } } // Saved MdlResGdsCustomFormat mdlResGdsCustomFormat = new MdlResGdsCustomFormat(); mdlResGdsCustomFormat.mdlGdsCustomFormat = mdlGdsCustomFormatReq.mdlGdsCustomFormat; if (mdlReqGdsCustomFormat.mdlGdsCustomFormat.binGdsCustomFormatId == 0) { mdlResGdsCustomFormat.mdlGdsCustomFormat.binGdsCustomFormatId = mdlDbResponse.binSqlPk ?? 0; mdlResGdsCustomFormat.mdlGdsCustomFormat.dtmCreated = mdlDbResponse.dtmCurrent; } else { mdlResGdsCustomFormat.mdlGdsCustomFormat.intSysActionId += 1; mdlResGdsCustomFormat.mdlGdsCustomFormat.dtmLastModified = mdlDbResponse.dtmCurrent; } mdlResGdsCustomFormat.sinStatus = 1; mdlResGdsCustomFormat.strStatusCode = "SUCCESS"; mdlResGdsCustomFormat.strMessage = "GDSCUSTOMFORMAT has been Saved."; return mdlResGdsCustomFormat; } public MdlResBaseIntId FnDeleteGdsCustomFormat(MdlReqBaseIntId mdlReqGdsCustomFormatBaseIntId) { // *** 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("binGdsCustomFormatId", NpgsqlDbType.Bigint) { Value = mdlReqGdsCustomFormatBaseIntId.mdlBaseIntId.binPkId, DataTypeName = "binGdsCustomFormatId"}, new NpgsqlParameter("intSysActionId", NpgsqlDbType.Integer) { Value = mdlReqGdsCustomFormatBaseIntId.mdlBaseIntId.binCustomValue, DataTypeName = "intSysActionId"}, new NpgsqlParameter("binDeletedUserId", NpgsqlDbType.Bigint) { Value = mdlReqGdsCustomFormatBaseIntId.linLoginUserId, DataTypeName = "binDeletedUserId"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_delete_gdsCustomFormat(:binGdsCustomFormatId, :intSysActionId, :binDeletedUserId, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#005", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "GDSCUSTOMFORMAT.CS#006", mdlDbResponse.strSqlSysMessage))); } } // Deleted MdlResBaseIntId mdlResGdsCustomFormatIntId = new MdlResBaseIntId(); mdlResGdsCustomFormatIntId.mdlBaseIntId = mdlReqGdsCustomFormatBaseIntId.mdlBaseIntId; mdlResGdsCustomFormatIntId.sinStatus = 1; mdlResGdsCustomFormatIntId.strStatusCode = "SUCCESS"; mdlResGdsCustomFormatIntId.strMessage = "GDSCUSTOMFORMAT has been deleted."; return mdlResGdsCustomFormatIntId; }