???File Path ========== ***...\Src\Corporate\CorporateCustomDataConf.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.Corporate; 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 FnGetAllCorporateCustomDataConfBaseData() { List lstMdlCorporateCustomDataConfBaseIntId = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_corporate_custom_data_conf()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlBaseIntId mdlBaseIntId = new MdlBaseIntId(); mdlBaseIntId.binPkId = (DBNull.Value != objRecord["pk_corporate_custom_data_conf_id"]) ? (long)Convert.ToUInt64(objRecord["pk_corporate_custom_data_conf_id"]) : mdlBaseIntId.binPkId; mdlBaseIntId.strCode = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strCode; mdlBaseIntId.strName = (DBNull.Value != objRecord[""]) ? Convert.ToString(objRecord[""]) : mdlBaseIntId.strName; lstMdlCorporateCustomDataConfBaseIntId.Add(mdlBaseIntId); } } return lstMdlCorporateCustomDataConfBaseIntId; } public List FnGetAllCorporateCustomDataConfWithActionUser() { List lstMdlCorporateCustomDataConf = new List(); // Get Data from DB DataSet objDataSet = this.insDbService.FnExcecuteSqlCommand("SELECT * FROM fn_get_all_corporate_custom_data_conf_with_action_user()"); if (objDataSet != null) { foreach (DataRow objRecord in objDataSet.Tables[0].Rows) { MdlCorporateCustomDataConf mdlCorporateCustomDataConf = new MdlCorporateCustomDataConf(); mdlCorporateCustomDataConf.binCorporateCustomDataConfId = (DBNull.Value != objRecord["pk_corporate_custom_data_conf_id"]) ? (long)Convert.ToInt64(objRecord["pk_corporate_custom_data_conf_id"]) : mdlCorporateCustomDataConf.binCorporateCustomDataConfId; mdlCorporateCustomDataConf.binCustomerId = (DBNull.Value != objRecord["fk_customer_id"]) ? (long)Convert.ToInt64(objRecord["fk_customer_id"]) : mdlCorporateCustomDataConf.binCustomerId; mdlCorporateCustomDataConf.binCorporateCustomDataId = (DBNull.Value != objRecord["fk_corporate_custom_data_id"]) ? (long)Convert.ToInt64(objRecord["fk_corporate_custom_data_id"]) : mdlCorporateCustomDataConf.binCorporateCustomDataId; mdlCorporateCustomDataConf.sinMandatory = (DBNull.Value != objRecord["sin_mandatory"]) ? (short)Convert.ToInt16(objRecord["sin_mandatory"]) : mdlCorporateCustomDataConf.sinMandatory; mdlCorporateCustomDataConf.sinOptional = (DBNull.Value != objRecord["sin_optional"]) ? (short)Convert.ToInt16(objRecord["sin_optional"]) : mdlCorporateCustomDataConf.sinOptional; mdlCorporateCustomDataConf.strDefault = (DBNull.Value != objRecord["vhr_default"]) ? Convert.ToString(objRecord["vhr_default"]) : mdlCorporateCustomDataConf.strDefault; mdlCorporateCustomDataConf.sinOrder = (DBNull.Value != objRecord["sin_order"]) ? (short)Convert.ToInt16(objRecord["sin_order"]) : mdlCorporateCustomDataConf.sinOrder; mdlCorporateCustomDataConf.intSysActionId = (DBNull.Value != objRecord["int_sys_action_id"]) ? (int)Convert.ToInt32(objRecord["int_sys_action_id"]) : mdlCorporateCustomDataConf.intSysActionId; mdlCorporateCustomDataConf.binCreatedUserId = (DBNull.Value != objRecord["fk_created_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_created_user_id"]) : mdlCorporateCustomDataConf.binCreatedUserId; mdlCorporateCustomDataConf.strCreatedUser = this.insDbService.FnGetValue(objRecord["vhr_created_user"], mdlCorporateCustomDataConf.strCreatedUser); mdlCorporateCustomDataConf.dtmCreated = (DBNull.Value != objRecord["dtm_created"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_created"]) : mdlCorporateCustomDataConf.dtmCreated; mdlCorporateCustomDataConf.binLastModifiedUserId = (DBNull.Value != objRecord["fk_last_modified_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_last_modified_user_id"]) : mdlCorporateCustomDataConf.binLastModifiedUserId; mdlCorporateCustomDataConf.strLastModifiedUser = this.insDbService.FnGetValue(objRecord["vhr_last_modified_user"], mdlCorporateCustomDataConf.strLastModifiedUser); mdlCorporateCustomDataConf.dtmLastModified = (DBNull.Value != objRecord["dtm_last_modified"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_last_modified"]) : mdlCorporateCustomDataConf.dtmLastModified; mdlCorporateCustomDataConf.binDeletedUserId = (DBNull.Value != objRecord["fk_deleted_user_id"]) ? (long)Convert.ToInt64(objRecord["fk_deleted_user_id"]) : mdlCorporateCustomDataConf.binDeletedUserId; mdlCorporateCustomDataConf.strDeletedUser = this.insDbService.FnGetValue(objRecord["vhr_deleted_user"], mdlCorporateCustomDataConf.strDeletedUser); mdlCorporateCustomDataConf.dtmDeleted = (DBNull.Value != objRecord["dtm_deleted"]) ? (DateTime)Convert.ToDateTime(objRecord["dtm_deleted"]) : mdlCorporateCustomDataConf.dtmDeleted; lstMdlCorporateCustomDataConf.Add(mdlCorporateCustomDataConf); } } return lstMdlCorporateCustomDataConf; } public MdlResCorporateCustomDataConf FnInsertOrUpdateCorporateCustomDataConf(MdlReqCorporateCustomDataConf mdlReqCorporateCustomDataConf) { // *** 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 (mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.binCorporateCustomDataConfId == 0) { mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.intSysActionId = 0; mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.binCreatedUserId = mdlReqCorporateCustomDataConf.linLoginUserId; mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.strCreatedUser = mdlReqCorporateCustomDataConf.strLoginUserName; mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.dtmCreated = mdlReqCorporateCustomDataConf.dtmCurrentDateTime; } else { mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.binLastModifiedUserId = mdlReqCorporateCustomDataConf.linLoginUserId; mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.strLastModifiedUser = mdlReqCorporateCustomDataConf.strLoginUserName; mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.dtmLastModified = mdlReqCorporateCustomDataConf.dtmCurrentDateTime; } // DB Actions NpgsqlParameter[] lstSqlParameter = { new NpgsqlParameter("mdlCorporateCustomDataConf", NpgsqlDbType.Jsonb) { Value = mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf, DataTypeName = "jsnCorporateCustomDataConf"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_insert_or_update_corporateCustomDataConf(:mdlCorporateCustomDataConf, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#002", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#003", mdlDbResponse.strSqlSysMessage))); } } // Saved MdlResCorporateCustomDataConf mdlResCorporateCustomDataConf = new MdlResCorporateCustomDataConf(); mdlResCorporateCustomDataConf.mdlCorporateCustomDataConf = mdlCorporateCustomDataConfReq.mdlCorporateCustomDataConf; if (mdlReqCorporateCustomDataConf.mdlCorporateCustomDataConf.binCorporateCustomDataConfId == 0) { mdlResCorporateCustomDataConf.mdlCorporateCustomDataConf.binCorporateCustomDataConfId = mdlDbResponse.binSqlPk ?? 0; mdlResCorporateCustomDataConf.mdlCorporateCustomDataConf.dtmCreated = mdlDbResponse.dtmCurrent; } else { mdlResCorporateCustomDataConf.mdlCorporateCustomDataConf.intSysActionId += 1; mdlResCorporateCustomDataConf.mdlCorporateCustomDataConf.dtmLastModified = mdlDbResponse.dtmCurrent; } mdlResCorporateCustomDataConf.sinStatus = 1; mdlResCorporateCustomDataConf.strStatusCode = "SUCCESS"; mdlResCorporateCustomDataConf.strMessage = "CORPORATECUSTOMDATACONF has been Saved."; return mdlResCorporateCustomDataConf; } public MdlResBaseIntId FnDeleteCorporateCustomDataConf(MdlReqBaseIntId mdlReqCorporateCustomDataConfBaseIntId) { // *** 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("binCorporateCustomDataConfId", NpgsqlDbType.Bigint) { Value = mdlReqCorporateCustomDataConfBaseIntId.mdlBaseIntId.binPkId, DataTypeName = "binCorporateCustomDataConfId"}, new NpgsqlParameter("intSysActionId", NpgsqlDbType.Integer) { Value = mdlReqCorporateCustomDataConfBaseIntId.mdlBaseIntId.binCustomValue, DataTypeName = "intSysActionId"}, new NpgsqlParameter("binDeletedUserId", NpgsqlDbType.Bigint) { Value = mdlReqCorporateCustomDataConfBaseIntId.linLoginUserId, DataTypeName = "binDeletedUserId"}, new NpgsqlParameter("jsnSqlRes", NpgsqlDbType.Json) { DataTypeName = "jsnSqlRes", Direction = ParameterDirection.Output } }; MdlDbResponse mdlDbResponse = this.insDbService.FnExcecuteOutSqlCommand("CALL sp_delete_corporateCustomDataConf(:binCorporateCustomDataConfId, :intSysActionId, :binDeletedUserId, NULL)", lstSqlParameter); // DB Error if (mdlDbResponse.sinSqlCustomStatus != 1) { if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#004", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_VALIDATION_ERROR") { throw (new TaasDatabaseValidationException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#005", mdlDbResponse.strSqlSysMessage))); } else if (mdlDbResponse.strSqlCustomErrorType == "DATABASE_SYSTEM_ERROR") { throw (new TaasDatabaseException(this.insGlobalFunctions.FnGetExceptionResponseString(mdlDbResponse.strSqlCustomMessage, "CORPORATECUSTOMDATACONF.CS#006", mdlDbResponse.strSqlSysMessage))); } } // Deleted MdlResBaseIntId mdlResCorporateCustomDataConfIntId = new MdlResBaseIntId(); mdlResCorporateCustomDataConfIntId.mdlBaseIntId = mdlReqCorporateCustomDataConfBaseIntId.mdlBaseIntId; mdlResCorporateCustomDataConfIntId.sinStatus = 1; mdlResCorporateCustomDataConfIntId.strStatusCode = "SUCCESS"; mdlResCorporateCustomDataConfIntId.strMessage = "CORPORATECUSTOMDATACONF has been deleted."; return mdlResCorporateCustomDataConfIntId; }