To use this function add it to the class library file named OrchestratorPluginSQLService.ts
GitHub Repository: https://github.com/SimonSparksUK/Orchestrator
Filename: OrchestratorPluginSQLService.ts
Description: Orchestrator Function to Update Records by Where Field(s) in an SQL Table
Private Function: SQLTableUpdateRecordByWhereField
publicSQLTableUpdateRecordByWhereField(objSQLTable:SQLTable,objPropertiesWhereField:Properties,objPropertiesUpdateField:Properties):number{
letobjSQLDatabase:SQLDatabase=objSQLTable.databaseasSQLDatabase;
letstrSQLQuery:string=`UPDATE${objSQLDatabase.name}.${objSQLTable.name} SET`
letarrKeyUpdate:string[]=objPropertiesUpdateField.keys;
if (arrKeyUpdate.length>0){
arrKeyUpdate.forEach((strKeyUpdate:string,intIndex:number):void=>{
letobjUpdateFieldValue:any=objPropertiesUpdateField.get(strKeyUpdate);
switch (typeofobjUpdateFieldValue){
case"number":{
strSQLQuery=`${strSQLQuery}${strKeyUpdate}=${objUpdateFieldValue}`;
break;
}
case"boolean":{
strSQLQuery=`${strSQLQuery}${strKeyUpdate}=${objUpdateFieldValue}`;
break;
}case"object":{
strSQLQuery=`${strSQLQuery}${strKeyUpdate}='${JSON.stringify(objUpdateFieldValue)}'`;
break;
}default:{
strSQLQuery=`${strSQLQuery}${strKeyUpdate}='${objUpdateFieldValue}'`;
break;
}
}
if (intIndex<arrKey.length-1){
strSQLQuery+=",";
}
});
}
letarrKey:string[]=objPropertiesWhereField.keys;
if (arrKey.length>0){
strSQLQuery=`${strSQLQuery} WHERE`;
arrKey.forEach((strValue:string,intIndex:number):void=>{
strSQLQuery=`${strSQLQuery}${strValue}='${objPropertiesWhereField.get(strValue)}'`;
if (intIndex<arrKey.length-1){
strSQLQuery=`${strSQLQuery} AND`;
}
});
}
strSQLQuery=`${strSQLQuery};`
letintQueryResult:number=this.SQLDatabaseExecuteCustomQuery(objSQLDatabase,strSQLQuery);
returnintQueryResult;
}Discover more from Cloud Build Tools
Subscribe to get the latest posts sent to your email.