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
public SQLTableUpdateRecordByWhereField(objSQLTable: SQLTable, objPropertiesWhereField: Properties, objPropertiesUpdateField: Properties): number {
let objSQLDatabase: SQLDatabase = objSQLTable.database as SQLDatabase;
let strSQLQuery: string = `UPDATE ${objSQLDatabase.name}.${objSQLTable.name} SET `
let arrKeyUpdate: string[] = objPropertiesUpdateField.keys;
if (arrKeyUpdate.length > 0) {
arrKeyUpdate.forEach((strKeyUpdate: string, intIndex: number): void => {
let objUpdateFieldValue: any = objPropertiesUpdateField.get(strKeyUpdate);
switch (typeof objUpdateFieldValue) {
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 += ", ";
}
});
}
let arrKey: 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};`
let intQueryResult: number = this.SQLDatabaseExecuteCustomQuery(objSQLDatabase, strSQLQuery);
return intQueryResult;
}Discover more from Cloud Build Tools
Subscribe to get the latest posts sent to your email.